Skip to content

Instantly share code, notes, and snippets.

/quick_ash_queries_report_export.xml

Created November 25, 2017 16:00
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save anonymous/a9ba2ff1d554d9c2e94cb2d4ab4b6025 to your computer and use it in GitHub Desktop.
Save anonymous/a9ba2ff1d554d9c2e94cb2d4ab4b6025 to your computer and use it in GitHub Desktop.
Download ZIP
Oracle Visualize ASH
Raw
quick_ash_queries_report_export.xml
<?xml version="1.0" encoding="UTF-8" ?>
<displays>
<folder>
<name><![CDATA[Performance]]></name>
<tooltip><![CDATA[Performance]]></tooltip>
<description><![CDATA[ ]]></description>
<display id="e841eb89-015f-1000-8001-0a3c20905948" type="" style="Chart" enable="true">
<name><![CDATA[ASH - Last 10 minutes]]></name>
<description><![CDATA[ASH information for last 10 minutes.
Contact Twitter: @AndrewSayer_ ]]></description>
<tooltip><![CDATA[]]></tooltip>
<drillclass><![CDATA[]]></drillclass>
<CustomValues>
<PLOT_HGRID_WIDTH>THINNER</PLOT_HGRID_WIDTH>
<Y1AXIS_SCALE_INCREMENT>10.0</Y1AXIS_SCALE_INCREMENT>
<Y2AXIS_SCALE_MAXIMUM>80.0</Y2AXIS_SCALE_MAXIMUM>
<Y1AXIS_LINE_WIDTH>THINNEST</Y1AXIS_LINE_WIDTH>
<Y1AXIS_TITLE_ALIGNMENT>CENTER</Y1AXIS_TITLE_ALIGNMENT>
<LEGEND_LOCATION>AUTOMATIC</LEGEND_LOCATION>
<LEGEND_ALIGNMENT>LEFT</LEGEND_ALIGNMENT>
<XAXIS_LINE_WIDTH>THINNEST</XAXIS_LINE_WIDTH>
<DATA_MAP_COLUMNS>\</DATA_MAP_COLUMNS>
<Y1AXIS_SCALE_MAXIMUM>60.0</Y1AXIS_SCALE_MAXIMUM>
<XAXIS_TITLE_ALIGNMENT>CENTER</XAXIS_TITLE_ALIGNMENT>
<PLOT_DATALABELS>false</PLOT_DATALABELS>
<XAXIS_TICK_LABEL_ROTATE>HORIZONTAL</XAXIS_TICK_LABEL_ROTATE>
<Y1AXIS_LOGARITHMIC_BASE>BASE_10</Y1AXIS_LOGARITHMIC_BASE>
<TYPE>BAR_VERT_CLUST</TYPE>
<GRID_WIDTH>THINNER</GRID_WIDTH>
<PLOT_DATALABELS_BAR_POSITION>ABOVE</PLOT_DATALABELS_BAR_POSITION>
<FOOTNOTE_ALIGNMENT>LEFT</FOOTNOTE_ALIGNMENT>
<XAXIS_TICK_LABEL_SKIP_MODE>AUTOMATIC</XAXIS_TICK_LABEL_SKIP_MODE>
<DATA_MAP_COLNAMES>\,"COUNT(*)","EVENT","MODULE_ACTION"</DATA_MAP_COLNAMES>
<DATA_MAP_SERIES>\</DATA_MAP_SERIES>
<Y2AXIS_LOGARITHMIC_BASE>BASE_10</Y2AXIS_LOGARITHMIC_BASE>
<DATA_MAP_COUNT>0</DATA_MAP_COUNT>
<STYLE>Default</STYLE>
<TITLE_ALIGNMENT>LEFT</TITLE_ALIGNMENT>
<TITLE_TEXT>Last minute</TITLE_TEXT>
<Y2AXIS_TICK_LABEL_ROTATE>HORIZONTAL</Y2AXIS_TICK_LABEL_ROTATE>
<Y2AXIS_SCALE_MINIMUM>10.0</Y2AXIS_SCALE_MINIMUM>
<Y2AXIS_LINE_WIDTH>THINNEST</Y2AXIS_LINE_WIDTH>
<Y1AXIS_TICK_LABEL_ROTATE>HORIZONTAL</Y1AXIS_TICK_LABEL_ROTATE>
<TITLE>true</TITLE>
<Y2AXIS_SCALE_INCREMENT>20.0</Y2AXIS_SCALE_INCREMENT>
<PLOT_VGRID_WIDTH>THINNER</PLOT_VGRID_WIDTH>
<Y2AXIS_TITLE_ALIGNMENT>CENTER</Y2AXIS_TITLE_ALIGNMENT>
<SUBTITLE_ALIGNMENT>LEFT</SUBTITLE_ALIGNMENT>
</CustomValues>
<query>
<sql><![CDATA[select * FROM (
SELECT case when coalesce(module,action) is null then 'NULL' else module||DECODE(action,NULL,NULL,'.'||action) end module_action
,nvl(event,'CPU') event
,COUNT(*)
FROM v$active_Session_history ash
WHERE sample_time BETWEEN SYSDATE-INTERVAL'10'MINUTE AND SYSDATE
and session_type = 'FOREGROUND'
GROUP BY case when coalesce(module,action) is null then 'NULL' else module||DECODE(action,NULL,NULL,'.'||action) end
,nvl(event,'CPU')
ORDER BY count(*) desc) where rownum <=25
order by 3]]></sql>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="null" author="null" subject="null" keywords="null" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="NONE" zip="false" />
</column>
<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>
                                                            null                                                       </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="ENTIRE_CONTENT">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
