Exadata Storage Space Calculation

Exadata the Oracle engineered database machine has taken the OLTP database database performance to the next level. It has a number of power pack features flash cache, Smart scans and storage indexes that really provides the great returns on the investment.

How much usable storage a cutomer gets with every configurtion of the Exadata (quarter, Half or full Rack) is a little tricky for the customers guage.


Now below diagram shows the Exadata disk architecture for every disk. The physical is virtualized as a cell disk and then a grid disk to be presented to the Disk groups. (i'll keep the disk architecture discussion for some later post)

now for all the cells the structure would be something like

Lets take the example and oracle Exadata quarter rack example ang calculate what exactly the customer gets in terms of storage space.

The Quarter Rack Consists of

- 2 DB nodes
- 3 Storage Cells


Now the 3 Storage cells ( with high performance disks) has the following configuration


Step 1: Calculate the RAW Storage 

total Raw capacity => 12 disk on each cell * 600 GB each disk * 3 cells
                   => 21600GB or 21.09TB


now 30 GB on the first two disks of every cell is reserved for Linux OS. So by default to a disk group is created with 30 GB of disk space from remaining 10 disks from every cell.

so we have a Disk Group name DBFS => 10 disks from each cell * 30 GB from each disk *3 cells
                                                   DBFS => 900 GB

so We are left with 570GB on each disk i-e a total of "20520 GB" or  20.03TB 



Step 2: Now consider the mirroring requirement

 After NORMAL Redundancy (i-e double mirroring) Avialable RAW CAPACITY 20.03/2 =  10.01TB.

Step 3: Reserve free space for fault tolerance. 

with Disk failure coverage (DFC) one disk of 600GB is required to protect against the failure of one disks in a cell.

NOTE: that is a very important part. You need to plan for a Disk failure coverage or for complete cell node failure coverge. The ORACLE support document "Understanding ASM Capacity and Reservation of Free Space in Exadata (Doc ID 1551288.1)" clearly describes the different strategies and their pros and cos.

NOTE: After the application of BPS10 (11.2.0.4.10) the default is now the Disk Failure Coverage rather than the Cell Failure Coverage.


 This means that available is 10.01 TB - 0.57TB (i-e 600GB) =>  9.4 TB

Step 4: Decide the proportion of the Recovery Area. For example 60/40 (40% for recovery area)


 So the Recovery area size (RECODG) would be => 3.76 TB
and the DATADG would be => 5.64TB


                                                       

Oracle Real-Time SQL Monitoring

Oracle real-time SQL Monitoring is one of the very exciting features i have used recently. Its a very effective way to identify problems related to performance in long running queries especillay with the ones with complex execution Plans. It shows Stats at every step in the execution plan with key performance metrics including elapsed time, CPU time, number of reads and writes, I/O wait time and various other wait times. It allows us to have a deeper look inside the query plans and pin point the exact pain areas and then develop a startegy for SQL tuning.

The good part is the SQL Monitoring is also available with Oracle Enterprise Manager. Below is the Snapshot from the Enterprise manager 12c

It can also be manually Extracted using the following script.
In the below example I am going to extract a SQL monitoring report for a very complex SQL. The SQL monitoring report extracted in HTML format clearly identify the steps where the most of the time id spent on

SET LONG 1000000SET LONGCHUNKSIZE 1000000SET LINESIZE 1000SET PAGESIZE 0SET TRIM ONSET TRIMSPOOL ONSET ECHO OFFSET FEEDBACK OFF
SPOOL report_sql_monitor.htmSELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => '2fj0jatxnstyu', -- pass the SQL id as the argumenttype => 'HTML',report_level => 'ALL') AS reportFROM dual;SPOOL OFF


The step # 32 shows 96% of the CPU time is spent on it.