-
Statpack help
Morning everbody, I hope everyone is having a great day so far..if not we now have the weekend...
Ill to the point, im running statspack to produce a report and have a few questions that dont seem to be answered by the help files
1) if I want to run the statspack on 30 second intervals, how would i do that. I know how to shell script and can automate this with crontab but is there a way to do it within Oracle, maybe change a parameter somewhere??.
2) when you run a standard statpack report at the bottom are some values that i dont understand namely something called 'Hash Values' and some associated values like buffets gets, executions, there seems to be some sql code that is kicked out with these values..does anyone know what this is??..
3) finally if i want to produce only the top part of the report, what level would i set??, where would i set that level? (is it a table or parameter).
I thank anyone in advance for any help that they can offer.
-
30 seconds it way too frequent IMO as it can take upto that just to run and there is a bit on the database while it runs.
-
What would you recommend using??, also what doesn IMO mean?
-
IMO = in my opinion, 15 minute intervals are roughly what you should be looking for if you are tuning something.
What are you trying to get from it, there may be a better way
Define what you mean by the top part of the report?
The SQL Code you are seeing is probably the TOP SQL part of the report, statspack lists the most resource consuming sql statements in the database (ordered by buffer gets, executions) each sql statement is given a unique hash value so thats what that is
-
top part:
B Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
EDIST1 972084764 EDIST1 1 9.2.0.6.0 NO swisn013
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 1 20-Oct-05 11:24:56 14 3.1
End Snap: 3 20-Oct-05 11:27:08 14 6.7
Elapsed: 2.20 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 352M Std Block Size: 8K
Shared Pool Size: 128M Log Buffer: 1,024K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 16,403.09 721,736.00
Logical reads: 41.55 1,828.00
Block changes: 19.58 861.33
Physical reads: 0.02 0.67
Physical writes: 0.02 1.00
User calls: 0.22 9.67
Parses: 1.00 44.00
Hard parses: 0.12 5.33
Sorts: 1.14 50.00
Logons: 0.01 0.33
Executes: 1.93 85.00
Transactions: 0.02
% Blocks changed per Read: 47.12 Recursive Call %: 97.84
Rollback per transaction %: 0.00 Rows per Sort: 287.97
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.96 In-memory Sort %: 100.00
Library Hit %: 91.80 Soft Parse %: 87.88
Execute to Parse %: 48.24 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 100.00 % Non-Parse CPU: 94.26
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 94.06 94.26
% SQL with executions>1: 55.53 56.01
% Memory for SQL w/exec>1: 60.84 63.32
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 2 85.84
log file parallel write 68 0 4.34
log file sync 11 0 4.17
control file parallel write 44 0 2.52
control file sequential read 124 0 2.33
-------------------------------------------------------------
Wait Events for DB: EDIST1 Instance: EDIST1 Snaps: 1 -3
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 68 0 0 2 22.7
log file sync 11 0 0 9 3.7
control file parallel write 44 0 0 1 14.7
control file sequential read 124 0 0 0 41.3
db file sequential read 2 0 0 9 0.7
db file parallel write 2 0 0 1 0.7
SQL*Net message from client 26 0 1,979 76128 8.7
virtual circuit status 5 5 146 29297 1.7
wakeup time manager 4 4 113 28325 1.3
SQL*Net message to client 26 0 0 0 8.7
-------------------------------------------------------------
Background Wait Events for DB: EDIST1 Instance: EDIST1 Snaps: 1 -3
-> 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 68 0 0 2 22.7
control file parallel write 44 0 0 1 14.7
control file sequential read 22 0 0 1 7.3
db file parallel write 2 0 0 1 0.7
rdbms ipc message 163 134 502 3077 54.3
smon timer 1 1 293 ###### 0.3
pmon timer 44 44 129 2937 14.7
-------------------------------------------------------------
part im not sure about:
SQL ordered by Gets for DB: EDIST1 Instance: EDIST1 Snaps: 1 -3
-> 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
--------------- ------------ -------------- ------ -------- --------- ----------
260 4 65.0 4.7 0.00 0.02 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
84 12 7.0 1.5 0.00 0.00 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
52 26 2.0 0.9 0.00 0.01 1316169839
select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
ext_date) and (next_date < :2)) or ((last_date is null) and
(next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
) and (this_date is null) order by next_date, job
39 13 3.0 0.7 0.00 0.00 1254950678
select file# from file$ where ts#=:1
26 26 1.0 0.5 0.00 0.00 1693927332
select count(*) from sys.job$ where (next_date > sysdate) and (n
ext_date < (sysdate+5/86400))
19 19 1.0 0.3 0.00 0.00 4274598960
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
density, col#, spare1, spare2, avgcln from hist_head$ where obj#
=:1 and intcol#=:2
15 1 15.0 0.3 0.00 0.00 2095543314
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
9 1 9.0 0.2 0.00 0.00 1356713530
select privilege#,level from sysauth$ connect by grantee#=prior
privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
1) and privilege#>0
8 1 8.0 0.1 0.00 0.00 3743621015
UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIM
E_DP=:4 WHERE TIME_MP = :5 AND THREAD = :6 AND ROWNUM <= 1
7 1 7.0 0.1 0.00 0.00 2201872251
SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD =
:1
SQL ordered by Gets for DB: EDIST1 Instance: EDIST1 Snaps: 1 -3
-> 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
Also, how would i set it then to take a snap every 15 minutes over 3 hours within oracle?
-
use dbms_job to schedule it ever 15 minutes and you cant just get the bit you want
-
cool thanks davey your help is greatly appreciated.
-
One more thing,
My performance guy is wanting metric measurements every 30 seconds, I have been told already by mrchrispy that running statspac every 30 seconds is not useful.
Is there anything else i can run that will give me metric measurements every 30 seconds that would be useful to my performance manager??
Cheers people.
-
Who is this performance guy? If he were any good, he'd know what he wanted and how to get it.
Perhaps you can keep him quiet with the pretty pictures from the diagnostics in OEM?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Originally Posted by DaPi
Perhaps you can keep him quiet with the pretty pictures from the diagnostics in OEM?
LOL...that just what we've done while we take the time to go over statspack reports.
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
|