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

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Branimir Petrovic Sent: Friday, September 02, 2005 10:32 AM To: 'oracle-l_at_freelists.org'
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:

1. 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)?
2. Apply "latest and greatest" patch set, and hope that 9.2.0.7 will somehow fix it?
3. 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