Oracle Scratchpad

January 26, 2022

system_stats() hint

Filed under: 18c,19c,Hints,Oracle,Statistics,System Stats — Jonathan Lewis @ 9:46 am GMT Jan 26,2022

Starting from 18.3 Oracle introduced the system_stats() hint, apparently allowing you to set the system statistics for the duration of a query. However the hint didn’t seem to have any effect in that version of Oracle – even though the fix_control that seemed to be the most relevant (QKSFM_DBMS_STATS_24952618) was set to 1, so maybe the hint was acting strictly according to the fix control description, which was: “turn on Exadata stats: MBRC,IOTFRSPEED,IOSEEKTIME” (or maybe the fix control had nothing to do with the hint)

According to my notes I had a test that showed it working on live SQL, which (in my notes) I said was running 19.2 at the time; however, I can’t get it to work on 19.11.0.0 or 21.3.0.0 on a Linux VM (or on the current Live SQL version) despite a load of fiddling with potentially relevant hidden parameters, fix controls, and numeric event numbers. So maybe it is only for Exadata.

It’s not documented, of course, but I’m fairly confident I’m using the correct syntax – which was quite easy to find (sometimes you get lucky) because a search through the binary for the hint text produced a perfect result:


[oracle@linux183 bin]$ strings -a oracle | grep -T -n  -i system_stats\(
1762556:BEGIN :1 := dbms_stats_internal.store_system_stats(:2, :3, :4); END;
1787190:system_stats(mbrc=%f ioseektim=%f iotfrspeed=%f)

So it would seem (from line 1787190) that we can override three of the system statistics: mbrc, ioseektim, and iotfrspeed. Thanks to the hint_report option that 19c introduced to dispay_xxxxxx() calls in dbms_xplan it’s easy to see that this syntax is correct but unused. From a call to dbms_xplan.display_cursor() in 19.11.0.0:

select  /*+ system_stats(mbrc=128 ioseektim=1 iotfrspeed=262144) */ count(*) from t1

Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  2732 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 50000 |  2732   (1)| 00:00:01 |
-------------------------------------------------------------------

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   0 -  STATEMENT
         U -  system_stats(mbrc=128 ioseektim=1 iotfrspeed=262144)

Other tests reported shorter versions of the hint (e.g. /*+ system_stats(mbrc=128) */ ) as errors:


Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
   1 -  SEL$1
         E -  system_stats

In passing, it’s interesting to note that the text was reported as a “query block” hint (sel$1) when it had a syntax error despite being a “statement-level” hint when it was recognised. Presumably the generic parsing rule is: “it’s a query block hint unless proved otherwise”.

The call to dbms_stat_internal.store_system_stats() that also dropped out of the scan of the executable looks as if it’s the function that sets the “noworkload” statistics – the three parameters are, in order: ioseektim, iotfrspeed, cpuspeednw – but possibly it’s the internal call used when you use the ‘EXADATA’ option for gathering system stats.

Bottom line:

Maybe there’s a way to switch this hint on to override the default system stats; maybe it just needs to be run on Exadata; and maybe – if it can be switched on – it could be attached as an SQL_Patch.  Experimentation left to readers who have access to an Exadata system, any results are welcome.

10 Comments »

  1. Yes, reported as used on Exadata

    Comment by Dom Brooks — January 26, 2022 @ 12:32 pm GMT Jan 26,2022 | Reply

  2. […] system_stats() hint – Jan 2022: notes on a hint that allows you to set the “noworkload” system statistics in a hint on Exadata. […]

    Pingback by Statistics catalogue | Oracle Scratchpad — January 27, 2022 @ 6:16 pm GMT Jan 27,2022 | Reply

  3. […] system_stats() hint – Jan 2022: notes on a hint that allows you to set the “noworkload” system statistics in a hint on Exadata. […]

    Pingback by Exadata Catalogue | Oracle Scratchpad — January 27, 2022 @ 6:18 pm GMT Jan 27,2022 | Reply

  4. Oracle recommends not using system statistics. Why would I want to use it as a hint?

    Comment by mgogala — January 27, 2022 @ 10:34 pm GMT Jan 27,2022 | Reply

    • Mladen,

      Thanks for the comment.

      I think the mirror to that question is; “If Oracle doesn’t want us to use system stats why have they (recently) introduced a hint for it?” If there’s a good answer to that question I’d be interested to hear it. I have to say that if it does do what it seems to say then it’s not a trivial hint to use but, as with all hints, there are bound to be cases where using the hint helps the optimizer to pick a better plan without being given an explicit set of join methods etc.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — January 28, 2022 @ 4:02 pm GMT Jan 28,2022 | Reply

  5. Hi Jonathan,
    Just in case, Oracle Always Free Tier provides access to Exadata.

    Comment by Yuri — January 28, 2022 @ 6:27 am GMT Jan 28,2022 | Reply

    • Yuri,

      Thanks for the comment.

      I did sign up to a free tier some time ago, but never got around to using it before the account lapsed. I may get onto it again some time, but with the limitations on going behind the scenes it’s not really very tempting at present.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — January 28, 2022 @ 4:04 pm GMT Jan 28,2022 | Reply

  6. […] System_stats() hint for Exadata (Jan 2022) […]

    Pingback by Hinting Catalogue | Oracle Scratchpad — January 28, 2022 @ 3:55 pm GMT Jan 28,2022 | Reply

  7. […] System_stats() hint (Jan 2022) – for Exadata, the optimizer can be told to override the stored system stats. […]

    Pingback by opt_estimate catalogue | Oracle Scratchpad — February 19, 2022 @ 10:53 am GMT Feb 19,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: