By Franck Pachot
.
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.
Test case in 11g
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
Pending stats in 11g
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.
12c
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.
12c use pending stats = true
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 😉
12c use pending stats = false
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.
10053
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.
Conclusion
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.
update
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
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)