error when running STATSPACK
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: error when running STATSPACK

  1. #1
    Join Date
    Sep 2002
    Posts
    411

    error when running STATSPACK

    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.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    the errors at the top explain ure errors, you bounced the database, timed statistics was off, certina snaps don't exist.

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Last edited by sambavan; 07-15-2003 at 05:10 PM.
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Sep 2002
    Posts
    411
    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

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Sep 2002
    Posts
    411
    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');

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    ok then post exact statements you are running from start to finish with no modifications

  9. #9
    Join Date
    Sep 2002
    Posts
    411
    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

  10. #10
    Join Date
    Sep 2002
    Posts
    411
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width