Oracle mechanics

Igor Usoltsev's Oracle blog

  • Categories


Posted by Игорь Усольцев on January 30, 2016

Suddenly the DB Time/Elapsed ratio bounced:

Elapsed:               30.08 (mins)
DB Time:            1,035.38 (mins)

Snap Time           Load    %busy    %user     %sys    %idle  %iowait
--------------- -------- -------- -------- -------- -------- --------
15-Jan 08:00:06     26.7      N/A      N/A      N/A      N/A      N/A
15-Jan 08:30:10     48.4     69.7     65.4      3.2     30.3      6.5 -- *
15-Jan 09:00:13      6.2     33.0     30.2      2.3     67.0      3.5

– causing significant Load [Average] rising (*) accordingly

The problem was caused by one problem sql_id, from the same AWR:

SQL ordered by Elapsed Time    DB/Inst: ORCL/orcl1  Snaps: 321153-321154
        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        32,145.0         33,505          0.96   51.7   72.6     .0 c9cxc4hy2tbsa -- obviously this
         9,798.9              0           N/A   15.8   45.7   45.9 1mbx3m11g17vn
         2,533.0          4,113          0.62    4.1   22.7   22.1 c8cx3cxf286d7

According to V$SQL/V$SQL_SHARED_CURSOR statistics query with sql_id = c9cxc4hy2tbsa used two execution plans with quite different average elapsed time (column ELA_PER_EXEC, in microseconds) for the problem period: Read the rest of this entry »

Posted in CBO, hints, Oracle | Leave a Comment »

Huge sql plans comparision

Posted by Игорь Усольцев on January 27, 2014

original in Russian

Once upon a day we’ve got a little trouble on OEBS (Oracle Apps)  db server:

– while there not exist any valuable I/O related waits, these temporary  difficulties were not catastrophic, and db servers still remain accessible and manageable. But high CPU load / LA reason are typical for Oracle performance problem, and this is interesting topic for the post imho

AWR for the problem period: Read the rest of this entry »

Posted in 11g, CBO, Oracle | Tagged: | Leave a Comment »


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 »

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

The elements of Bind-Aware Cursor Sharing and Cardinality Feedback in Oracle 11g

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

Sometimes there may be noticed unusual Oracle 11g behavior – by some not obvious reasons the magic Adaptive/Extended Cursor Sharing is not used. And this can happen exactly where execution plan changes are required for better SQL with new set of binds performance. From Oracle Optimizer team we know that Bind Aware Cursor Sharing (BASC) is complex technology which consists of couple of consequential applying Adaptive Cursor Sharing (ACS) and Extended Cursor Sharing (ECS) technologies:

“How does ACS work?

  • Monitor queries where ECS is applicable (binds were peeked, predicate shape)
  • Identify queries with variable data volume
  • Enable ECS for these queries”

In MOS Adaptive Cursor Sharing Overview [ID 740052.1] can be found a list of preventing ECS usage conditions:

If any of the following checks fail ECS will be disabled :

  • Extended cursor sharing is disabled
  • The query has no binds
  • Parallel query is used
  • Certain parameters like (“bind peeking”=false) are set
  • Hints are in use
  • Outlines are being used
  • It is a recursive query
  • The number of binds in a given sql statement are greater than 14

What we have to do if all conditions were complied and ECS still doesn’t work? Initial problem was successfully resolved by adding /*+ BIND_AWARE*/ hint into query – according to Bug 9532657:

For cursors where one knows it wants to be bind aware then add a /*+ BIND_AWARE */ hint. This makes the cursor aware of bind values rather than automatically trying to work out …

– fortunately, there was possibility to change the query text in our case.

But questions remain: how to force or forbid BACS technology usage on system / session / single query level?

On RUOUG conference I presented simple SQL*Plus demonstration of Bind Aware Cursor Sharing (as well as closely linked Cardinality Feedback) elements on Oracle This note explains some interesting test results

Read the rest of this entry »

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

Optimizer point of view on literal values usage

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

Limitations of the Oracle Cost Based Optimizer [ID 212809.1]:

“From the optimizer point of view, it is recommended to use literal values where there is the possibility of significant performance differences associated with using different bind variable values and applying the same plan for all executions. In these cases the choice of a good plan usually significantly outweighs the potential usage of shared pool space (assuming that the shared pool usage is not excessive)”

