Oracle mechanics

Igor Usoltsev's Oracle blog

Archive for the ‘wait events’ Category

_very_large_object_threshold

Posted by Игорь Усольцев on June 4, 2013

original in Russian

From Oracle 11.2 choice between serial direct path read and buffered read (through db buffer cache SGA) for INDEX FAST FULL SCAN (IFFS) operation depends on parameter:

SQL> @param_ _very_large_object_threshold

NAME                         VALUE IS_DEF   DSC
---------------------------- ----- -------- -----------------------------------------------------
_very_large_object_threshold 500   TRUE     upper threshold level of object size for direct reads

The correct parameter meaning is: the percent of buffer cache size (more precisely % of _db_block_buffers value). If index size exceeds this threshold than this index is considered as “huge”, and for IFFS operation will be used direct path read. Otherwise IFFS will use standard buffered reads – exactly as Sayan Malakshinov described in Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats). In the same note Sayan described hint INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X), which can be often noticed for SQL Profiles usage

Simple tests show _very_large_object_threshold influence on direct path read usage for full table scan, together with event 10949 Read the rest of this entry »

Advertisements

Posted in 11g, Oracle, parameters, wait events | Tagged: , | 1 Comment »

virtual circuit wait

Posted by Игорь Усольцев on March 26, 2011

When using shared (multi-threaded) servers connections with queries returning huge number of rows, the wait events virtual circuit wait may became noticable. Top 5 from AWR report for Oracle 11.1.0.7, for examle:

                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                           41,456          24.1
db file scattered read           16,480,224      40,887      2   23.7 User I/O
virtual circuit wait                674,827      39,310     58   22.8 Network
db file sequential read           1,820,163      27,374     15   15.9 User I/O
direct path read temp               166,394       3,653     22    2.1 User I/O

Adequate event description from Bug 6653834 – Split “virtual circuit status” into idle / non-idle waits [ID 6653834.8]:

This is a performance monitoring enhancement to split the ‘virtual circuit status’ wait event into two new wait events:

“shared server idle wait” – for when the shared server is idle waiting for something to do
“virtual circuit wait” – for when the shared server is blocked waiting on a specific circuit / message

i.e starting from Oracle  version 11.1.0.7/11.2.0.1 wait event virtual circuit status was splitted into two: shared server idle wait (wait class idle) and virtual circuit wait – non-idle event (wait class – network)

SQL> select name, wait_class
2    from v$event_name
3   where name in ('virtual circuit wait', 'shared server idle wait');

NAME                                                             WAIT_CLASS
---------------------------------------------------------------- ----------
virtual circuit wait                                             Network
shared server idle wait                                          Idle

Let’s make some tests to clarify this event actual meaning Read the rest of this entry »

Posted in 11.2, Oracle, wait events | 2 Comments »