-
Performance analysis
I've run the sp_systime_9i.sql script that picks up information on statistics collected by statspack. I would like to know where to look to drill down to find the causes of the latch free waits on the server.
They are consuming 1/3 of the resources.
Code:
Service Seconds % of % of
Day Hour or Wait Name Spent Total Total
------ ----- -------- ----------------------------------- ------------------- ------- -------
17-MAR 07:00 Wait library cache pin 948.10 13.11 89.83
Service Parsing SQL 272.19 3.76 93.60
Wait log file sync 205.43 2.84 96.44
08:00 Wait latch free 4,611.64 33.95 33.95
Service SQL execution 4,564.23 33.60 67.54
Wait db file sequential read 1,494.64 11.00 78.55
Wait library cache pin 1,285.44 9.46 88.01
Wait log file sync 666.68 4.91 92.92
Service Parsing SQL 581.89 4.28 97.20
09:00 Service SQL execution 5,905.88 36.13 36.13
Wait latch free 5,288.69 32.36 68.49
Wait db file sequential read 1,812.97 11.09 79.58
Wait library cache pin 1,131.06 6.92 86.50
Wait log file sync 858.40 5.25 91.75
Service Parsing SQL 699.98 4.28 96.04
10:00 Service SQL execution 6,790.22 37.44 37.44
Wait latch free 6,213.64 34.26 71.70
Wait db file sequential read 1,499.27 8.27 79.97
Wait library cache pin 1,270.15 7.00 86.97
Wait log file sync 1,012.01 5.58 92.55
Service Parsing SQL 897.07 4.95 97.50
11:00 Service SQL execution 7,305.83 36.42 36.42
Wait latch free 6,662.61 33.21 69.63
Wait db file sequential read 1,902.22 9.48 79.12
Wait library cache pin 1,499.84 7.48 86.59
Service Parsing SQL 989.27 4.93 91.52
Wait log file sync 974.23 4.86 96.38
-
are you using bind variables
-
The application doesnt use bind variables and the DB is flooded with different sql statements. Thinking about changing the cursor_sharing parameter from EXACT to SIMILAR/FORCE and also setting session_cached_cursors. Just wondering whether there is anything else I can look at before I go down that route.
-
well your latch free and library cache hits will be becuase of that then plus if you are waiting on log file sync then you are probably committing too often as well
-
you cannot do nothing except change the DB is used of consider using cursor_sharing to force or similar - but be carefull as this might make some problems with the Oracle Cost based optimizer - sibce if you use cursor_sharing = force a query like:
select * from table where id = 123
it will make it:
select * from table where id = :b1
and the optimizer will not know wheter to use index or not.
-
It is a very read intensive system with lots of parses.....Would setting session_cached_cursors > Curs/Sess alongside changing cursor_sharing improve performance?
Code:
Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- -------------------
Begin Snap: 18358 14-Mar-06 00:00:06 78 122.4
End Snap: 18588 14-Mar-06 23:00:06 89 148.9
Elapsed: 1,380.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,024M Std Block Size: 8K
Shared Pool Size: 304M Log Buffer: 3,072K
Load Profile ~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 115,890.83 3,893.61
Logical reads: 150,697.82 5,063.03
Block changes: 705.33 23.70
Physical reads: 928.52 31.20
Physical writes: 26.02 0.87
User calls: 1,340.17 45.03
Parses: 460.56 15.47
Hard parses: 106.40 3.57
Sorts: 20.65 0.69
Logons: 0.03 0.00
Executes: 471.45 15.84
Transactions: 29.76
% Blocks changed per Read: 0.47 Recursive Call %: 30.59
Rollback per transaction %: 4.54 Rows per Sort: 57.87
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.39 In-memory Sort %: 100.00
Library Hit %: 88.96 Soft Parse %: 76.90
Execute to Parse %: 2.31 Latch Hit %: 99.58
Parse CPU to Parse Elapsd %: 29.37 % Non-Parse CPU: 88.77
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 68.51 55.64
% SQL with executions>1: 35.69 37.35
% Memory for SQL w/exec>1: 33.20 33.06
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 156,641 43.46
latch free 13,844,248 111,607 30.97
db file sequential read 28,582,231 34,784 9.65
library cache pin 32,490 23,542 6.53
log file sync 2,352,339 19,986 5.55
-
my.god
106 hard parses per second??????? you need to fix the app, no two ways about that
-
ha ha! I know its truly awful.
Ok what would you look at in the meatime?
-
find a new job to get away from it.
Fix the app - that is what to look at
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
|