Cost, selectivity and cardinality - all changes in 10g rel2 10053 trace file

Few days back o one of the google groups, there was issue posted by a gentleman about "index selectivity". Per him the "index selectivity" should be different than what is being shown by the optimizer. He was inquiring whether the procedure he followed to calculate "index selectivity" is correct or not.

Here is what he had to say:

I don't know how the optimizer has calculated ix_sel of the index
IDT_FCMO_TMS.
Oracle version: 10.2.0.3.0
Any idea?

SQL Statement:
select /* PRUEBA */ count(*) FROM TDTC_CAPACIDAD_MODALIDAD WHERE
CMO_CODTSG = 'HT' AND CMO_CODTMS = 'DBL

10053 Trace:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TDTC_CAPACIDAD_MODALIDAD Alias: TDTC_CAPACIDAD_MODALIDAD
#Rows: 82240 #Blks: 1132 AvgRowLen: 82.00
Index Stats::
Index: IDT_FCMO_CHA Col#: 5
LVLS: 1 #LB: 170 #DK: 939 LB/K: 1.00 DB/K: 14.00 CLUF:13425.00
Index: IDT_FCMO_TMS Col#: 34 <----- the index
LVLS: 1 #LB: 214 #DK: 471 LB/K: 1.00 DB/K: 26.00 CLUF:12648.00 <----- the index
Index: IDT_FCMO_TSU Col#: 31
LVLS: 0 #LB: 1 #DK: 58 LB/K: 1.00 DB/K: 6.00 CLUF: 370.00
Index: RDT_PCMO Col#: 1 2
LVLS: 1 #LB: 206 #DK: 82061 LB/K: 1.00 DB/K: 1.00 CLUF:28928.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#3): CMO_CODTSG(VARCHAR2)
AvgLen: 3.00 NDV: 21 Nulls: 0 Density: 0.047619 <---------- first column of the index
Column (#4): CMO_CODTMS(VARCHAR2)
AvgLen: 5.00 NDV: 387 Nulls: 0 Density: 0.002584 <---------- second column of the index
Table: TDTC_CAPACIDAD_MODALIDAD Alias:
TDTC_CAPACIDAD_MODALIDAD
Card: Original: 82240 Rounded: 10 Computed: 10.12 Non Adjusted:
10.12
Access Path: TableScan
Cost: 184.60 Resp: 184.60 Degree: 0
Cost_io: 181.00 Cost_cpu: 29454495
Resp_io: 181.00 Resp_cpu: 29454495
Access Path: index (index (FFS))
Index: IDT_FCMO_TMS
resc_io: 36.00 resc_cpu: 15484960
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 37.89 Resp: 37.89 Degree: 1
Cost_io: 36.00 Cost_cpu: 15484960
Resp_io: 36.00 Resp_cpu: 15484960
Access Path: index (AllEqRange)
Index: IDT_FCMO_TMS
resc_io: 1.00 resc_cpu: 42971
ix_sel: 0.0021231 ix_sel_with_filters: 0.0021231 <-------------------- wrong ix_sel?
Cost: 1.01 Resp: 1.01 Degree: 1
Best:: AccessPath: IndexRange Index: IDT_FCMO_TMS
Cost: 1.01 Degree: 1 Resp: 1.01 Card: 10.12 Bytes: 0
***************************************

IX_SEL should be 0.047619 * 0.002584 (which is far from what optimizer showed i.e 0.0021231)

According to Jonathan lewis book "Cost-Based Oracle Fundamentals", it is well explain in Chapter-4 P 66,67 that in case of select using multiple column index, index selectivity is calculated using the multiplication of individual selectivities of columns.
And that is precisely what the person seeking support showed.

While testing this result on 10.2.0.2, I found the index selectivity never gets multiplied in case of multicolumn index. In fact optimizer calculates index selectivity based on "distinct keys" of index.In this case (per the trace shown above) it is 471.
So the index selectivity comes out to be 1/(DK of index)=1/471=0.0021231, which is what is shown by optimizer.

I think, by the time Jonathan's book was published, not all the changes were tested. In fact I visited his blog and some couple of finding similar to this posted there.

ORA-01115 and ORA-27067 during crash recovery

Yesterday, one of our development server got a power outage because of which all our databases running on that server crashed.

After the server was brought up, I started these databases manually. One of the db refused to startup with normal "startup" command.

Here was the front-end error.

SQL> startup
ORACLE instance started.

Total System Global Area 432013312 bytes
Fixed Size 2169944 bytes
Variable Size 356115368 bytes
Database Buffers 62914560 bytes
Redo Buffers 10813440 bytes
Database mounted.
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/user/oracle/data/initdb/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1572864

And here is what I saw in alert.log

ALTER DATABASE OPEN
Wed Mar 12 10:01:14 2008
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Wed Mar 12 10:01:15 2008
Started redo scan
Wed Mar 12 10:01:16 2008
Completed redo scan
130874 redo blocks read, 760 data blocks need recovery
Wed Mar 12 10:01:16 2008
Started redo application at
Thread 1: logseq 49756, block 4723
Wed Mar 12 10:01:16 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 49756 Reading mem 0
Mem# 0 errs 0: /mnt/filer15adapp/data/adap_dev/redo1.log
Wed Mar 12 10:01:16 2008
Errors in file /mnt/oracle-dev1/admin/adap_dev/bdump/adap_dev_p001_4721.trc:
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1409024
Wed Mar 12 10:01:19 2008
Aborting crash recovery due to slave death, attempting serial crash recovery
Wed Mar 12 10:01:19 2008
Beginning crash recovery of 1 threads
Wed Mar 12 10:01:19 2008
Started redo scan
Wed Mar 12 10:01:20 2008
Completed redo scan
130874 redo blocks read, 760 data blocks need recovery
Wed Mar 12 10:01:25 2008
Aborting crash recovery due to error 1115
Wed Mar 12 10:01:25 2008
Errors in file /mnt/oracle-dev1/admin/adap_dev/udump/adap_dev_ora_4717.trc:
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1572864
ORA-1115 signalled during: ALTER DATABASE OPEN...
Wed Mar 12 10:02:37 2008
Shutting down instance: further logons disabled


This was the first time i got this error in my experience. Db being running in noarchive log mode (this being dev server) just added to my worries.

Googling didnt help. After some research work, i encountered a note on metalink Note:423128.1.

Though they didnt explain the cause of the problem, but suggested the workaround.

i.e.

1) Mount the database
2) Issue "recover database" command
-- Wait for "Media recovery complete"
3) alter database open

