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. The purpose of this report, compared to 'DBA AWR SQL Performance Summary' is to retrieve SQLs which are not in the AWR, either becasue they ran after the most recent snapshot or because their performance impact is too small to be written to the AWR (see topnsql https://docs.oracle.com/... more more less

Description

Categories: DBA, Enginatics

Database SQL performance summary from the SGA to give an overview of top SQL load and performance issues. The purpose of this report, compared to 'DBA AWR SQL Performance Summary' is to retrieve SQLs which are not in the AWR, either becasue they ran after the most recent snapshot or because their performance impact is too small to be written to the AWR (see topnsql https://docs.oracle.com/... more

Copy

select
gsa.inst_id,
decode(gsbc.row_number,null,
&order_by/sum(decode(gsbc.row_number,null,&order_by)) 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.code,
case when gsa.program_line#>0 then gsa.program_line# end code_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.time_exec,
decode(gsbc.row_number,null,gsa.buffer_io) buffer_io,
decode(gsbc.row_number,null,gsa.disk_io) disk_io,
gsa.io_exec,
gsa.rows_processed,
gsa.rows_processed/gsa.executions_ rows_exec,
gsa.buffer_io/gsa.rows_processed_ io_row,
1000000*gsa.buffer_io/xxen_util.zero_to_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,
xxen_util.client_time(to_date(gsa.first_load_time,'YYYY-MM-DD/HH24:MI:SS')) first_load_time,
xxen_util.client_time(gsa.last_active_time) 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
gsa.elapsed_time/1000000/xxen_util.zero_to_null(gsa.executions) time_exec,
vp.value*gsa.buffer_gets/1000000/xxen_util.zero_to_null(gsa.executions) io_exec,
xxen_util.zero_to_null(gsa.executions) executions_,
xxen_util.zero_to_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.*,
(select so.name from sys.obj$ so where gsa.program_id=so.obj#) code,
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
where
1=1
) 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
&order_by desc nulls last,
gsa.sql_id
&bind_order

select gsa.inst_id, decode(gsbc.row_number,null, &order_by/sum(decode(gsbc.row_number,null,&order_by)) 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.code, case when gsa.program_line#>0 then gsa.program_line# end code_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.time_exec, decode(gsbc.row_number,null,gsa.buffer_io) buffer_io, decode(gsbc.row_number,null,gsa.disk_io) disk_io, gsa.io_exec, gsa.rows_processed, gsa.rows_processed/gsa.executions_ rows_exec, gsa.buffer_io/gsa.rows_processed_ io_row, 1000000*gsa.buffer_io/xxen_util.zero_to_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, xxen_util.client_time(to_date(gsa.first_load_time,'YYYY-MM-DD/HH24:MI:SS')) first_load_time, xxen_util.client_time(gsa.last_active_time) 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 gsa.elapsed_time/1000000/xxen_util.zero_to_null(gsa.executions) time_exec, vp.value*gsa.buffer_gets/1000000/xxen_util.zero_to_null(gsa.executions) io_exec, xxen_util.zero_to_null(gsa.executions) executions_, xxen_util.zero_to_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.*, (select so.name from sys.obj$ so where gsa.program_id=so.obj#) code, 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 where 1=1 ) 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 &order_by desc nulls last, gsa.sql_id &bind_order

Parameter Name

SQL text

Validation

Exclude SYS User

gsa.parsing_user_id<>0

LOV

Exclude PLSQL Code

gsa.command_type not in (47)

LOV

Show Bind Values

LOV

Package Name

gsa.program_id in (select do.object_id from dba_objects do where 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

Order By

select 'gsa.'||lower(translate(:oder_by_column,' ','_')) from dual

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,

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 DBA SGA Active Session History DBA Blocking Sessions new DBA AWR Active Session History FND Concurrent Requests DBA AWR System Metrics Summary Blitz Report Execution History DBA SGA Buffer Cache Object Usage