_direct_read_decision_statistcs_driven, _small_table_threshold and direct path reads on partitioned tables in 11.2.0.3 (Part 2)

This is continuation of my last post regarding direct path reads on partitioned tables in Oracle 11.2.0.3.

To recap, the behavior I observed is that direct path reads will be performed if number of blocks for all partitions that will be accessed exceeds _small_table_threshold value. That is if a table is consisted of 10 partitions each having 100 blocks and if a query goes after two of the partitions, direct path reads will be performed if _small_table_threshold is lower than 200.

Also regardless of how much data has been cached(in the buffer cache)  for each of the partitions, if direct path reads are to be performed, all partition segments will be directly scanned. So, it is all or nothing situation.

I also indicated that _direct_read_decision_statistics_driven parameter was set to TRUE (default) for the tests done in my earlier post.

What is _direct_read_decision_statistics_driven anyway? According to the parameter description, it enables direct path read decision to be based on optimizer statistics. If the parameter is set to FALSE Oracle will use segment headers to determine how many blocks the segment has. (read Tanel Poder’s blogpost for more information)

Let’s see how queries that access table partitions (full scan) behave if _direct_read_decsision_statiscs_driven parameter is set to FALSE in 11.2.0.3. My expectation was that it should be the same as if it was set to TRUE. I thought that once Oracle gets information about the number of blocks in each of the partitions it would use the same calculation as if the parameter was set to TRUE. Let’s see.

But, before moving forward a small disclaimer: Do not perform these tests in production or any other important environment. Changing of undocumented parameters should be done under the guidance of Oracle Support. The information presented here is for demonstration purposes only.

I will use the same table, TEST_PART, that I used in my earlier post.

I started with flushing the buffer cache (to make sure none of the partitions has blocks in the cache).

I set _direct_read_decision_statistcs_driven parameter to false and ran a query that selects data from PART_1 partition only. Each of the partitions contains 4000 rows stored in 65 blocks, plus one segment header block.

_small_table_threshold in my sandbox environment was set to 117.

SQL>  alter session set "_direct_read_decision_statistics_driven"=FALSE;

Session altered.

SQL> SELECT count(1) FROM test_part WHERE col1 in (1);

  COUNT(1)
----------
      4000

As expected, no direct path reads were performed (I used my sese.sql script that scans v$sesstat for statistics that match given keyword)

SQL> @sese direct

no rows selected

Now let’s see what happens with a query that accesses the first two partitions. Remember if _direct_read_decision_statistcs_driven parameter is set to TRUE, this query would perform direct path reads because the number of blocks in both partitions, 130 (2x65) exceeds
_small_table_threshold(117) parameter.

SQL> select count(1) from test_part where col1 in (1,2);

  COUNT(1)
----------
      8000

SQL> @sese direct

no rows selected

No direct reads. Definitely different compared to when _direct_read_decision_statistcs_driven was set to TRUE.

How about for a query that accesses three partitions:

 SQL> select count(1) from test_part where col1 in (1,2,3);

  COUNT(1)
----------
     12000

SQL> @sese direct

no rows selected

Still no direct path reads.

How about if we access all 7 partitions:

SQL>  select count(1) from test_part where col1 in (1,2,3,4,5,6,7);

  COUNT(1)
----------
     28000

SQL> @sese direct

no rows selected

No direct path reads.

So what is going on? Seems when _direct_read_decision_statistcs_driven is set to FALSE, Oracle makes decision on partition by partition basis. If the number of blocks in the partition is less or equal than _small_table_threshold buffer cache will be used, otherwise direct path reads.

What if some of the partitions were already cached in the buffer cache?

In the next test I’ll:

• Flush the buffer cache again
• Set _direct_read_decision_statistcs_driven is set to FALSE
• Run a query that accesses the first two partitions
• Decrease the value for _small_table_threshold to 60
• Run a query that accesses the first three partitions.
• Check if direct path reads were performed and how many

With this test I’d like to see if Oracle will utilize the buffer cache if the segment data is cached and the number of blocks in partition is greater than _small_table_threshold.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter session set "_direct_read_decision_statistics_driven"=FALSE;

Session altered.

SQL> select count(1) from test_part where col1 in (1,2);

  COUNT(1)
----------
      8000

SQL> @sese direct

no rows selected

At this point, PART_1 and PART_2 partitions should be entirely in the buffer cache. If you want, you could query X$KCBOQH to confirm this (from a different session logged in as SYS).

SQL> conn /as sysdba
Connected.
SQL> select  o.subobject_name, b.obj#, sum(b.num_buf)
2 from    X$KCBOQH b, dba_objects o
3 where   b.obj#=o.data_object_id
4     and o.object_name='TEST_PART'
5 group by  o.subobject_name, b.obj#
6 order by 1;  

SUBOBJECT_NAME                       OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1                             146024             66
PART_2                             146025             66

As expected, both partitions are in the buffer cache.

Now let’s change decrease _small_table_threshold to 60 and run a query that scans the first three partitions:

SQL>  alter session set "_small_table_threshold"=60;

Session altered.

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> select count(1) from test_part where col1 in (1,2,3);

  COUNT(1)
----------
     12000

alter session set events '10046 trace name context off';

SQL> @sese direct

       SID         ID NAME                                                    VALUE
---------- ---------- -------------------------------------------------- ----------
         9         76 STAT.consistent gets direct                                65
         9         81 STAT.physical reads direct                                 65
         9        380 STAT.table scans (direct read)                              1

Here they are, 65 direct path reads, one table scan (direct read) which means one of the partitions was scanned using direct path reads. Which one? Yes, you are right, the one that is not in the buffer cache (PART_3 in this example).

If you query X$KCBOQH again you can see that only one block of PART_3 is in the cache. That is the segment header block.

SQL> conn /as sysdba
Connected.
SQL>  select  o.subobject_name, b.obj#, sum(b.num_buf)
2 from    X$KCBOQH b, dba_objects o
3 where   b.obj#=o.data_object_id
4     and o.object_name='TEST_PART'
5 group by  o.subobject_name, b.obj#
6 order by 1;  

SUBOBJECT_NAME                       OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1                             146024             66
PART_2                             146025             66
PART_3                             146026              1 <===

This means that when _direct_read_decision_statistcs_driven is set to FALSE, in 11.2.0.3, Oracle uses totally different calculation compared to the one used when the parameter is set to TRUE (see in my earlier post).

Moreover, seems Oracle examines each of the partitions separately (which I initially expected to be a case even when _direct_read_decision_statistcs_driven is set to TRUE ) and applies the rules as described in Alex Fatkulin’s blogpost. That is, if any of the following is true, oracle will scan the data in the buffer cache, otherwise direct path reads will be performed: 

•  the number of blocks in the segment is lower or equal than _small_table_threshold 
•  at least 50% of the segment data blocks are in the buffer cache
•  at least 25% of the data blocks are dirty 

The conclusion so far is that in 11.2.0.3, you may observe different behavior for the queries that access table partitions using FTS if you decide to change _direct_read_decision_statistcs_driven parameter.

I will stop here. I ran the same tests against 11.2.0.4 and 12.1.0.1 and noticed some differences in the behavior compared to the one I just wrote about (11.2.0.3). I will post these results in the next few days.

Stay tuned...

_small_table_threshold and direct path reads on partitioned tables in 11.2.0.3

I was troubleshooting a performance problem few days ago. The database the problem was experienced on was recently migrated from Oracle 10.2.0.4 to Oracle 11.2.0.3.

Long story short, the problem was described as performance of a query that scans two or more partitions in a table is much worse compared to combined performances of queries accessing each of the partitions separately.

After a short investigation I narrowed down the problem to “direct path reads” being the culprit of the problem.

As you know, due to the adaptive direct read feature introduced in 11g full table scans may utilize PGA instead of the buffer cache as it was a case in the earlier versions.

There are few good articles on this change in behavior among which I personally favor Tanel’s blogpost and hacking session and the post by Alex Fatkulin. You could also check MOS Note 793845.1.

What I observed in 11.2.0.3.0 was quite surprising and a bit different from what I’ve read so far. I know that there are different parameters/variables that influence the decision whether or not direct part reads should be used. I tried to be careful and not to fall in any of these traps.

Please note all the tests were done in a sandbox environment. I advise against trying these tests in any production environment.

The database version was 11.2.0.3.0.

_serial_direct_read = auto

_direct_read_decision_statistics_driven = TRUE

_small_table_threshold = 117

I used ASSM, a tablespace with uniform extent size(64K)

As you may know _small_table_threshold parameter is set to about 2% of the size of the buffer cache. On my test machine I have pretty small buffer cache, 5892 buffers big (117 is 1.98% of 5892)

SQL> SELECT name,block_size,buffers FROM v$buffer_pool;

NAME                                               BLOCK_SIZE    BUFFERS
-------------------------------------------------- ---------- ----------
DEFAULT                                                  8192       5892

I will try to simplify the problem by using a partitioned table, TEST_PART containing 7 partitions.

CREATE TABLE test_part 
( 
    col1 NUMBER NOT NULL
  , col2 VARCHAR2(100)
 ) 
 PARTITION BY LIST (col1) 
 (  
    PARTITION PART_1  VALUES (1)
  , PARTITION PART_2  VALUES (2)
  , PARTITION PART_3  VALUES (3)
  , PARTITION PART_4  VALUES (4)
  , PARTITION PART_5  VALUES (5)
  , PARTITION PART_6  VALUES (6)
  , PARTITION PART_7  VALUES (7)
 ) ;

Each of the 7 partitions will be populated with 4000 rows (for total of 65 blocks allocated per partition) using the following SQL statement:

INSERT INTO test_part (SELECT mod(rownum,7)+1, rpad('A',100,'A') FROM dual CONNECT BY rownum<=28000);

I will collect stats using the statement below:

exec dbms_stats.gather_table_stats(user,'TEST_PART');

As you can see from the output below, each of the partitions has 65 blocks below the HWM:

SQL> select table_name, partition_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name='TEST_PART';  2    3

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- -----------
TEST_PART                      PART_1                               4000         65         104
TEST_PART                      PART_2                               4000         65         104
TEST_PART                      PART_3                               4000         65         104
TEST_PART                      PART_4                               4000         65         104
TEST_PART                      PART_5                               4000         65         104
TEST_PART                      PART_6                               4000         65         104
TEST_PART                      PART_7                               4000         65         104

7 rows selected.

Observation #1 -
 _small_table_threshold is applied on the total number of blocks expected to be returned by the query (considering all partition segments that will be accessed)

As you can see number of blocks in each of the partitions (65) is lower than _small_table_threshold value (117). Therefore a query that accesses only one of the partitions uses the buffer cache to store the segment blocks.

SQL> select count(1) from test_part where col1 in (1);

  COUNT(1)
----------
      4000

I will use my sese.sql script to check the values for specific session statistics. It simply scans v$sesstat for the current session and a given keyword. If there are statistics that contain the specified keyword and their value is greater than 0 they will be reported. As you can see no direct path reads were performed.

SQL> @sese direct

no rows selected

I expected to see the next query utilizing the buffer cache as well. It scans two partitions. As you know, each of the partitions has 65 blocks which is less than _small_table_threshold value (117), hence I thought I won't see any direct path reads.

SQL> select count(1) from test_part where col1 in (1,2);

  COUNT(1)
----------
      8000

However, direct path reads were performed. Moreover, even though one of the partitions I previously scanned was already in the buffer cache, both partitions were scanned using direct path reads. As shown in the output below, two segments were fully scanned using direct reads for total of 130 direct reads were performed (2x65).

SQL>  @sese direct

       SID         ID NAME                                                    VALUE
---------- ---------- -------------------------------------------------- ----------
         7         76 STAT.consistent gets direct                               130
         7         81 STAT.physical reads direct                                130
         7        380 STAT.table scans (direct read)                              2

Let’s see what happens when I increase _small_table_threshold to 130 and run the last query.

SQL> alter session set "_small_table_threshold"=130;

