Statpack help

[Linux_cat]

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??

[davey23uk]

need to find out what information he is after.

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

[serg1257]

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

[davey23uk]

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

[jeromespitfire]

[Linux_cat]

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.

[davey23uk]

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)

[Linux_cat]

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?

[davey23uk]

run this as the perfstat user

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

[skdas]

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