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

DBA SGA SQL Performance Summary

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||'%')