Oracle SQL

SQL*Plus tips #4: Branching execution

Posted on by Posted in oracle, SQL*Plus Leave a comment

Today I’ll show trick how we can use branching in SQL*Plus.
Although I previously showed the conditional execution of scripts and it really can be used for branching, but today I’ll show how to do it without splitting the script into several smaller scripts. In contrast to the conditional script execution, I’ll use a different method.

It is very simple, as usual – if you want to execute only one part of script, you can just comment out all unnecessary. So depending on the conditions, we can execute a script which will start a comment.

Suppose we need to create a script, which, depending on the input parameter will be required to execute a specific query.
See how this can be done:
1. “test.sql”:

def param = &1

@if &param=1
   select 1 from dual;
/* end_if */

@if &param=2
   select 2 from dual;
/* end_if */

@if &param=3
   select 3 from dual;
/* end_if */

2. “if.sql”:

col do_next new_val do_next noprint;
select 
      case 
         when &1 then 'inc/null'
         else 'inc/comment_on'
      end as do_next
from dual;
@&do_next

3. “inc/comment_on.sql” contains only 2 chars:

/*

4. “inc/null.sql” is the same as in the previous examples – just empty file.

Ok, lets test it:

SQL> @test 1

         1
----------
         1


SQL> @test 2


         2
----------
         2


SQL> @test 3


         3
----------
         3

As you see, we got what we wanted. Please note that we have to close the multiline comments in the right places only(/* end_if */). So we cannot use in these parts another “*/”. But you can use it in another child scripts.

Same way we can make an analogue of switch/case:
“test2.sql”: 

@switch &1

   @when 1 then
      select 1 from dual;
   /* end when */

   @when 2 then
      select 2 from dual;
   /* end when */

   @when 3 then
      select 3 from dual;
   /* end when */

/* end switch */

Spoiler:: switch.sql SelectShow

Spoiler:: when.sql SelectShow

Example: 
SQL> @test2 2

         2
----------
         2

SQL> @test2 3

         3
----------
         3

SQL*Plus tips #3: Iterators and recursions

Posted on by Posted in oracle, SQL*Plus 8 Comments

We all used to iterators, recursions and branching in programming, but sql*plus does not have such commands. Today I will show how to do iterators/recusions.

Suppose we want to call a script 3 times. So it would be convenient if we can do it like: 

@iterate 3 @some_script

It is very easy: We can recursively call the script by reducing variable until it is greater than zero. and if a variable is zero, then call an empty “NULL” script.
iterate.sql:

-- initializing input parameters as i showed in previous tips:
@inc/input_params_init.sql;
-- for example max number of input parameters = 14:
def _INIT_PARAMS = '"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
def _ITER = "&1"

prompt ------   iteration &_ITER start: "@&2  -----------;
set termout off


col _SCRIPT    new_val _SCRIPT   noprint;
col _CONTINUE  new_val _CONTINUE noprint;
col _PARAMS    new_val _PARAMS   noprint;
select 
   replace('"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
          ,'${ITER}'
          ,'&_ITER'
          )          as "_PARAMS"
  ,case 
      when &1 > 0 then 'iterate '||(&1 - 1)
      else 'inc/null'
   end               as "_CONTINUE"
  ,case 
      when &1 > 0 then '&2' 
      else 'inc/null'
   end               as "_SCRIPT"
from dual;
set termout on

@&_script            &_PARAMS
@&_CONTINUE &_SCRIPT &_INIT_PARAMS

As you see i also added option to pass iteration_number as input parameter, so we can call it like: @iterate 3 script.sql ${ITER}
And null.sql in “inc” directory is just empty file.

Lets test it:

SQL> $cat test.sql
select &1 a,'&2' b,'&3' c from dual;

SQL> @iterate 3 test.sql 1 2 3
------   iteration 3 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 2 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 1 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 0 start: "@test.sql  -----------

SQL> @iterate 2 test.sql 1 2 ${ITER}
------   iteration 2 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 2

------   iteration 1 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 1

------   iteration 0 start: "@test.sql  -----------

Note that last string “iteration 0 start” just means that it is last empty call. I made this only for showing how we can do postprocessing after all iterations.
Ok, it works good, but with a little modification we can also add option to call such script with list of values too:

Spoiler:: final iterate.sql SelectShow

See how it works:

-- iterate by list of values  (7,3,10):
SQL> @iterate (7,3,10) test.sql 1 2 ${ITER}
******   iteration 7 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 7

******   iteration 3 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 3

******   iteration 10 start: "@test.sql  ***********

         A B C
---------- - --
         1 2 10

