error when running STATSPACK

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

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.

Code:

---


      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

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

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

Please give us the following information:

Code:

---


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

---

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

Code:

---


    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

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');

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

Quote:

---

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

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

i mean exact output from the screen

Statspack is Database instance oriented. So, if you have imported an old perfstat user and try to run the stats report/snap it will not work. The solution to this proble as you mentioned is to reinstall the statspack from the scrach on the instance and proceed.

-Sam