I like Oracle SQL real time monitor reports as they contain a huge amount of valuable information and can be viewed and transferred very simple.
It's content increased over time - and as oracle updates the java script libraries to generate a nice view, sometimes the same report reveals even more details when you open the same old report later! So it was a kind of downer when I saw the amount of content decreasing since 19c! Here some screenshots what changed:
19c:
21c:
23c free:
That's quite sad!
And this information is really missing in the html file - so we can't blame the java script library for this.
The data is stored as a XML structure in the html report. But to be readable for humans, we need to decode it first - at least I can't read this directly:
<script id="fxtmodel" type="text/xml">
<!--FXTMODEL-->
<report db_version="19.0.0.0.0" elapsed_time="3.23" cpu_time="3.21" cpu_cores="4" hyperthread="N" con_id="3" con_name="PDB1" timezone_offset="0" packs="2" service_type="0" encode="base64" compress="zlib">
<report_id><![CDATA[/orarep/sqlmonitor/main%3fsql_exec_id%3d16777216%26sql_exec_start%3d09%3a23%3a2023%2012%3a42%3a10%26sql_id%3d2u4jc5hhwhc7n]]></report_id>
eAHtffl348aR8O/+K/AxTnZmP9siDl6OxLc6OI4cHWMdSbz79vFRFCTRpkiZBOfI
X79VfaAPNIAGCFIaDbz7MiLQ6Kvu6qrq3eUf0+HjfDaJ5ovhInyaLyLnQ7hYTuaz
vUbwQ7PhLD8vb0dRuNdo9nY8f8drer7jej8G3o9ut9H/xnF26WfDp9Fi9BhG8DE+
hefY9eS2762C38ath4ePD+PObHeHPRVNwk/hGNu57U6n47lt2oQ/1doto9Ei6ien
...
Luckily it claims what it is doing: encode="base64" compress="zlib" And so Franck Pachot already cared about a way to decode it.
I slightly adapted it to my laptops needs:
xmllint --xpath '/html/body/script/report' 19c.html | grep -v report | base64 -id | \
python3 -c "import zlib,sys;sys.stdout.buffer.write(zlib.decompress(sys.stdin.buffer.read()))" > 19c.xml
The missing sections in 21c and 23cfree are
Activity:
<activity_sampled>
<activity class="Cpu">1</activity>
</activity_sampled>
<activity_detail start_time="09/23/2023 12:42:10" end_time="09/23/2023 12:42:19" first_sample_time="09/23/2023 12:42:10" last_sample_time="09/23/2023 12:42:10" duration="1" sample_interval="1" bucket_interval="1" bucket_count="10" bucket_duration="10" cpu_cores="4" total_cpu_cores="4" hyperthread="N">
<bucket number="1">
<activity class="Cpu" line="77">1</activity>
</bucket>
</activity_detail>
Metrics:
<stattype name="metrics" cpu_cores="4" hyperthread="N">
<stat_info>
<stat id="1" name="nb_cpu"/>
<stat id="2" name="nb_sess"/>
<stat id="3" name="reads" unit="per_sec"/>
<stat id="4" name="writes" unit="per_sec"/>
<stat id="5" name="read_kb" unit="bytes_per_sec" factor="1024"/>
<stat id="6" name="write_kb" unit="bytes_per_sec" factor="1024"/>
<stat id="7" name="interc_kb" unit="bytes_per_sec" factor="1024"/>
<stat id="8" name="cache_kb" unit="bytes_per_sec" factor="1024"/>
<stat id="9" name="pga_kb" unit="bytes" factor="1024"/>
<stat id="10" name="tmp_kb" unit="bytes" factor="1024"/>
</stat_info>
<buckets bucket_interval="1" bucket_count="10" start_time="09/23/2023 12:42:10" end_time="09/23/2023 12:42:19" duration="10">
<bucket bucket_id="1">
<stat id="1" value=".26"/>
<stat id="3" value="494"/>
<stat id="5" value="4008"/>
<stat id="7" value="4008"/>
<stat id="8" value="154224"/>
<stat id="9" value="12586"/>
</bucket>
</buckets>
</stattype>
Outlines:
<outline_data>
<hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint>
<hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint>
<hint><![CDATA[DB_VERSION('19.1.0')]]></hint>
<hint><![CDATA[ALL_ROWS]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$3")]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$4")]]></hint>
<hint><![CDATA[OUTLINE_LEAF(@"SEL$11")]]></hint>
...
It would be a quite depressing post when the story ends here.
But there is hope:
In 23c_free Oracle introduced new parameters for _fix_control.
This one is really helpful:
| 35069142 | 1 | QKSFM_CBO_35069142 | use ADVANCED format for plan display in SQL Monitor |
With a simple
alter session set "_fix_control"='35069142:1';
My Report shows much more content now:
This contains of 2 sections:
Line ID | Query Block | Message |
|---|---|---|
| SEL$43 | The query block has 2 cartesian products which may be expensive. Consider adding join conditions or removing the disconnected tables or views. | |
| SEL$E029B2FF / "O"@"SEL$5" | The following columns have predicates which preclude their use as keys in index range scan. Consider rewriting the predicates. "NAME" "TYPE#" | |
| SEL$D0BBB93D / "O"@"SEL$19" | The following columns have predicates which preclude their use as keys in index range scan. Consider rewriting the predicates. "NAME" "TYPE#" | |
| SEL$B2BBC534 / "O"@"SEL$33" | The following columns have predicates which preclude their use as keys in index range scan. Consider rewriting the predicates. "NAME" "TYPE#" |
selected
which comes from the XML section
<sqla>
<sec id="1">
<n><![CDATA[SEL$43]]></n>
<msg>
<m li="1"><![CDATA[The query block has 2 cartesian products which may be ]]></m>
<m li="2"><![CDATA[expensive. Consider adding join conditions or removing the ]]></m>
<m li="3"><![CDATA[disconnected tables or views.]]></m>
</msg>
</sec>
<sec id="13">
<n><![CDATA[SEL$E029B2FF / "O"@"SEL$5"]]></n>
<msg>
<m li="1"><![CDATA[The following columns have predicates which preclude their ]]></m>
<m li="2"><![CDATA[use as keys in index range scan. Consider rewriting the ]]></m>
<m li="3"><![CDATA[predicates.]]></m>
<m li="4" list="Y"><![CDATA["NAME"]]></m>
<m li="5" list="Y"><![CDATA["TYPE#"]]></m>
</msg>
</sec>
<sec id="45">
<n><![CDATA[SEL$D0BBB93D / "O"@"SEL$19"]]></n>
<msg>
<m li="1"><![CDATA[The following columns have predicates which preclude their ]]></m>
<m li="2"><![CDATA[use as keys in index range scan. Consider rewriting the ]]></m>
<m li="3"><![CDATA[predicates.]]></m>
<m li="4" list="Y"><![CDATA["NAME"]]></m>
<m li="5" list="Y"><![CDATA["TYPE#"]]></m>
</msg>
</sec>
<sec id="77">
<n><![CDATA[SEL$B2BBC534 / "O"@"SEL$33"]]></n>
<msg>
<m li="1"><![CDATA[The following columns have predicates which preclude their ]]></m>
<m li="2"><![CDATA[use as keys in index range scan. Consider rewriting the ]]></m>
<m li="3"><![CDATA[predicates.]]></m>
<m li="4" list="Y"><![CDATA["NAME"]]></m>
<m li="5" list="Y"><![CDATA["TYPE#"]]></m>
</msg>
</sec>
</sqla>
Line ID | Query Block | Hint | Hint Status |
|---|---|---|---|
| SEL$1 | materialize | Applied Successfully | |
| SEL$15 | materialize | Applied Successfully | |
| SEL$29 | materialize | Applied Successfully |
with the XML
<hreport>
<leg>
<t>3</t>
</leg>
<sec id="2">
<n><![CDATA[SEL$1]]></n>
<h><![CDATA[materialize]]></h>
</sec>
<sec id="34">
<n><![CDATA[SEL$15]]></n>
<h><![CDATA[materialize]]></h>
</sec>
<sec id="66">
<n><![CDATA[SEL$29]]></n>
<h><![CDATA[materialize]]></h>
</sec>
</hreport>
We will see how 35069142 evolves over time.
