INDEX | Neil Chandler's DB Blog

Oracle Optimizer System Statistics

03/02/2021 3 Comments

System Statistics Are a Little Complex

Oh System statistics! Should we gather them? Should we not?
What do they mean? What are they doing to the optimizer?

[tl;dr – in almost all cases, DON’T GATHER SYSTEM STATISTICS. DON’T SET MBRC. LET THEM DEFAULT!]

First you need to be armed with a piece of information. When Oracle optimizes your SQL, it produces a COST to compare each SQL execution plan. Exactly what is this COST? It’s not seconds. It’s not quite the amount of I/O required. It’s not an arbitrary figure either. Oracle Optimizer Cost is the cost of getting your data in terms of SINGLE BLOCK READS. Remember this for later.

Lets have a look at the system stats defaults :

SELECT sname,pname,pval1 FROM SYS.AUX_STATS$ ORDER BY 1,2; SNAME PNAME PVAL1 -------------------- ------------------------------ ---------- SYSSTATS_INFO DSTART SYSSTATS_INFO DSTOP SYSSTATS_INFO FLAGS 0 SYSSTATS_INFO STATUS SYSSTATS_MAIN CPUSPEED SYSSTATS_MAIN CPUSPEEDNW 2911 (this will vary) SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN MAXTHR SYSSTATS_MAIN MBRC SYSSTATS_MAIN MREADTIM SYSSTATS_MAIN SLAVETHR SYSSTATS_MAIN SREADTIM

What we are looking for here is the the 3 metrics highlighted in colour. As we can see, the are not set. By default, we need to calculate those, or know where to find the defaults for those values to get to them. Once we have those 3 metrics, we can calculate the RATIO used by the optimizer to convert MULTIBLOCK READ into a cost metric of SINGLE BLOCK READS.

The CPU speed is used to calculate the COST of the query in terms of CPU required. This is small percentage of the query cost but it may help decide which plan is chosen. This is also converted from CPU cost into a cost metric of SINGLE BLOCK READS.

Lets get the values for MBRC, MREADTIM and SREADTIM.

MBRC is easy. If it is not explicitly set, it uses the init.ora parameter “db_file_multiblock_read_count” and uses that. However, by default (and also my recommendation) this should not be set, meaning Oracle will use the hidden parameter “_db_file_optimizer_read_count” to cost your queries. This defaults to 8. [note: this is not the value used in execution. Oracle attempts to do 1MB reads, and uses the value in “_db_file_exec_read_count” to control the multiblock reads at execution time. For an 8K block size, this is set to 128.

SREADTIM and MREADTIM are calculations based upon information we now have:
SREADTIM = IOSEEKTIM + db_block_size / IOTFRSPEED = 10+(8192 /4096) = 12 MREADTIM = IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED = 10+(8192*8/4096) = 26

Right! Now we have more information and can calculate a ratio. The multi block cost-per-block. This will allow us to take the number of blocks in (for example) a table scan [DBA_TAB_STATISTICS.BLOCKS statistic] and covert it to the cost metric os SINGLE BLOCK READS, meaning we can compare (for example) a FULL TABLE SCAN’s I/O directly with the I/O required for an Index Range Scan and Table Lookup.

multi-block cost-per-block = 1/MBRC * MREADTIM/SREADTIM = 1/8 * 26/12 = 0.270833

If we pull some data from a 10053 trace, where I have a table scan of a table containing 1,000,000 blocks, we should see the 1,000,000 blocks being converted to 270,833 “single block read blocks” for COST purposes.

[10053] SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for COST_CHECK[COST_CHECK]
  SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE
  Table: COST_CHECK  Alias: COST_CHECK
    Card: Original: 1000000.000000  Rounded: 1000000  Computed: 1000000.000000  Non Adjusted:1000000.000000
  Scan IO  Cost (Disk) =   270835.000000
  Scan CPU Cost (Disk) =   7411440000.000001
  Total Scan IO  Cost  =   270835.000000 (scan (Disk))
                       =   270835.000000
  Total Scan CPU  Cost =   7411440000.000001 (scan (Disk))
                       =   7411440000.000001
  Access Path: TableScan
    Cost:  271041.492812  Resp: 271041.492812  Degree: 0
      Cost_io: 270835.000000  Cost_cpu: 7411440000
      Resp_io: 270835.000000  Resp_cpu: 7411440000
  Best:: AccessPath: TableScan
         Cost: 271041.492812  Degree: 1  Resp: 271041.492812  Card: 1000000.000000  Bytes: 0.000000

Well we were close! There’s a fudge factor in play here, but 270,835 is pretty much 270,833 🙂

We can also work out the CPU element of the COST.
Scan IO Cost (Disk) = 270835.000000
Cost: 271041.492812
so CPU cost must be 271041.492812 – 270835.000000 = 206.492812

Scan CPU Cost (Disk)=7411440000.000001 so doing the maths to convert to units of “single block read time”…

CPUSPEEDNW = 2,991 (Mhz, but we convert 2,991,000 Khz)
= 7,411,440,000 / 2,991,000 / SREADTIM ‭= ‭2,477.913741223671013039 / 12 ‭= 206.49281176863925108659311267135‬ cost of CPU in units of SREADTIM

So now you can see how your system statistics can fundamentally change the optimizer by changing the ratio of multiblock-reads to single block reads.

You want to play yourself and see what happens? Of course you do…

Here’s an excel spreadsheet where you can plug in your numbers and see what happens to the ratio between single and multi block reads to see how system stats influence the optimizer. By plugging in different numbers, you will see how complex the interactions are – so what’s the ideal numbers? Use the DEFAULTS in almost all cases** [WARNING – don’t go changing your system stats based on this statement. You might experience a significant amount of plan changes, some of which may be very very bad!]:

[warning – I wrote this is a few mins and it’s not very tested] system_statistics.xls Download


Also, this is my version of a script by Franck Pachot, and also based on work by Chris Antognini, which you can run against your system:

select pname,pval1,calculated,formula from sys.aux_stats$ where sname='SYSSTATS_MAIN'
model
reference sga on (
select 'Database Buffers' name,sum(bytes) value from v$sgastat where name in ('shared_io_pool','buffer_cache')
) dimension by (name) measures(value)
reference parameter on (
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_file_multiblock_read_count' and ismodified!='FALSE'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='sessions'
union all
select name,decode(type,3,to_number(value)) value from v$parameter where name='db_block_size'
union all
SELECT a.ksppinm name, to_number(b.ksppstvl) value FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND ksppinm like '_db_file_optimizer_read_count'
)
dimension by (name) measures(value)
partition by (sname) dimension by (pname) measures (pval1,pval2,cast(null as number) as calculated,cast(null as varchar2(120)) as formula) rules(
calculated['MBRC']=coalesce(pval1['MBRC'],parameter.value['db_file_multiblock_read_count'],parameter.value['_db_file_optimizer_read_count'],8),
calculated['MREADTIM']=coalesce(pval1['MREADTIM'],pval1['IOSEEKTIM'] + (parameter.value['db_block_size'] * calculated['MBRC'] ) / pval1['IOTFRSPEED']),
calculated['SREADTIM']=coalesce(pval1['SREADTIM'],pval1['IOSEEKTIM'] + parameter.value['db_block_size'] / pval1['IOTFRSPEED']),
calculated['_multi block Cost per block']=round(1/calculated['MBRC']*calculated['MREADTIM']/calculated['SREADTIM'],4),
calculated['_single block Cost per block']=1,
formula['MBRC']=case when pval1['MBRC'] is not null then 'MBRC' when parameter.value['db_file_multiblock_read_count'] is not null then 'db_file_multiblock_read_count' when parameter.value['_db_file_optimizer_read_count'] is not null then '_db_file_optimizer_read_count (db_file_multiblock_read_count not set, which is good!)' else '= not sure so used 8' end,
formula['MREADTIM']=case when pval1['MREADTIM'] is null then '= IOSEEKTIM + db_block_size * MBRC / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'*'||calculated['MBRC']||'/'||pval1['IOTFRSPEED']||')',
formula['SREADTIM']=case when pval1['SREADTIM'] is null then '= IOSEEKTIM + db_block_size / IOTFRSPEED' end||' = '||pval1['IOSEEKTIM']||'+('||parameter.value['db_block_size']||'/'||pval1['IOTFRSPEED']||')',
formula['_multi block Cost per block']='= 1/MBRC * MREADTIM/SREADTIM = 1/'||calculated['MBRC']||' * '||calculated['MREADTIM']||'/'||calculated['SREADTIM'],
calculated['_maximum mbrc']=sga.value['Database Buffers']/(parameter.value['db_block_size']*parameter.value['sessions']),
formula['_maximum mbrc']='= buffer cache blocks/sessions (small cache limiter) = ' || sga.value['Database Buffers']/parameter.value['db_block_size']||'/'||parameter.value['sessions'],
formula['_single block Cost per block']='relative to the multi blovk cost per block. Always 1!',
formula['CPUSPEED']='overrides CPUSPEEDNW when set',
formula['CPUSPEEDNW']='CPU speed Mhz - non workload',
formula['IOSEEKTIM']='IO seek time in ms',
formula['IOTFRSPEED']='IO transfer speed in KB/s',
formula['MAXTHR']='Maximum IO system throughput',
formula['SLAVETHR']='average parallel slave IO throughput'
) order by 1;

