The error occur when there is requirement for higher version of GLIBC library which mismatch with the OS version.You try to use a executable which is not compatible with the older version of OS (linux) and needs an upgrade.
[oracle@orcl u01]$ ./runInstaller
/u01/perl/bin/perl: /lib64/libc.so.6: version `GLIBC_2.14' not found (required by /u01/perl/bin/perl)
Create a mount point with type(-t) ext4 on /dev/sde
[root@orcl oracle]# mkfs -t ext4 /dev/sde
mke2fs 1.43-WIP (20-Jun-2013)
/dev/sde is entire device, not just one partition!
Proceed anyway? (y,n) y
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
786432 inodes, 3145728 blocks
157286 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=3221225472
96 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208
Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done
Create a directory to mount the logical mount point
[root@orcl oracle]# mkdir /u01
[root@orcl oracle]# mount /dev/sde /u01
Check if the mount point is added to the OS.Note that so far we have temporarily mounted the filesystem.Post reboot of the system,changes will not reflect for the mount point
[root@orcl by-uuid]# ls -lrt
total 0
lrwxrwxrwx 1 root root 10 Feb 27 10:43 5307b124-8485-4f2c-ac2f-88af16028c11 -> ../../sdd3
lrwxrwxrwx 1 root root 10 Feb 27 10:43 de13c2f8-d3f3-41a7-9928-46001af5faf9 -> ../../sdd1
lrwxrwxrwx 1 root root 10 Feb 27 10:43 1121a652-c653-4bc5-b475-6285703020ba -> ../../sdd5
lrwxrwxrwx 1 root root 10 Feb 27 10:43 e3a73203-7975-4750-8ab4-e24542c24f31 -> ../../sdd2
lrwxrwxrwx 1 root root 9 Feb 27 11:38 1c1e8596-f9c2-4250-9fcc-1658ab4fec69 -> ../../sde
[root@orcl by-uuid]# pwd
/dev/disk/by-uuid
As we have already mounted the filesystem for current session earlier which vanish after reboot.So we have to update the fstab entry for permanent change
SQL> drop table test1.regions;
Table dropped.
SQL> select * from test1.regions;
select * from test1.regions
*
ERROR at line 1:
ORA-00942: table or view does not exist
Starting from oracle 12c,there is recover table feature which can recover the particular accidentally modified or dropped table to the previous point in time.
This feature is an alternative to flashback feature which has its own advantage and disadvantage
This feature needs backup to be taken and cannot be used in restoring the table to the recent changes which can be solved by flashback
Below is an example simulation of this feature
I purposefully pasted the whole output so that the steps performed by RMAN are important to understand
WARNING:This method requires sufficient space in the disk equal to the tablespace size.whichever table that you recover and the tablespace where the table exist both space should be considered.
check tablespace size where table reside
SQL> select bytes/1073741824 as SIZE_IN_GB,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='USERS';
SIZE_IN_GB TABLESPACE_NAME
---------- ------------------------------
6.9934082 USERS
Find the tablespace of corrupted table
SQL> @colformat.sql
Procedure created.
Procedure created.
SQL> select table_name,tablespace_name from dba_tables where table_name='REGIONS';
TABLE_NAME TABLESPACE_NAME
--------------- ---------------
REGIONS USERS
check corrupted table size
SQL> select bytes/1024 as SIZE_IN_KB from dba_segments where segment_name='REGIONS';
SIZE_IN_KB
----------
64
Recover table command
RMAN> recover table test1.regions until time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')" auxiliary destination '/home/oracle';
Starting recover at 25-FEB-21
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='sobu'
initialization parameters used for automatic instance:
db_name=ORCL19X
db_unique_name=sobu_pitr_ORCL19X
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/apps01/base
_system_trig_enabled=FALSE
sga_target=1504M
processes=120
db_create_file_dest=/home/oracle
log_archive_dest_1='location=/home/oracle'
#No auxiliary parameter file used
starting up automatic instance ORCL19X
Oracle instance started
Total System Global Area 1577058304 bytes
Fixed Size 2924832 bytes
Variable Size 402656992 bytes
Database Buffers 1157627904 bytes
Redo Buffers 13848576 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 25-FEB-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/04vo2gsh_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/04vo2gsh_1_1 tag=TAG20210225T102357
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/home/oracle/ORCL19X/controlfile/o1_mf_j3hcc0dy_.ctl
Finished restore at 25-FEB-21
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 9 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 8 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 9, 4, 3, 8;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/ORCL19X/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 25-FEB-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/ORCL19X/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /home/oracle/ORCL19X/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/ORCL19X/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/ORCL19X/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /home/oracle/ORCL19X/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/03vo2glu_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/03vo2glu_1_1 tag=TAG20210225T102357
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:20
Finished restore at 25-FEB-21
datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=1065468835 file name=/home/oracle/ORCL19X/datafile/o1_mf_system_j3hcd1lg_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=16 STAMP=1065468836 file name=/home/oracle/ORCL19X/datafile/o1_mf_system_j3hcd2bn_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=1065468837 file name=/home/oracle/ORCL19X/datafile/o1_mf_undotbs1_j3hcd1yg_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=18 STAMP=1065468838 file name=/home/oracle/ORCL19X/datafile/o1_mf_sysaux_j3hcd146_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=19 STAMP=1065468839 file name=/home/oracle/ORCL19X/datafile/o1_mf_sysaux_j3hcd23d_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 9 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 8 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 8 online
Starting recover at 25-FEB-21
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 78 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_78_1060279903.dbf
archived log for thread 1 with sequence 79 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_79_1060279903.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=77
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/05vo2gsu_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/05vo2gsu_1_1 tag=TAG20210225T102742
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
archived log file name=/home/oracle/1_77_1060279903.dbf thread=1 sequence=77
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_78_1060279903.dbf thread=1 sequence=78
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_79_1060279903.dbf thread=1 sequence=79
media recovery complete, elapsed time: 00:01:32
Finished recover at 25-FEB-21
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/home/oracle/ORCL19X/controlfile/o1_mf_j3hcc0dy_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1577058304 bytes
Fixed Size 2924832 bytes
Variable Size 402656992 bytes
Database Buffers 1157627904 bytes
Redo Buffers 13848576 bytes
sql statement: alter system set control_files = ''/home/oracle/ORCL19X/controlfile/o1_mf_j3hcc0dy_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1577058304 bytes
Fixed Size 2924832 bytes
Variable Size 402656992 bytes
Database Buffers 1157627904 bytes
Redo Buffers 13848576 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 25-FEB-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /home/oracle/SOBU_PITR_ORCL19X/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/03vo2glu_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/03vo2glu_1_1 tag=TAG20210225T102357
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 25-FEB-21
datafile 6 switched to datafile copy
input datafile copy RECID=21 STAMP=1065469296 file name=/home/oracle/SOBU_PITR_ORCL19X/datafile/o1_mf_users_j3hcxq5k_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('25-FEB-21 19:20:00','dd-mon-yy hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 6 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 6 online
Starting recover at 25-FEB-21
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 78 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_78_1060279903.dbf
archived log for thread 1 with sequence 79 is already on disk as file /apps01/product/12.1.0/dbhome_1/dbs/arch1_79_1060279903.dbf
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=77
channel ORA_AUX_DISK_1: reading from backup piece /apps01/product/12.1.0/dbhome_1/dbs/05vo2gsu_1_1
channel ORA_AUX_DISK_1: piece handle=/apps01/product/12.1.0/dbhome_1/dbs/05vo2gsu_1_1 tag=TAG20210225T102742
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/oracle/1_77_1060279903.dbf thread=1 sequence=77
channel clone_default: deleting archived log(s)
archived log file name=/home/oracle/1_77_1060279903.dbf RECID=78 STAMP=1065469303
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_78_1060279903.dbf thread=1 sequence=78
archived log file name=/apps01/product/12.1.0/dbhome_1/dbs/arch1_79_1060279903.dbf thread=1 sequence=79
media recovery complete, elapsed time: 00:00:07
Finished recover at 25-FEB-21
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_sobu_DrmC":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
EXPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
EXPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported "TEST1"."REGIONS" 5.546 KB 4 rows
EXPDP> Master table "SYS"."TSPITR_EXP_sobu_DrmC" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_sobu_DrmC is:
EXPDP> /home/oracle/tspitr_sobu_50421.dmp
EXPDP> Job "SYS"."TSPITR_EXP_sobu_DrmC" successfully completed at Thu Feb 25 19:43:49 2021 elapsed 0 00:00:50
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_sobu_pqnF" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_sobu_pqnF":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "TEST1"."REGIONS" 5.546 KB 4 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/COMMENT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
IMPDP> Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
IMPDP> Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_sobu_pqnF" successfully completed at Thu Feb 25 19:45:30 2021 elapsed 0 00:00:47
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_temp_j3hcp6y2_.tmp deleted
auxiliary instance file /home/oracle/SOBU_PITR_ORCL19X/onlinelog/o1_mf_3_j3hd1h0j_.log deleted
auxiliary instance file /home/oracle/SOBU_PITR_ORCL19X/onlinelog/o1_mf_2_j3hd1d8g_.log deleted
auxiliary instance file /home/oracle/SOBU_PITR_ORCL19X/onlinelog/o1_mf_1_j3hd1798_.log deleted
auxiliary instance file /home/oracle/SOBU_PITR_ORCL19X/datafile/o1_mf_users_j3hcxq5k_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_sysaux_j3hcd23d_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_sysaux_j3hcd146_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_undotbs1_j3hcd1yg_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_system_j3hcd2bn_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/datafile/o1_mf_system_j3hcd1lg_.dbf deleted
auxiliary instance file /home/oracle/ORCL19X/controlfile/o1_mf_j3hcc0dy_.ctl deleted
auxiliary instance file tspitr_sobu_50421.dmp deleted
Finished recover at 25-FEB-21
RMAN> exit
Recovery Manager complete.
My table is back now!
SQL> select count(*) from test1.regions;
COUNT(*)
----------
4
SQL> select * from test1.regions;
REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
Often, when invoking cellcli utility in exadata storage server,you see a ratio banner called cell efficiency ratio(CER) after copyright message.Have you ever think of what it is and why has it been displayed everytime?
I see this banner everytime and ignore it because the ratio value is always small.But suddenly , i saw a huge rise in the CER due to some reason.I know that before i see this rise ,i had offloaded lot of sql queries with smart scan,storage index,HCC,flash cache keep etc.. This is the main reason for the huge increase in CER
To define CER, it is the ratio between the data touched on the storage tier and data processed on the database tier
CER = Data touched on storage cell/Data processed by database tier
In above picture,CER is 489 which means most data is processed by database itself or to be descriptive,less data is offloaded for smart scan,storage index is not efficiently used and touched from storage layer
CellCLI> list cell attributes offloadEfficiency
489.3
Dont get frustated by seeing the low efficiency value but be happy that you dont have any negative values over there!Because negative value indicate that you dont use exadata upto the optimum level for what you pay
Though, CER is no longer increased in one day or night.The value gets better gradually on offloading data.
So always try to offload most of the oltp or batch workloads to the cell storage server and gradually increase CER for which you get blistering performance
The above output shows pretty impressive cell efficiency rate which shows that cell storage server is optimally used
CellCLI> list cell attributes offloadEfficiency
16,291.9
Lot of objects are kept in flashcache to increase CER.For example, look the following output
The more objects you keep in flash memory,more IO is reduced for database
I create diskgroups for RAC node using ASMCA on 11g but the diskgroup fail to create due to timeouts by VKTM background process and it took almost 3 days for the diskgroup
The thing is even after 3 days, ASMCA still spinning the gears and it pretty frustates.
I checked each and every corner of the ASM,ASMCA,database and clusterware logs but still it is like chasing the tail in a loop
Workaround:
Always give appropriate value to kernel parameters which indirectly affects the performance of lot of tasks performed in future
Finally diskgroups got created with in fraction of seconds which really shocked .
As i try to create diskgroups by rebuilding the whole clusterware and database setup almost for 10 to 15 times
All the time it took more than 2 days and i could find timeout and connection retry errors repeatedly
Connect retry: sleeping for 2 seconds, connect attempt 3130 out of maximum 7 attempts
There were lot of vktm hang messages continuously without any trace.Atlast after applying the recommended changes from DBESP blog,the issue got resolved and diskgroups created very fast.
In sqlplus commandline, we dont get proper output on the screen for large number because of width and column number format rather we get scientific notation which is absurd in case of dbid or scn number of a database.
There are two ways to get proper output with full digits
One variable is numwidth
kish@exdbx<>select 10000000000*9 from dual;
10000000000*9
-------------
9.0000E+10
Elapsed: 00:00:00.00
kish@exdbx<>set numwidth 25
kish@exdbx<>select 10000000000*9 from dual;
10000000000*9
-------------------------
90000000000
Another variable is col column_name format 99999….
kish@exdbx<>select 10000000000*3 from dual;
10000000000*3
-------------
3.0000E+10
Elapsed: 00:00:00.01
kish@exdbx<>col 10000000000*3 format 99999999999
kish@exdbx<>select 10000000000*3 from dual;
10000000000*3
-------------
30000000000
Elapsed: 00:00:00.02
To stop the cluster resources on all nodes in RAC cluster
[root@exdbadm01 oracle]# crsctl stop cluster -all
CRS-2673: Attempting to stop 'ora.crsd' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.crsd' on 'exdbadm02'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.MGMT.dg' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'exdbadm01'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.oc4j' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.cvu' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.MGMT.dg' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'exdbadm02'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.exdbadm01.vip' on 'exdbadm01'
CRS-2677: Stop of 'ora.DATA.dg' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'exdbadm02'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.exdbadm02.vip' on 'exdbadm02'
CRS-2677: Stop of 'ora.FRA.dg' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.DATA.dg' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.cvu' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.exdbadm01.vip' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.FRA.dg' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.scan1.vip' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.exdbadm02.vip' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.MGMT.dg' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm01'
CRS-2677: Stop of 'ora.MGMT.dg' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm02'
CRS-2677: Stop of 'ora.asm' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'exdbadm01'
CRS-2677: Stop of 'ora.asm' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.ons' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'exdbadm01'
CRS-2677: Stop of 'ora.net1.network' on 'exdbadm01' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'exdbadm01' has completed
CRS-2677: Stop of 'ora.crsd' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.evmd' on 'exdbadm01'
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm01'
CRS-2677: Stop of 'ora.evmd' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.asm' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'exdbadm01'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'exdbadm01'
CRS-2677: Stop of 'ora.oc4j' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'exdbadm02'
CRS-2677: Stop of 'ora.cssd' on 'exdbadm01' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'exdbadm01'
CRS-2677: Stop of 'ora.ons' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'exdbadm02'
CRS-2677: Stop of 'ora.net1.network' on 'exdbadm02' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'exdbadm02' has completed
CRS-2677: Stop of 'ora.crsd' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.evmd' on 'exdbadm02'
CRS-2673: Attempting to stop 'ora.asm' on 'exdbadm02'
CRS-2677: Stop of 'ora.diskmon' on 'exdbadm01' succeeded
CRS-2677: Stop of 'ora.evmd' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.asm' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'exdbadm02'
CRS-2677: Stop of 'ora.ctssd' on 'exdbadm02' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'exdbadm02'
CRS-2677: Stop of 'ora.cssd' on 'exdbadm02' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'exdbadm02'
CRS-2677: Stop of 'ora.diskmon' on 'exdbadm02' succeeded
To start cluster resources on local node
[root@exdbadm01 oracle]# crsctl start cluster
CRS-2672: Attempting to start 'ora.mdnsd' on 'exdbadm01'
CRS-2676: Start of 'ora.mdnsd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'exdbadm01'
CRS-2676: Start of 'ora.gpnpd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.gipcd' on 'exdbadm01'
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'exdbadm01'
CRS-2676: Start of 'ora.cssdmonitor' on 'exdbadm01' succeeded
CRS-2676: Start of 'ora.gipcd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'exdbadm01'
CRS-2672: Attempting to start 'ora.diskmon' on 'exdbadm01'
CRS-2676: Start of 'ora.diskmon' on 'exdbadm01' succeeded
CRS-2676: Start of 'ora.cssd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.ctssd' on 'exdbadm01'
CRS-2676: Start of 'ora.ctssd' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.evmd' on 'exdbadm01'
CRS-2672: Attempting to start 'ora.drivers.acfs' on 'exdbadm01'
CRS-2679: Attempting to clean 'ora.cluster_interconnect.haip' on 'exdbadm01'
CRS-2681: Clean of 'ora.cluster_interconnect.haip' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.cluster_interconnect.haip' on 'exdbadm01'
CRS-2676: Start of 'ora.evmd' on 'exdbadm01' succeeded
CRS-2676: Start of 'ora.drivers.acfs' on 'exdbadm01' succeeded
CRS-2676: Start of 'ora.cluster_interconnect.haip' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'exdbadm01'
CRS-2676: Start of 'ora.asm' on 'exdbadm01' succeeded
CRS-2672: Attempting to start 'ora.crsd' on 'exdbadm01'
CRS-2676: Start of 'ora.crsd' on 'exdbadm01' succeeded
To check ocr integrity between each nodes and corruption check
[oracle@exdbadm01 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3128
Available space (kbytes) : 258992
ID : 1861193736
Device/File Name : +MGMT
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
To display all the backup of ocr file
[oracle@exdbadm01 ~]$ ocrconfig -showbackup
exdbadm02 2021/03/28 20:26:49 /u01/app/11.2.0/grid/cdata/exdbadm-scan/backup00.ocr
exdbadm02 2021/03/28 16:26:48 /u01/app/11.2.0/grid/cdata/exdbadm-scan/backup01.ocr
exdbadm02 2021/03/26 22:44:59 /u01/app/11.2.0/grid/cdata/exdbadm-scan/backup02.ocr
exdbadm02 2021/03/28 16:26:48 /u01/app/11.2.0/grid/cdata/exdbadm-scan/day.ocr
exdbadm02 2021/03/15 16:34:25 /u01/app/11.2.0/grid/cdata/exdbadm-scan/week.ocr
PROT-25: Manual backups for the Oracle Cluster Registry are not available
To check the voting disk
[oracle@exdbadm01 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 65371b4d26a54fa0bfce069c3b2d52ae (o/192.168.56.33/MGMT_CD_cell15_stocell) [MGMT]
Located 1 voting disk(s).
[oracle@exdbadm01 ~]$ srvctl status nodeapps
VIP exdbadm01-vip is enabled
VIP exdbadm01-vip is running on node: exdbadm01
VIP exdbadm02-vip is enabled
VIP exdbadm02-vip is running on node: exdbadm02
Network is enabled
Network is running on node: exdbadm01
Network is running on node: exdbadm02
GSD is disabled
GSD is not running on node: exdbadm01
GSD is not running on node: exdbadm02
ONS is enabled
ONS daemon is running on node: exdbadm01
ONS daemon is running on node: exdbadm02
To check the status of network interface
[oracle@exdbadm01 ~]$ oifcfg getif
eth0 192.168.56.0 global public
eth1 192.168.57.0 global cluster_interconnect
Other commands
To check the list of nodes in cluster
[oracle@exdbadm01 ~]$ olsnodes -n -s -t
exdbadm01 1 Active Unpinned
exdbadm02 2 Active Unpinned
To check ocr integrity with cluster utility
[oracle@exdbadm01 ~]$ cluvfy comp ocr
Verifying OCR integrity
Checking OCR integrity...
Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations
ASM Running check passed. ASM is running on all specified nodes
Checking OCR config file "/etc/oracle/ocr.loc"...
OCR config file "/etc/oracle/ocr.loc" check successful
Disk group for ocr location "+MGMT" available on all the nodes
NOTE:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.
OCR integrity check passed
Verification of OCR integrity was successful.
To check grid versions
[oracle@exdbadm01 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.4.0]
[oracle@exdbadm01 ~]$ crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.4.0]
[oracle@exdbadm01 ~]$ crsctl query crs softwareversion
Oracle Clusterware version on node [exdbadm01] is [11.2.0.4.0]
Storage inde(SI) itself is not an index but a filter which is a cache area used in cell server to reduce disk IO on the database layer.
Note:In this article,i will use the term SI instead of storage index so dont get confused
Research say that SI is stored in heap of cellsrv which also has swapping mechanism due to insufficient memory.SI has a regional index which has min and max value column upto eight columns which stores the cached data in 1MB chunks of storage
Storage index also stores nulls to track all the null values from the table
There are certain conditions whenever storage index should be used
Smart scan,At least one predicate and comparison operators.Below are the operators which are most commonly used and conditions which satisfy SI
=,<,>,BETWEEN,>=,<=,IN,IS NULL,IS NOT NULL
There are three hidden parameters which can be used to influence storage index
kish@exdbx<>select KSPPINM from x$ksppi where (KSPPINM like '%kcfis_sto%') or (KSPPINM like '_cell%mode');
KSPPINM
--------------------------------------------------------------------------------
_kcfis_storageidx_disabled
< == used to disable storage index (default = enabled)
_kcfis_storageidx_diag_mode
< == database layer cause storage cell to generate trace files on affected cell nodes
_cell_storidx_mode
< == used for filter options w.r.t conditions(>,<,= ...) (EVA,KDST,ALL)
How to identify if a storage index is used or not
‘cell physical IO bytes saved by storage index‘ is the statistic name used for storage index in exadata which denote the total IO saved by using storage index
We can get these details of storage in combination of v$sysstat,v$statname and v$mystat views
Also storage index(SI) requires sufficient memory for utilization because of its nature.If there are less pages to handle the incoming processes,then storage filter cant be used.But all time,there is sufficient memory to use SI though
kish@exdbx<>select name from v$statname where name like '%storage%';
NAME
----------------------------------------------------------------
cell physical IO bytes saved by storage index
Zero indicates that storage index is not being used and some value indicates the amount of IO (mb) saved by storage index
I just perform a test to check how storage index works
First i connect to fresh session and there is no statistics(0 MB) for offloading in database.Every time we disconnect from session,all these statistical information are wiped out from the memory.Hence output of query values are zero
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like '%cell physical%'); 2 3 4
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 0
physical write total bytes 0
cell physical IO interconnect bytes 0
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 0
cell physical IO bytes saved by storage index 0
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO interconnect bytes returned by smart scan 0
cell IO uncompressed bytes 0
10 rows selected.
I check the count of total rows from sales table.Observe that there is no predicate on the query.As we should be aware that,storage index doesnot work on query without predicate clause.This is an evidence that storage index cannot be used in non predicate query.
kish@exdbx<>set autot traceonly
kish@exdbx<>select count(*) from sales;
Elapsed: 00:00:01.09
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3222 (1)| 00:00:39 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS STORAGE FULL| SALES | 1048K| 3222 (1)| 00:00:39 |
----------------------------------------------------------------------------
Check the statistics ‘cell physical IO bytes saved by storage index‘ row.It is zero.
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name in ('physical read total bytes',
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like '%cell physical%'); 2 3 4
NAME MB
---------------------------------------------------------------- ----------
physical read total bytes 185.6875
physical write total bytes 0
cell physical IO interconnect bytes 24.8948441
cell physical IO bytes saved during optimized file creation 0
cell physical IO bytes saved during optimized RMAN file restore 0
cell physical IO bytes eligible for predicate offload 185.359375
cell physical IO bytes saved by storage index 0
<==========
cell physical IO bytes sent directly to DB node to balance CPU 0
cell physical IO interconnect bytes returned by smart scan 24.5667191
cell IO uncompressed bytes 185.539063
10 rows selected.
EXAMPLES:
lets query with equality predicate.I have just demonstrated multiple examples belowIf it bores you,i suggest you to skip the examples and go to performance part
Above output shows storage filter in predicate information section of execution plan which means that oracle has used the storage index column values to filter the unique value.If you see below output,you can see the statistics which shows the IO utlization of 92 MB from the storage layer which saved almost X% of IO
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b
where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 91.6875
<===========
If i check the size of the particular row,it shows 6 bytes as in execution plan.As an assumption,there should be some additional processing from storage index to scan from min value(1) to 1234 which lead to 91 MB IO cost.
kish@exdbx<>set pages 200
kish@exdbx<>set lines 1000
kish@exdbx<>SELECT NVL(vsize(COUNTRY),0)+1+NVL(vsize(ITEM_TYPE),0)+1+NVL(vsize(SALES_CHANNEL),0)+1+NVL(vsize(ORDER_PRIORITY),0)+1+NVL(vsize(ORDER_DATE),0)+1+NVL(vsize(ORDER_ID),0)+1+NVL(vsize(SHIP_DATE),0)+1+NVL(vsize(UNITS_SOLD),0)+1+NVL(vsize(UNIT_PRICE),0)+1+NVL(vsize(TOTAL_REVENUE),0)+1+NVL(vsize(TOTAL_COST),0)+1+NVL(vsize(TOTAL_PROFIT),0)+1 as ROWSIZE_IN_BYTES from sales where order_id='1234';
ROWSIZE_IN_BYTES
----------------
67
Lets query with less than operator
kish@exdbx<>select count(*) from sales where order_id < 1234;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage("ORDER_ID"<1234)
filter("ORDER_ID"<1234)
In below output,you can find an increment in storage IO statistics which is double the value (184MB) compared to previous
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 184
<===========
Lets query with between operator
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;
COUNT(*)
----------
11112
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207
2 - storage("ORDER_ID"<=12345 AND "ORDER_ID">=1234)
filter("ORDER_ID"<=12345 AND "ORDER_ID">=1234)
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 274.6875
Again,the output value of storage IO is incremented to 275 MB.These examples proves that using certain operators in predicate leads to SI scan thus reducing the need for a traditional BTREE index in database
kish@exdbx<>select count(*) from sales where order_id in ('1234','12345');
COUNT(*)
----------
2
Elapsed: 00:00:00.03
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 365.375
<= operator:
kish@exdbx<>select count(*) from sales where order_id <='1234';
COUNT(*)
----------
1234
Elapsed: 00:00:00.03
kish@exdbx<>select a.name, b.value/1024/1024 MB from v$sysstat a, v$mystat b where a.statistic# = b.statistic# and (a.name like '%cell physical%storage%');
NAME MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 457.6875
PERFORMANCE TEST:
Lets test the performance difference between storage index and no storage index
kish@exdbx<>alter session set "_kcfis_storageidx_disabled"=true;
Session altered.
Elapsed: 00:00:00.01
kish@exdbx<>alter system flush shared_pool;
System altered.
Elapsed: 00:00:01.74
kish@exdbx<>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:08.35
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;
COUNT(*)
----------
11112
Elapsed: 00:00:03.99
<=========
It took 4 milliseconds for the select query to execute
Lets test the select query with storage index enabled
kish@exdbx<>alter session set "_kcfis_storageidx_disabled"=false;
Session altered.
Elapsed: 00:00:00.01
kish@exdbx<>alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
kish@exdbx<>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.06
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;
COUNT(*)
----------
11112
Elapsed: 00:00:00.10
<===
It took 0.10 milliseconds to complete which is almost 4 – 0.10 = 3.90 seconds faster than previous run.Amazing is it ! The query executed 3 times faster than normal run without SI
Now let us create a BTREE index on order_id column of sales table and check if there is any additional performance impact or not.I didnot want to focus on the importance of BTREE here but just curious to compare the performance
kish@exdbx<>create index BTREE_ORDER_ID on sales(order_id);
Index created.
Lets check how the index impacts optimizers plan.Note that i have not disabled the storage index.
kish@exdbx<>set autot on;
kish@exdbx<>select count(*) from sales where order_id between 1234 and 12345;
COUNT(*)
----------
11112
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 1371073561
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 27 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| BTREE_ORDER_ID | 11113 | 66678 | 27 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_ID">=1234 AND "ORDER_ID"<=12345)
The above output shows that eventhough i didnot disable the storage index optimizer choose BTREE over storage index
BTREE gives a tough fight when it comes to index scans.If the goal is to perform single block IO, then BTREE index comes into picture but if the goal is to scan multiblock IO,then storage index is better.However it depends on various factors of how data stored in the table and optimizer shortest possible path.
If we want to check if the columns in a table are marked by storage index,then we should identify the object id of the table,tablespace id where the object is stored and database unique id of the database.Then enable dump trace from cell server
Identify the Object id of table
kish@exdbx<>select data_object_id from dba_objects where owner = 'SYS' and object_name = 'SALES';
DATA_OBJECT_ID
--------------
88693
Next,identify ts# where the object reside
kish@exdbx<>select ts# from v$tablespace t, dba_segments s, dba_objects o where t.name=s.tablespace_name and s.segment_name=o.object_name and o.data_object_id = 88693;
TS#
----------
0
6
6
6
6
6
6
6
6
6
6
6
6
6
6
6
6
4
Get the database unique from kernel view
kish@exdbx<>select ksqdngunid from x$ksqdn;
KSQDNGUNID
----------
3727006149
Dump the contents of the storage index on each celldisk to a tracefile
[root@exceladm00 trace]# cellcli
CellCLI: Release 11.2.3.2.1 - Production on Sat Feb 20 16:52:46 GMT+05:30 2021
Copyright (c) 2007, 2012, Oracle. All rights reserved.
Cell Efficiency Ratio: 4,545
CellCLI> alter cell events = "immediate cellsrv.cellsrv_storidx('dumpridx','all',88693,6,3727006149);
Dump sequence #1 has been written to /opt/oracle/cell11.2.3.2.1_LINUX.X64_130109/log/diag/asm/cell/exceladm00/trace/svtrc_4014_27.trc
Cell stocell successfully altered
Get ready to find valuable insights from the tracefile
Again inside the tracefile,you can see object id and count of the rows of table
lo(low) and hi(high) indicate the min and max value data stored in 1MB chunks of storage index and there is col id [6] which represents column number 6 in table sales.Check also the scn which has an unique identifier for record in index.I marked the important points with “[[[ … ]]]” square braces in the below trace to understand the points covered in this paragraph.Isnt that fascinating to see how storage index works !To me it is 🙂
Storage index is a boon for exadata which acts as an alterntive to partitioning.However,the way to get the full potential from SI depends on the data stored in physical storage.And that being said,sorted data attracts storage index and performance compared to unsorted data
