Search Oracle Related Sites

Tuesday, December 31, 2013

Exadata Wait Event - Cell Statistics Gather


cell statistics gather

This wait event appears when a select is done on the V$CELL_STATE , V$CELL_THREAD_HISTORY , or V$CELL_REQUEST_TOTALS tables. During the select, data from the cells and any wait events are shown in this wait event.


cell statistics gather ==> appears when a select is done on the V$CELL_STATE, V$CELL_THREAD_HISTORY, or V$CELL_REQUEST_TOTALS tables. During the select, data from the cells and any wait events are shown in this wait event.

Exadata Wait Event - Cell Smart Table Scan


cell smart table scan

This wait event appears when the database is waiting for table scans to complete on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.


cell smart table scan ==> appears when the database is waiting for table scans to complete on a cell

Exadata Wait Event - Cell Smart Restore from Backup


cell smart restore from backup

This wait event appears when the database is waiting for the completion of a file initialization for restore from backup on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.
 
cell smart restore from backup ==> Appears when the database is waiting for the completion of a file initialization for restore from backup on a cell

Exadata Wait Event - Cell Smart Index Scan


cell smart index scan

This wait event appears when the database is waiting for index or index-organized table (IOT) fast full scans. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.


cell smart index scan ==> Appears when the database is waiting for index or index-organized table (IOT) fast full scans

Exadata Wait Event - Cell Smart Incremental Backup


cell smart incremental backup

This wait event appears when the database is waiting for the completion of an incremental backup on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell when compared to the rest of the cells.

Exadata Wait Event - Cell Smart File Creation


cell smart file creation
This wait event appears when the database is waiting for the completion of a file creation on a cell. The cell hash number in the P1 column in the V$SESSION_WAIT view for this event should help identify a slow cell compared to the rest of the cells.

cell smart file creation ==> Appears when the database is waiting for the completion of a file creation on a cell

Exadata Wait Event - Cell Single Block Physical Read


cell single block physical read

This wait event is equivalent to db file sequential read for a cell. The P1 , P2 , and P3 columns in the V$SESSION_WAIT view for this event identify the cell hash number, disk hash number, and the number of bytes processed during the I/O read operation.


cell single block physical read ==> equivalent to db file sequential read for a cell

Exadata Wait Event - Cell Multiblock Physical Read


cell multiblock physical read

This wait event is equivalent to db file scattered read for a cell. The P1 , P2 , and P3 columns in the V$SESSION_WAIT view for this event identify the cell hash number, disk hash number, and the total number of bytes processed during the I/O read operation.


cell multiblock physical read ==> Equivalent to db file scattered read for a cell

Exadata Wait Event - Cell List of Blocks Physical Read


cell list of blocks physical read

This wait event is equivalent to db file parallel read for a cell. The P1, P2, and P3 columns in V$SESSION_WAIT view for this event identify the cell hash number, disk hash number, and the number of blocks processed during the I/O read operation.

cell list of blocks physical read ==> Equivalent to db file parallel read for a cell

Exadata Wait Events - Cell Interconnect Retransmit During Physical Read



cell interconnect retransmit during physical read
 
This wait event appears during retransmission for an I/O of a single-block or multiblock read. The cell hash number in the P1 column in the V$SESSION_WAIT view is the same cell identified for cell single block physical read and cell multiblock physical read. The P2 column contains the subnet number to the cell, and the P3 column contains the number of bytes processed during the I/O read operation.


cell interconnect retransmit during physical read ==> Appears during retransmission for an I/O of a single-block or multiblock read.


Saturday, December 28, 2013

Top 5 Timed Foreground Events - enq: TX - allocate ITL entry

 enq: TX - allocate ITL entry (Interested Transaction Lists)

Enqueues are locks that coordinate access to database resources. This event indicates that the session is waiting for a lock that is held by another session. 

The name of the enqueue is included as part of the wait event name, in the form enq: enqueue_type - related_details. In some cases, the same enqueue type can be held for different purposes, such as the following related TX types:
enq: TX - allocate ITL entry
enq: TX - contention
enq: TX - index contention
enq: TX - row lock contention

These are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.


The event “enq: TX - allocate ITL entry” indicates “enqueues” (locks) that are waiting for serial access to Oracle resources, in this case the transaction is waiting for a lock that is held by a competing session.

When too many concurrent DML transactions are competing for the same data block we observe this wait event - " enq: TX - allocate ITL entry"


One can find out the segments which are associated with this wait event in the same AWR under the “Segments by ITL Waits” section


By looking at the Segments by ITL waits we can find out the  segments which are experiencing this wait event are Indexes. 

One can also find out by running the below Sql’s

select 
   sql_id, 
   event, 
   count(*), 
   lpad(round(ratio_to_report(count(*)) over () * 100)||'%',10,' ') percent
from 
   dba_hist_active_sess_history
where 
   sample_time between timestamp 'XXXX-XX-XX 10:00:00' 
and 
   timestamp 'XXXX-XX-XX 11:00:00'
and 
   event = 'enq: TX - allocate ITL entry'
group by 
   sql_id, event
order by 
   percent desc;

Once you know the SQL_ID (from the above query, you can plug it into this query to find the exact object that is experiencing the wait:


select * 
from (
   select 
      ash.sql_id,
      ash.current_obj#,
      obj.object_name,
      count(*) as wait_count
   from 
      dba_hist_active_sess_history ash 
   left join 
      dba_objects obj 
   on 
      obj.object_id = ash.current_obj#
   where 
      ash.sample_time between timestamp 'XXXX-XX-XX 10:00:00' 
   and 
      timestamp 'XXXX-XX-XX 11:00:00'
   group by 
      ash.sql_id, 
      ash.top_level_sql_id, 
      ash.current_obj#, 
      obj.object_name 
   order by 
      4 desc
) 
where 
   sql_id = 'xxxxxx'
and 
   rownum <= 10;



By default INITRANS value for table is 1 and for index is 2. When too many concurrent DML transactions are competing for the same data block we observe this wait event - " enq: TX - allocate ITL entry"

Solutions

Once the table or index is reorganized by altering the INITRANS or PCTFREE parameter, it helps to reduce "enq: TX - allocate ITL entry" wait events.
To reduce enq: TX - allocate ITL entry" wait events, We need to follow the below steps.

Solution 1

1) Depending on the amount of transactions in the table we need to alter the value of INITRANS.
alter table <table name> INITRANS 50;

2) Then re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of this table as below

alter index <index_name> rebuild INITRANS 50;

 If the issue is not resolved by the above steps, please try by increasing PCTFREE


Solution 2

1) Spreading rows into more number of blocks will also helps to reduce this wait event.
alter table <table name>  PCTFREE 40;

2) Then re-organize the table using move (alter table service_T move;)

3) Rebuild index

alter index index_name  rebuild PCTFREE 40;

 OR You can combine steps A and B as below

1) Set INITRANS to 50  pct_free to 40

alter table <table_name> PCTFREE 40  INITRANS 50;

2) Then re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of the table as below

alter index <index_name>  rebuild PCTFREE 40 INITRANS 50;

NOTE:

The table/index can be altered to set the new value for INITRANS. But the altered value takes effect for new blocks only. Basically you need to rebuild the objects so that the blocks are initialized again.

For an index this means the index needs to be rebuild or recreated.

For a table this can be achieved through:
  • exp/imp
  • alter table move
  • dbms_redefenition
If one has to understand and learn more on ITL waits please visit Arup Nanda’s blog below where he clearly explained it with examples.





Analyzing and Interpreting AWR Report - Slideshare PPT

 

The PPT talks about the architectural changes of AWR and why it is more powerful than the Statspack Report. Detailed discussion on Oracle Time Model, Wait Classes & Metrics.

http://www.slideshare.net/pasalapudi/analyzing-and-interpre?from_search=5

SQL ordered by Physical Reads (UnOptimized) from 11g AWR

This blog describes the 'SQL ordered by Physical Reads (UnOptimized)' section in AWR reports from Oracle 11.2 ownwards and explains how to interpret certain sections. This section has been added keeping in view the features of Smart Flash Cache features in 11g as well as Exadata

The  'SQL ordered by Physical Reads (UnOptimized)' section would look similar to the following







Optimized Read Requests are read requests that are satisfied from the Smart Flash Cache ( or the Smart Flash Cache in OracleExadata  (Note that despite same name, concept and use of  'Smart Flash Cache' in Exadata  is different from  'Smart Flash Cache' in Database Smart Flash Cache)). Read requests that are satisfied from this cache are termed 'optimized' since they are provided much faster than requests from disk (implementation uses solid state device (SSD) technology). Additionally, read requests accessing Storage Indexes using smart scans in Oracle Exadata  (and significantly reducing  I/O operations) also fall under the category 'optimized read requests'  since they avoid reading blocks that do not contain relevant data. 

In database systems where 'Optimized Read Requests' are not present, UnOptimized Read Reqs will be equal to Physical Read Reqs (I/O requests satisfied from disk).  In this case columns 'UnOptimized Read Reqs' and 'Physical Read Reqs' will display the same values and column '%Opt' will display zero (as seen in extract from AWR report above)


Note that the 'Physical Read Reqs' column in the 'SQL ordered by Physical Reads (UnOptimized)' section is the number I/O requests and not the number of blocks returned. Be careful not to confuse these with the Physical Reads statistics from the AWR section 'SQL ordered by Reads', which counts database blocks read from the disk not actual I/Os (a single I/O operation  may return many blocks from disk)


If you look at the example 'SQL ordered by Reads' section below from same AWR report for database not using smart cache,notice the physical reads




Note the difference between 'Physical Reads' in the 'SQL ordered by Reads'  and the 'Physical Read Reqs' in the 'SQL ordered by Physical Reads (UnOptimized)' section above for the SQL with "SQL_ID=8q4akpck0nxgg"  from same AWR report.

Source:My Oracle Support



Tuesday, May 21, 2013

Top 5 Timed Foreground Events - control file sequential read


control file sequential read

This wait events occurs If one has all their control files on a disk with high disk I/O then access to control file for updating the SCN etc may result in this wait.Reading from the control file. This happens in many cases. For example, while
1)Making a backup of the control files
2)Sharing information (between instances) from the control file
3)Reading other blocks from the control files
4)Reading the header block

Wait Time: The wait time is the elapsed time of the read








In the above u see control file sequential read is on the top 5 list with wait class as System I/O. In this case if has come on the top as the no of transactions hitting the system have crossed 1000 Transactions Per Second(TPS) and the database is an high commit oriented OLTP system. That is the reason we see log file sync as one of the top 5 wait events.

Solution: Identify the Control file locations and try to place them on faster disks or less activity disks.