Reports2017-11-18T12:27:27+00:00

DBA SGA SQL Performance Summary

➜  Back to Library
Description
Categories: DBA, Enginatics
Database SQL performance summary from the SGA to give an overview of top SQL load and performance issues. 

select
gsa.inst_id,
decode(gsbc.row_number,null,
decode(:order_by,
'elapsed time',gsa.elapsed_time/sum(decode(gsbc.row_number,null,gsa.elapsed_time)) over (partition by gsa.inst_id)*100,
'IO',gsa.buffer_io/sum(decode(gsbc.row_number,null,gsa.buffer_io)) over (partition by gsa.inst_id)*100,
'executions',gsa.executions/sum(decode(gsbc.row_number,null,gsa.executions)) over (partition by gsa.inst_id)*100
)
) percentage,
xxen_util.responsibility(gsa.module,gsa.action) responsibility,
xxen_util.module_type(gsa.module,gsa.action) module_type,
xxen_util.module_name(gsa.module) module_name,
gsa.module,
gsa.object_name program,
case when gsa.program_line#>0 then gsa.program_line# end program_line#,
&literals_columns
gsa.sql_id,
gsa.plan_hash_value,
gsa.sql_fulltext sql_text,
&bind_columns
decode(gsbc.row_number,null,gsa.executions) executions,
xxen_util.time(gsa.elapsed_time/1000000) time,
decode(gsbc.row_number,null,gsa.elapsed_time/1000000) elapsed_time,
decode(gsbc.row_number,null,gsa.user_io_wait_time/1000000) user_io_wait_time,
decode(gsbc.row_number,null,gsa.cpu_time/1000000) cpu_time,
decode(gsbc.row_number,null,gsa.plsql_exec_time/1000000) plsql_exec_time,
decode(gsbc.row_number,null,gsa.concurrency_wait_time/1000000) concurrency_wait_time,
decode(gsbc.row_number,null,gsa.application_wait_time/1000000) application_wait_time,
gsa.elapsed_time/1000000/gsa.executions_ time_exec,
decode(gsbc.row_number,null,gsa.buffer_io) buffer_io,
decode(gsbc.row_number,null,gsa.disk_io) disk_io,
gsa.buffer_io/gsa.executions_ io_exec,
gsa.rows_processed,
gsa.rows_processed/gsa.executions_ rows_exec,
gsa.buffer_io/gsa.rows_processed_ io_row,
1000000*gsa.buffer_io/decode(gsa.elapsed_time,0,null,gsa.elapsed_time) io_sec,
case when gsa.executions>100 then gsa.buffer_io/gsa.hours/3600 end io_sec_avg,
1000000*(gsa.buffer_io-gsa.disk_io)/xxen_util.zero_to_null(gsa.cpu_time) buffer_rate,
1000000*gsa.disk_io/xxen_util.zero_to_null(gsa.user_io_wait_time) disk_rate,
100*gsa.disk_io/xxen_util.zero_to_null(gsa.buffer_io) disk_percentage,
case when gsa.executions>100 then gsa.executions/gsa.hours end execs_per_hour,
case when gsa.executions>100 then 100*gsa.elapsed_time/1000000/gsa.hours/3600 end time_percentage,
gsa.is_bind_sensitive,
gsa.is_bind_aware,
gsa.parsing_schema_name schema,
gsa.parse_calls,
gsa.sorts,
to_date(gsa.first_load_time,'YYYY-MM-DD/HH24:MI:SS') first_load_time,
gsa.last_active_time,
decode(gsa.command_type,1,'create table',2,'insert',3,'select',6,'update',7,'delete',9,'create index',11,'alter index',26,'lock table',42,'alter session',44,'commit',45,'rollback',46,'savepoint',47,'pl/sql block',48,'set transaction',50,'explain',62,'analyze table',90,'set constraints',170,'call',189,'merge','other') command_type,
gsa.action
from
(
select
decode(gsa.executions,0,null,gsa.executions) executions_,
decode(gsa.rows_processed,0,null,gsa.rows_processed) rows_processed_,
vp.value*gsa.buffer_gets/1000000 buffer_io,
vp.value*gsa.disk_reads/1000000 disk_io,
(decode(gsa.last_active_time,to_date(gsa.first_load_time,'YYYY-MM-DD/HH24:MI:SS'),to_date(null),gsa.last_active_time)-to_date(gsa.first_load_time,'YYYY-MM-DD/HH24:MI:SS'))*24 hours,
gsa.*,
do.object_name,
count(distinct decode(gsa.force_matching_signature,0,null,gsa.sql_id)) over (partition by gsa.force_matching_signature) literals_dupl_count,
row_number() over (partition by gsa.force_matching_signature order by gsa.inst_id,gsa.sql_id,gsa.plan_hash_value) literals_row_number
from
(select vp.value from v$parameter vp where vp.name like 'db_block_size') vp,
gv$sqlarea gsa,
dba_objects do
where
1=1 and
gsa.parsing_user_id<>0 and
gsa.program_id=do.object_id(+)
) gsa,
(
select
decode(row_number() over (partition by gsbc.inst_id, gsbc.sql_id order by gsbc.child_number desc, gsbc.position),1,null,2) row_number,
gsbc.*
from
(
select distinct
gsbc.inst_id,
gsbc.sql_id,
gsbc.child_number,
gsbc.name,
case
when gsbc.datatype_string like 'TIMESTAMP%' then to_char(anydata.accesstimestamp(gsbc.value_anydata))
when gsbc.datatype_string='DATE' then to_char(anydata.accessdate(gsbc.value_anydata))
else gsbc.value_string
end value_string,
gsbc.last_captured,
min(gsbc.position) over (partition by gsbc.inst_id, gsbc.sql_id, gsbc.child_number, gsbc.name, gsbc.value_string, gsbc.last_captured) position
from
gv$sql_bind_capture gsbc
where
'&show_binds'='Y' and
gsbc.was_captured='YES'
) gsbc
) gsbc
where
2=2 and
gsa.inst_id=gsbc.inst_id(+) and
gsa.sql_id=gsbc.sql_id(+)
order by
&literals_order_by
decode(:order_by,'elapsed time',gsa.elapsed_time,'IO',gsa.buffer_gets,'executions',gsa.executions,'last active time',gsa.last_active_time-sysdate) desc nulls last,
gsa.sql_id
&bind_order

