-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|