It seems interesting for me! This quote from MOS doc is not ancient – last update 25-JUL-2011. Bind peeking ( from 9i), and Adaptive Cursor Sharing (11g) technologies work for a long time already, but the literal values usage are still preferable for CBO %)

Posted in CBO, Oracle | Leave a Comment »

Recursive queries for materialized view operations – 2

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

Not long ago I’ve met again the problem of the strange execution plan for CREATE MV recursive query. It looks like some query transformation were not executed for the plan building process.

Finally, I’ve found very important MOS note which explains this strange behaviour – Create Materialized View or Complete Refresh Taking Longer Than CTAS or Insert-Select [ID 763718.1]:

which was closed as not feasible to fix and, as only workaround, to force optimizer to pick same plan for non-refresh operations.

This is caused by CBQT being disabled for mview operations»

i.e. Cost Based Query Transformation (CBQT) are not available for Create Materialized View / Complete Refresh operations in Oracle 10.2.-11.2

I’ve tried to fix the optimal execution plan for the Create Materialized View recursive sql – unsuccessfully, despite of dba_outlines.used column became “USED” just after CREATE MV execution

SQL> select name, used, version from dba_outlines
2  /

NAME                           USED   VERSION
------------------------------ ------ ------------------------------------
TEST_QUERY2                    USED

Posted in 10.2, 11.2, bugs, MV | Leave a Comment »

Unusable index, hint INDEX and updated RBO in Oracle 11.2

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

Oracle shows some interesting things when the hinted query runs against the table with unusable index.

Until the query does not include hints, we can see the expected behavior – optimizer chooses INDEX RANGE SCAN when index status is VALID, and TABLE FULL SCAN when index is UNUSABLE:> select count(rowid) from t where owner = 'OUTLN';
 | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 |   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
 |   1 |  SORT AGGREGATE   |       |     1 |    18 |            |          |
 |*  2 |   INDEX RANGE SCAN| T_IDX |    80 |  1440 |     1   (0)| 00:00:01 |
 ---------------------------------------------------------------------------> alter index t_idx unusable;

Index altered.> select count(rowid) from t where owner = 'OUTLN';
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 |   0 | SELECT STATEMENT   |      |       |       |    12 (100)|          |
 |   1 |  SORT AGGREGATE    |      |     1 |    18 |            |          |
 |*  2 |   TABLE ACCESS FULL| T    |    80 |  1440 |    12   (0)| 00:00:01 |

Read the rest of this entry »

Posted in 11.2, CBO, hints | Leave a Comment »

Recursive queries for materialized view operations with database link

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

For operations Create Materialized View or Complete Refresh for mviews with remote tables (over database links), the execution plan can significantly differ from the base query plan, as well as from Create Table As Select (CTAS) plan. Some tests:

SQL> create table t as select * from dba_tables;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYSTEM','T');

PL/SQL procedure successfully completed.

SQL> create materialized view t_mv as select t1.* from t@db_link tr, t t1,t t2 where tr.table_name = t1.table_name and t2.table_name = t1.table_name;

In Oracle 11.2 there can be found two open active cursors for my session in v$open_cursor, and one of cursors  is marked as OPEN-RECURSIVE (new column CURSOR_TYPE in v$open_cursor 11.2 version), with the strange value for LAST_SQL_EXEC_TIME column. In Oracle 10.2 and 11.1 there isn’t the field CURSOR_TYPE in v$open_cursor yet, and recursive cursor can be easily found by v$sql.sql_text

SID USER_NAME ADDRESS  HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIME SQL_EXEC_ID CURSOR_TYPE
--- --------- -------- ---------- ------------- ------------------------------------------------------------ -------------------- ----------- --------------
67  SYSTEM    233CD018 2317899508 3vcya0y52hprn CREATE TABLE "SYSTEM"."T_MV"  AS select t1.* from t@db_link  01-JAN-70              16777217  OPEN-RECURSIVE
67  SYSTEM    2639FCDC 1245822999 2dvw4u1543h0r create materialized view t_mv as select t1.* from t@db_link                         16777217  OPEN

Read the rest of this entry »

Posted in 10.2, 11.2, heuristics, MV, Oracle | Leave a 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, for examle:

                                                          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 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 »