Oracle mechanics

Igor Usoltsev's Oracle blog

Archive for the ‘11g’ Category

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 »

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

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