RE: To patch 'n pray or to keep looking for the root cause: that is the question

["Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>]

I'd recommend checking the wait events for the export session in 
v$session_event to see where most of the time is spent waiting, and then go 
from there . . .



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Branimir Petrovic
Sent: Friday, September 02, 2005 10:32 AM
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

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

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

Other related posts:

To patch 'n pray or to keep looking for the root cause: that is the question

RE: To patch 'n pray or to keep looking for the root cause: that is the question

RE: To patch 'n pray or to keep looking for the root cause: that is the question

RE: To patch 'n pray or to keep looking for the root cause: that is the question

Re: To patch 'n pray or to keep looking for the root cause: that is the question