<display id="" type="" style="Table" enable="true">
<name><![CDATA[Action & Event]]></name>
<description><![CDATA[]]></description>
<tooltip><![CDATA[]]></tooltip>
<drillclass><![CDATA[]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[select sql_id, sql_plan_hash_value, cnt, execs, (select substr(sql_text,1,100) sql_text from v$sql s where s.sql_id = ash.sql_id and rownum <2) sql_text FROM (
SELECT sql_id,sql_plan_hash_value
,COUNT(*) cnt
,max(sql_exec_id)-min(sql_exec_id) execs
FROM v$active_Session_history ash
WHERE sample_time BETWEEN SYSDATE-INTERVAL'10'MINUTE AND SYSDATE
and case when coalesce(module,action) is null then 'NULL' else module||DECODE(action,NULL,NULL,'.'||action) end = :MODULE_ACTION
and nvl(event,'CPU') = :EVENT
and session_type = 'FOREGROUND'
GROUP BY sql_id,sql_plan_hash_value
ORDER BY count(*) desc) ash
where rownum <=25 order by cnt desc]]></sql>
<binds>
<bind id="MODULE_ACTION">
<prompt><![CDATA[MODULE_ACTION]]></prompt>
<tooltip><![CDATA[MODULE_ACTION]]></tooltip>
<value><![CDATA[NULL_VALUE]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
<bind id="EVENT">
<prompt><![CDATA[EVENT]]></prompt>
<tooltip><![CDATA[EVENT]]></tooltip>
<value><![CDATA[NULL_VALUE]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
</binds>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="null" author="null" subject="null" keywords="null" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="NONE" zip="false" />
</column>
<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>
                                                                        null                                                                   </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="ENTIRE_CONTENT">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
</display>
</display>
<display id="eb5dd5a5-015f-1000-8005-c0a8016b9df9" type="" style="Table" enable="true">
<name><![CDATA[SQL ASH 12c]]></name>
<description><![CDATA[Shows where the time is going executing a sql_id
Contact Twitter: @AndrewSayer_ ]]></description>
<tooltip><![CDATA[]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[with
ash
AS (select sql_id
,sql_plan_hash_value
,sql_full_plan_hash_value
,sql_plan_line_id
,nvl(event,'CPU/'||ash.top_level_call_name||'-'||sc.command_name) event
,nvl2(event,decode(ash.current_obj# ,0,'UNDO',o.object_name),null) object_name
,count(distinct sample_id) cnt_distinct_samples
,count(*) cnt
,round(count(distinct session_id)/nullif(count(distinct sql_exec_id),0)) cnt_sess
,max(sample_time) most_recent_time
from v$active_session_history ash
left join dba_objects o
on ash.current_obj# = o.object_id
left join v$sqlcommand sc
on ash.top_level_sql_opcode = sc.command_type
where ash.sql_id = :SQL_ID
group by sql_id
,sql_plan_hash_value
,sql_full_plan_hash_value
,sql_plan_line_id
,nvl(event,'CPU/'||ash.top_level_call_name||'-'||sc.command_name)
,nvl2(event,decode(ash.current_obj# ,0,'UNDO',o.object_name),null)
ORDER BY 1,2,3,5
)
,sp
as (select sp.plan_hash_value
,sp.full_plan_hash_value
,sp.id
,sp.sql_id
,lpad(' ',sp.depth,' ')||sp.operation||' '||sp.options "Operation"
,sp.object_name "Name"
,max(sp.cardinality) cardinality
,max(sp.io_cost) cost
,sp.object_name
from v$sql_plan sp
where sp.sql_id = :SQL_ID
group by sp.plan_hash_value
,sp.full_plan_hash_value
,sp.id
,sp.sql_id
,lpad(' ',sp.depth,' ')||sp.operation||' '||sp.options
,sp.object_name
)
select active
,full_plan_hash_value
--,plan_hash_value
,"Id"
,"Operation"
,"Name"
,cardinality
,cost
,event
,object_name
,activity
,cnt
,activity_pct PCT
,cnt_distinct_samples
from (select case when ash.most_recent_time > sysdate-interval'5'second then '*' else ' ' end active
,nvl(sp.full_plan_hash_value,ash.sql_full_plan_hash_value) full_plan_hash_value
,nvl(sp.plan_hash_value,ash.sql_plan_hash_value) plan_hash_value
,nvl(sp.id,ash.sql_plan_line_id) "Id"
,sp."Operation"
,sp.object_name "Name"
,sp.cardinality
,sp.cost
,ash.event
,ash.object_name
,nvl2(ash.cnt,'SQLDEV:GAUGE:0:'||sum(ash.cnt) over (partition by nvl(sp.full_plan_hash_value,ash.sql_full_plan_hash_value))
||':'||(sum(ash.cnt) over (partition by nvl(sp.full_plan_hash_value,ash.sql_full_plan_hash_value))/10)
||':'||(sum(ash.cnt) over (partition by nvl(sp.full_plan_hash_value,ash.sql_full_plan_hash_value))/3)||':'
||ash.cnt,null) as activity
,ash.cnt
,nvl2(ash.cnt,round(100*ash.cnt/sum(ash.cnt) over (partition by nvl(sp.full_plan_hash_value,ash.sql_full_plan_hash_value)),1)||'%',null) activity_pct
,ash.cnt_distinct_samples
from sp
full join ash
on sp.full_plan_hash_value = ash.sql_full_plan_hash_value
and sp.id = ash.sql_plan_line_id
and sp.sql_id = ash.sql_id
order by sp.full_plan_hash_value, sp.plan_hash_value,sp.id, ash.cnt
)
order by full_plan_hash_value, "Id",plan_hash_value , cnt]]></sql>
<binds>
<bind id="SQL_ID">
<prompt><![CDATA[SQL_ID]]></prompt>
<tooltip><![CDATA[SQL_ID]]></tooltip>
<value><![CDATA[9sq8x12p5435f]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
</binds>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="null" author="null" subject="null" keywords="null" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="NONE" zip="false" />
</column>
<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>
null </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
</display>
<display id="f3c77440-015f-1000-8004-c0a8000d1c9f" type="" style="Table" enable="true">
<name><![CDATA[SQL ASH Pre 12c]]></name>
<description><![CDATA[Shows where the time is going executing a sql_id
Contact Twitter: @AndrewSayer_ ]]></description>
<tooltip><![CDATA[]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[with
ash
AS (select sql_id
,sql_plan_hash_value
--,sql_full_plan_hash_value
,sql_plan_line_id
,nvl(event,'CPU/'||ash.top_level_call_name||'-'||sc.command_name) event
,nvl2(event,decode(ash.current_obj# ,0,'UNDO',o.object_name),null) object_name
,count(distinct sample_id) cnt_distinct_samples
,count(*) cnt
,round(count(distinct session_id)/nullif(count(distinct sql_exec_id),0)) cnt_sess
,max(sample_time) most_recent_time
from v$active_session_history ash
left join dba_objects o
on ash.current_obj# = o.object_id
left join v$sqlcommand sc
on ash.top_level_sql_opcode = sc.command_type
where ash.sql_id = :SQL_ID
group by sql_id
,sql_plan_hash_value
--,sql_full_plan_hash_value
,sql_plan_line_id
,nvl(event,'CPU/'||ash.top_level_call_name||'-'||sc.command_name)
,nvl2(event,decode(ash.current_obj# ,0,'UNDO',o.object_name),null)
ORDER BY 1,2,3,5
)
,sp
as (select sp.plan_hash_value
--,sp.full_plan_hash_value
,sp.id
,sp.sql_id
,lpad(' ',sp.depth,' ')||sp.operation||' '||sp.options "Operation"
,sp.object_name "Name"
,max(sp.cardinality) cardinality
,max(sp.io_cost) cost
,sp.object_name
from v$sql_plan sp
where sp.sql_id = :SQL_ID
group by sp.plan_hash_value
--,sp.full_plan_hash_value
,sp.id
,sp.sql_id
,lpad(' ',sp.depth,' ')||sp.operation||' '||sp.options
,sp.object_name
)
select active
--,full_plan_hash_value
,plan_hash_value
,"Id"
,"Operation"
,"Name"
,cardinality
,cost
,event
,object_name
,activity
,cnt
,activity_pct PCT
,cnt_distinct_samples
from (select case when ash.most_recent_time > sysdate-interval'5'second then '*' else ' ' end active
--,nvl(sp.full_plan_hash_value,ash.sql_full_plan_hash_value) full_plan_hash_value
,nvl(ash.sql_plan_hash_value,sp.plan_hash_value) plan_hash_value
,nvl(sp.id,ash.sql_plan_line_id) "Id"
,sp."Operation"
,sp.object_name "Name"
,sp.cardinality
,sp.cost
,ash.event
,ash.object_name
,nvl2(ash.cnt,'SQLDEV:GAUGE:0:'||sum(ash.cnt) over (partition by nvl(sp.plan_hash_value,ash.sql_plan_hash_value))
||':'||(sum(ash.cnt) over (partition by nvl(sp.plan_hash_value,ash.sql_plan_hash_value))/10)
||':'||(sum(ash.cnt) over (partition by nvl(sp.plan_hash_value,ash.sql_plan_hash_value))/3)||':'
||ash.cnt,null) as activity
,ash.cnt
,nvl2(ash.cnt,round(100*ash.cnt/sum(ash.cnt) over (partition by nvl(sp.plan_hash_value,ash.sql_plan_hash_value)),1)||'%',null) activity_pct
,ash.cnt_distinct_samples
from sp
full join ash
on sp.plan_hash_value = ash.sql_plan_hash_value
and sp.id = ash.sql_plan_line_id
and sp.sql_id = ash.sql_id
order by nvl(ash.sql_plan_hash_value,sp.plan_hash_value), nvl(sp.id,ash.sql_plan_line_id), ash.cnt
)
order by plan_hash_value, "Id", cnt]]></sql>
<binds>
<bind id="SQL_ID">
<prompt><![CDATA[SQL_ID]]></prompt>
<tooltip><![CDATA[SQL_ID]]></tooltip>
<value><![CDATA[9sq8x12p5435f]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
</binds>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="null" author="null" subject="null" keywords="null" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="NONE" zip="false" />
</column>
<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>
null </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
</display>
</folder>
</displays>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment