Sep 24

Oracle Wait Class Definition

Note :-- I will keep updating this post as i progress

Oracle Wait Class Definition 

1) PX Deq Credit: send blkd 

English Defiintion :--  PX  -- Parallel Execution
                                    Deq -- To remove the first available element from queue
                                    Credit  -- Acknowledgment of Some Work
                                    Send  :-- to permit
                                    blkd    :-- blocked

Technical Definition :-- Parallel Execution process wants to send message to slave and its not allowed

Note :-- Any parallel execution process has two sub process . One process say p0 queries the database and send the output to p1 another parallel sub process to send back the output of the query back to client or program

alter table emp parallel (degree 2); // this will enable two parallel process. The parallel process of any table in can be viewed in degree column of user_tables

PX Deq Credit: send blkd and PX Deq Credit: need buffer are same . The first one is seem more on localsystems and second one is seen on RAC systems 

There is no general recommendation to reduce this wait times

2) direct path read

English Definition :--  Direct :-- send toward a place or object
                               Path   :--- a course of action
                               Read :-- To anticipate

Technical Definition :-- The direct path read occurs when oracle access datafile into the PGA instead of SGA buffer memory .

This happens under these conditions 

1) Reading a Lob Segment

2) Doing a full-table scan

3) Reading a temp file 

This happen in case when table in query has more degree of parallelism . Because oracle thinks that doing a full table scan is more beneficial than doing index scan