Statpack help

[davey23uk]

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?

[Linux_cat]

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.

[robertbalmer]

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

[davey23uk]

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

[Linux_cat]

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

[davey23uk]

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

[Linux_cat]

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

[davey23uk]

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