May-14

28-May

OS watcher location in  Exadata (Linux)

$ cd /opt/oracle.oswatcher/osw

To see a compressed file

$ bzcat <file name>   >  head -10

SQL monitor details using DBMS_SQLTUNE

set pagesize 0 echo on timing off
set linesize 200 trimspool on trim on
set long 2000000 longchunksize 2000000
select DBMS_SQLTUNE.REPORT_SQL_MONITOR( sql_id=>'&p_sqlid',event_detail=>'YES',report_level=>'ALL',type=>'TEXT') from dual;

11-May

DBA_HIST_ACTIVE_SESSION_HISTORY –  aggregate with SQL_PLAN_LINE_ID or SQL_PLAN_OPERATION to see in which line operation the time is spent.

Combine V$SQL_PLAN and DBA_HIST_ACTIVE_SESSION_HISTORY with SQL_ID and SQL_CHILD_NUMBER.

08-May

You can purge / enable /disable storage indexes

alter cell events = “immediate cellsrv.cellsrv_storidx(‘disable’, ‘ALL’, 0, 0, 0)”;
alter cell events = “immediate cellsrv.cellsrv_storidx(‘enable’, ‘ALL’, 0, 0, 0)”;
alter cell events = “immediate cellsrv.cellsrv_storidx(‘purge’, ‘ALL’, 0, 0, 0)”;

Enable / disable Flashcache compression

alter cell flashCacheCompress=TRUE.

On an X3 Exadata you should also run

alter cell flashCacheCompX3Support= TRUE

04- May

Database server disk controller configuration details – linux (run as root)

/opt/MegaRAID/MegaCli/MegaCli64 AdpAllInfo -aALL | grep “Device Present” -A 8

To check RDS protocol is sued for cluster interconnect over IB –

$ORACLE_HOME/bin/skgxpinfo

Exadata flahcache ‘write-back’ configuration is helpful to avoid  ‘Free buffer waits” as the dirty blocks will be flushed to the flashcache 10x times faster than disks.  Interestingly flashcache contents are persistent over cell server re-starts.

Ora600 error layers

http://www.eygle.com/digest/2010/08/ora_00600_code_explain.html

03-May

Oracle database background processes communicate with storage cells using iDB protocol which is built into the Oracle database core.  iDB protocol intelligent enough to manage cell offload processing and I/O Resource Manager (IORM). iDB is built using  Reliable Datagram Sockets (RDS)   which is the same protocol using for RAC inter instance communication over Infiniband network.  RDS is capable to do Remote Direct Memory Accesses (RDMA) to enable faster data copy by passing OS, known Zero-copy Datagram Protocol (ZDP).

 Storage Server software components

Cell Server (CellSRV) : Is the core of the cell server which responsible for servicing iDB requests from the DB nodes.  Also manages DBRM and IORM and priorities the requests.

Management Server (MS): is the primary interface for the administrator to monitor, manage, query and administer the Exadata cells using cellcli utility.  Dceli can be used to execute a single command across cell servers in the cluster.

Restart Server (RS):  Ensures the ongoing functionality of the CELLSRV and MS and the storage cell is available to the database.

Visit my facebook page: https://www.facebook.com/ora600tom