Oracle mechanics

Igor Usoltsev's Oracle blog

Archive for the ‘hints’ Category

INDEX as inverse hint for BITMAP_TREE/INDEX_COMBINE

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

Suddenly the DB Time/Elapsed ratio bounced:

WORKLOAD REPOSITORY report for
...
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 »

Advertisements

Posted in CBO, hints, Oracle | Leave a 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 11.2.0.2. This note explains some interesting test results

Read the rest of this entry »

Posted in 11g, hints, Oracle, parameters | Tagged: , , | 1 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:

10.2.0.4.0#SQL> 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 |
 ---------------------------------------------------------------------------

10.2.0.4.0#SQL> alter index t_idx unusable;

Index altered.

10.2.0.4.0#SQL> 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 »