Oracle Scratchpad

January 28, 2022

Performance catalogue

Filed under: Oracle — Jonathan Lewis @ 4:52 pm GMT Jan 28,2022

This is a list of articles that discuss design ideas that can have a big impact on performance. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

 

 

Bug Catalogue

Filed under: Oracle — Jonathan Lewis @ 4:42 pm GMT Jan 28,2022

This is a list of all articles I’ve written that describe bugs. Most of them are version specific and many of them will have been fixed by the latest versions of Oracle so its likely to be only the first few that might be of interestg. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

 

Transformations Catalogue

Filed under: Oracle — Jonathan Lewis @ 2:50 pm GMT Jan 28,2022

This is a list of all articles I’ve written that talk describe details of optimizer transformations. Many of these articles will have been listed in the Execution Plans catalogue and Optimizer Catalogue. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

Execution Plans Catalogue

Filed under: Oracle — Jonathan Lewis @ 2:47 pm GMT Jan 28,2022

This is a list of all articles I’ve written discussing execution plans. Each entry has a date stamp and a short note of the contents. The articles are (may be / will be) grouped in a couple of different categories – “long case studies” and “short notes” are a couple of obvious classes –  but otherwise are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

 

Quiz Catalogue

Filed under: Oracle — Jonathan Lewis @ 2:45 pm GMT Jan 28,2022

This is a list of all “quiz night” / “quiz time” articles I’ve written. Most of them will appear in some other catalogue since they were intended to highlight some little detail of how Oracle behaves. The articles are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

 

Partitioning Catalogue

Filed under: Oracle — Jonathan Lewis @ 2:41 pm GMT Jan 28,2022

This is a list of all articles I’ve written about partitioning. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

Fragmentation catalogue

Filed under: Oracle — Jonathan Lewis @ 2:40 pm GMT Jan 28,2022

This is a list of all articles I’ve written that discuss “fragmentation”, space wastage and rebuilds. Each entry has a date stamp and a short note of the contents. The articles are (may be / will be) grouped in a couple of different categories, but otherwise are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

Block Size catalogue

Filed under: Oracle — Jonathan Lewis @ 2:39 pm GMT Jan 28,2022

This is a list of all articles I’ve written that discuess database block sizing. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

Hinting Catalogue

Filed under: Oracle — Jonathan Lewis @ 2:38 pm GMT Jan 28,2022

This is a list of all articles I’ve written that talk about hints. Each entry has a date stamp and a short note of the contents. The articles are (may be / will be) grouped in a couple of different categories, but otherwise are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

Parallel Execution Catalogue

Filed under: Oracle — Jonathan Lewis @ 2:37 pm GMT Jan 28,2022

This is a list of all articles I’ve written that talk about the mechanisms and diagnostics of parallel execution. Each entry has a date stamp and a short note of the contents. The articles are (may be / will be) grouped in a couple of different categories, but otherwise are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

LOB Catalogue

Filed under: Oracle — Jonathan Lewis @ 2:35 pm GMT Jan 28,2022

This is a list of all articles I’ve written that talk about LOB handling. Each entry has a date stamp and a short note of the contents. The articles will generally be listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

 

Optimizer catalogue

Filed under: Oracle — Jonathan Lewis @ 2:28 pm GMT Jan 28,2022

This is a list of all articles I’ve written that talk about the mechanisms the optimizer can choose. Each entry has a date stamp and a short note of the contents. The articles are (may be / will be) grouped in a couple of different categories, but otherwise are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

 

 

January 27, 2022

Exadata Catalogue

Filed under: Oracle — Jonathan Lewis @ 6:18 pm GMT Jan 27,2022

This is a list of all articles I’ve written that talk about Exadata, with a date stamp and short note of the contents. The articles are listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

Statistics catalogue

Filed under: Oracle — Jonathan Lewis @ 6:16 pm GMT Jan 27,2022

This is a list of all articles I’ve written that talk about statistics, with a date stamp and short note of the contents. The articles are (will be) grouped in a couple of different categories, but otherwise are listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

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 as a hint; 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 executabl 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.

Next Page »

Website Powered by WordPress.com.