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?

Quote:

---

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.

lol......

funnily enough this geezer does knows what hes doing, just there is no money available for an oracle license for loadrunner which he would normally use, so its up to me to provide him with the information that he needs!!!!

I'll try the pretty picture approach...but if that doesnt work what else could i do??

need to find out what information he is after.

If he knows what he is doing, should be able to tell you

You can get more information if you trace problem session (sessions)
so you got EXACT (not aggregated) information that's going on.

he didnt want session information, wanted an overview of the database

right people

Im going to produce a statpack report every 15 minutes for him can someone direct me to a resource that will explain how to do this on an oracle DB??, I will need a new file produced at every run, not an overite of the old one, with a timestamp.

Im also going to kick out the following v$sysstat metrics every minute:

CPU used by this session
bytes received via SQL*Net from dblink
logons current
opens of replaced files
user calls
SQL*Net roundtrips to/from client
opened cursors current
db block changes
total file opens


I can do this via a shell script that will kick out a new timestamped file every minute into a directory over a three hour load test.

Does anyone have any comments on the approach i am taking??, any ideas where i can improve, am I on the right track? etc.

thanks for all your help so far.

connect as the perfstat user and run statspack.snap; every 15 minutes (use dbms_job to schedule it) then when youa re you need to run ?/rdbms/admin/spreport to generate the actual report, there you specify which time slots you want to create the report for and it uses the information gathered previously to generate it (and you call it the filename you want)

cool thanks davey, im still a little unsure on how to do this is there any resource on the interenet that will let me run through this in a tutorial fashion?

run this as the perfstat user

Code:

---

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job      => X
    ,what      => 'statspack.snap;'
    ,next_date => sysdate
    ,interval  => 'TRUNC(SYSDATE+15/1440,''MI'')'
    ,no_parse  => TRUE
    ,force    => TRUE
    );
END;

---

it will create the snaps every 15 minutes, you will have to create the reports manually

Submit the Job Today at 3:00 PM and after that every 10 Minutes

sqlplus perfstat/perfstat@dbname

DECLARE
jobno number;
BEGIN
DBMS_JOB.SUBMIT (
jobno,
'statspack.snap(i_snap_level=>7);',
(TRUNC(SYSDATE)+ 15/24) + 10/1440,
'TRUNC(SYSDATE,''MI'')+10/1440'
);
COMMIT;
END;
/


To take the report

@(ORACLE_HOME)/rdbms/admin/spreport

he wanted 15 minutes, not 10 and why post the same imformation again

thanks guys, that is most helpful, I will run those scripts. one more thing it takes a snapshot every 10/15 mins, how will i get it to stop taking them??, is there something i type to switch it off?/

Can you also direct me to a good book where it would explain all of this stuff, as i really want to understand what your code is doing exactly.

Cheers.

Davey could you just clarify my understanding my comments are in {}

DECLARE
X NUMBER; {this would be the number of snaps that I want to take?}
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X {im unsure what this X if for??}
,what => 'statspack.snap;'
,next_date => sysdate
,interval => 'TRUNC(SYSDATE+15/1440,''MI'')' {run every 15 minutes but what is the /1140 and the "MI" mean?}
,no_parse => TRUE
,force => TRUE
);
END;


running the report manually, i could just do this in a shell script right??

Thanks for all your help so far, you have been an Oracle of knowledge (excuse the pun).

job => X

dbms_job.submit is a procedure which schedules a job, one of arguments of the procedure is job, X will be the job number and it will be assigned to the job argument.

in a day we have 1440 minutes right?

TRUNC(SYSDATE+15/1440,'MI') means get current time down to minutes (if you have 15:09:34 this will get you 15:09:00) and add 15 minutes

thanks dude that is much clearer.

one more thing i have tried running this in sqlplus but I cant get it to end and go back to the sql> prompt im getting this:

SQL> DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'statspack.snap;'
,next_date => sy 2 3 4 sdate
,interval => 'TRUNC(SYSDATE+15/1440,''MI'')'
,no_parse => TRUE
,force => TRUE
);
END; 5 6 7 8 9 10 11 12
13
14
15
16 \

what am i doing wrong?

you need a forward slash at the end, not a backwards one

and type commit; after as well or it wont save

if it doesnt save then it will only be actioned during that session?? and will reset when i log out??

when do i type commit after the /??

ok type this each line here corresponds to a separate line in sqlplus

Code:

---

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'statspack.snap;'
,next_date => sy 2 3 4 sdate
,interval => 'TRUNC(SYSDATE+15/1440,''MI'')'
,no_parse => TRUE
,force => TRUE
);
END;
/
commit;

---

cheers davey......now im getting the following error messaged

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1208997577 EDISS1 1 EDISS1

SP2-0310: unable to open file "sprepins.sql"


any idea what this means...

sorry to be a pain, and thanks for all your help.

and what are you typing, and is this on the database server machine?

sprepins is a file in ORACLE_HOME/rdbms/admin, do you have access to it?

yes sorry, sorted it, I was passing it through a shell script and was making a mistake with the path.

I am not getting the following error in my output:



DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1208997577 EDISS1 1 EDISS1


Completed Snapshots

Snap Snap
Instance DB Name Id Snap Started Level Comment
------------ ------------ ----- ----------------- ----- ----------------------
EDISS1 EDISS1 1 07 Nov 2005 11:52 5
2 07 Nov 2005 12:07 5
3 07 Nov 2005 12:22 5
4 07 Nov 2005 12:37 5
5 07 Nov 2005 12:52 5
6 07 Nov 2005 13:07 5
7 07 Nov 2005 13:22 5


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2
Begin Snapshot Id specified: 2

Enter value for end_snap: 3
End Snapshot Id specified: 3


Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_2_3. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: report


Using the report name report

begin STATSPACK.STAT_CHANGES
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'STAT_CHANGES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


STATSPACK report for

DB Name DB Id Instance Inst Num Release OPS Host
------------ ----------- ------------ -------- ----------- --- ------------
EDISS1 1208997577 EDISS1 1 9.2.0.6.0 NO swisn013

Snap Id Snap Time Sessions
------- ------------------ --------
Begin Snap: 2 07-Nov-05 12:07:04
End Snap: 3 07-Nov-05 12:22:04
Elapsed: 15.00 (mins)

Cache Sizes
~~~~~~~~~~~
db_block_buffers: log_buffer:
db_block_size: shared_pool_size:

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size:
Logical reads:
Block changes:
Physical reads:
Physical writes:
User calls:
Parses:
Hard parses:
Sorts:
Logons:
Executes:
Transactions:

% Blocks changed per Read: Recursive Call %:
Rollback per transaction %: Rows per Sort:

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: Redo NoWait %:
Buffer Hit %: In-memory Sort %:
Library Hit %: Soft Parse %:
Execute to Parse %: Latch Hit %:
Parse CPU to Parse Elapsd %: % Non-Parse CPU:

Shared Pool Statistics Begin End
------ ------
Memory Usage %:
% SQL with executions>1: 80.17 80.48
% Memory for SQL w/exec>1: 69.82 71.16
100*((e.time_waited - nvl(b.time_waited,0))/:twt)) pctwtt
*
ERROR at line 9:
ORA-00904: "B"."TIME_WAITED": invalid identifier


(e.time_waited - nvl(b.time_waited,0)) /
*
ERROR at line 7:
ORA-00904: "B"."TIME_WAITED": invalid identifier


im kinda baffled as to what this could mean, has the DBA installed statspack properly you think?.


Also how do i stop the pl/sql statement?, reset the db_jobs?, as i dont want it to fill up the file space, and only want it to run at specific times.

Maybe you should read \\Oraclehome\rdbms\admin\spdoc.txt thoroughly.

use dbms_job.broken(, TRUE) to stop the job you can get the job by selecting job from user_jobs

Robertbalmer, I will read the spdoc at a later date, but right now i dont have much time to get this done, im not an ORACLE DBA but have been given the task to do some what looks to me like admin work.

If there is a problem with the installation of statpack then i can get the DBA to sort it out, i just need to know if its a problem on my part or his. Can you help???

davey if the job is 1, what would be the complete syntax??

exec dbms_job.broken(1,TRUE);
commit;

cheers mate, and to start it up again i would re-enter the pl/sql procedure on the previous page you gave me?.

exec dbms_job.broken(1,FALSE,sysdate);
commit;