Recognize the magic optimizer numbers

Well I figured I document some of the magic numbers that the optimizer uses to help remember them, and help others. The back ground of this is simple.

I was looking through a query that was running for a long, long time, and the cardinality looked wrong.  I know the developers were using a table operation (looping over a LOB that was treated like  table). 

The Cardinality estimate for the step was 8168, and I thought hmmmm I've seen that before when dynamic sampling didn't happen.  Well after some digging I came across this page. Cardinality

The page contained this handy chart below...  These are important numbers to remember because when you see a cardinality matching this chart it is probably because the optimizer couldn't estimate the correct cardinality, and it couldn't dynamically sample.  Below is a snippet from the query I was investigating. Notice the cardinality on the first line.

0  0  0   COLLECTION ITERATOR PICKLER FETCH PARSE_DYNAMIC_COLS 

                    (cr=0 pr=0 pw=0 time=0 us cost=29 size=16336 card=8168)
0  0  0 HASH JOIN RIGHT OUTER (cr=0 pr=0 pw=0 time=0 us cost=8757 size=233200 card=100)
0  0  0  VIEW  (cr=0 pr=0 pw=0 time=0 us cost=8614 size=14 card=1)
0  0  0   HASH UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=8614 size=2069 card=1)
0  0  0    FILTER  (cr=0 pr=0 pw=0 time=0 us)
0  0  0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
0  0  0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=8613 size=2069 card=1)
0  0  0       HASH JOIN  (cr=0 pr=0 pw=0 time=0 us cost=8612 size=2044 card=1)

Default cardinality for database objects

The following table demonstrates the estimated cardinalities (using a 8K blocksize) of various objects which have had no statistics generated for them :

Object Type	Estimated Cardinality
Heap Table	82
Global Temporary Table	8168
Index-Organized Table	1
System Generated Materialized View (such as the output of the TABLE operator)	8168

Exadata sizing updated for 3tb drives 1/2 rack SATA

OK, Now I new Exadata coming in that has 3tb drives, and the first question asked is .. How much disk to I have to configure on it ?  Well I'm going to expand on a previous entry I did on sizing .

1/2 Rack. Sata drives. normal redundancy

This means we have

• 7 storage cells
• Each storage cell contains 12 disks
• each disk is 3tb (which is about 2.794 tb usable)  *** This is calculated using base 1024 
• The first 2 disks in each storage cell has 29.103g already partitioned for the OS (which is mirrored).
• The rest of the disks in the group are used for DBFS

Given this, I am going to calculate out the total disk available then subtract out the 29.103g (for OS and DBFS).

First 12 disks * 7 cells x 2.794 = 234.696 tb of total raw storage/
Subtract out 29g* 2 disks * 7 cells = 406g    ----- OS
Subtract out 29g * 10 disks * 7 cells = 2.03tb  -----   DBFS
Available raw is 234.696 - 2.436 = 232.26  

Now I said we were running Normal Redundancy.. This means that we loose 1/2

DBFS = 1.015tb
OS        29g
Remaining for Data and Reco = 116.13

But of course we need to account for cell being off line.  This takes out 1/7 of the storage.

DBFS   === .870 tb (29g * 10 * 6)/2
Everything else  ===  ( 2.765 * 12 disks * 6 cells)/2   == 99.54

So now we have 99.54 raw storage available for Data and Reco.

This is now easy to figure out now.. You have really 100tb raw storage (with normal redundancy) to split up between Data and Reco.

Now a full rack is easy to do.

2.765 * 12 disks * 13 cells) / 2 =  215.67tb 

What extended stats do I have on my database?

I've been starting to work with Extended statistics to help the optimizer find the best plan. This is a great feature that is outlined by @sqlmaria (Maria Coogan) here.

But once you create extended statistics, how do you know what is there ?  I wrote this query to find out what function based indexes, what extended statistics, and what their definition are. 

Here is my script.

column table_owner alias "owner" format a15
column table_name alias  "Table Name" format  a30
column function_index alias  "F Index" format  a8
column Index_name  alias  "Index Name"  format a30
column data_default alias  "Definition"  format a50
set pagesize 1000
select table_owner,
         table_name,
        nvl2(index_name,'YES','NO') function_index,
        index_name,
        data_default
        from
        (
select owner table_owner,table_name,
(select distinct index_name from dba_ind_columns b where a.column_name=b.column_name and a.owner=b.index_owner and a.table_name=b.table_name) index_name
,data_default
-- ,     DBMS_LOB.SUBSTR( to_lob(data_default),100,1)
 from dba_tab_cols a
  where virtual_column='YES' and hidden_column='YES'  and (owner not in ('SYS','WMSYS','XDB','SYSMAN','MDSYS','EXFSYS','PR_MDS') and owner not like 'APEX_%')
  )
order by table_owner,table_name;

and this is what the output looks like..

TABLE_OWNER     TABLE_NAME                     FUNCTION INDEX_NAME                     DATA_DEFINITION
--------------- ------------------------------ -------- ------------------------------ --------------------------------------------------
BGRENN          TAB_SCHR_PERD                      NO                                  COALESCE("COL1","COL2")
BGRENN          TAB2                              YES   IDX_TAB2                       "COL1"||' '||"COL2"
BGRENN          TAB3                               NO                                  COALESCE("COL1","COL2")
BGRENN          TAB4                              YES   IDX_TAB4                       COALESCE("COL1","COL2",0)
BGRENN          TAB4                              YES   IDX_TAB4                       COALESCE("COL3",0)
BGRENN          TAB5                              YES   IDX_TAB5                       COALESCE("COL1","COL2",0)
BGRENN          TAB6                              YES   IDX_TAB6                       NVL("COL1",(-1))
BGRENN          TAB6                              YES   IDX_TAB6                       NVL("COL2",(-1))
BGRENN          TAB6                              YES   IDX_TAB6                       NVL("COL3",(-1))
BGRENN          TAB6                              YES   IDX_TAB6                       NVL("COL4",'x')
BGRENN          TAB7                              YES   IDX_COMPOSITE                  "COL1"
BGRENN          TAB7                              YES   IDX_COMPOSITE                  "COL3"

Notice the Function colunmn. This is a "YES" or "NO" depending on if this is a function based index, or just extended statistics.

This should help tell where your extended statistics are in your database.