** there is a case for gathering ‘exadata’ system stats. Increasing the IOTRFSPEED to 200,000 and changing the MBRC to (probably) 128 or 64 will *really* change the ratios, forcing a lot of full table scans down onto the storage cells, instead of using mediocre indexes. This should be considered (and thoroughly tested) if you have a DW on a dedicated Exadata. From a little more on exadata stats, check this blog post

Filed under Administration, Performance and Tuning Tagged with aux_sttat$, cpuspeed, cpuspeednw, db_file_multi_block_read_count, INDEX, ioseektim, iotfrspeed, maxthr, mbrc, mreadtim, oracle, range scan, slavethr, sreadtim, statistics, stats, system statistics, v$parameter

Primarys Keys and their supporting indexes

29/01/2016 1 Comment

Sometimes things just happen which makes you realise that stuff you thought all experienced DBA’s knew about isn’t common knowledge. I suppose it’s a side effect of working with the same evolving product for over 25 years at dozens of clients, when the colleague you are talking to has a mere decade of experience at fewer companies (and therefore less diversity of challenge).

Today I noticed that a release was creating a table, then an index, and then adding a Primary Key based upon the index. Pretty normal, but the indexes were non-unique. Interesting. Is that valid? (yes) It is sensible? (under some circumstances, also yes). Is it wrong but nobody noticed until it got to me? Probably.

However, there’s more variables at play here than first meets the eye. The Primary Key is being enforced by a pre-existing non-unique index, rather than create its own index “dynamically” (which would be unique under most circumstances). So therefore we have a few questions to answer

Is the index being used optimal?
How do I know it’s using a pre-created index and the constraint did not create its own index?
What happens if I drop the PK constraint?

Scenario 1: create table, create constraint

You get a nice neat fast index enforcing the constraint. If you drop the constraint, the index will also get removed. This may or may not be what you want. You may need to put a new index back on the table…

Scenario 2: create table, create unique index, create constraint

You get a nice neat fast index enforcing the constraint. If you drop the constraint, the index does not get removed. This may or may not be what you want. The index which was left behind might cause a problem if you miss the fact it’s still around…

Scenario 3: create table, create non-unique index (same as constraint columns), create constraint

The index enforcing the constraint is slightly less efficient enforcing the constraint as it has to optimize to an index range scan, not a unique scan. However, if you drop the constraint you still have a non-unique index. This may be a very useful ability for bulk data loads with questionable data, although the use-cases are limited and tend to be Warehouse-centric.

If you have a deferrable constraint, you must have a non-unique index to enforce the constraint as there may temporarily be duplicate keys in the table part-way through the transaction.

Scenario 4: create table, create non-unique composite index, create constraint which uses leading columns of that index but not all of the columns

The index may be somewhat less efficient enforcing the constraint by having less rows per block and possibly more levels, but this inefficiency may help overall system efficiency by having to maintain less indexes – maintaining 2 indexes instead of 1 is a 100% overhead in index maintenance. The composite index cannot be a unique index.

Some worked example showing the above scenarios (ran on Oracle 12.1.0.2.0 in a PDB) :

