Exadata Concept - Storage Index , Smart Scan - Demo

Exadata Concept - Storage Index , Smart Scan - Demo

Exadata is a Database Appliance containing a Storage Server which is database aware, hence it provides the fastest medium to reduce I/O and ensures that only required blocks travels between Storage Server to Database Servers. Although Exadata has an infiniband to communicate between storage server and database machine and infiniband provides a 40Gbps communication.

In the below test case i would be demonstrating the following things

1) Storage Server Access via CELLCLI
2) Smart Scan in Exadata
3) Storage Indexes in Exadata

Lets see some of the hardware portion of Exadata.

Physical Structures

Command

Utilities

Physical Disk & LUN

list physicaldisk;

CELLCLI

CELL Disks

list celldisk

CELLCLI

GRID Disk

list griddisk

CELLCLI

ASM Disk

lsdg -G DATA

ASMCMD

ASM Disk Group

lsdg -G DATA

ASMCMD

ASM Files

ls -s

ASMCMD



One Physical disk can be part of LUN's as defined in the Striping, then Each LUN or Physical Disk could be mapped to CELL Disks inside exadata. These Cell Disks are partitioned and grouped in as GRID Disk.

When you create an ASM Disk Group, the ASM Disks are mapped to GRID Disk at storage server. Since Exadata Storage Server runs on Oracle Enterprise Linux, it is possible to login on the storage server as root user and use the new utility called as CELLCLI, this utility allows you to quert exadata cell disks and Grid disk along with creation of FLASH Disk and different IORM Management.


[root@qr01cel01 ~]# cellcli
CellCLI: Release 11.2.2.1.0 - Production on Thu Oct 27 03:44:36 EDT 2011

Copyright (c) 2007, 2009, Oracle. All rights reserved.
Cell Efficiency Ratio: 11M

CellCLI> list physical disk;
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK00

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK00 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK01

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK01 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK02

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK02 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK03

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK03 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK04

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK04 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK05

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK05 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK06

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK06 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK07

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK07 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK08

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK08 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK09

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK09 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK10

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK10 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK11

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK11 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH00

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH00 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH01

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH01 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH02

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH02 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH03

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH03 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH04

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH04 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH05

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH05 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH06

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH06 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH07

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH07 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH08

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH08 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH09

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH09 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH10

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH10 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH11

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH11 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH12

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH12 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH13

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH13 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH14

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH14 normal
/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH15

/opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/FLASH15 normal


You can list the celldisk as well, remember cell disks are mapped to physical disks or LUN

CellCLI> list celldisk;
CD_00_qr01cel01 normal
CD_01_qr01cel01 normal
CD_02_qr01cel01 normal
CD_03_qr01cel01 normal
CD_04_qr01cel01 normal
CD_05_qr01cel01 normal
CD_06_qr01cel01 normal
CD_07_qr01cel01 normal
CD_08_qr01cel01 normal
CD_09_qr01cel01 normal
CD_DISK10_qr01cel01 normal
CD_DISK11_qr01cel01 normal
FD_00_qr01cel01 normal
FD_01_qr01cel01 normal
FD_02_qr01cel01 normal
FD_03_qr01cel01 normal
FD_04_qr01cel01 normal
FD_05_qr01cel01 normal
FD_06_qr01cel01 normal
FD_07_qr01cel01 normal
FD_08_qr01cel01 normal
FD_09_qr01cel01 normal
FD_10_qr01cel01 normal
FD_11_qr01cel01 normal
FD_12_qr01cel01 normal
FD_13_qr01cel01 normal
FD_14_qr01cel01 normal
FD_15_qr01cel01 normal

Check the Details of celldisk for individual celldisk and list out the devices being used


CellCLI> list celldisk CD_00_qr01cel01 detail;
name: CD_00_qr01cel01
comment:
creationTime: 2010-12-09T21:10:33-05:00
deviceName: /opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK00
devicePartition: /opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK00
diskType: HardDisk
errorCount: 0
freeSpace: 0
id: 0000012c-ce0b-412f-0000-000000000000
interleaving: none
lun: /opt/oracle/cell11.2.2.1.0_LINUX.X64_100929/disks/raw/DISK00
raidLevel: "RAID 0"
size: 1.203125G
status: normal


