FreeLists / oracle-l / RE: To patch 'n pray or to keep looking for the root cause: that is the question

["Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>]

Branimir,

Have you tried direct exports and logged any differences?  Obviously
this problem is either server based or Oracle based.

Direct exports would skip the SGA - thus eliminating any busy buffer
problems.

And also look for other events happening on the server.  Are your disk
mounted locally, or on a SAN?  This could be another cause for delay -
network problems interfering with SAN writes.

Tom

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Branimir Petrovic
Sent: Friday, September 02, 2005 1:32 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: To patch 'n pray or to keep looking for the root cause: that is
the question

Whether 'tis nobler in the mind to suffer... is yet another nagging
question for another occasion, this time though I am facing an issue
(non a major one) with Oracle export utility and am pondering options. 

Basic facts:

- On Windows platform,

- On relatively "popular" Oracle 9.2.0.1 development instances of 
miniscule proportions (few gigs only) used daily by up to few 
developers and up to few testers, 

- That are backed up - daily, method - logical backups via full 
exports, scheduled during time nobody is using said databases,


Observation:

- Given the fact no one is using database when full export happens,
given the fact that database is not changing in size, time it takes 
to do full export - wildly differs! 
(Example DB#1 - from 30 min to 180+ min!???)

- Any time database is bounced and export taken immediately afterwards,
it takes steady minimum time for export to finish.

- Any day there were lots of things happenin' with the database (high
daily activity against it) - fully export times tend to slip and may 
become unpredictably long (from much longer to much-much longer) 


What did do I about it so far:

- Not much, started to take statspack snapshot moments before starting
full export and moments after it is done, then looked at (many) 
differences between "normal" and "long a time" export times and 
scratched my head in bewilderment (not sure what to do with all them 
numbers)... Illustration:

When EXP time is NORMAL export's Elapsed Time:   28.82 (mins)

Load Profile
~~~~~~~~~~~~                      Per Second       Per Transaction
                             ---------------       --------------- 
        Logical reads:              4,339.64            341,056.18


SGA breakdown
Pool   Name                      Begin value        End value  % Diff
------ -------------------- ---------------- ---------------- -------
shared KGLS heap                  31,069,644       57,844,052   86.18
shared KQR M PO                   25,728,948       25,911,320    0.71
shared KQR S PO                    2,769,208        2,769,208    0.00
shared free memory                13,291,884       13,969,300    5.10




When EXP time is SLOW export's Elapsed Time:     72.48 (mins)

Load Profile
~~~~~~~~~~~~                      Per Second       Per Transaction
                            ---------------       --------------- 
                Logical reads:         1,715.26            339,074.86


SGA breakdown
Pool   Name                      Begin value        End value  % Diff
------ -------------------- ---------------- ---------------- -------
shared KGLS heap                     936,152       69,954,168 #######
shared KQR M PO                      277,516       24,946,396 #######
shared KQR S PO                       39,728        2,684,552 #######
shared free memory               163,899,464       13,584,080  -91.71




Options I am pondering about:

a) Over and above taking statspack snapshots before and after Oracle 
export, start tracing export sessions, run through some of available 
profilers and scratch head some more (become even more bewildered as 
new data emerges)?

b) Apply "latest and greatest" patch set, and hope that 9.2.0.7 will 
somehow fix it?

c) Tuck the issue under the proverbial carpet, behave as if it does 
not exist, (since I anyway hold no leverage over the most likely 
culprits - good Elbonian folks)?

What would you do?


Branimir 

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l