-- now with simple 2 iterations:
SQL> @iterate 2 test.sql 1 2 ${ITER}
******   iteration 2 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 2

******   iteration 1 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 1

So if you call script with “iterate N …” it will iterate N times, and if you specify “iterate (X,Y,…,Z)” it will iterate by this list.

PS. About a branching i will wrote later…

SQL*Plus tips. #2

Posted on by Posted in oracle, SQL*Plus 2 Comments

I think you know the famous print_table procedure by Tom Kyte. It is really great, but a little hard(it requires create procedure or place it in anonymous block) and in last oracle versions we can do same with one simple query with xmltable/xmlsequence:

SQL> select *
  2  from
  3     xmltable( '/ROWSET/ROW/*'
  4               passing xmltype(cursor(select * from hr.employees where rownum<3))
  5               columns
  6                  col varchar2(100) path 'name()'
  7                 ,val varchar2(100) path '.'
  8        );

COL                            VAL
------------------------------ ------------------------------------------------------
EMPLOYEE_ID                    198
FIRST_NAME                     Donald
LAST_NAME                      OConnell
EMAIL                          DOCONNEL
PHONE_NUMBER                   650.507.9833
HIRE_DATE                      21-JUN-07
JOB_ID                         SH_CLERK
SALARY                         2600
MANAGER_ID                     124
DEPARTMENT_ID                  50
EMPLOYEE_ID                    199
FIRST_NAME                     Douglas
LAST_NAME                      Grant
EMAIL                          DGRANT
PHONE_NUMBER                   650.507.9844
HIRE_DATE                      13-JAN-08
JOB_ID                         SH_CLERK
SALARY                         2600
MANAGER_ID                     124
DEPARTMENT_ID                  50

20 rows selected.

It is very easy, but for conveniency we need to add “rownum”:

SQL> select *
  2  from
  3     xmltable( 'for $a at $i in /ROWSET/ROW
  4                   ,$r in $a/*
  5                     return element ROW{
  6                                       element ROW_NUM{$i}
  7                                      ,element COL_NAME{$r/name()}
  8                                      ,element COL_VALUE{$r/text()}
  9                                      }'
 10               passing xmltype(cursor(select * from hr.employees where rownum<3))
 11               columns
 12                  row_num   int
 13                 ,col_name  varchar2(30)
 14                 ,col_value varchar2(100)
 15        );

   ROW_NUM COL_NAME                       COL_VALUE
---------- ------------------------------ ------------------------------------------
         1 EMPLOYEE_ID                    198
         1 FIRST_NAME                     Donald
         1 LAST_NAME                      OConnell
         1 EMAIL                          DOCONNEL
         1 PHONE_NUMBER                   650.507.9833
         1 HIRE_DATE                      21-JUN-07
         1 JOB_ID                         SH_CLERK
         1 SALARY                         2600
         1 MANAGER_ID                     124
         1 DEPARTMENT_ID                  50
         2 EMPLOYEE_ID                    199
         2 FIRST_NAME                     Douglas
         2 LAST_NAME                      Grant
         2 EMAIL                          DGRANT
         2 PHONE_NUMBER                   650.507.9844
         2 HIRE_DATE                      13-JAN-08
         2 JOB_ID                         SH_CLERK
         2 SALARY                         2600
         2 MANAGER_ID                     124
         2 DEPARTMENT_ID                  50

20 rows selected.

Now we can create simple script for it with formatting:

Spoiler:: print_table.sql SelectShow

Usage example:print_table example
As you see script works fine, but it is require to pass query as parameter, though sometimes it is not so convenient. For example if we want started query and later decided to show it with print_table. In that case we can create scripts with tricks from previous part:
Spoiler:: print_last.sql SelectShow

Example:
Spoiler: SelectShow

PS. if you will use xmltype(cursor(…)) on versions less that 11.2.0.3 you can get errors with xml rewriting. In this case you need to disable it:

alter session set events '19027 trace name context forever, level 0x1';
Update:
Vladimir Przyjalkowski rightly pointed out that such approach will be suboptimal in case of big amount of data:

A nice toy, but i’m sure it will not tolerate big data.

It is absolutely true, because at first, “xmltype(cursor(…))” aggregated data as xmltype, and only then xmltable returns data.
But I use print_table for small amount of data only, and in case of when i want to see sample of data, i usually add limitation by rownum, which also convenient because it automatically changes optimizer mode with enabled parameter “_optimizer_rownum_pred_based_fkr” (it is default):

SQL> @param_ rownum

NAME                               VALUE   DEFLT  TYPE     DESCRIPTION
---------------------------------- ------- ------ -------- ------------------------------------------------------
_optimizer_rownum_bind_default     10      TRUE   number   Default value to use for rownum bind
_optimizer_rownum_pred_based_fkr   TRUE    TRUE   boolean  enable the use of first K rows due to rownum predicate
_px_rownum_pd                      TRUE    TRUE   boolean  turn off/on parallel rownum pushdown optimization

However, we can easily change the our query to make it optimally with a lot of data too:

select row_num
      ,t2.*
from 
   (select rownum row_num
         , column_value x
    from table(xmlsequence(cursor( &1 )))
   ) t1
  ,xmltable( '/ROW/*'
             passing t1.x
             columns
                col_num for ordinality
               ,col_name  varchar2(30) path 'name()'
               ,col_value varchar2(100) path '.'
      )(+) t2;

Lets test it with pipelined function which will pipe rows infinitely and log count of fetched rows:

-- drop table xt_log purge;
-- drop function f_infinite;

create table xt_log(n int);

create function f_infinite 
   return sys.ku$_objnumset pipelined 
as
   i int:=0;
   pragma autonomous_transaction;
begin
   loop 
      i:=i+1;
      insert into xt_log values(i);
      commit;
      pipe row(i);
   end loop;

exception 
   when NO_DATA_NEEDED then
      commit;
end;
/

Spoiler:: New version of print_table: print_table2.sql SelectShow

And look final test:

SQL> select * from xt_log;

no rows selected

SQL> @print_table2 "select * from table(f_infinite)"
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         1          1 COLUMN_VALUE                   1
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         2          1 COLUMN_VALUE                   2
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         3          1 COLUMN_VALUE                   3
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         4          1 COLUMN_VALUE                   4
Press Enter to view next row...

   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
         5          1 COLUMN_VALUE                   5
Press Enter to view next row...


6 rows selected.
-------------------------------
SQL> select * from xt_log;

         N
----------
         1
         2
         3
         4
         5
         6
         7

7 rows selected.

So we got what we wanted!

PS. Full scripts:
1. Simple: print_table.sql
2. For big queries: print_table2.sql

SQL*Plus tips. #1

Posted on by Posted in oracle, SQL*Plus 1 Comment

If you are using SQL*Plus, you are likely to use the input parameters. And if you omit one of them, SQL*Plus will show prompt for it, like this:

SQL> get test.sql
  1  select 'Input variable 1 = &1' from dual
  2  union all
  3  select 'Input variable 2 = &2' from dual
  4  union all
  5* select 'Input variable 3 = &3' from dual
SQL> @test var1 var2
Enter value for 3:

'INPUTVARIABLE1=VAR1'
-----------------------
Input variable 1 = var1
Input variable 2 = var2
Input variable 3 =

Elapsed: 00:00:00.01

It is allright, if all variables are needed, but what if we don’t want to press enter for all omitted variables or specify they(especially if script can be start in silent, non interactive mode) and want to use default values for omitted variables or these variables can be unnecessary?
Strictly speaking, there are many different techniques for solving it, see some of them:

Spoiler:: Comma-separated params SelectShow

Spoiler:: With SPOOL and DEFINE SelectShow

Last example also shows a very useful way to read file into a variable.
But i think, the best option for initializing parameters is the solution by Vladimir Begun:

SQL> get test3
  1  set termout off
  2  COLUMN 1 NEW_VALUE 1    noprint
  3  COLUMN 2 NEW_VALUE 2    noprint
  4  COLUMN 3 NEW_VALUE 3    noprint
  5  SELECT '' "1", '' "2", '' "3" FROM dual WHERE 1=0;
  6  SELECT nvl('&1','default1') "1"
  7       , nvl('&2','default2') "2"
  8       , nvl('&3','default3') "3"
  9    FROM dual;
 10  col var1 clear;
 11  col var2 clear;
 12  col var3 clear;
 13  set termout on
 14  prompt var1 = &1, var2 = &2, var3 = &3;
 15  undef 1
 16  undef 2
 17* undef 3
 18  .
SQL> @test3 1 2 3
var1 = 1, var2 = 2, var3 = 3
SQL> @test3 1 2
var1 = 1, var2 = 2, var3 = default3

So i can create 2 include files – for execution at the start and at the end of all scripts. I created directory “inc” for include files and files:

Spoiler:: inc/s_begin.sql SelectShow

and
Spoiler:: inc/s_end.sql SelectShow
And for example get_index.sql:
Spoiler:: get_index.sql SelectShow

Spoiler:: Sample output SelectShow
As you see i can omit owner parameter and in this case it will search in all schemas.

Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)

Posted on by Posted in adaptive serial direct path reads, oracle, undocumented Leave a comment

Previously i showed not obvious example with hint “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X, rows=Y)“. Strictly speaking i don’t know how exactly cbo calculates number of index leaf blocks in that case: in those examples they was 1981 for “blocks=1, rows=50″ and 49525 for “blocks=5, rows=10″.
But i know that with “INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X)” i can set exact blocks number.
Also those test-cases didn’t show when occurs decision changing. So todays my test will show it.

UPD: btw previously I wrote that _very_large_object_threshold is a percentage of _db_block_buffers and Igor Usoltsev have tested and confirmed my guess

Spoiler:: Test code SelectShow

Spoiler:: Test results SelectShow

Test script: controlling_direct_reads_with_profiles.sql

Unresolved quiz: Avoiding in-list iterator

Posted on by Posted in oracle, query optimizing 7 Comments

A couple days ago i had very interesting quiz, which is not resolved yet.
Look at this simplified query:

  select *
  from xt1,xt2
  where
       xt1.b=10
   and xt1.a=xt2.a
   and xt2.b in (1,2);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2715236140

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |   100 | 36900 |   501   (0)| 00:00:07 |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   501   (0)| 00:00:07 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:02 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|   5 |    INLIST ITERATOR            |        |       |       |            |          |
|*  6 |     INDEX RANGE SCAN          | IX_XT2 |     1 |       |     3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("XT1"."B"=10)
   6 - access("XT1"."A"="XT2"."A" AND ("XT2"."B"=1 OR "XT2"."B"=2))

Spoiler:: Full test case SelectShow

As you see, in such queries cbo always generating plans with INLIST ITERATOR, and it is reasonably in cases when there are many rows with different values of field B for most values of A, and this number is much larger than number of values in the “INLIST”. But in such case as shown, will be better to use index range scan with access by A and filter by B:

SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b in (1,2);

no rows selected

Statistics
----------------------------------------------------------
        ...
        505  consistent gets
SQL> -- without inlist iterator:
SQL> select *
  2  from xt1,xt2
  3  where
  4       xt1.b=10
  5   and xt1.a=xt2.a
  6   and xt2.b+0 in (1,2);

no rows selected

Statistics
----------------------------------------------------------
        ...
        305  consistent gets

But how we can do it? I know 5 options:
1. Trace event 10157
2. Rewrite code. for example replacing “b in (1,2)” to “b+0 in (1,2)”
3. Changing query with “Advanced query rewrite” (DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE)
4. Recreating index from xt2(a,b) to xt2(a,1,b)
5. Changing optimizer_mode to “rule” through hint or SQL profile/baseline

But unfortunately all of them are inapplicable for the my real problem, because i cannot for some reasons rewrite query or change query with advanced rewrite, cannot recreate/add index, and can’t change optimizer_mode, because execution plan for the real query will become worst than plan generated with CBO with inlist iterator(some operations aren’t exists in RBO).

Could anybody suggest any another solution?

UPDATE #1:
This bug is fixed now in 12.2, and patch 16516751 is available now for 11.2.0.3 Solaris64.
Changes:
1. CBO can consider filters in such cases now
2. Hint NUM_INDEX_KEYS fixed and works fine

UPDATE #2:
Very interesting solution by Igor Usoltsev(in russian):
Ignored hint USE_CONCAT(OR_PREDICATES(N)) allows to avoid inlist iterator.
Example:

select--+ USE_CONCAT(OR_PREDICATES(32767))
 * from xt1,xt2
where
     xt1.b=10
 and xt1.a=xt2.a
 and xt2.b in (1,2)
/

Plan hash value: 2884586137          -- good plan:
 
----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |       |       |   401 (100)|          |
|   1 |  NESTED LOOPS                 |        |       |       |            |          |
|   2 |   NESTED LOOPS                |        |   100 | 36900 |   401   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| XT1    |   100 | 31000 |   101   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IX_XT1 |   100 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | IX_XT2 |     1 |       |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | XT2    |     1 |    59 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("XT1"."B"=10)
   5 - access("XT1"."A"="XT2"."A")
       filter(("XT2"."B"=1 OR "XT2"."B"=2))

From 10053 trace on nonpatched 11.2.0.3:
inlist_concat_diff_10053

Deterministic function vs scalar subquery caching. Part 3

Posted on by Posted in deterministic functions, oracle, scalar subquery caching Leave a comment

In previous parts i already point out that:

  1. Both mechanisms are based on hash functions.
  2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
  3. Hash collisions depends on the single parameter “_query_execution_cache_max_size” for both mechanizms, but they are more frequent in SSC.
  4. Deterministic functions does not keeps last result as scalar subquery caching
  5. Caching of deterministic functions results turns off after a certain number of attempts to get the value from the cache. But SSC always returns results from cache if values already cached.

Today’s post is just addition to previous topics:

I wrote about turning caching off after many unsuccessfull attempts to get value from cache, but i didn’t say what it is the number. In fact caching of deterministic functions also depends on two another hidden parameters:

SQL> @param_ plsql%cach

NAME                                 VALUE        DEFLT    TYPE       DESCRIPTION
------------------------------------ ------------ -------- ---------- ------------------------------------------------------------------
_plsql_cache_enable                  TRUE         TRUE     boolean    PL/SQL Function Cache Enabled
_plsql_minimum_cache_hit_percent     20           TRUE     number     plsql minimum cache hit percentage required to keep caching active

First parameter “_plsql_cache_enable” is just a parameter which enables/disables this caching mechanism.
But the second parameter – “_plsql_minimum_cache_hit_percent” – is responsible for the percentage of unsuccessful attempts which disables caching.

I will show their effects with the example from the previous post:

-- set this parameter to big value for maximizing caching:
alter session set "_query_execution_cache_max_size" = 131072;
-- log table clearing:
truncate table t_params;
-- test with percentage = 50
alter session set "_plsql_minimum_cache_hit_percent"=50;
select sum(f_deterministic(n)) fd
from
  xmltable('1 to 10000,1 to 10000'
           columns n int path '.'
          );
 
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
                      0
*/
-- now i change cache hit percentage parameter to 0:
alter session set "_plsql_minimum_cache_hit_percent"=0;
truncate table t_params;
select sum(f_deterministic(n)) fd
from
  xmltable('1 to 10000,1 to 10000'
           columns n int path '.'
          );
 
select 10000-count(count(*)) "Count of cached results"
from t_params
group by p
having count(*)>1;
/*
Count of cached results
-----------------------
                   2039
*/

How works optimization of loops in PL/SQL in 11g: Part 1. Deterministic functions

Posted on by Posted in deterministic functions, oracle, PL/SQL optimization 4 Comments

As you know, since 10g Oracle can optimize PL/SQL loops with moving code out of loops for reducing number of identical code executions.
If you have not read yet about it, I advise you to first read these two articles:
1. “PLSQL_OPTIMIZE_LEVEL: The optimization strategy of Oracle” by Saurabh K. Gupta
2. “PL/SQL optimisation in 10g” by Adrian Billington

But since 11g Oracle also can optimize code with deterministic functions too. For this to happen, code must meet the following conditions:
1. PLSQL_OPTIMIZE_LEVEL greater or equal 2
2. Parameters should not be changed in the loop body. Strictly speaking, there should not be any assignment of parameters.
3. Should not be any implicit conversions of parameters in function call, i.e. all variables/literals must be the same type as input parameters declared.
4. Should not be any call of non-deterministic functions (except some standard sql functions like to_date, to_char, nvl) or procedures in the loop

Note that this rules concerns only same scope level as this loop and not inner loops or another blocks.

Let me show how it works on simple examples:

Controlling “direct path reads” decision with INDEX_STATS/table_stats

Posted on by Posted in adaptive serial direct path reads, oracle 3 Comments

Since 11.2.0.2 direct path read decision on full scans(FTS/IFFS) can be based on the statistics.

And if my test is correct, it appears that we can control this behavior on query level with changing number of blocks through index_stats/table_stats outlines:

UPD: I did a little change of the test case for avoiding impact of hard parse on main test.

Spoiler:: Test case SelectShow

Spoiler:: Result SelectShow

Workaround for deadlock with select for update order by on 11.2.0.2-11.2.0.3

Posted on by Posted in bug, oracle 2 Comments

There is well-known bug with “for update order by” on 11.2, when rows locks not in specified order, although the ordering occurs.
I already wrote on my russian blog about the appearance of “buffer sort” in plans with “for update” even if sort order was not specified. And this behavior can be disabled for example by specifying /*+ opt_param( ‘optimizer_features_enable’ ’11.1.0.7′ ) */.
But if we want to solve problem with deadlock, we need to force index full scan/index range scan ascending with “buffer sort” usage.

UPD: The patch is already available for 11.2.0.2 and 11.2.0.3: Patch 13371104: LOCK ORDER HAS CHANGED BETWEEN 10.2.0.5 AND 11.2.0.2.
Read more: Simple example