As i told earlier that Grid disks are group of cell disks, you can list out grid disk by the below command.

CellCLI> list griddisk;
DATA_CD_00_qr01cel01 active
DATA_CD_01_qr01cel01 active
DATA_CD_02_qr01cel01 active
DATA_CD_03_qr01cel01 active
DATA_CD_04_qr01cel01 active
DATA_CD_05_qr01cel01 active
DATA_CD_06_qr01cel01 active
DATA_CD_07_qr01cel01 active
DATA_CD_08_qr01cel01 active
DATA_CD_09_qr01cel01 active
highredint1 active
highredint2 active
highredint3 active
jgd active
RECO_CD_00_qr01cel01 active
RECO_CD_01_qr01cel01 active
RECO_CD_02_qr01cel01 active
RECO_CD_03_qr01cel01 active
RECO_CD_04_qr01cel01 active
RECO_CD_05_qr01cel01 active
RECO_CD_06_qr01cel01 active
RECO_CD_07_qr01cel01 active
RECO_CD_08_qr01cel01 active
RECO_CD_09_qr01cel01 active
SYSTEMDG_CD_00_qr01cel01 active
SYSTEMDG_CD_01_qr01cel01 active
SYSTEMDG_CD_02_qr01cel01 active
SYSTEMDG_CD_03_qr01cel01 active
SYSTEMDG_CD_04_qr01cel01 active
SYSTEMDG_CD_05_qr01cel01 active
SYSTEMDG_CD_06_qr01cel01 active
SYSTEMDG_CD_07_qr01cel01 active
SYSTEMDG_CD_08_qr01cel01 active
SYSTEMDG_CD_09_qr01cel01 active

Details of Grid Disk for individual grid disk and its cell disk can be displayed by using the below command

CellCLI> list griddisk DATA_CD_00_qr01cel01 detail
name: DATA_CD_00_qr01cel01
availableTo:
cellDisk: CD_00_qr01cel01
comment:
creationTime: 2010-12-09T21:11:49-05:00
diskType: HardDisk
errorCount: 0
id: 0000012c-ce0c-6c96-0000-000000000000
offset: 128M
size: 688M
status: active

CellCLI>


Now lets have a look at ASMCMD which is part of our Database machine and see how our disk groups are mapped to Storage Server.

ASMCMD> lsdsk -G DATA
Path
o/192.168.1.11/DATA_CD_00_qr01cel01
o/192.168.1.11/DATA_CD_01_qr01cel01
o/192.168.1.11/DATA_CD_02_qr01cel01
o/192.168.1.11/DATA_CD_03_qr01cel01
o/192.168.1.11/DATA_CD_04_qr01cel01
o/192.168.1.11/DATA_CD_05_qr01cel01
o/192.168.1.11/DATA_CD_06_qr01cel01
o/192.168.1.11/DATA_CD_07_qr01cel01
o/192.168.1.11/DATA_CD_08_qr01cel01
o/192.168.1.11/DATA_CD_09_qr01cel01
o/192.168.1.12/DATA_CD_00_qr01cel02
o/192.168.1.12/DATA_CD_01_qr01cel02
o/192.168.1.12/DATA_CD_02_qr01cel02
o/192.168.1.12/DATA_CD_03_qr01cel02
o/192.168.1.12/DATA_CD_04_qr01cel02
o/192.168.1.12/DATA_CD_05_qr01cel02
o/192.168.1.12/DATA_CD_06_qr01cel02
o/192.168.1.12/DATA_CD_07_qr01cel02
o/192.168.1.12/DATA_CD_08_qr01cel02
o/192.168.1.12/DATA_CD_09_qr01cel02
o/192.168.1.13/DATA_CD_00_qr01cel03
o/192.168.1.13/DATA_CD_01_qr01cel03
o/192.168.1.13/DATA_CD_02_qr01cel03
o/192.168.1.13/DATA_CD_03_qr01cel03
o/192.168.1.13/DATA_CD_04_qr01cel03
o/192.168.1.13/DATA_CD_05_qr01cel03
o/192.168.1.13/DATA_CD_06_qr01cel03
o/192.168.1.13/DATA_CD_07_qr01cel03
o/192.168.1.13/DATA_CD_08_qr01cel03
o/192.168.1.13/DATA_CD_09_qr01cel03
ASMCMD> exit

You can see that all ASM Disks are mapped to GRID Disks, and a disk group DATA is created out of it.


Lets start with the Storage Index and Smart Scan technology by login into the Database Machine and Oracle Database for our test scenario.
For the test case, i have created a user and provided necessary privileges to it.

[oracle@qr01db01 ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 27 07:46:52 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create user ritesh identified by pass;

User created.

Elapsed: 00:00:00.27
SQL> grant dba to ritesh;

Grant succeeded.

Elapsed: 00:00:00.01


Lets login to Ritesh User and create a table which contains 10 Million rows, since we are using connect by level to generate 10 million
rows, we need to have a high PGA memory. Instead of setting PGA, i have changed the workarea_size_policy to manual and used
sort_area_size to 1GB.


SQL> conn ritesh/pass

Connected.

SQL> alter session set sort_area_size=1073741824;

Session altered.

Elapsed: 00:00:00.00

SQL> alter session set workarea_size_policy=MANUAL;

Session altered.

Elapsed: 00:00:00.00

SQL> create table oracledba_emp1 as
select rownum t1,rownum+1 t2 ,'Testing for Storage Indexes' t3
from dual connect by level<10000000; Table created. Elapsed: 00:00:43.48 SQL> select bytes/1024/1024 as gb from user_segments where segment_name='ORACLEDBA_EMP1';

GB
----------
496

Elapsed: 00:00:00.41


The table that i have created is nearly 500 MB at the moment, now the below query would demonstrate the use of storage index and smart
scan. Everytime when we run an operation in a session and we want to verify whether the operation is using the feature of exadata or now we could run this query and see how much physicall I/O is avoided to be accessed or to be travelled.

SQL> select
2 name,value/1024/1024 as stat_value
3 from v$mystat s, v$statname n
4 where s.statistic# = n.statistic# and n.name in (
5 'cell physical IO bytes saved by storage index',
6 'cell physical IO interconnect bytes returned by smart scan')
/

NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan .000328064

Elapsed: 00:00:00.33

SQL> save sess_wait;
Created file sess_wait.sql
SQL> @ sess_wait

NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan .000328064

Elapsed: 00:00:00.16

Now lets run a query against the table, note that there is no physical index being created on the table. lets see how the query behaves.


SQL> select * from ORACLEDBA_EMP1 where T1=4711;

T1 T2 T3
---------- ---------- ---------------------------
4711 4712 Testing for Storage Indexes

Elapsed: 00:00:20.01
SQL> @ sess_wait

NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 0
cell physical IO interconnect bytes returned by smart scan .072860718

You can see that there is no use of storage index and the query has taken nearly 20 seconds to finish. Now lets run the query again.

SQL> select * from ORACLEDBA_EMP1 where T1=4711;

T1 T2 T3
---------- ---------- ---------------------------
4711 4712 Testing for Storage Indexes

Elapsed: 00:00:00.06
SQL> @ sess_wait

NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 485.015625
cell physical IO interconnect bytes returned by smart scan .147186279

You can see that when we executed the query for the second time, then the query took only .06 seconds to finish. Query against a 500 MB table without a index lead to an improvement of nearly 1000% with exadata because of Storage Indexes. The value of 485 MB visible in by session statistics shows that storage index has saved at least 485 MB of data for a physical read.

Note : Storage Index takes time to create and they are created at storage server, thats why its being told that Exadata Storage Server is a database aware storage.

