Oracle 11g Anti-Hackers Cookbook

PACKT has published a new Oracle Security book called the "Oracle 11g Anti-hacker's Cookbook" , written by Adrian Neagu.  They have kindly sent me a copy of the book which I shall be reviewing over the next  few weeks.  The book contains commands and configuration parameters to cover a wide range of security mechanisms from the OS, Network / Database Listener, Data, Authentication etc.  Apparently, it contains recipes and is not a full Reference Guide on the commands.  It shows usage of commands and scripts, assuming that you access the actual documentation on each command for more information.
.
.
.

Separate Child Cursor with varying bind allocation length

Although usage of bind variables can mean that the SQL statement does not need to be re-parsed at every execution, if the length of the bind changes, Oracle may create a seperate child cursor.

For example, if I run this test code :

declare
instring varchar2(2000);
  begin
   for i in 1..1999 loop
     instring := rpad('X',i);
     execute immediate 'insert into abc values (:instring) ' using instring ;
   end loop;
end;
/
commit;

I am making 1999 executions of the statement with a bind length that starts at 1 and ends at 1,999. The resultant SQL has 3 Child Cursors as :

SQL> select sql_id, child_number, executions  from v$sql where sql_id = '5p20kaht8s5bc';

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
5p20kaht8s5bc            0         32
5p20kaht8s5bc            1         96
5p20kaht8s5bc            2       1871

SQL> 

The first child was executed 32 times with a bind length of upto 32 bytes. The second child was executed 96 times for the next bind length "group" of upto 128 bytes. The third child was executed for the "group" of upto 2000 bytes.

 If I then run the loop as :

declare
instring varchar2(4000);
  begin
   for i in 1..2001 loop
     instring := rpad('X',i);
     execute immediate 'insert into abc values (:instring) ' using instring ;
    end loop;
end;
/
commit;

I see Child Cursor executions as :

SQL> select sql_id, child_number, executions  from v$sql where sql_id = '5p20kaht8s5bc';

SQL_ID        CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
5p20kaht8s5bc            0         32
5p20kaht8s5bc            1         96
5p20kaht8s5bc            2       1872
5p20kaht8s5bc            3          1

SQL> 

So the next "group" is 2000 bytes.

See Jonathan Lewis's reference to this behaviour of Bind Variables.

.
.
.

Cardinality Decay

There was a forums question on Cardinality decay when a desired  value is beyond the MAX value in a column.

Here is a quick demo in 11.2.0.1:

SQL> drop table test_cardinality purge;

Table dropped.

SQL> create table test_cardinality as select mod(rownum,100) as ID, dbms_random.string('X',25) as col_2
  2  from dual  connect by level < 10000;

Table created.

SQL> exec dbms_stats.gather_table_stats('HEMANT','TEST_CARDINALITY',method_opt=>'FOR ALL  COLUMNS SIZE 1',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL> select min(id), max(id), count(distinct(id)) from test_cardinality;

   MIN(ID)    MAX(ID) COUNT(DISTINCT(ID))
---------- ---------- -------------------
         0         99                 100

SQL> 
SQL> explain plan for select * from test_cardinality where id=50;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |   100 |  2900 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |   100 |  2900 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

   1 - filter("ID"=50)

13 rows selected.

SQL> 

SQL> explain plan for select * from test_cardinality where id=100;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    99 |  2871 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    99 |  2871 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

   1 - filter("ID"=100)

13 rows selected.

SQL> 

SQL> explain plan for select * from test_cardinality where id=125;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    74 |  2146 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    74 |  2146 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

   1 - filter("ID"=125)

13 rows selected.

SQL> 
SQL> explain plan for select * from test_cardinality where id=200;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |    29 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |     1 |    29 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

   1 - filter("ID"=200)

13 rows selected.

SQL> 
SQL> explain plan for select * from test_cardinality where id=150;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1710481294

--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |    48 |  1392 |    16   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_CARDINALITY |    48 |  1392 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

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

   1 - filter("ID"=150)

13 rows selected.

SQL> 

You can see that for ID values beyond 100, the expected cardinality does "decay". For the target value of 125, the expected cardinality is 74. For the target value of 150, the expect cardinality is 48. For the target value of 200, the expected cardinality is 1.
.
.
.