Home » RDBMS Server » Performance Tuning » Statspack Report - Suggestion (9.2.0.7)
| Statspack Report - Suggestion [message #345935] |
Fri, 05 September 2008 06:20  |
nazbrian
Messages: 36
Registered: July 2008
|
Member |
|
|
Hi,
Following is my Statspack Report.
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
PROD 1936202 PROD 1 9.2.0.7.0 NO dcdwdb01
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 6 02-Sep-08 21:00:03 78 39.2
End Snap: 7 02-Sep-08 22:00:05 78 39.6
Elapsed: 60.03 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 608M Std Block Size: 8K
Shared Pool Size: 288M Log Buffer: 10,240K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 6,840.20 5,278.15
Logical reads: 33,552.59 25,890.41
Block changes: 43.40 33.49
Physical reads: 32,890.20 25,379.28
Physical writes: 0.46 0.36
User calls: 18.25 14.08
Parses: 4.44 3.43
Hard parses: 0.00 0.00
Sorts: 3.56 2.75
Logons: 0.00 0.00
Executes: 15.66 12.08
Transactions: 1.30
% Blocks changed per Read: 0.13 Recursive Call %: 41.99
Rollback per transaction %: 0.02 Rows per Sort: 1.64
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 1.97 In-memory Sort %: 100.00
Library Hit %: 100.00 Soft Parse %: 100.00
Execute to Parse %: 71.62 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 100.00 % Non-Parse CPU: 99.98
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 33.97 33.97
% SQL with executions>1: 78.53 78.53
% Memory for SQL w/exec>1: 78.71 78.71
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 3,343 69.93
db file scattered read 14,914,700 1,406 29.41
log file parallel write 7,380 15 .31
log file sync 4,644 10 .22
db file sequential read 13,402 4 .09
-------------------------------------------------------------
Wait Events for DB: PROD Instance: PROD Snaps: 6 -7
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read 14,914,700 0 1,406 0 3,195.1
log file parallel write 7,380 0 15 2 1.6
log file sync 4,644 0 10 2 1.0
db file sequential read 13,402 0 4 0 2.9
control file parallel write 1,197 0 1 1 0.3
SQL*Net break/reset to clien 5,352 0 1 0 1.1
process startup 3 0 0 35 0.0
latch free 574 0 0 0 0.1
control file sequential read 536 0 0 0 0.1
LGWR wait for redo copy 802 0 0 0 0.2
buffer busy waits 62 0 0 0 0.0
SQL*Net more data to client 92 0 0 0 0.0
direct path read (lob) 6 0 0 0 0.0
SQL*Net message from client 72,241 0 151,174 2093 15.5
queue messages 1,363 1,363 13,041 9568 0.3
pipe get 2,635 2,635 10,545 4002 0.6
wakeup time manager 118 118 3,365 28517 0.0
jobq slave wait 60 60 176 2939 0.0
SQL*Net more data from clien 3 0 0 18 0.0
SQL*Net message to client 72,242 0 0 0 15.5
-------------------------------------------------------------
Background Wait Events for DB: PROD Instance: PROD Snaps: 6 -7
-> ordered by wait time desc, waits desc (idle events last)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 7,380 0 15 2 1.6
db file sequential read 1,492 0 3 2 0.3
db file scattered read 804 0 2 3 0.2
control file parallel write 1,197 0 1 1 0.3
control file sequential read 476 0 0 0 0.1
LGWR wait for redo copy 802 0 0 0 0.2
latch free 1 0 0 0 0.0
rdbms ipc message 10,853 3,563 14,047 1294 2.3
pmon timer 1,206 1,206 3,516 2916 0.3
smon timer 12 12 3,333 ###### 0.0
-------------------------------------------------------------
SQL ordered by Gets for DB: PROD Instance: PROD Snaps: 6 -7
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
120,172,833 1,278 94,032.0 99.4 3325.35 3255.41 3848753087
Module: JDBC Thin Client
select user_data from jtf_ih_bulk_qtbl where msgid = :1 for upda
te
....
Initially I had Buffer Cache of 167M, I have incresed SGA_MAX_SIZE to 2G(Memory 20G) and increased Buffer Cache of 608M.
While running application for I get Buffer hit around 98% for first half an hour. After that it continuously reduces.
Is there any suggestion ?
Regards,
Brian.
|
|
|
|
|
|
|
|
| Re: Statspack Report - Suggestion [message #345999 is a reply to message #345997] |
Fri, 05 September 2008 09:12  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Or doing a lot of Full table scans of vig tables,
If you look at the load profile at the start: Redo size: 6,840.20 5,278.15
Logical reads: 33,552.59 25,890.41
Block changes: 43.40 33.49
Physical reads: 32,890.20 25,379.28
Physical writes: 0.46 0.36
POst the Top SQL by Physical Reads, and also by Block Reads.
|
|
|
|
| Re: Statspack Report - Suggestion [message #347740 is a reply to message #345935] |
Sat, 13 September 2008 09:23 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi JRowbottom ,
Following SQL's by Physiscal Reads and Block Gets,
I understand that
select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update is is creating the problem,
I Analyze the table jtf_ih_bulk_qtbl regularly, Also there is index on column msgid . Its the type RAW.
In the SQL mentioned above, same record will not be accessed second time.
Pls suggest what should be done
SQL ordered by Gets for DB: PROD Instance: PROD Snaps: 6 -7
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
120,172,833 1,278 94,032.0 99.4 3325.35 3255.41 3848753087
Module: JDBC Thin Client
select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update
316,254 118 2,680.1 0.3 5.35 5.32 2614951860
select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
ues q where aft.table_objno = t.objno and aft.owner_instance = :
1 and q.table_objno = t.objno and q.usage = 0 and b
itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n
93,075 255 365.0 0.1 0.88 2493.53 3120565770
Module: JDBC Thin Client
BEGIN WF_EVENT_OJMSTEXT_QH.DEQUEUE(:1, :2, :3); END;
58,015 8,260 7.0 0.0 1.00 1.03 3371479671
select t.name, (select owner_instance from sys.aq$_queue_table_
affinities where table_objno = t.objno) from system.aq$_queue
_tables t where t.name = :1 and t.schema = :2 for update skip lo
cked
32,109 3,567 9.0 0.0 0.65 0.60 3893339647
---------------------------------------------------------
SQL ordered by Reads for DB: PROD Instance: PROD Snaps: 6 -7
-> End Disk Reads Threshold: 1000
CPU Elapsd
Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
118,470,471 1,278 92,699.9 100.0 3325.35 3255.41 3848753087
Module: JDBC Thin Client
select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update
3,732 1 3,732.0 0.0 0.42 6.07 4115672663
select u.name, o.name, t.spare1, t.pctfree$ from sys.obj$ o,
sys.user$ u, sys.tab$ t where (bitand(t.trigflag, 1048576) =
1048576) and o.obj#=t.obj# and o.owner# = u.user#
648 3 216.0 0.0 1.46 2.75 333476733
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
78 110 0.7 0.0 0.08 0.44 2043612021
Thanks in advance,
Brayan.
|
|
|
|
|
|
|
|
|
|
| Re: Statspack Report - Suggestion [message #348220 is a reply to message #345935] |
Tue, 16 September 2008 01:21 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi Ana,
Please find the explain plan.
SQL> explain plan for
2 select user_data from jtf_ih_bulk_qtbl where msgid = :1 for update ;
Explained.
SQL> @/proddb/proddb/9.2.0/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4778 | 443K| 9063 |
| 1 | FOR UPDATE | | | | |
|* 2 | TABLE ACCESS FULL | JTF_IH_BULK_QTBL | 4778 | 443K| 9063 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
2 - filter(RAWTOHEX("JTF_IH_BULK_QTBL"."MSGID")=:Z)
Note: cpu costing is off
15 rows selected.
SQL>
But, If I use
select user_data from jtf_ih_bulk_qtbl where msgid = HEXTORAW(:1) for update ; Then I'll have diferent Plan. Since this is a standard Application from Oracle I can not customize the application.
SQL> @/proddb/proddb/9.2.0/rdbms/admin/utlxpls.sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 3 |
| 1 | FOR UPDATE | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| JTF_IH_BULK_QTBL | 1 | 95 | 3 |
|* 3 | INDEX UNIQUE SCAN | SYS_C00127277 | 1 | | 2 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
3 - access("JTF_IH_BULK_QTBL"."MSGID"=HEXTORAW(:Z))
Note: cpu costing is off
16 rows selected.
Brian
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 09:47:45 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
