Statpack help
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 38

Thread: Statpack help

  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Statpack help

    Morning everbody, I hope everyone is having a great day so far..if not we now have the weekend...


    Ill to the point, im running statspack to produce a report and have a few questions that dont seem to be answered by the help files

    1) if I want to run the statspack on 30 second intervals, how would i do that. I know how to shell script and can automate this with crontab but is there a way to do it within Oracle, maybe change a parameter somewhere??.

    2) when you run a standard statpack report at the bottom are some values that i dont understand namely something called 'Hash Values' and some associated values like buffets gets, executions, there seems to be some sql code that is kicked out with these values..does anyone know what this is??..


    3) finally if i want to produce only the top part of the report, what level would i set??, where would i set that level? (is it a table or parameter).

    I thank anyone in advance for any help that they can offer.

  2. #2
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    30 seconds it way too frequent IMO as it can take upto that just to run and there is a bit on the database while it runs.

  3. #3
    Join Date
    Oct 2005
    Posts
    26
    What would you recommend using??, also what doesn IMO mean?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    IMO = in my opinion, 15 minute intervals are roughly what you should be looking for if you are tuning something.

    What are you trying to get from it, there may be a better way

    Define what you mean by the top part of the report?

    The SQL Code you are seeing is probably the TOP SQL part of the report, statspack lists the most resource consuming sql statements in the database (ordered by buffer gets, executions) each sql statement is given a unique hash value so thats what that is

  5. #5
    Join Date
    Oct 2005
    Posts
    26
    top part:

    B Name DB Id Instance Inst Num Release Cluster Host
    ------------ ----------- ------------ -------- ----------- ------- ------------
    EDIST1 972084764 EDIST1 1 9.2.0.6.0 NO swisn013

    Snap Id Snap Time Sessions Curs/Sess Comment
    --------- ------------------ -------- --------- -------------------
    Begin Snap: 1 20-Oct-05 11:24:56 14 3.1
    End Snap: 3 20-Oct-05 11:27:08 14 6.7
    Elapsed: 2.20 (mins)

    Cache Sizes (end)
    ~~~~~~~~~~~~~~~~~
    Buffer Cache: 352M Std Block Size: 8K
    Shared Pool Size: 128M Log Buffer: 1,024K

    Load Profile
    ~~~~~~~~~~~~ Per Second Per Transaction
    --------------- ---------------
    Redo size: 16,403.09 721,736.00
    Logical reads: 41.55 1,828.00
    Block changes: 19.58 861.33
    Physical reads: 0.02 0.67
    Physical writes: 0.02 1.00
    User calls: 0.22 9.67
    Parses: 1.00 44.00
    Hard parses: 0.12 5.33
    Sorts: 1.14 50.00
    Logons: 0.01 0.33
    Executes: 1.93 85.00
    Transactions: 0.02

    % Blocks changed per Read: 47.12 Recursive Call %: 97.84
    Rollback per transaction %: 0.00 Rows per Sort: 287.97

    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait %: 100.00 Redo NoWait %: 100.00
    Buffer Hit %: 99.96 In-memory Sort %: 100.00
    Library Hit %: 91.80 Soft Parse %: 87.88
    Execute to Parse %: 48.24 Latch Hit %: 100.00
    Parse CPU to Parse Elapsd %: 100.00 % Non-Parse CPU: 94.26

    Shared Pool Statistics Begin End
    ------ ------
    Memory Usage %: 94.06 94.26
    % SQL with executions>1: 55.53 56.01
    % Memory for SQL w/exec>1: 60.84 63.32

    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~ % Total
    Event Waits Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    CPU time 2 85.84
    log file parallel write 68 0 4.34
    log file sync 11 0 4.17
    control file parallel write 44 0 2.52
    control file sequential read 124 0 2.33
    -------------------------------------------------------------

    Wait Events for DB: EDIST1 Instance: EDIST1 Snaps: 1 -3
    -> s - second
    -> cs - centisecond - 100th of a second
    -> ms - millisecond - 1000th of a second
    -> us - microsecond - 1000000th of a second
    -> ordered by wait time desc, waits desc (idle events last)

    Avg
    Total Wait wait Waits
    Event Waits Timeouts Time (s) (ms) /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    log file parallel write 68 0 0 2 22.7
    log file sync 11 0 0 9 3.7
    control file parallel write 44 0 0 1 14.7
    control file sequential read 124 0 0 0 41.3
    db file sequential read 2 0 0 9 0.7
    db file parallel write 2 0 0 1 0.7
    SQL*Net message from client 26 0 1,979 76128 8.7
    virtual circuit status 5 5 146 29297 1.7
    wakeup time manager 4 4 113 28325 1.3
    SQL*Net message to client 26 0 0 0 8.7
    -------------------------------------------------------------

    Background Wait Events for DB: EDIST1 Instance: EDIST1 Snaps: 1 -3
    -> ordered by wait time desc, waits desc (idle events last)

    Avg
    Total Wait wait Waits
    Event Waits Timeouts Time (s) (ms) /txn
    ---------------------------- ------------ ---------- ---------- ------ --------
    log file parallel write 68 0 0 2 22.7
    control file parallel write 44 0 0 1 14.7
    control file sequential read 22 0 0 1 7.3
    db file parallel write 2 0 0 1 0.7
    rdbms ipc message 163 134 502 3077 54.3
    smon timer 1 1 293 ###### 0.3
    pmon timer 44 44 129 2937 14.7
    -------------------------------------------------------------



    part im not sure about:
    SQL ordered by Gets for DB: EDIST1 Instance: EDIST1 Snaps: 1 -3
    -> End Buffer Gets Threshold: 10000
    -> Note that resources reported for PL/SQL includes the resources used by
    all SQL statements called within the PL/SQL code. As individual SQL
    statements are also reported, it is possible and valid for the summed
    total % to exceed 100

    CPU Elapsd
    Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
    --------------- ------------ -------------- ------ -------- --------- ----------
    260 4 65.0 4.7 0.00 0.02 2614951860
    select t.schema, t.name, t.flags, q.name from system.aq$_queue_t
    ables t, sys.aq$_queue_table_affinities aft, system.aq$_que
    ues q where aft.table_objno = t.objno and aft.owner_instance = :
    1 and q.table_objno = t.objno and q.usage = 0 and b
    itand(t.flags, 4+16+32+64+128+256) = 0 and NOT ( t.n

    84 12 7.0 1.5 0.00 0.00 3371479671
    select t.name, (select owner_instance from sys.aq$_queue_table_
    affinities where table_objno = t.objno) from system.aq$_queue
    _tables t where t.name = :1 and t.schema = :2 for update skip lo
    cked

    52 26 2.0 0.9 0.00 0.01 1316169839
    select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n
    ext_date) and (next_date < :2)) or ((last_date is null) and
    (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)
    ) and (this_date is null) order by next_date, job

    39 13 3.0 0.7 0.00 0.00 1254950678
    select file# from file$ where ts#=:1

    26 26 1.0 0.5 0.00 0.00 1693927332
    select count(*) from sys.job$ where (next_date > sysdate) and (n
    ext_date < (sysdate+5/86400))

    19 19 1.0 0.3 0.00 0.00 4274598960
    select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim
    estamp#, sample_size, minimum, maximum, distcnt, lowval, hival,
    density, col#, spare1, spare2, avgcln from hist_head$ where obj#
    =:1 and intcol#=:2

    15 1 15.0 0.3 0.00 0.00 2095543314
    select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t whe
    re t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0

    9 1 9.0 0.2 0.00 0.00 1356713530
    select privilege#,level from sysauth$ connect by grantee#=prior
    privilege# and privilege#>0 start with (grantee#=:1 or grantee#=
    1) and privilege#>0

    8 1 8.0 0.1 0.00 0.00 3743621015
    UPDATE SMON_SCN_TIME SET SCN_WRP=:1, SCN_BAS=:2, TIME_MP=:3, TIM
    E_DP=:4 WHERE TIME_MP = :5 AND THREAD = :6 AND ROWNUM <= 1


    7 1 7.0 0.1 0.00 0.00 2201872251
    SELECT MIN(TIME_MP), COUNT(*) FROM SMON_SCN_TIME WHERE THREAD =
    :1


    SQL ordered by Gets for DB: EDIST1 Instance: EDIST1 Snaps: 1 -3
    -> End Buffer Gets Threshold: 10000
    -> Note that resources reported for PL/SQL includes the resources used by
    all SQL statements called within the PL/SQL code. As individual SQL
    statements are also reported, it is possible and valid for the summed
    total % to exceed 100



    Also, how would i set it then to take a snap every 15 minutes over 3 hours within oracle?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    use dbms_job to schedule it ever 15 minutes and you cant just get the bit you want

  7. #7
    Join Date
    Oct 2005
    Posts
    26
    cool thanks davey your help is greatly appreciated.

  8. #8
    Join Date
    Oct 2005
    Posts
    26
    One more thing,

    My performance guy is wanting metric measurements every 30 seconds, I have been told already by mrchrispy that running statspac every 30 seconds is not useful.

    Is there anything else i can run that will give me metric measurements every 30 seconds that would be useful to my performance manager??

    Cheers people.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Who is this performance guy? If he were any good, he'd know what he wanted and how to get it.

    Perhaps you can keep him quiet with the pretty pictures from the diagnostics in OEM?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    Quote Originally Posted by DaPi
    Perhaps you can keep him quiet with the pretty pictures from the diagnostics in OEM?
    LOL...that just what we've done while we take the time to go over statspack reports.

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