I'm s not sure about the cause as this Note on metalink refers to a Bug 5362935 - "CRASH RECOVERY FAILS WITH ORA-27067 ERROR", which is closed with status "Suspended, Req'd Info not Avail" and lots of useful information is hidden so people like us, who are not working in Oracle Corp cant see it.

How to pass a value from Oracle to Unix shell environment variable?

Though its too trivial, but I thought its not a bad idea to put it here to help the newbies.
Following information can be of help when you want to store a table record or some computation in a shell variable.

env_variable=`sqlplus username}/{password} <<>
set echo off verify off feed off termout off pages 0
{{SELECT STMT}};
exit
EOF`


For example:

-bash-3.00$ DB_USED=`sqlplus -s "/ as sysdba" << EOF
> set echo off verify off feed off termout off pages 0
> select sum(tot.bytes/1024-sum(nvl(fre.bytes,0))/1024)/3.8
> from dba_free_space fre,
> (select tablespace_name, sum(bytes) bytes
> from dba_data_files
> group by tablespace_name) tot,
> dba_tablespaces tbs
> where tot.tablespace_name = tbs.tablespace_name
> and fre.tablespace_name(+) = tbs.tablespace_name
> group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
> /
> exit
> EOF`

-bash-3.00$ echo $DB_USED
4189795.79

This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.