Session altered.

SQL> select count(1) from test_part where col1 in (1,2);

  COUNT(1)
----------
      8000

SQL>  @sese direct

       SID         ID NAME                                                    VALUE
---------- ---------- -------------------------------------------------- ----------
         7         76 STAT.consistent gets direct                               130
         7         81 STAT.physical reads direct                                130
         7        380 STAT.table scans (direct read)                              2

The number of direct path reads stayed the same, which means no direct path reads were performed.

How about if I we add one more partition to the equation now (_small_table_threshold=130):

SQL> select count(1) from test_part where col1 in (1,2,3);

  COUNT(1)
----------
     12000

SQL> @sese direct

       SID         ID NAME                                                    VALUE
---------- ---------- -------------------------------------------------- ----------
         7         76 STAT.consistent gets direct                               325
         7         81 STAT.physical reads direct                                325
         7        380 STAT.table scans (direct read)                              5

Now since we scan 3 partitions, that is 195 blocks Oracle went back to direct path reads and the statistic numbers went up by 195 (3x65) , 130+195=325 or three new table/segment scans.

Therefore seems the logic behind the decision whether or not to perform direct path reads is:

IF SUM(blocks of all partitions that are accessed)>_small_table_threshold THEN
     perform direct path reads for all partitions that are accessed
ELSE
     utilize buffer cache

Again, just to remind you this behavior is specific to 11.2.0.3.


Observation #2 -
The percentage of cached blocks per partition is not relevant


This brings me to the second observation. If you query X$KCBOQH.NUM_BUF for the partition segments (read Tanel’s blogpost or watch his hacking session ) you can see that even though partitions PART_1 and PART_2 were in the cache, Oracle still performed direct path reads for all three partitions:

SQL> conn /as sysdba
Connected.

SQL> select  o.subobject_name, b.obj#, sum(b.num_buf)
2 from    X$KCBOQH b, dba_objects o
3 where   b.obj#=o.data_object_id
4     and o.object_name='TEST_PART'
5 group by  o.subobject_name, b.obj#
6 order by 1;  

SUBOBJECT_NAME                       OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1                             146024             66
PART_2                             146025             66
PART_3                             146026              1 

I ran the output above after the last test. As you can see PART_1 and PART_2 segments are completely in the buffer cache, 66 blocks each (65 blocks for the data and 1 block for the segment header). PART_3 however has only one block in the cache and that is most likely the segment header block.

But, even when all 3 partitions were fully loaded in the buffer cache, Oracle still performed direct path reads:

SQL> conn *****/*****
Connected.
SQL> select count(1) from test_part where col1 in (3);

  COUNT(1)
----------
      4000

SQL> conn /as sysdba
Connected.

SQL> select  o.subobject_name, b.obj#, sum(b.num_buf)
2 from    X$KCBOQH b, dba_objects o
3 where   b.obj#=o.data_object_id
4     and o.object_name='TEST_PART'
5 group by  o.subobject_name, b.obj#
6 order by 1;

SUBOBJECT_NAME                       OBJ# SUM(B.NUM_BUF)
------------------------------ ---------- --------------
PART_1                             146024             66
PART_2                             146025             66
PART_3                             146026             66

SQL> conn *****/*****
Connected.
SQL> @sese direct

no rows selected

SQL> select count(1) from test_part where col1 in (1,2,3);

  COUNT(1)
----------
     12000

SQL> @sese direct

       SID         ID NAME                                                    VALUE
---------- ---------- -------------------------------------------------- ----------
         7         76 STAT.consistent gets direct                               195
         7         81 STAT.physical reads direct                                195
         7        380 STAT.table scans (direct read)                              3

SQL>

I will stop with this post here. Tomorrow I will publish another post where I'll show what difference _direct_read_decision_statistics_driven could make for partitioned tables in 11.2.0.3.
(Update: Part2 - what difference does _direct_read_decision_statistics_driven=FALSE make)

I will repeat the same tests in 11.2.0.4 and 12.1.0.1 and see if the behavior is any different.

Stay tuned.
 

Find the enabled events using ORADEBUG EVENTDUMP

Just a quick note on how to use ORADEBUG in order to find events that are enabled on system or session level.

Starting from Oracle 10.2 you could use ORADEBUG EVENTDUMP in order to get all events enabled either on session or system level. (not sure if this is available in 10.1)

The synopsis is:

oradebug eventdump 

Where level is either system, process or session:
 

SQL> oradebug doc event action eventdump
eventdump
        - list events that are set in the group
Usage
-------
eventdump( group           < system | process | session >)

For demonstration purposes I will set three events, two on session and one on system level.
 

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> alter session set events '942 trace name errorstack';

Session altered.

SQL> alter system set events 'trace[px_control][sql:f54y11t3njdpj]';

System altered.

Now, let's check what events are enabled on SESSION or SYSTEM level using ORADEBUG EVENTDUMP

SQL> oradebug setmypid

SQL> oradebug eventdump session;
trace [RDBMS.PX_CONTROL] [sql:f54y11t3njdpj]
sql_trace level=8
942 trace name errorstack

SQL> oradebug eventdump system
trace [RDBMS.PX_CONTROL] [sql:f54y11t3njdpj]

As you may already know, ORADEBUG requires SYSDBA privilege. In order to check events set for other session, one could do so by attaching to the other session process using oradebug  setospid or oradebug setorapid.

This note was more for my own reference. I hope someone else finds it useful too.

Fun with global temporary tables in Oracle 12c

Few months ago I wrote a post about 12c session specific statistics for global temporary tables (link). Long awaited feature no matter what.

Recently I had some discussions on the same subject with members of my team.

One interesting observation was the behavior of transaction specific GTTs with session specific statistics enabled. What attracted our interest was the fact that data in global temporary tables is not deleted after DBMS_STATS package is invoked.

Prior to 12c, a call to DBMS_STATS will result with an implicit commit. This would wipe out the content of a transaction specific global temporary table.

I’ll digress here a bit. Yes, I know, who would call DBMS_STATS to collect statistics on a transaction specific GTT knowing the data in the table will be lost. Well, things change a bit in 12c.