NEIL @ ORCL01 > -- Scenario 1
NEIL @ ORCL01 > drop table neil;
Table dropped.
NEIL @ ORCL01 > create table neil (col_id number, col_txt varchar2(10 char));
Table created.
NEIL @ ORCL01 > alter table neil add constraint neil_pk primary key (col_id) ;
Table altered.
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name = 'NEIL';
INDEX_NAME           UNIQUENESS
-------------------- --------------------
NEIL_PK              UNIQUE
NEIL @ ORCL01 > insert into neil values (1,'A');
1 row created.
NEIL @ ORCL01 > insert into neil values (1,'B');
insert into neil values (1,'B')
*
ERROR at line 1:
ORA-00001: unique constraint (NEIL.NEIL_PK) violated
NEIL @ ORCL01 > commit;
Commit complete.
NEIL @ ORCL01 > alter table neil drop constraint neil_pk;
Table altered.
NEIL @ ORCL01 > -- And the index is gone...
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name = 'NEIL';
no rows selected

NEIL @ ORCL01 > -- Scenario 2
NEIL @ ORCL01 > drop table neil;
Table dropped.
NEIL @ ORCL01 > create table neil (col_id number, col_txt varchar2(10 char));
Table created.
NEIL @ ORCL01 > create unique index neil_i on neil(col_id);
Index created.
NEIL @ ORCL01 > alter table neil add constraint neil_pk primary key (col_id) ;
Table altered.
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name = 'NEIL';
INDEX_NAME           UNIQUENESS
-------------------- --------------------
NEIL_I               UNIQUE
NEIL @ ORCL01 > insert into neil values (1,'A');
1 row created.
NEIL @ ORCL01 > insert into neil values (1,'B');
insert into neil values (1,'B')
*
ERROR at line 1:
ORA-00001: unique constraint (NEIL.NEIL_PK) violated
NEIL @ ORCL01 > commit;
Commit complete.
NEIL @ ORCL01 > alter table neil drop constraint neil_pk;
Table altered.
NEIL @ ORCL01 > -- And the index survives
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name = 'NEIL';
INDEX_NAME           UNIQUENESS
-------------------- --------------------
NEIL_I               UNIQUE

NEIL @ ORCL01 > -- Scenario 3
NEIL @ ORCL01 > drop table neil;
Table dropped.
NEIL @ ORCL01 > create table neil (col_id number, col_txt varchar2(10 char));
Table created.
NEIL @ ORCL01 > create index neil_i on neil(col_id);
Index created.
NEIL @ ORCL01 > alter table neil add constraint neil_pk primary key (col_id) using index neil_i;
Table altered.
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name = 'NEIL';
INDEX_NAME           UNIQUENESS
-------------------- --------------------
NEIL_I               NONUNIQUE
NEIL @ ORCL01 > insert into neil values (1,'A');
1 row created.
NEIL @ ORCL01 > insert into neil values (1,'B');
insert into neil values (1,'B')
*
ERROR at line 1:
ORA-00001: unique constraint (NEIL.NEIL_PK) violated
NEIL @ ORCL01 > commit;
Commit complete.
NEIL @ ORCL01 > alter table neil drop constraint neil_pk;
Table altered.
NEIL @ ORCL01 > -- And the index survives
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name = 'NEIL';
INDEX_NAME           UNIQUENESS
-------------------- --------------------
NEIL_I               NONUNIQUE

NEIL @ ORCL01 > -- Scenario 4
NEIL @ ORCL01 > drop table neil;
Table dropped.
NEIL @ ORCL01 > create table neil (col_id number, col_txt varchar2(10 char));
Table created.
NEIL @ ORCL01 > create index neil_i_composite on neil(col_id,col_txt);
Index created.
NEIL @ ORCL01 > alter table neil add constraint neil_pk primary key (col_id) using index neil_i_composite;
Table altered.
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name = 'NEIL';
INDEX_NAME           UNIQUENESS
-------------------- --------------------
NEIL_I_COMPOSITE     NONUNIQUE
NEIL @ ORCL01 > insert into neil values (1,'A');
1 row created.
NEIL @ ORCL01 > insert into neil values (1,'B');
insert into neil values (1,'B')
*
ERROR at line 1:
ORA-00001: unique constraint (NEIL.NEIL_PK) violated
NEIL @ ORCL01 > commit;
Commit complete.
NEIL @ ORCL01 > alter table neil drop constraint neil_pk;
Table altered.
NEIL @ ORCL01 > -- And the index survives
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name = 'NEIL';
INDEX_NAME           UNIQUENESS
-------------------- --------------------
NEIL_I_COMPOSITE     NONUNIQUE

