error when running STATSPACK

[mike2000]

Hi all,

I tried to run the statspack and I got the below error and I ran the spreport.sql

ERROR: Begin Snapshot Id specified does not exist for this database/instance

ERROR: End Snapshot Id specified does not exist for this database/instance

WARNING: timed_statitics setting changed between begin/end snaps: TIMINGS ARE IN
VALID

ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID

ERROR: Session statistics are for different sessions: RESULTS NOT PRINTED
begin
*
ERROR at line 1:
ORA-20100: Missing Init.ora parameter db_block_size
ORA-06512: at "PERFSTAT.STATSPACK", line 727
ORA-06512: at "PERFSTAT.STATSPACK", line 1126
ORA-06512: at line 2


STATSPACK report for

DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
test 203776249 test 1 9.2.0.3.0 NO mycomp
: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
avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date

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
avg
count current exists max min prior sql stddev su

I am on 9.2.0.3 running on Windows 2000 server.

Please advise.

[davey23uk]

the errors at the top explain ure errors, you bounced the database, timed statistics was off, certina snaps don't exist.

[sambavan]

Make sure the system parameter TIMED_STATISTICS=TRUE and did you gather the stats? Once after you install the statspack, you have to gather some stats. Once this was completed, you run the stats report there you will have to specify the starting and ending snap numbers to generate the stats report.

       sqlplus perfstat@instance_name
       SQL> execute statspack.snap(i_snap_level=>10, i_modify_parameter=> 'true');

Execute the above code a few times and then try generating your report.

Hope this will help.

Sam

[mike2000]

I didn't bounce the datatbase neither the timed_statistics is set to false but some reason I still have problems, I ran many statspack in 8i before and I never encounter any issues, this is the first statspack I rn on 9i and I have problem, below is that I did

1. log in as sysdba
2. alter session set timed_statistics=true;
3. exec statspack.snap
4. 4 hours later run another exec.statspack.snap
5. run the spreport.sql with begining value let's say 50 and end value let's 55

and I got this error:

ERROR: Begin Snapshot Id specified does not exist for this database/instance

ERROR: End Snapshot Id specified does not exist for this database/instance

WARNING: timed_statitics setting changed between begin/end snaps: TIMINGS ARE IN
VALID

ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID

ERROR: Session statistics are for different sessions: RESULTS NOT PRINTED
begin
*
ERROR at line 1:
ORA-20100: Missing Init.ora parameter db_block_size
ORA-06512: at "PERFSTAT.STATSPACK", line 727
ORA-06512: at "PERFSTAT.STATSPACK", line 1126
ORA-06512: at line 2


STATSPACK report for

[davey23uk]

you must alter SYSTEM set timed_statistics=true not alter session or else it is meaningless as the statspack spans eevery session database wide. And it must stay on for entire duration not just when you take a snap or its totally useless

If you have been running that same stuff against 8i then you have been doing it wrong and not following the doco's.

Also your snapshot interval is wwwaaayyy too long, the data will just be meaningless after 4 hours. You really should read Tom Kytes book or follow any piece of Oracle advice which advises a 15 minute window.

Suggest you do some reading on this tool

[sambavan]

Please give us the following information:

   SELECT name, value 
     FROM v$parameter
    WHERE UPPER(name) LIKE '%TIMED%';

if it was set to false, then set it to true

    ALTER SYSTEM SET timed_statistics=true;

Then let us know what happens. Also please post your snap command set to see what kind of stats you were trying to collect.

-Sam

[mike2000]

SQL> select name,value from v$parameter
2 where upper(name) like '%TIMED%';
NAME
----------------------------------------------------------------
VALUE
------------------------------------------------------------------------------
timed_statistics
TRUE
timed_os_statistics
0

SQL> spool off



AND THEN I ISSUE THE BELOW COMMAND TO TAKE A SNAP OF THE DATABASE, THIS SCRIPT WAS RUN COUPLE TIMES BEFORE I RUN THE SPREPORT.SQL, BUT I STILL HAVE PROBLEM


exec statspack.snap(i_snap_level=>10,i_modify_parameter=>'true');

[davey23uk]

ok then post exact statements you are running from start to finish with no modifications

[mike2000]

Originally posted by davey23uk
ok then post exact statements you are running from start to finish with no modifications

As I mentioned all along that timed_statistics = true and my bad to type in alter session instead of alfter system. Ok, below is what I did:

1. timed_statistics=true
2. exec statspack.snap(i_snap_level=>10,i_modify_parameter=>'true');
3. exec statspack.snap(i_snap_level=>10,i_modify_parameter=>'true');
4. run the spreport.sql, the begining =70 and ending=71, and the location is c:\temp\file_name.txt

[mike2000]

I am going to drop perfstat and reinstall it b/c I ran the statspack from another database and I have no problem.


thanks all of the help