In Oracle 12c, no implicit commit is invoked when DBMS_STATS.GATHER_TABLE_STATS is invoked on a transaction specific with session specific statistics enabled thus letting users take advantage of session specific statistics for this type of GTTs.

This behavior is documented in Oracle documentation.

I’ll try to put some more light on this behavior through couple of examples:

For this purpose I’ll start with three tables. T1 and T2 are transaction specific temporary tables. T3 is a regular table. By default, in 12c, session specific statistics are used.

CREATE GLOBAL TEMPORARY TABLE t1 (id NUMBER);

CREATE GLOBAL TEMPORARY TABLE t2 (id NUMBER);

CREATE TABLE t3 (id NUMBER);

Scenario #1 – Insert 5 rows to each of the three tables and observe the state of the data after DBMS_STATS is invoked on a transaction specific GTT.

SQL> INSERT INTO t1 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> INSERT INTO t2 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> INSERT INTO t3 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(user,'T1');
PL/SQL procedure successfully completed.

SQL> SELECT count(1) FROM t1;
  COUNT(1)
----------
  5

As you can see the data in T1 is still present. Furthermore if you open another session you can also see that T3 has no rows. This means commit was not invoked when session specific statistics were collected for T1.

Scenario 2# Insert 5 rows in each of the three tables and collect statistics only on the regular table, T3.

SQL> INSERT INTO t1 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> INSERT INTO t2 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> INSERT INTO t3 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(user,'T3');
PL/SQL procedure successfully completed.

SQL> SELECT count(1) FROM t1;
  COUNT(1)
----------
  0

As you can see in this scenario implicit commit was invoked which resulted with data in T1 being purged.

Hope this helps … :-)

Cheers!

Playing with Oracle Spatial - An important consideration when using SDO_NN operator

I've been playing with Oracle Spatial quite a bit lately and ran into some interesting things that I  plan to write about in the next couple of posts.

This post covers an important consideration, I think, one should make when using SDO_NN spatial operator with sdo_num_res parameter.

But first, just briefly on the operator itself. SDO_NN operator is used to identify the nearest neighbors for a particular geometry. The full operator synopsis can be found in the official documentation

Please note, the database I use in the examples below is Oracle 11.2.0.3.0 Enterprise Edition with no additional patches applied and default Oracle Spatial settings.

Let's consider the following example, there is a table RESTAURANTS having only an id and a geometry column (point).

 CREATE TABLE restaurants
(
 id NUMBER NOT NULL,
 point MDSYS.SDO_GEOMETRY
); 

ALTER TABLE restaurants ADD CONSTRAINT restaurants_pk PRIMARY KEY (id);

Let's create a spatial index on restaruants.point column:

 INSERT INTO user_sdo_geom_metadata 
    (   TABLE_NAME
      , COLUMN_NAME
      , DIMINFO
      ,   SRID)
    VALUES 
   (   'restaurants'
      , 'POINT'
      , SDO_DIM_ARRAY(   
           SDO_DIM_ELEMENT('X', 0, 10000000, 0.005),
           SDO_DIM_ELEMENT('Y', 0, 10, 0.005)
        )
      , null   -- SRID
);
 
CREATE INDEX restaurants_sidx ON restaurants(point) INDEXTYPE IS MDSYS.SPATIAL_INDEX; 

For simplicity let's say there are 500,000 restaurants distributed on a single line (x-axis of two dimensional coordinate system, y=0, x=1,2,3,...500,000).

INSERT INTO restaurants 
(SELECT rownum   --id
    ,   SDO_GEOMETRY(
                      2001 -- 2 dimensional point
                   ,  null  -- SDO SRID
                   ,  SDO_POINT_TYPE(rownum,0,null) –-x=rownum, y=0
                   ,  null
                   ,  null
        )
 FROM dual CONNECT BY rownum<=500000
);

In order to find the first 5 nearest neighbors to point (10,0) one could use the following spatial query:

SQL> SELECT p.id, sdo_nn_distance(1) distance
  2  FROM restaurants p
  3  WHERE SDO_NN(p.point,
  4                      SDO_GEOMETRY(
  5                                     2001 -- 2 dimensional point
  6                             ,       null
  7                             ,       SDO_POINT_TYPE(10,0,null)
  8                             ,       null
  9                             ,       null
 10                             ),
 11                      'sdo_num_res=5',
 12                      1
 13                     )='TRUE'
 14* ORDER BY 2;

        ID   DISTANCE
---------- ----------
        10          0
         9          1
        11          1
        12          2
         8          2

SQL>

Now suppose each restaurant has 5 menu items that are stored in MENU_ITEMS table. The table has 4 columns, a unique identifier ID; a reference to RESTAURANTS table, rest_id; an identifier that identifies an item within the restaurant menu, item_number; and lastly a description, 100 characters long column that I use in this example to make this table a bit bigger.

CREATE TABLE menu_items
(id NUMBER NOT NULL,
 rest_id NUMBER NOT NULL,
 item_number NUMBER NOT NULL,
 description varchar2(100)
);

ALTER TABLE menu_items ADD CONSTRAINT menu_items_pk PRIMARY KEY (id); 

ALTER TABLE menu_items ADD CONSTRAINT menu_items_rest_FK FOREIGN KEY (rest_id) REFERENCES restaurants(id); 

CREATE INDEX menu_items_rest_idx ON menu_items(rest_id);

 
INSERT INTO menu_items 
(SELECT rownum
      , b.rn
      , a.rn 
      , rpad(rownum,100,'A')
 FROM 
     (SELECT rownum rn FROM dual CONNECT BY rownum<=5) a, --5 menu items per rest.
     (SELECT rownum rn FROM dual CONNECT BY rownum<=500000) b
);

commit;

Suppose you want to get the menu items of the top 5 nearest restaurants to a particular location (10,0) for an example. One way would be with the following spatial query (but first of course collect the necessary stats):

BEGIN
        DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'restaurants',cascade=>TRUE);
        DBMS_STATS.GATHER_TABLE_STATS(ownname=>user,tabname=>'menu_items',cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');
END;

SELECT t1.id
     , t2.id
     , t2.restaurants_id
     , t2.item_number
FROM restaurants t1
   , menu_items t2
WHERE t1.id=t2.restaurants_id
  AND SDO_NN(t1.point,
              SDO_GEOMETRY(
                            2001 -- 2 dimensional point
                         ,  null 
                         ,  SDO_POINT_TYPE(10,0,null)
                         ,  null
                         ,  null
              ),
             'sdo_num_res=5',
             1 
       )='TRUE'
ORDER BY t1.id, t2.item_number;

The query produces the desired result. However, let's look how it performed. I re-run the query, but this time I used gather_plan_statistics hint to get the execution statistics for the query.

SQL>SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
  2  FROM restaurants t1
  3     , menu_items t2
  4  WHERE t1.id=t2.restaurants_id
  5    AND SDO_NN(t1.point,
  6                      SDO_GEOMETRY(
  7                                     2001 -- 2 dimensional point
  8                             ,       null
  9                             ,       SDO_POINT_TYPE(10,0,null)
 10                             ,       null
 11                             ,       null
 12                             ),
 13                      'sdo_num_res=5',
 14                      1
 15                     )='TRUE'
 16  ORDER BY t1.id, t2.item_number; 

        ID         ID RESTAURANTS_ID ITEM_NUMBER
---------- ---------- -------------- -----------
         8          8              8           1
         8     500008              8           2
         8    1000008              8           3
         8    1500008              8           4
         8    2000008              8           5
         9          9              9           1
         9     500009              9           2
         9    1000009              9           3
         9    1500009              9           4
         9    2000009              9           5
        10         10             10           1
        10     500010             10           2
        10    1000010             10           3
        10    1500010             10           4
        10    2000010             10           5
        11         11             11           1
        11     500011             11           2
        11    1000011             11           3
        11    1500011             11           4
        11    2000011             11           5
        12         12             12           1
        12     500012             12           2
        12    1000012             12           3
        12    1500012             12           4
        12    2000012             12           5

25 rows selected.

The query took 1.32 seconds to complete as you can see from the execution statistics:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gwpqub3k0awqm, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id,
t2.item_number FROM restaurants t1    , menu_items t2 WHERE
t1.id=t2.restaurants_id   AND SDO_NN(t1.point,     SDO_GEOMETRY(
2001 -- 2 dimensional point     ,  null     ,
SDO_POINT_TYPE(10,0,null)     ,  null     ,  null     ),
'sdo_num_res=5',     1    )='TRUE' ORDER BY t1.id, t2.item_number

Plan hash value: 2076547507


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |     25 |00:00:01.32 |   41540 |  41465 |       |       |          |
|   1 |  SORT ORDER BY                |                  |      1 |  24678 |     25 |00:00:01.32 |   41540 |  41465 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN                   |                  |      1 |  24678 |     25 |00:00:01.32 |   41540 |  41465 |  1517K|  1517K|  886K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| RESTAURANTS      |      1 |   5000 |      5 |00:00:00.01 |      64 |      0 |       |       |          |
|*  4 |     DOMAIN INDEX              | RESTAURANTS_SIDX |      1 |        |      5 |00:00:00.01 |      63 |      0 |       |       |          |
|   5 |    TABLE ACCESS FULL          | MENU_ITEMS       |      1 |   2500K|   2500K|00:00:00.62 |   41476 |  41465 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="T2"."RESTAURANTS_ID")
   4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='
              TRUE')


29 rows selected.

SQL> 

The problem

Well, one could say that 1.32 seconds is not that bad. However, if you look at the plan more carefully you can notice that CBO used a HASH JOIN method to join RESTAURANTS and MENU_ITEMS tables and as a result it performed full table scan on MENU_ITEMS table. Now imagine if MENU_ITEMS table was quite big (suppose you have stored the items of all restaurants across US)?!

What made CBO to pick a HASH JOIN? Look at the estimated (5,000) vs. actual rows (5) selected from RESTAURANTS table. We were only after the 5 nearest neighbors. We used sdo_num_res=5 in our SDO_NN call. One could expect that CBO would recognize that fact and estimate cardinality of 5. But, no, CBO applied selectivity of 1% (500,000 restaurants x 1% = 5,000)

Why selectivity of 1%?

SDO_NN is an operator that is bound to the implementation of the function NN that is part of PRVT_IDX package owned by MDSYS.

By default, statistics for PRVT_IDX package is defined by SDO_STATISTICS type. That is Extensible Optimizer feature is used to define how cardinality and the cost will be calculated for functions/procedures defined in the package. (if statistics are not associated, CBO uses default selectivity of 1%)

Seems like the logic implemented in ODCIStatsSelectivity() function is not good enough to detect that we are only after the first N rows, as defined with SDO_NUM_RES parameter.

You can clearly see this if you create 10053 trace file in the section where MDSYS.SDO_STATISTICS.ODCIStatsSelectivity procedure is used to calculate selectivity.

  MDSYS.SDO_STATISTICS.ODCIStatsFunctionCost returned:
    CPUCost      : 100000000000000
    IOCost       : 100000000000000
    NetworkCost  : -1
  Calling user-defined selectivity function...
    predicate: "MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE'
  declare
     sel number;
     obj0 "MDSYS"."SDO_GEOMETRY" := "MDSYS"."SDO_GEOMETRY"(NULL, NULL, NULL, NULL, NULL);

    begin
      :1 := "MDSYS"."SDO_STATISTICS".ODCIStatsSelectivity(
                     sys.ODCIPREDINFO('MDSYS',
                            'PRVT_IDX',
                            'NN',
                            45),
                     sel,
                     sys.ODCIARGDESCLIST(sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(2, 'RESTAURANTS', 'TEST', '"POINT"', NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL)),
                     :3,
                     :4
                     , obj0, "MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL), :5,
                     sys.ODCIENV(:6,:7,:8,:9));
      if sel IS NULL then
        :2 := -1.0;
      else
        :2 := sel;
      end if;
      exception
        when others then
          raise;
    end;
Bind :3 Value 'TRUE'
Bind :4 Value 'TRUE'
Bind :5 Value 'sdo_num_res=5'
ODCIEnv Bind :6 Value 0
ODCIEnv Bind :7 Value 0
ODCIEnv Bind :8 Value 0
ODCIEnv Bind :9 Value 3
  MDSYS.SDO_STATISTICS.ODCIStatsSelectivity returned selectivity: 1.00000000%
  Table: RESTAURANTS  Alias: T1
    Card: Original: 500000.000000  Rounded: 5000  Computed: 5000.00  Non Adjusted: 5000.00

If statistics are not associated CBO would also use selectivity of 1%. So what is the differences? Under what circumstances one could take advantage of the extensible optimizer feature and generate better selectivity when SDO_NN operator is used along with sdo_num_res parameter? I couldn't find any article or documentation piece that will help me answer these questions.

What I do know however, is that it shouldn't be that difficult to implement a piece of logic that will make SDO_STATISTICS.ODCIStatsSelectivity procedure generate more accurate numbers.

As a matter of fact, I played a bit with this.

Disclaimer: Please be aware the following examples are for demonstration purposes only and shouldn't be used in an actual, supported, database environment. The logic I show below is quite simple and is for illustration only. Please contact Oracle Support if you want to implement something similar in your environment.

I created a simple type named SDO_STATISTICS_CUSTOM under MDSYS schema using the Extensible Optimizer interface (link )

I implemented ODCIStatsSelectivity procedure as shown below (one more time, this implementation is to demonstrate that with appropriate logic ODCIStatsSelectivity can return desired values):

STATIC FUNCTION ODCIStatsSelectivity (
                      pred_info      IN  SYS.ODCIPredInfo,
                      sel          OUT NUMBER,
                      args           IN  SYS.ODCIArgDescList,
                      p_start          IN  VARCHAR2,
                      p_stop           IN  VARCHAR2,
                      geometry1      IN MDSYS.SDO_GEOMETRY,
        geometry2      IN MDSYS.SDO_GEOMETRY,
               param          IN VARCHAR2,
                      env            IN  SYS.ODCIEnv
                      ) RETURN NUMBER IS
      BEGIN
   
    *******************  
         if param=NULL then 
  sel:=1;
  else if lower(substr(param,1,instr(param,'=')-1)) = 'sdo_num_res' then
   SELECT  decode(num_rows,null,1,
     decode(to_number(trim(substr(param,instr(param,'=')+1))),0,1,to_number(trim(substr(param,instr(param,'=')+1)))/num_rows)*100 
    )
           INTO   sel
           FROM   dba_tables where owner=args(3).TABLESCHEMA and table_name=args(3).TABLENAME;

   if sel>100 then 
    sel:=100 ;
   end if;   

  end if;
  end if;
      
****************     
          
         RETURN ODCIConst.success;
      END ODCIStatsSelectivity; 
 
 

The logic I chose is simple, if sdo_num_res parameter is defined, get the number of records from the statistics available for the table and set the selectivity variable (sel) to (sdo_num_res/num_rows)*100.

Let's see how it works:

SQL> EXPLAIN PLAN FOR
  2  SELECT t1.id, t2.id, t2.restaurants_id, t2.item_number
  3  FROM restaurants t1
  4     , menu_items t2
  5  WHERE t1.id=t2.restaurants_id
  6    AND SDO_NN(t1.point,
  7                           SDO_GEOMETRY(
  8                                          2001 -- 2 dimensional point
  9                                  ,       null
 10                                  ,       SDO_POINT_TYPE(10,0,null)
 11                                  ,       null
 12                                  ,       null
 13                                  ),
 14                           'sdo_num_res=5',
 15                           1
 16                          )='TRUE'
 17* ORDER BY t1.id, t2.item_number
SQL> /

Explained.

SQL> set line 200 pagesize 9999
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2341889131

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |    25 |  1000 |    36   (3)| 00:00:01 |
|   1 |  SORT ORDER BY                |                            |    25 |  1000 |    36   (3)| 00:00:01 |
|   2 |   NESTED LOOPS                |                            |    25 |  1000 |    35   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| RESTAURANTS                |     5 |    80 |     0   (0)| 00:00:01 |
|*  4 |     DOMAIN INDEX              | RESTAURANTS_SIDX           |       |       |     0   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| MENU_ITEMS                 |     5 |   120 |     7   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | MENU_ITEMS_RESTAURANTS_IDX |     5 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,N
              ULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
   6 - access("T1"."ID"="T2"."RESTAURANTS_ID")

20 rows selected.

As you can see the cardinality is correctly calculated which resulted CBO to produce better execution plan. With this plan in place, the query completes in less than 1 cs.

If you look into 10053 trace file you can also see that the calculated selectivity is 0.001%:

  MDSYS.SDO_STATISTICS_CUSTOM.ODCIStatsFunctionCost returned:
    CPUCost      : 10000
    IOCost       : 10000
    NetworkCost  : 10000
  Calling user-defined selectivity function...
    predicate: "MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE'
  declare
     sel number;
     obj0 "MDSYS"."SDO_GEOMETRY" := "MDSYS"."SDO_GEOMETRY"(NULL, NULL, NULL, NULL, NULL);

    begin
      :1 := "MDSYS"."SDO_STATISTICS_CUSTOM".ODCIStatsSelectivity(
                     sys.ODCIPREDINFO('MDSYS',
                            'PRVT_IDX',
                            'NN',
                            45),
                     sel,
                     sys.ODCIARGDESCLIST(sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(2, 'RESTAURANTS', 'TEST', '"POINT"', NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL), sys.ODCIARGDESC(3, NULL, NULL, NULL, NULL, NULL, NULL)),
                     :3,
                     :4
                     , obj0, "MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL), :5,
                     sys.ODCIENV(:6,:7,:8,:9));
      if sel IS NULL then
        :2 := -1.0;
      else
        :2 := sel;
      end if;
      exception
        when others then
          raise;
    end;
Bind :3 Value 'TRUE'
Bind :4 Value 'TRUE'
Bind :5 Value 'sdo_num_res=5'
ODCIEnv Bind :6 Value 0
ODCIEnv Bind :7 Value 0
ODCIEnv Bind :8 Value 0
ODCIEnv Bind :9 Value 3
  MDSYS.SDO_STATISTICS_CUSTOM.ODCIStatsSelectivity returned selectivity: 0.00100000%

Anyway, since the current implementation of SDO_STATISTICS type returns selectivity of 1%, the next logical question is what one can do in order to workaround the problem.

Workaround

The problem can be addressed by providing CBO that extra bit of information, that is how many rows we expect to get back after applying SDO_NN operator. In order to do this one could consider using the cardinality hint or rewrite the query and add one more predicate ROWNUM<=5 that would tell CBO how many rows we are expecting to select from RESTAURANTS table. (there may be other strategies available too)

Use cardinality hint:

SELECT /*+ cardinality(t1 5) gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
FROM restaurants t1
   , menu_items t2
WHERE t1.id=t2.restaurants_id
  AND SDO_NN(t1.point,
              SDO_GEOMETRY(
                            2001 -- 2 dimensional point
                         ,  null 
                         ,  SDO_POINT_TYPE(10,0,null)
                         ,  null
                         ,  null
              ),
             'sdo_num_res=5',
             1 
      )='TRUE'
ORDER BY t1.id, t2.item_number;

The execution statistics in this case look like:

Plan hash value: 2341889131


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |      1 |        |     25 |00:00:00.01 |      92 |      5 |       |       |          |
|   1 |  SORT ORDER BY                |                            |      1 |     25 |     25 |00:00:00.01 |      92 |      5 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS                |                            |      1 |     25 |     25 |00:00:00.01 |      92 |      5 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| RESTAURANTS                |      1 |      5 |      5 |00:00:00.01 |      55 |      0 |       |       |          |
|*  4 |     DOMAIN INDEX              | RESTAURANTS_SIDX           |      1 |        |      5 |00:00:00.01 |      54 |      0 |       |       |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| MENU_ITEMS                 |      5 |      5 |     25 |00:00:00.01 |      37 |      5 |       |       |          |
|*  6 |     INDEX RANGE SCAN          | MENU_ITEMS_RESTAURANTS_IDX |      5 |      5 |     25 |00:00:00.01 |      12 |      0 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("MDSYS"."SDO_NN"("T1"."POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
   6 - access("T1"."ID"="T2"."RESTAURANTS_ID")


29 rows selected. 

Rewrite the query and use ROWNUM<=n predicate where n is the number associated with sdo_num_rows parameter:

SELECT /*+ gather_plan_statistics */ t1.id, t2.id, t2.restaurants_id, t2.item_number
FROM (SELECT * 
      FROM restaurants 
      WHERE
           SDO_NN(point,
                  SDO_GEOMETRY(
                                   2001 -- 2 dimensional point
                                ,  null 
                                ,  SDO_POINT_TYPE(10,0,null)
                                ,  null
                                ,  null
                    ),
                 'sdo_num_res=5',
                 1 
           )='TRUE'
           AND rownum<=5
    ) t1
   , menu_items t2
WHERE t1.id=t2.restaurants_id    
ORDER BY t1.id, t2.item_number;

… and the execution statistics :

Plan hash value: 3570932640

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |      1 |        |     25 |00:00:00.01 |     101 |       |       |          |
|   1 |  SORT ORDER BY                   |                            |      1 |     25 |     25 |00:00:00.01 |     101 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS                   |                            |      1 |        |     25 |00:00:00.01 |     101 |       |       |          |
|   3 |    NESTED LOOPS                  |                            |      1 |     25 |     25 |00:00:00.01 |      76 |       |       |          |
|   4 |     VIEW                         |                            |      1 |      5 |      5 |00:00:00.01 |      64 |       |       |          |
|*  5 |      COUNT STOPKEY               |                            |      1 |        |      5 |00:00:00.01 |      64 |       |       |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| RESTAURANTS                |      1 |      5 |      5 |00:00:00.01 |      64 |       |       |          |
|*  7 |        DOMAIN INDEX              | RESTAURANTS_SIDX           |      1 |        |      5 |00:00:00.01 |      63 |       |       |          |
|*  8 |     INDEX RANGE SCAN             | MENU_ITEMS_RESTAURANTS_IDX |      5 |      5 |     25 |00:00:00.01 |      12 |       |       |          |
|   9 |    TABLE ACCESS BY INDEX ROWID   | MENU_ITEMS                 |     25 |      5 |     25 |00:00:00.01 |      25 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(ROWNUM<=5)
   7 - access("MDSYS"."SDO_NN"("POINT","MDSYS"."SDO_GEOMETRY"(2001,NULL,"SDO_POINT_TYPE"(10,0,NULL),NULL,NULL),'sdo_num_res=5',1)='TRUE')
   8 - access("T1"."ID"="T2"."RESTAURANTS_ID")


34 rows selected.

As you can see, Oracle CBO made correct cardinality estimates in the last two examples and used NESTED LOOPS method to join both tables that resulted with performance improvement.

I hope you got some valuable information out of this post.

CURSOR_SHARING=SIMILAR available in Oracle 12c, or not ?

It was announced before Oracle Database 12c was released that CURSOR_SHARING=SIMILAR will be deprecated (MOS Note 1169017.1). Moreover, according to the same note the ability to set this value will be removed.

And indeed, when I looked into 12c documentation, I found EXACT and FORCE being the only available values where the former is default value. (link)

I decided to play around a bit and immediately figured that SIMILAR could still be assigned to CURSOR_SHARING.

test[12,133]> ALTER SESSION SET CURSOR_SHARING=SIMILAR;

Session altered. 

When I tried to assign an invalid value to CURSOR_SHARING using SQL*Plus I got an error message that says EXACT, SIMILAR and FORCE are acceptable values for CURSOR_SHARING parameter.