Parameter Name SQL text Validation
Exclude PLSQL Code 
gsa.command_type not in (47)
 LOV
Show Bind Values 
Y
 LOV
Package Name 
do.object_name=:package_name
 LOV
Using Index 
(gsa.inst_id,gsa.sql_id,gsa.plan_hash_value) in (select /*+ cardinality(gsp 1) */ gsp.inst_id, gsp.sql_id, gsp.plan_hash_value from gv$sql_plan gsp where gsp.object_name=:index_name and gsp.operation='INDEX')
 LOV
Module Type 
xxen_util.module_type(gsa.module,gsa.action)=:module_type
 LOV
Schema 
gsa.parsing_schema_name=:schema
 LOV
Literals Duplication Count 
gsa.literals_dupl_count desc,
gsa.force_matching_signature,
gsa.literals_row_number,
Literals Duplication Count 
gsa.literals_dupl_count,
to_char(gsa.force_matching_signature) force_matching_signature,
Show Bind Values 
,gsbc.child_number desc,
gsbc.position
Show Bind Values 
gsbc.child_number,
gsbc.name bind,
gsbc.value_string bind_value,
gsbc.last_captured capture_date,
Order By 
 
 LOV
Command Type 
decode(gsa.command_type,1,'create table',2,'insert',3,'select',6,'update',7,'delete',9,'create index',11,'alter index',26,'lock table',42,'alter session',44,'commit',45,'rollback',46,'savepoint',47,'pl/sql block',48,'set transaction',50,'explain',62,'analyze table',90,'set constraints',170,'call',189,'merge','other')=:command_type
 LOV
Examples per Duplicate 
gsa.literals_row_number<=:literals_dupl_examples
 LOV
Literals Duplication Count 
gsa.literals_dupl_count>=:literals_dupl_count
 Number
Last Active to 
gsa.last_active_time<=:last_active_to
 DateTime
Last Active from 
gsa.last_active_time>=:last_active_from
 DateTime
SQL Id 
gsa.sql_id=:sql_id
Module contains 
lower(gsa.module) like '%'||lower(:module)||'%'
 LOV
SQL Text contains 
lower(gsa.sql_fulltext) like lower('%'||:sql_text||'%')

Download
 

Related Blitz Reports
DBA AWR SQL Performance Summary
DBA AWR Active Session History
new DBA AWR Active Session History
DBA SGA Active Session History
DBA SGA Buffer Cache Object Usage
DBA Blocking Sessions
DBA AWR System Metrics Summary
DBA SGA+PGA Memory Configuration
FND Concurrent Requests

© 2018 Copyright by Enginatics GmbH