Home » RDBMS Server » Performance Tuning » statspack
statspack [message #118237] Tue, 03 May 2005 15:32 Go to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #118440 is a reply to message #118237] Thu, 05 May 2005 04:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you run that statement as user perfstat?

hth
Re: statspack [message #118476 is a reply to message #118440] Thu, 05 May 2005 08:42 Go to previous messageGo to next message
balavignesh
Messages: 180
Registered: March 2005
Senior Member
hai ,

i ran the statement ofcourse in the perstat user , however it is blowing errors.plz help out .

regards
bala
Re: statspack [message #118508 is a reply to message #118476] Thu, 05 May 2005 11:49 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI

Try after resintalling statpack once. Probably that may work.

Regds
Girish
Re: statspack [message #118522 is a reply to message #118237] Thu, 05 May 2005 12:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Look in the logfiles for errors during install.

hth
Re: statspack [message #202974 is a reply to message #118522] Mon, 13 November 2006 08:05 Go to previous messageGo to next message
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 #202975 is a reply to message #202974] Mon, 13 November 2006 08:07 Go to previous messageGo to next message
sssss
Messages: 9
Registered: November 2006
Junior Member
May be this document can help you.
  • Attachment: spdoc.txt
    (Size: 84.16KB, Downloaded 1387 times)
Re: statspack [message #202982 is a reply to message #202975] Mon, 13 November 2006 08:14 Go to previous messageGo to next message
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.
Re: statspack [message #267393 is a reply to message #118237] Thu, 13 September 2007 03:24 Go to previous messageGo to next message
mikemike
Messages: 1
Registered: September 2007
Junior Member
Pls try to connect as sysdba and execute
@spdrop
@spcreate
Re: statspack [message #267431 is a reply to message #267393] Thu, 13 September 2007 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think it is useful to answer to a message that is more than 2 years old.

Regards
Michel
Re: statspack [message #267865 is a reply to message #267431] Sat, 15 September 2007 17:33 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello,

maybe this helps:

http://www.orafaq.com/forum/m/266903/110238/?srch=statspack#msg_266903

Regards,


mson77
Previous Topic: Finding Status Progress of Query with Single Block I/O
Next Topic: Urgent help needed in tuning the query
Goto Forum:
  


Current Time: Sat Dec 10 07:17:31 CST 2016

Total time taken to generate the page: 0.04996 seconds