Now lets disable the feature of storage index of Exadata and see how it behaves in Non-Exadata environment. I am doing this by setting up an Underscore parameter.

SQL> conn / as sysdba
Connected.
SQL> col NAME format a40
SQL> col VALUE format a10
SQL> col DESCRIPTION format a40
SQL> col TYPE forma a10
SQL> col DEFLT forma a10
SQL> set lines 140
SQL> select a.ksppinm name,
2 b.ksppstvl value,
3 b.ksppstdf deflt,
4 decode(a.ksppity, 1,'boolean', 2,'string', 3,'number', 4,'file', a.ksppity) type,
5 a.ksppdesc description
6 from
7 sys.x$ksppi a,sys.x$ksppcv b
8 where
9 a.indx = b.indx
10 and (a.ksppinm like '\_%storageidx_disabled%' escape '\' or
11 12 a.ksppinm like '\_%cell_smart_scan%' escape '\'
13 ) order by name;

NAME VALUE DEFLT TYPE DESCRIPTION
---------------------------------- ---------- ---------- ---------- --------------------------
_allow_cell_smart_scan_attr TRUE TRUE boolean Allow checking

smart_scan_capable Attr
_kcfis_storageidx_disabled FALSE TRUE boolean Don't use storage index
optimization on the
storage cell

Now lets see if we disable the storage index feature by setting the undocumented parameter, how system behaves thereafter.

dbm1> conn ritesh/pass
Connected.
dbm1> select * from ORACLEDBA_EMP1 where T1=4711; -- With Storage Index
T1 T2 T3
---------- ---------- ---------------------------
4711 4712 Testing for Storage Indexes

Elapsed: 00:00:00.14

dbm1> @ sess_wait
NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 485.015625
cell physical IO interconnect bytes returned by smart scan .001792908

Elapsed: 00:00:00.01

dbm1> alter session set "_kcfis_storageidx_disabled"=TRUE; -- Storage Index Disabled Here

Session altered.

Elapsed: 00:00:00.00
dbm1> select * from ORACLEDBA_EMP1 where T1=4711; -- Without Storage Index
T1 T2 T3
---------- ---------- ---------------------------
4711 4712 Testing for Storage Indexes

Elapsed: 00:00:11.36

dbm1> @ sess_wait
NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 485.015625
cell physical IO interconnect bytes returned by smart scan .074325562

Elapsed: 00:00:00.00


dbm1> alter session set "_kcfis_storageidx_disabled"=FALSE; -- Enable Storage Indexes

Session altered.

Elapsed: 00:00:00.00
dbm1> select * from ORACLEDBA_EMP1 where T1=4711; -- With Storage Indexes

T1 T2 T3
---------- ---------- ---------------------------
4711 4712 Testing for Storage Indexes

Elapsed: 00:00:00.06
dbm1> @ sess_wait

NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 970.03125
cell physical IO interconnect bytes returned by smart scan .076118469

Elapsed: 00:00:00.01

Storage Index also works with bind variables, have a check with that.

dbm1> @ sess_wait

NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 970.03125
cell physical IO interconnect bytes returned by smart scan .076118469

Elapsed: 00:00:00.01

dbm1> variable b number
dbm1> exec :b:=1541;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

dbm1> select * from ORACLEDBA_EMP1 where T1=:b;
T1 T2 T3
---------- ---------- ---------------------------
1541 1542 Testing for Storage Indexes

Elapsed: 00:00:00.05

dbm1> @ sess_wait
NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 1,455.0469
cell physical IO interconnect bytes returned by smart scan .0779

Elapsed: 00:00:00.01

Now lets see a small glimpse of smart scan feature

dbm1> create table oracledba_emp2 as select * from oracledba_emp1;

Table created.

Elapsed: 00:00:40.34
dbm1> @ sess_wait
NAME STAT_VALUE
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index 1,455.0469
cell physical IO interconnect bytes returned by smart scan 440.5917

Elapsed: 00:00:00.00
dbm1>