ADDM tieing SQL_ID with a SQL statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: ADDM tieing SQL_ID with a SQL statement

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    ADDM tieing SQL_ID with a SQL statement

    I ran addmrpt.sql against one of my DB's and got the following output (see below).

    My question is how is there a way to tie a SQL_ID (b6usrg82hwsa3) with
    a particular SQL statement. Or to simplify, how can I tell what query caused
    the issue if the report only returns back a cryptic SQL_ID.

    Secondly, is there a rule of thumb to follow as to what percent impact I
    should start investigating problems. I would think 53% impact would be
    something I need to look into.

    Thanks to all who answer


    FINDING 1: 53% impact (178 seconds)
    -----------------------------------
    SQL statements consuming significant database time were found.

    RECOMMENDATION 1: SQL Tuning, 43% benefit (144 seconds)
    ACTION: Investigate the SQL statement with SQL_ID "b6usrg82hwsa3" for
    possible performance improvements.
    RELEVANT OBJECT: SQL statement with SQL_ID b6usrg82hwsa3
    call dbms_stats.gather_database_stats_job_proc ( )
    RATIONALE: SQL statement with SQL_ID "b6usrg82hwsa3" was executed 3
    times and had an average elapsed time of 48 seconds.

    RECOMMENDATION 2: SQL Tuning, 4.8% benefit (16 seconds)
    ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID
    "8a1pvy4cy8hgv".
    RELEVANT OBJECT: SQL statement with SQL_ID 8a1pvy4cy8hgv
    insert into histgrm$(obj#,intcol#,row#,bucket,endpoint,col#,epvalue)
    values(:1,:2,:3,:4,:5,:6,:7)
    ACTION: Investigate the SQL statement with SQL_ID "8a1pvy4cy8hgv" for
    possible performance improvements.
    RELEVANT OBJECT: SQL statement with SQL_ID 8a1pvy4cy8hgv
    insert into histgrm$(obj#,intcol#,row#,bucket,endpoint,col#,epvalue)
    values(:1,:2,:3,:4,:5,:6,:7)
    RATIONALE: SQL statement with SQL_ID "8a1pvy4cy8hgv" was executed 2715
    times and had an average elapsed time of 0.0059 seconds.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    you can get the SQL statement using SQL ID from v$sql view.

    set long 10000
    select sql_fulltext from v$sql where sql_id='SQL ID';

    Alternately, you can generate awr report during that period to get the top sqls running that time.

    There is rule of thump to consider addm recommendations. Any thing that is impacting database performance is candidate to tune / configure.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

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