This was first published on https://blog.dbi-services.com/can-you-have-pending-system-statistics (2015-06-12)
Republishing here for new followers. The content is related to the the versions available at the publication date
Your system statistics seems to be wrong and you want to gather or set more relevant ones. But you don’t want to see all your application execution plans changing between nested loops and hash joins. For object statistics, we can gather statistics in a pending mode, test them in a few sessions, and publish them when we are ok with them. But for system statistics, can you do the same? It can be risky to try it, so I’ve done it for you in my lab.
SQL> select banner from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production SQL> create table DEMO as select rownum id , ora_hash(rownum,10) a , ora_hash(rownum,10) b , lpad('x',650,'x') c from xmltable('1 to 100000'); Table created.
Here are my system statistics:
SQL> select '' savtime,sname,pname,pval1,pval2 from sys.aux_stats$ where pval1 is not null or pval2 is not null order by 1,2 desc,3; SAVTIME SNAME PNAME PVAL1 PVAL2 -------------------- ---------------- ---------- ---------- -------------------- SYSSTATS_MAIN CPUSPEEDNW 2719 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_INFO DSTART 06-10-2015 08:11 SYSSTATS_INFO DSTOP 06-10-2015 08:11 SYSSTATS_INFO FLAGS 0 SYSSTATS_INFO STATUS COMPLETED
I check a full table scan cost:
SQL> set autotrace trace explain SQL> select * from DEMO DEMO1; Execution Plan ---------------------------------------------------------- Plan hash value: 4000794843 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 88550 | 30M| 2752 (1)| 00:00:34 | | 1 | TABLE ACCESS FULL| DEMO | 88550 | 30M| 2752 (1)| 00:00:34 | --------------------------------------------------------------------------
No surprise here. I’ve 10000 blocks in my tables, SREATDIM= IOSEEKTIM + db_block_size / IOTFRSPEED= 12 ms and MREADTIM= IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED = 26 ms. Then the cost based on a MBRC of 8 is ( 26 * 10000 / 8 ) / 12 = 2700
I set ‘PUBLISH’ to false in order to have pending statistics:
SQL> exec dbms_stats.SET_GLOBAL_PREFS('PUBLISH', 'FALSE') ; PL/SQL procedure successfully completed.
Then I set some system statistics manually to simulate a fast storage:
17:14:38 SQL> exec dbms_stats.set_system_stats('IOSEEKTIM',1); PL/SQL procedure successfully completed. 17:14:38 SQL> exec dbms_stats.set_system_stats('IOTFRSPEED','204800'); PL/SQL procedure successfully completed.
and I run the same explain plan:
Execution Plan ---------------------------------------------------------- Plan hash value: 4000794843 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 88550 | 30M| 1643 (2)| 00:00:02 | | 1 | TABLE ACCESS FULL| DEMO | 88550 | 30M| 1643 (2)| 00:00:02 | --------------------------------------------------------------------------
The cost is better. I’m not using pending statistics, which means that the published stats have been changed – despie the PUBLISH global preference set to FALSE:
SQL> select '' savtime,sname,pname,pval1,pval2 from sys.aux_stats$ where pval1 is not null or pval2 i s not null order by 1,2 desc,3; SAVTIME SNAME PNAME PVAL1 PVAL2 -------------------- ---------------- ---------- ---------- -------------------- SYSSTATS_MAIN CPUSPEEDNW 2719 SYSSTATS_MAIN IOSEEKTIM 1 SYSSTATS_MAIN IOTFRSPEED 204800 SYSSTATS_INFO DSTART 06-10-2015 08:14 SYSSTATS_INFO DSTOP 06-10-2015 08:14 SYSSTATS_INFO FLAGS 1 SYSSTATS_INFO STATUS COMPLETED
As you see, the SYS.AUX_STATS$ show my modified values (note that the date/time did not change by the way). So be careful, when you set or gather or delete system statistics in 11g you don’t have the pending/publish mechanism. It’s the kind of change that may have a wide impact changing all your execution plans.
With the values I’ve set the SREADTIM is near 1 ms and MREADTIM is about 1.3 ms so the cost is ( 1.3 * 10000 / 8 ) / 1 = 1625 which is roughly what has been calculated by the CBO on my new not-so-pending statistics.
If you look at 12c you will see new procedures in dbms_stats which suggest that you can have pending system statistics:
SQL> select banner from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production SQL> select procedure_name from dba_procedures where object_name='DBMS_STATS' and procedure_name like '%PENDIN G_SYSTEM_STATS'; PROCEDURE_NAME -------------------------------------------- DELETE_PENDING_SYSTEM_STATS EXPORT_PENDING_SYSTEM_STATS PUBLISH_PENDING_SYSTEM_STATS
but be careful, they are not documented. Let’s try it anyway. I start as I did above, with a demo table and default statistics:
SQL> select '' savtime,sname,pname,pval1,pval2 from sys.aux_stats$ where pval1 is not null or pval2 is not nul l order by 1,2 desc,3; SAVTIME SNAME PNAME PVAL1 PVAL2 -------------------- ---------------- ---------- ---------- -------------------- SYSSTATS_MAIN CPUSPEEDNW 2725 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_INFO DSTART 06-10-2015 17:25 SYSSTATS_INFO DSTOP 06-10-2015 17:25 SYSSTATS_INFO FLAGS 0 SYSSTATS_INFO STATUS COMPLETED SQL> set autotrace trace explain SQL> select * from DEMO DEMO1; Execution Plan ---------------------------------------------------------- Plan hash value: 4000794843 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 80500 | 28M| 2752 (1)| 00:00:01 | | 1 | TABLE ACCESS FULL| DEMO | 80500 | 28M| 2752 (1)| 00:00:01 | --------------------------------------------------------------------------
I set PUBLISH to false and set manual system stats:
SQL> exec dbms_stats.SET_GLOBAL_PREFS('PUBLISH', 'FALSE') ; PL/SQL procedure successfully completed. SQL> exec dbms_stats.set_system_stats('IOSEEKTIM',1); PL/SQL procedure successfully completed. SQL> exec dbms_stats.set_system_stats('IOTFRSPEED','204800'); PL/SQL procedure successfully completed.
and I check the SYS.AUX_STATS$ table:
SQL> select '' savtime,sname,pname,pval1,pval2 from sys.aux_stats$ where pval1 is not null or pval2 is not nul l order by 1,2 desc,3; SAVTIME SNAME PNAME PVAL1 PVAL2 -------------------- ---------------- ---------- ---------- -------------------- SYSSTATS_MAIN CPUSPEEDNW 2725 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_INFO DSTART 06-10-2015 17:25 SYSSTATS_INFO DSTOP 06-10-2015 17:25 SYSSTATS_INFO FLAGS 0 SYSSTATS_INFO STATUS COMPLETED
Good ! I still have the previous values here. The new stats have not been published.
The pending stats are stored in the history table, with a date in the future:
SQL> select savtime,sname,pname,pval1,pval2 from sys.wri$_optstat_aux_history where pval1 is not null or pval2 is not null and savtime>sysdate-30/24/60/60 order by 1,2 desc,3; SAVTIME SNAME PNAME PVAL1 PVAL2 -------------------- ---------------- ---------- ---------- -------------------- 01-dec-3000 01:00:00 SYSSTATS_MAIN CPUSPEEDNW 2725 01-dec-3000 01:00:00 SYSSTATS_MAIN IOSEEKTIM 10 01-dec-3000 01:00:00 SYSSTATS_MAIN IOTFRSPEED 204800 01-dec-3000 01:00:00 SYSSTATS_INFO DSTART 06-10-2015 17:29 01-dec-3000 01:00:00 SYSSTATS_INFO DSTOP 06-10-2015 17:29 01-dec-3000 01:00:00 SYSSTATS_INFO FLAGS 1 01-dec-3000 01:00:00 SYSSTATS_INFO STATUS COMPLETED
That’s perfect. It seems that I can gather system statistics without publishing them. And I don’t care about the Y3K bug yet.
First, I’ll check that a session can use the pending stats if chosen explicitly:
SQL> alter session set optimizer_use_pending_statistics=true; Session altered.
the I run the query:
SQL> set autotrace trace explain SQL> select * from DEMO DEMO2; Execution Plan ---------------------------------------------------------- Plan hash value: 4000794843 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 80500 | 28M| 1308 (1)| 00:00:01 | | 1 | TABLE ACCESS FULL| DEMO | 80500 | 28M| 1308 (1)| 00:00:01 | --------------------------------------------------------------------------
Cost is lower. This is exacly what I expected with my new – unpublished – statistics. Good. I don’t know what it’s lower than in 11g. Maybe the formula has changed. This is another place for comments
Ok I checked that the published statistics are the same as before, but let’s try to use them:
SQL> alter session set optimizer_use_pending_statistics=false; Session altered.
and once again run the same query:
SQL> set autotrace trace explain SQL> select * from DEMO DEMO3; Execution Plan ---------------------------------------------------------- Plan hash value: 4000794843 ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 80500 | 28M| 1541 | | 1 | TABLE ACCESS FULL| DEMO | 80500 | 28M| 1541 | ---------------------------------------------------------- Note ----- - cpu costing is off (consider enabling it)
Oh. There is a problem here. ‘cpu costing is off’ means that there are no system statistics. The cost has been calculated as it were in old versions whithout system statistics. This is bad. I have gathered pending statistics, not published, but all sessions have their costing changed now.
Just a look at the 10053 trace show that I have a problem:
----------------------------- SYSTEM STATISTICS INFORMATION ----------------------------- System Stats are INVALID. ... Table: DEMO Alias: DEMO3 Card: Original: 80500.000000 Rounded: 80500 Computed: 80500.000000 Non Adjusted: 80500.000000 Scan IO Cost (Disk) = 1541.000000 Scan CPU Cost (Disk) = 0.000000 Total Scan IO Cost = 1541.000000 (scan (Disk)) = 1541.000000 Total Scan CPU Cost = 0.000000 (scan (Disk)) = 0.000000 Access Path: TableScan Cost: 1541.000000 Resp: 1541.000000 Degree: 0 Cost_io: 1541.000000 Cost_cpu: 0 Resp_io: 1541.000000 Resp_cpu: 0 Best:: AccessPath: TableScan Cost: 1541.000000 Degree: 1 Resp: 1541.000000 Card: 80500.000000 Bytes: 0.000000
It seems that with pending statistics the optimizer can’t simply get the published values, and falls back as if there were no system statistics. This is a bug obviously. I’ve not used the undocumented new functions. They were used in the background, but it’s totally supported to set PUBLISH to FALSE and the gather system statistics. The behavior should be either the same as in 11g – publishing the gathered stats – or gathering into pending stats only and session continue to use the published ones by default.
In 11g, be careful, system statistic changes are always published.
In 12c, don’t gather system statistics when PUBLISH is set to false. We can expect that nice new feature in further versions, but for the moment it messes up everything. I’ll not open an SR yet but hope it’ll be fixed in future versions.
Further investigations done by Stefan Koehler on this twitter conversation:
My latest blog post is about pending system statistics. https://t.co/yJ0NSfmS6g
— Franck Pachot (@FranckPachot) June 10, 2015
I’ve opened a SR for that and, thanks to the kind support engineer who reproduced the issue, A bug has been opened: Bug 21326597 : 12C PENDING SYSTEM STATS ARE NOT BEHAVING AS EXPECTED. One more reason I blog: it’s easier to post the tstcase on a blog and link to it from MOS as MOS do not have monospaced fonts.