So how do I tell if, when I drop my constraint, I will also drop the index? This does not appear to be exposed in any DBA_ view of which I am aware, so I have had to rely upon this post from Jonathan Lewis to help me decipher the information. You need to see if certain bits are set in the column sys.ind$.property. Here’s my worked example to show the information we seek:

NEIL @ ORCL01 > drop table neil_cons_create;
Table dropped.
NEIL @ ORCL01 > create table neil_cons_create (col_id number, col_txt varchar2(10 char));
Table created.
NEIL @ ORCL01 > alter table neil_cons_create add constraint neil_cons_create_pk primary key (col_id);
Table altered.
NEIL @ ORCL01 > drop table neil_pre_create;
Table dropped.
NEIL @ ORCL01 > create table neil_pre_create (col_id number, col_txt varchar2(10 char));
Table created.
NEIL @ ORCL01 > create unique index neil_pre_create_i on neil_pre_create(col_id);
Index created.
NEIL @ ORCL01 > alter table neil_pre_create add constraint neil_pre_create_pk primary key (col_id);
Table altered.
NEIL @ ORCL01 > select index_name,uniqueness from user_indexes where table_name like 'NEIL%';
INDEX_NAME           UNIQUENESS
-------------------- --------------------
NEIL_CONS_CREATE_PK  UNIQUE
NEIL_PRE_CREATE_I    UNIQUE

select do.owner,do.object_name,do.object_id,ind.property, 'Keep Index' action
  from sys.ind$ ind
       ,dba_objects do
where do.object_name like 'NEIL%'
  and do.object_id = ind.obj#
  and bitand(ind.property,4097) = 4097
union all
select do.owner,do.object_name,do.object_id,ind.property, 'Drop Index' action
  from sys.ind$ ind
      ,dba_objects do
where do.object_name like 'NEIL%'
  and do.object_id = ind.obj#
  and bitand(ind.property,4097) <> 4097
order by 1,2,3
/
OWNER        OBJECT_NAME         OBJECT_ID  PROPERTY   ACTION
------------ ------------------- ---------- ---------- --------------------
NEIL         NEIL_CONS_CREATE_PK      93814       4097 Keep Index
NEIL         NEIL_PRE_CREATE_I        93816          1 Drop Index

Filed under Administration, Development, oracle, SCHEMA, SQL Tagged with bitand, constraint, drop, ind$, ind.property, INDEX, key, oracle, pk, primary, sys.ind$

Online index problem – ORA-08104

12/03/2014 4 Comments

So, you’re creating (or rebuilding) an index ONLINE on a busy system. Your session dies, or it becomes necessary to kill the command, you may find that Oracle does not (always manage to) automatically clean up after itself.

CREATE INDEX my_ind ON my_table (mycol ASC)
 LOCAL LOGGING COMPRESS 1 ONLINE;

(ctrl-c)
ORA-01013: user requested cancel of current operation

select * from user_indexes where index_name = 'my_ind';

INDEX_NAME INDEX_TYPE
my_ind     NORMAL

OMG! WTF! TLA’s! The index is there, even though I cancelled the create statement! Lets drop it…

 
drop index my_ind;
 *
 ERROR at line 1:
 ORA-08104: this index object 79722 is being online built or rebuilt

So, HOW do I sort out this mess? Use DBMS_REPAIR!

  
  1  declare
  2  lv_ret BOOLEAN;
  3  begin
  4  lv_ret := dbms_repair.online_index_clean(79722);
  5* end;

select * from user_indexes where index_name = 'ind_name';

no rows selected

Bang! and the index (or, rather, left-over temporary extents from the build attempt) is gone, ready for you to try again.

Filed under Administration, Problem Solving Tagged with 8104, INDEX, ONLINE, ORA-08104, ORA-8104, oracle, REBUILD

Neil Chandler's DB Blog

Oracle Optimizer System Statistics

Oh System statistics! Should we gather them? Should we not?
What do they mean? What are they doing to the optimizer?

Primarys Keys and their supporting indexes

Online index problem – ORA-08104

Categories

Archives

Meta

Neil Chandler's DB Blog

Oracle Optimizer System Statistics

Oh System statistics! Should we gather them? Should we not?What do they mean? What are they doing to the optimizer?

Primarys Keys and their supporting indexes

Online index problem – ORA-08104

Categories

Archives

Meta

Oh System statistics! Should we gather them? Should we not?
What do they mean? What are they doing to the optimizer?