| statspack [message #118237] |
Tue, 03 May 2005 15:32  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
hai all,
i am new to performance tuning using statspack.can u plz give me some tips on the usage of that basically.i have installed statspack with separate user (perfstat).i want to generate a report regarding statistics by executing @spreport in perfstat schema but it is blowing the following errors.
SQL> @spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1966006042 TCMS 1 tcms
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Using 1966006042 for database Id
Using 1 for instance number
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1
Enter value for end_snap: 2
End Snapshot Id specified: 2
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: baba
Using the report name test
ERROR: Database/Instance does not exist in STATS$DATABASE_INSTANCE
ERROR: Begin Snapshot Id specified does not exist for this database/instance
ERROR: End Snapshot Id specified does not exist for this database/instance
, stats$parameter e
*
ERROR at line 6:
ORA-00942: table or view does not exist
ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID
ERROR: Session statistics are for different sessions: RESULTS NOT PRINTED
STATSPACK.STAT_CHANGES
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'STATSPACK.STAT_CHANGES' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
TCMS 1966006042 tcms 1
:ela := ;
*
ERROR at line 4:
ORA-06550: line 4, column 17:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
The symbol "null" was substituted for ";" to continue.
ORA-06550: line 6, column 16:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
( - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev su
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: M Std Block Size: K
Shared Pool Size: M Log Buffer: K
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:
, stats$sql_statistics e
*
ERROR at line 13:
ORA-00942: table or view does not exist
, stats$system_event e
*
ERROR at line 15:
ORA-00942: table or view does not exist
, stats$dlm_misc e
*
ERROR at line 6:
ORA-00942: table or view does not exist
plz help out in this issue and give me some usage tips on statspack.
regards
bala
|
|
|
|
| Re: statspack [message #118279 is a reply to message #118237] |
Wed, 04 May 2005 02:10  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Statspack reports differences between two snapshots. In order to being able to create such a report, you have to create at least two snapshots.
This is done with statspack.snap; which normally is called by a job that runs every 15 minutes.
hth
|
|
|
|
| Re: statspack [message #118338 is a reply to message #118279] |
Wed, 04 May 2005 08:52 |
oskarthik
Messages: 26
Registered: March 2005
|
Junior Member |
|
|
hai ,
thanks a lot for ur reply.if to create two reports using statspack.snap it is blowing errors as follows
SQL> exec statspack.snap
BEGIN statspack.snap; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'STATSPACK.SNAP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
whats wrong in the flow..?
help out in this issue...
regards
bala
|
|
|
|
|
|
|
|
|
|
|
|
| Re: statspack [message #202974 is a reply to message #118522] |
Mon, 13 November 2006 08:05 |
sssss
Messages: 9
Registered: November 2006
|
Junior Member |
|
|
|
Hey there is a dynamic( alter system set)INIT parameter called Timed_statistics, you have to set that as True, and may be the size of the tablespace is allocated is not enough.
|
|
|
|
|
|
| Re: statspack [message #202982 is a reply to message #202975] |
Mon, 13 November 2006 08:14 |
sssss
Messages: 9
Registered: November 2006
|
Junior Member |
|
|
|
you can not shutdown the instance while taking two snapshots. i mean both the snapshots should be from the same instance startup.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
