Exadata Database Machine

Exadata database machine configurations

List of Exadata Models
Model
Rack Size
DB Servers
Storage Servers
High Capacity
X2-2
Quarter
2
3
33 TB
Half
4
7
77 TB
Full
8
14
154 TB
X2-8
Exadata DB Machine
2
14
154 TB
X3-2
Eighth
2
3
23 TB
Quarter
2
3
48 TB
Half
4
7
112 TB
Full
8
14
224 TB
X3-8
Full
2
14
224 TB
X4-2
Eighth
2
3
72 TB
Quarter
2
3
144 TB
Half
4
7
336 TB
Full
8
14
672 TB
X5-2
Eighth
2
3
9.6 TB Flash
144 TB HD
Quarter
2
3
19.2 TB Flash
144 TB HD
Half
4
7
44.8 TB Flash
336 TB HD
Full
8
14
89.6 TB Flash
672 TB HD
Storage cell Command line utilities
  • Users configured for each storage server:
    • root
    • celladmin
    • cellmonitor
  • Configuration files
    • cellini.ora
    • cellip.ora
  • cellcli – cell command line – one cell at a time (local cell.)
    • Commands
      • CALIBRATE FORCE
        • must run as root.
        • I/O rates for LUNs
        • cellsrv will be shutdown – with FORCE
      • ALTER CELL
        • can be done as celladmin
        • ALTER CELL {SHUTDOWN | RESTART | STARTUP} SERVICES {RS | MS | CELLSRV | ALL}
          • ALTER CELL RESTART SERVICES ALL
        • ALTER CELL
          • smtpServer=’mail.example.com’
          • smtpFromAddr=’exadata@example.com’
          • smtpToAddr=’dba@example.com’
          • notificationPolicy=’critical,warning,clear’
          • notificatioMethod=’mail’
      • LIST CELLDISK
      • CREATE CELLDISK ALL HARDDISK
      • list iormplan detail
      • alter iormplan objective = basic | low_latency | high_throughput | balanced | auto
      • ALLOCATION BASED: alter iormplan dbplan =(
        • (name=db_prod1, level=1, allocation=80, limit=90),
        • (name=db_prod2, level=1, allocation=20, limit=30),
        • (name=db_dev, level=2, allocation=100),
        • (name=db_test, level=3, allocation=50, limit=75),
        • (name=other, level=3, allocation=50, limit=75)),
      • catplan=”
      • Maximum of 32 directives allowed
      • Maximum of 8 levels allowed
      • remove interdatabase plan: ALTER IORMPLAN dbplan=”
      • SHARE BASED: alter iormplan dbplan =(
        • (name=db_prod1, share=8),
        • (name=db_prod2, share=2),
        • (name=db_dev, share=10),
        • (name=db_test, share=5),
        • (name=default, share=5),
      • catplan=”
      • Maximum of 1024 directives
      • Maximum of 32 shares (share value from 1 to 32)
      • In case of Data Guard standby:
      • alter iormplan dbplan =(
        • (name=primary, level=1, allocation=30, role=primary),
        • (name=standby, level=1, allocation=35, role=primary),
        • (name=primary, level=2, allocation=20, role=standby),
        • (name=standby, level=2, allocation=25, role=standby),
        • (name=other, level=3, allocation=50)),
      • catplan=”
dcli – for multiple hosts at a time (database or storage)
  • dcli = distributed command line – multiple hosts at a time
    • Can run from database server to effect cell servers – must have cell admin rights
    • group file: list of hosts
    • Commands
      • dcli -g <group_file> -l <user_id> <command>
      • dcli -g db_group -l oracle date
        • <shows the date in all hosts in the db_group host list>
        • exdb01: Thu Apr 10 03:20:53 CDT 2014
        • exdb02: Thu Apr 10 03:20:53 CDT 2014
      • dcli -g <group_file> cellcli -e <command>
      • dcli -g cell_group cellcli -e list cell
        • <shows the status in all hosts in the cell_group host list>
        • cell01: online
        • cell02: online
      • dcli -g <group_file> -x <command_file>
      • dcli -g cell_group -x cellclicommands.scl
      • dcli -g <group_file> -l <user> -x <shell_file>
      • dcli -g db_group -l root -x dbwork.sh
Smart Scan
Was a Smart Scan  executed?
Check these fields in this view: V$SQL or GV$SQL
  • io_cell_offload_eligible_bytes = bytes of data that qualify for offload
  • io_cell_offload_returned_bytes = bytes returned by the regular I/O path (not offloaded to the cells)
  • io_cell_offload_eligible_bytes – io_cell_offload_returned_bytes = bytes actually offloaded during query execution.
  • No Smart Scan = both columns equal zero.
  • Special case:
    • io_cell_offload_eligible_bytes = 0 AND io_cell_offload_returned_bytes != 0
      • Referencing fixed views
      • Data dictionary views
  • Smart Scan operations in a query plan:
    • TABLE ACCESS STORAGE FULL
    • INDEX STORAGE FULL SCAN
    • INDEX STORAGE FAST FULL SCAN
  • Smart Scan processing
    • Column projection – limits columns
    • Predicate filtering – limits rows
    • Storage indexes
select  sql_id,
     io_cell_offload_eligible_bytes qualifying,
     io_cell_offload_returned_bytes not_offloaded,
     round(((io_cell_offload_eligible_bytes – io_cell_offload_returned_bytes)/io_cell_offload_eligible_bytes)*100, 2) pct_offloaded
from v$sql
where sql_id = ‘&sql_id’
V$SQL columns:
PHYSICAL_READ_BYTES
PHYSICAL_WRITE_BYTES
IO_INTERCONNECT_BYTES
IO_CELL_OFFLOAD_ELIGIBLE_BYTES
IO_CELL_UNCOMPRESSED_BYTES
IO_CELL_OFFLOAD_RETURNED_BYTES
OPTIMIZED_PHY_READ_REQUESTS
Statistics:
select s.name, m.value/1024/1024 MB
from v$sysstat s, v$mystat m
where s.statistic# = m.statistic#
and   (s.name LIKE ‘physical%total bytes’
or    s.name LIKE ‘cell phys%’
or    s.name LIKE ‘cell IO%’);
Wait Events:
select distinct event, total_waits, time_waited/100 wait_secs, average_wait/100 avg_wait_secs
from v$session_event e, v$mystat s
where event LIKE ‘cell%’
and   e.sid = s.sid;
Turn Smart Scan on/off
alter session set cell_offload_processing=false;
alter session set “_serial_direct_read”=false;
View to check functions that can be offloaded
V$SQLFN_METADATA
Storage Indexes
Storage Indexes were used
value > 0
select *

from v$mystat
where statistic# = (select statistic# from v$statname where name = ‘cell physical IO bytes saved by storage index’);
If there is no 1 MB segment that does not contain the searched for value, the storage index cannot tell Oracle to skip any of the segments.  Thus the value of cell physical IO bytes saved by storage index is equal to zero.
Database parameters that control storage index usage
ALTER SESSION SET “_…..” = <value>;
_kcfis_storageidx_disabled = FALSE (default) TRUE disables storage index usage
_kcfis_storageidx_diag_mode = 0 (default) 2 enables tracing; 1 disables storage index usage
_cell_storidx_mode = EVA (default) EVA, KDST, ALL.
Smart Flash Cache
Smart flash Cache was used
SELECT sn.name,ms.value

FROM V$MYSTAT ms, V$STATNAME sn
WHERE ms.STATISITIC#=sn.STATISTIC#
AND sn.name = ‘cell flash cache read hits’;
When the attribute cell_flash_cache is set to DEFAULT, the Smart Scans are slower and no cell flash cache read hits are generated.  Change the cell_flash_cache attribute to KEEP and the Smart Scans will be faster and cell flash cache read hits will increase.
alter table kso.skew3 storage (cell_flash_cache keep);
Check to see if a table has been designated to be kept in the Smart Flash Cache:
select owner, table_name, status, last_analyzed, cell_flash_cache
from dba_tables
where cell_flash_cache like nvl(‘&cell_flash_cache’, ‘KEEP’);
Hybrid Columnar Compression (HCC)
Will only compress when loading data using direct path loads.
Conventional inserts and updates will be stored using OLTP compression.
Levels of Compression
  1. QUERY LOW
  2. QUERY HIGH
  3. ARCHIVE LOW
  4. ARCHIVE HIGH
CREATE TABLE . . . COMPRESS FOR <HCC TYPE>
CREATE TABLE . . . COMPRESS FOR ARCHIVE HIGH;
Alter table compress will not compress the existing data;  need to move the table:
ALTER TABLE . . . MOVE COMPRESS FOR QUERY HIGH;
Query to check the type of compression for a given table
select distinct

        ‘&&owner’ owner,
        ‘&&tabname’ table_name,
        dbms_compression.get_compression_type(‘&&owner’,’&&tabname’, rowid) comp_typ,decode(dbms_compression.get_compression_type(‘&&owner’,’&&tabname’, rowid),
               1, ‘NONE’,
               2, ‘OLTP’,
               4, ‘QUERY HIGH’,
               8, ‘QUERY LOW’,
              16, ‘ARCHIVE HIGH’,
              32, ‘ARCHIVE LOW’) type_name
from &&owner..&&tabname;
Parallel Processing
Parameters:
parallel_adaptive_multi_user = TRUE (default); need to be set to FALSE in Exadata
parallel_force_local = FALSE; set to TRUE to use all nodes in a RAC cluster.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s