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.
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
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)
% 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)
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?
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??
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
Reply With Quote
Originally Posted by DaPi
Bookmarks