test[12,133]> ALTER SESSION SET CURSOR_SHARING=foo;
ERROR:
ORA-00096: invalid value FOO for parameter cursor_sharing, must be 

from among SIMILAR, EXACT, FORCE 

Couple of reasons I can think of as why SIMILAR is still allowed. The first, it may be some sort of backward compatibility (so that applications that explicitly set this value don't break when migrated to 12c); or it may be that CURSOR_SHARING=SIMILAR is still a valid option if COMPATIBLE parameter is set to an earlier release where the value was supported. (didn't have time to play with COMPATIBLE parameter)

Anyway, my main question was how Oracle will behave if CURSOR_SHARING is set to SIMILAR in 12c.

I ran a quick test. I created one table, named TAB1 and then executed one sub-optimally shareable statement 5 times passing 5 different literal values for each of the executions.

When CURSOR_SHARING is set to SIMILAR and a suboptimal statement using literals is executed, Oracle would not share any of the existing cursors and will perform a hard parse.

CREATE TABLE tab1 AS (SELECT * FROM user_objects);

ALTER SESSION SET CURSOR_SHARING=SIMILAR;

SELECT COUNT(1) FROM tab1 WHERE object_id>1;

SELECT COUNT(1) FROM tab1 WHERE object_id>2;

SELECT COUNT(1) FROM tab1 WHERE object_id>3;

SELECT COUNT(1) FROM tab1 WHERE object_id>4;

SELECT COUNT(1) FROM tab1 WHERE object_id>5; 

For each of these statements the literals were replaced with bind variables hence the SQL that was parsed has sql id c73v21bgp4956 and text like the one below:

SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"

After running the statements from above, I observed the content in v$sqlarea, v$sql and v$sql_shared_cursor views to see if Oracle did a hard parsing.

 
set line 200
col sql_text format a80
SELECT sql_id, sql_text, executions, child_number FROM v$sql WHERE sql_id='c73v21bgp4956';

SQL_ID        SQL_TEXT                                                                         EXECUTIONS CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------- ------------
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             5            0

As you can see Oracle reused the same child cursor 5 times, which means even though CURSOR_SHARING was set to SIMILAR it behaved as it was set to FORCE.

I ran the same example from above in 11.2.0.2 database. This time Oracle behaved as expected when CURSOR_SHARING is set to SIMILAR and used 5 different child cursors.

 
set line 200
col sql_text format a80
SELECT sql_id, sql_text, executions, child_number FROM v$sql WHERE sql_id='c73v21bgp4956';

SQL_ID        SQL_TEXT                                                                         EXECUTIONS CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------- ------------
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            0
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            1
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            2
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            3
c73v21bgp4956 SELECT COUNT(:"SYS_B_0") FROM tab1 WHERE object_id>:"SYS_B_1"                             1            4

When I checked v$sql_shared_cursor I found that all of the child cursors had Y for HASH_MATCH_FAILED column which is expected when suboptimal statement is executed with CURSOR_SHARING=SIMILAR.

Conclusion

Even though setting SIMILAR to CURSOR_SHARING initialization parameter is still acceptable value, it looks Oracle ignore it and behave as if FORCE is used. (this may not be true if compatible parameter is set to an earlier release, like 11.2.0.2 for example. I didn't have time to test this scenario)

Hope this helps.

ORA-12514 during switchover using Data Guard Broker (Update)

This is just a short update for an earlier post about getting ORA-12514 while performing switchover using DataGuard broker.

There was a comment on whether or not _DGMGRL static service is still required when performing a switchover in 11.2 and onwards.

In order for the broker to be able to successfully start an instance during a switchover operation, static service needs to be registered with the listener. Starting from 11.2.0.1 this service doesn’t have to be "<db_unique_name>_DGMGRL.<db_domain>". Oracle introduced a new instance-level property, StaticConnectIdentifier. As a value, this property accepts a valid net service name (defined in tnsnames.ora) or full connection identifier. Therefore, starting from 11.2.0.1 you have flexibility to use any service, which still needs to be statically registered with the listener.

Here is an example:

There are two databases db112a (primary) and db112b(standby) hosted on hosta and hostb respectively.

Below is the content of the tnsnames.ora. It is identical for both hosts:

DB112A=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB112A)
    )
  )

DB112B=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB112B)
    )
  )

listener.ora on HOSTA has the following content:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db112a)
      (ORACLE_HOME = /oracle/product/11.2/dbms)
      (SID_NAME = db112a)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hosta)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

listener.ora for HOSTB has db112b service statically registered:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = db112b)
      (ORACLE_HOME = /oracle/product/11.2/dbms)
      (SID_NAME = db112b)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hostb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Now the only thing left would be to set the StaticConnectIdentifier property for each of the databases.

DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration

Configuration - db112

  Protection Mode: MaxPerformance
  Databases:
    dg112a - Primary database
    dg112b - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

DGMGRL> edit database db112a set property staticConnectidentifier='db112a'; 
DGMGRL> edit database db112b set property staticConnectidentifier='db112b'; 

And you should be all set to perform a switchover:

DGMGRL> switchover to dg112b
Performing switchover NOW, please wait...
New primary database "dg112b" is opening...
Operation requires shutdown of instance "dg112a" on database "dg112a"
Shutting down instance "dg112a"...
ORACLE instance shut down.
Operation requires startup of instance "dg112a" on database "dg112a"
Starting instance "dg112a"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg112b"

DGMGRL> show configuration

Configuration - db112

  Protection Mode: MaxPerformance
  Databases:
    dg112b - Primary database
    dg112a - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Should you not want to deal with staticConnectidentifier you still need to have "<db_unique_name>_DGMGRL.<db_domain>" statically registered in order for the broker to be able to start the instance.

Useful resources:

11g -  Oracle Data Guard Broker 11g Release 2 (11.2) - Oracle Data Guard Installation

12c-  Oracle Data Guard Broker 12c Release 1 (12.1) - Oracle Data Guard Installation

MOS Note # 1305019.1 - 11.2 Data Guard Physical Standby Switchover Best Practices using the Broker

Hope this helps.