|
-
analyze SYS or SYSTEM
I am on 10.2.0.2
1. could you please tell me if there is a benefit to ANALYZE SYS schema?
2. I have an performance issue, I have DEV and PRD, all of the parameter are the same, alsmost everything is the same between the two beside HARDWARE. my question why do I have to different execution plan when I trace the SQL?
thanks
-
1-- Why in the world you would like to to that?
2-- Something is different, most likely:
- Data
- Performance stats
- Available indexes
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
I know Oracle doesn't recommend to analyze SYS and SYSTEM on 9i but I thought I saw somewhere on the internet saying you can analyze SYS and SYSTEM on 10G now so I just throw out there.
Any way, I took the same query and turn on the trace and execute on the environment, there are significant different between the FETCH time on PROD. what could make the fecth time longer?
thanks,
-
only sys, not system - try it, you can always delete the stats after
-
thanks, could you please tell me the rule of thumb for longer FETCH time? the possible reasons that could make the FECTH time longer?
-
post the plans and statistics about the query, there is no ROT
-
Davey23UK, here is what I have in PRD
Code:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.04 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 1.52 1.49 0 81344 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1.57 1.53 0 81344 0 5
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 5
Rows Row Source Operation
------- ---------------------------------------------------
5 SORT ORDER BY (cr=81344 pr=0 pw=0 time=1492240 us)
5 FILTER (cr=81344 pr=0 pw=0 time=1403371 us)
5 NESTED LOOPS (cr=81344 pr=0 pw=0 time=1402881 us)
40419 NESTED LOOPS (cr=504 pr=0 pw=0 time=243069 us)
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=116 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=73 us)(object id 44)
40419 TABLE ACCESS FULL ARGUMENT$ (cr=502 pr=0 pw=0 time=202534 us)
5 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=80840 pr=0 pw=0 time=1207571 us)
40419 INDEX UNIQUE SCAN I_OBJ1 (cr=40421 pr=0 pw=0 time=484684 us)(object id 36)
1 FIXED TABLE FULL X$KZSPR (cr=0 pr=0 pw=0 time=450 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN I_OBJAUTH1 (cr=0 pr=0 pw=0 time=0 us)(object id 103)
0 FIXED TABLE FULL X$KZSRO (cr=0 pr=0 pw=0 time=0 us)
Last edited by davey23uk; 09-16-2007 at 07:16 AM.
-
1.5 seconds, not really a lot is it
-
Thanks Davey23UK,
I agreed it's only 1.5 sec but the user complained so much about the performance on PRD compared to DEV, below is what I have in PRD:
Code:
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 100M Std Block Size: 8K
Shared Pool Size: 152M Log Buffer: 2,048K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 24,280.23 7,651.63
Logical reads: 34,763.94 10,955.44
Block changes: 153.80 48.47
Physical reads: 3.66 1.15
Physical writes: 3.14 0.99
User calls: 75.44 23.77
Parses: 39.34 12.40
Hard parses: 4.61 1.45
Sorts: 9.91 3.12
Logons: 0.00 0.00
Executes: 62.84 19.80
Transactions: 3.17
% Blocks changed per Read: 0.44 Recursive Call %: 82.97
Rollback per transaction %: 5.51 Rows per Sort: 482.30
Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.99 In-memory Sort %: 100.00
Library Hit %: 92.14 Soft Parse %: 88.28
Execute to Parse %: 37.40 Latch Hit %: 99.75
Parse CPU to Parse Elapsd %: 99.59 % Non-Parse CPU: 71.41
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.63 91.97
% SQL with executions>1: 52.88 62.10
% Memory for SQL w/exec>1: 50.72 55.24
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 1,901 96.3
log file sync 5,930 29 5 1.5
log file parallel write 6,676 27 4 1.4
Log archive I/O 275 5 17 .2
log file sequential read 106 3 29 .2
-------------------------------------------------------------
Host CPU (CPUs: 7)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
1.05 0.82 19.63 7.17 73.21 1.52 0.08
Instance CPU
~~~~~~~~~~~~
% of total CPU for Instance: 14.46
% of busy CPU for Instance: 53.96
%DB time waiting for CPU - Resource Mgr:
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 15,976.1 15,976.1
SGA use (MB): 400.0 400.0
PGA use (MB): 135.2 131.8
% Host Mem used for SGA+PGA: 3.3 3.3
-------------------------------------------------------------
Time Model System Stats DB/Inst: PRD/PRD Snaps: 11-21
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % of DB time
----------------------------------- -------------------- ------------
DB CPU 1,859.7 98.7
sql execute elapsed time 1,305.3 69.3
parse time elapsed 667.4 35.4
hard parse elapsed time 654.9 34.7
hard parse (sharing criteria) elaps 51.0 2.7
hard parse (bind mismatch) elapsed 44.8 2.4
PL/SQL compilation elapsed time 20.7 1.1
sequence load elapsed time 3.3 .2
PL/SQL execution elapsed time 1.9 .1
connection management call elapsed 0.6 .0
repeated bind elapsed time 0.2 .0
failed parse elapsed time 0.0 .0
DB time 1,884.9
background elapsed time 50.6
background cpu time 20.1
-------------------------------------------------------------
Wait Events DB/Inst: PRD/PRD Snaps: 11-21
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg
%Time Total Wait wait Waits
Event Waits -outs Time (s) (ms) /txn
--------------------------------- ------------ ------ ---------- ------ --------
log file sync 5,930 0 29 5 1.0
log file parallel write 6,676 0 27 4 1.1
Log archive I/O 275 0 5 17 0.0
log file sequential read 106 0 3 29 0.0
db file sequential read 1,632 0 3 2 0.3
db file parallel write 1,801 0 3 2 0.3
control file parallel write 761 0 1 2 0.1
db file scattered read 763 0 0 0 0.1
log file switch completion 3 0 0 89 0.0
SQL*Net more data to client 1,673 0 0 0 0.3
log file single write 4 0 0 33 0.0
latch: shared pool 159 0 0 1 0.0
control file sequential read 1,363 0 0 0 0.2
Data file init write 25 0 0 1 0.0
rdbms ipc reply 20 0 0 1 0.0
db file single write 12 0 0 1 0.0
direct path write 13 0 0 0 0.0
enq: TX - row lock contention 1 0 0 5 0.0
LGWR wait for redo copy 61 0 0 0 0.0
latch: cache buffers chains 78 0 0 0 0.0
buffer busy waits 8 0 0 0 0.0
SQL*Net break/reset to client 2 0 0 1 0.0
SQL*Net message from client 132,807 0 28,416 214 22.5
Streams AQ: qmn slave idle wait 65 0 1,800 27685 0.0
Streams AQ: qmn coordinator idle 140 54 1,800 12854 0.0
SQL*Net message to client 132,808 0 0 0 22.5
SQL*Net more data from client 5,418 0 0 0 0.9
-------------------------------------------------------------
again, I am on 10G and I only see CPU is problem but not sure where to tackle beside the two query and I put a question on here earlier. Do you see anything that you can recommend?
btw, log file sync is another issue, log buffer is 2M now and I am thinking to increase to 10M, your thoughts?
thanks
Last edited by matthew00; 09-16-2007 at 05:53 PM.
-
not possible to notice the difference between 0 secs and 1.5 secs
anyway, need both sets of data to compare
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote