changes in SQL real time monitor report in 23c free

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:

Here the report misses Activity and Metrics. 

23c free: 

This report misses Activity, Metrics and Outlines - compared to 19c. 

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:

Activity, Metrics and Outlines are back and new content is visible also: SQL Analysis. 

This contains of 2 sections: 

SQL Analysis

Line ID	Query Block	Message
1	SEL$43	The query block has 2 cartesian products which may be expensive. Consider adding join conditions or removing the disconnected tables or views.
13	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#"
45	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#"
77	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>

Hint Usage Issues

Line ID	Query Block	Hint	Hint Status
2	SEL$1	materialize	Applied Successfully
34	SEL$15	materialize	Applied Successfully
66	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.

SQL Real Time Monitoring pure HTML report (thanks to SQLDeveloper 18.3)

Yesterday (from the writing of this post perspective) SQLDeveloper 18.3 came out.
(it seems SQL Developer does not strict follow Oracle Database Release Number schema, otherwise it must be 18.4 already as it's released in 4th quarter of 2018)

There are many bugs fixed on 18.3 and some nice enhancements there as well.

My favorite enhancement is Real Time SQL Monitoring, HTML exports now available - no flash!

As Tanel Poder wrote about it already (Generate Oracle SQL Monitoring Reports as HTML using SQL Developer v18.3 (no Flash needed)) I'll show how you can use it without SQL Developer.
I'm not against SQL Developer, in fact I'm very happy with it!
Just the Real Time SQL Monitor tab seems kind of unresponsive to me, especially on DBs with many active sessions and high load.


So if I don't want to watch a java process drawing circles and showing blur previews, I first go to query v$sql_monitor. Beside all the columns I'll need to identify my SQL of interest, I need the columns SQL_ID, SQL_EXEC_ID, and SQL_EXEC_START.
And, of course at least once you must create a new HTML Report with SQL Developer! Save this report as a template for later use.

When you open the HTML report in an editor, there are 2 lines of special interest:

 var data_sqlId = '3v64dcg0rja6k'; 
 var data_xml = '<report db_version="12.1.0.2.0...>"

You can replace them easily with the context of your SQL of interest.
To get the proper XML you can use DBMS_SQLTUNE.report_sql_monitor. But as this function provides a multiline XML, but the HTML expects the XML in one line, the call should be

SELECT XMLSerialize( DOCUMENT xmltype(DBMS_SQLTUNE.report_sql_monitor(
  sql_id         => '3v64dcg0rja6k',
  type           => 'XML',
  SQL_EXEC_ID    => 33554648, 
  SQL_EXEC_START => to_date('2018-10-10 05:36:17'),
  report_level   => 'ALL')) NO INDENT ) AS XML_report
FROM dual;

If you omit SQL_EXEC_ID or SQL_EXEC_START, the report will still compile but you should understand the difference in data you get displayed.

So the minimal count you create a new HTML-Only SQL Real Time Monitoring report with SQL Developer should be once. But if you read this ( and your DBs are properly licensed), spread the good news with all the people who might benefit from these reports!

---

Please keep in mind that the HTML file is loading CSS and JS files from and URL at Oracles Content Delivery platform - so they are subject to any change without a new release of SQL Developer, RDBMS or anything. So expect unexpected changes every time!

checking suspicious bind variables in v$sql_monitor

In my current company we try to stabilize the performance of our Peoplesoft application. So on a more or less regular base I get a call to investigate as "it" is "slow" now.
During my research I found one error-pattern I'd like to show here: SQLs which where parsed for very selective BINDs (like customer-id) are executed with a single space (" ") as bind.
Unfortunately in Peoplesoft this character is used similar to NULL - so when it doesn't know what value to store, a space is used.
In general such BINDs lead to improper NESTED LOOPs.
Every time I found such executions, they where accepted as defect by development.

I'm in the lucky position to have tuning pack licensed, so I can use real time sql monitoring. That's very handy as I can save the a SQL in question together with a lot of infos (client, machine, module, start-time, BINDs, ...) in a html file and save it for later analysis/forward.

As v$sql_monitor shows specific executions, and it has all the BINDs stored as well, I can use it now to find other suspicious statements. So this little statement gives me some infos where to look closer:

SELECT sql_id, 
       name, 
       '--' || value || '--' 
FROM   (SELECT KEY, 
               sql_id, 
               xmltype.Createxml(binds_xml) confval 
        FROM   v$sql_monitor) v, 
       XMLTABLE('/binds/bind' passing v.confval 
                COLUMNS name  VARCHAR2(25)   path '@name', 
                        value VARCHAR2(4000) path '.'      ) 
WHERE  value = ' ' 
        OR value IS NULL; 

with an output like this:

SQL_ID        NAME                      VALUE
------------- ------------------------- -----
63pw6bqt84793 :2                        -- -- 

Please do NOT start with this statement when your application is "slow" - not even when it's Peoplesoft.