(Source:www.oracledba.in: Author: Ritesh Singh)
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.
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.
- See more at: http://www.oracledba.in/Articles/display_article.aspx?article_id=54#sthash.Z1OlOPJE.dpuf
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.
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>
No comments:
Post a Comment