Monthly Archives: March 2013

SIMULATE BUFFER BUSY WAIT AND IDENTIFY HOT OBJECT

Tuning, Uncategorized
In my earlier post on Buffer Cache Wait Events, I had talked about buffer busy waits. 
 
Lets see a demonstration of how to simulate a Buffer Busy Wait scenario and how to find out object part of buffer busy wait.
Login to your database as sys user
SYS> create user test identified by test;
     grant dba to test;
     grant select on v_$session to test;
Now Connect to Test User and create a table with 10000 records .
SYS> conn test/test
TEST> create table t nologging
      as
      select rownum t1,rownum+1 t2
      from dual
      connect by level<=10000;
The
Next thing would be to create a package which will allow me to access
the same set of blocks from multiple session at the  same time.
TEST>CREATE OR REPLACE PACKAGE GEN_BUF_BUSY
    authid current_user
AS
TYPE t_RefCur IS REF CURSOR;
FUNCTION RetDSQL(p_sql IN VARCHAR2) RETURN t_RefCur;
PROCEDURE RUNSELECT;
PROCEDURE RUNUPDATE1;
PROCEDURE RUNUPDATE2;
procedure kill_session(p_username varchar2);
END GEN_BUF_BUSY;
/
Package would have one select procedure and two update procedure updaing different columns of the table at the same row.
TEST>CREATE OR REPLACE PACKAGE BODY GEN_BUF_BUSY AS
FUNCTION RetDSQL(p_sql IN VARCHAR2) RETURN t_RefCur
as
v_RetCur t_RefCur;
BEGIN
OPEN v_RetCur FOR p_sql;
RETURN v_RetCur;
end RETDSQL;
PROCEDURE RUNSELECT
as
cursor dummy is select * from t;
c1 t_refcur;
rec dummy%rowtype;
begin
for i in 1..1000000
loop
c1:=retDSQL(‘select * from t’);
loop
fetch c1 into rec;
exit when c1%notfound;
end loop;
close c1;
end loop;
end RUNSELECT;
PROCEDURE RUNUPDATE1
as
BEGIN
for i in 1..1000000
loop
update t set t1=rownum;
commit;
end loop;
END RUNUPDATE1;
PROCEDURE RUNUPDATE2
as
BEGIN
for i in 1..1000000
loop
update t set t2=rownum;
commit;
end loop;
END RUNUPDATE2;
procedure kill_session(p_username varchar2)
as
cursor c1 is select sid,serial# from sys.v_$session where username=p_username;
rec c1%rowtype;
begin
open c1;
loop
fetch c1 into rec;
exit when c1%notfound;
execute immediate ‘alter system kill session ”’||rec.sid||’,’||rec.serial#||””;
end loop;
end;
end GEN_BUF_BUSY;
/
Now open Six sessions
Session 1 :  SYS User
Session 2 :  TEST
Session 3 :  TEST
Session 4 :  TEST
Session 5 :  TEST
Session 6 :  TEST
and run the following procedures
exec GEN_BUF_BUSY.runUPDATE1; — Run on SESSION 2
exec GEN_BUF_BUSY.runUPDATE2; — Run on SESSION 3
exec GEN_BUF_BUSY.runSELECT;  — Run on SESSION 4
exec GEN_BUF_BUSY.runSELECT;  — Run on SESSION 5
exec GEN_BUF_BUSY.runSELECT;  — Run on SESSION 6
From session one (SYS) monitor by following queries 
SYS> col event for a30
     select event,p1 file#,p2 block#
     from v$session_wait
     where sid in
         (select sid from v$session
          where username=’TEST’);
EVENT                               FILE#     BLOCK#
—————————— ———- ———-
log buffer space                        0          0
SQL*Net message from client    1650815232          1
buffer busy waits                      4     190828
buffer busy waits                       4     190828
enq: TX – row lock contention  1415053318     131096
SQL*Net message from client    1650815232          1
P1 stands for File id and P2 stands for Block id when you work with Buffer Busy Waits.
Now we have two methods to identify the object the wait belongs to :
1.Using the dump of the file/block
2. Using DBA_EXTENTS
Let’s use both of them :
1. Take a dump of the specified file/block
    Identify the seg/obj  in trace file . It is given in hexadecimal format in  trace file.
   Convert the hexadecimal no. to decimal no.
   Identify the object from dba_objects
- Take the dump of the specified file/block
SYS>alter system dump datafile 4 block 190828 ;
- Identify the tracefile of the session
SYS> col value for a70
          select  value from v$diag_info
          where name like ‘%Trace File%';
VALUE
———————————————————————-
/u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_ora_9088.trc
-    Identify the seg/obj  in trace file .
SYS>ho vi u01/app/oracle/diag/rdbms/orcl1/orcl1/trace/orcl1_ora_9088.trc
Object id on Block? Y
 seg/obj: 0x128cf csc: 0x00.fdd256  itc: 3  flg: E  typ: 1 – DATA
     brn: 0  bdba: 0x102e968 ver: 0x01 opc: 0
     inc: 0  exflg: 0
-  The object-id in hexadecimal format is 128cf
-   Convert the hexadecimal no. to decimal no. – It can be done using simple maths or by creating a function to do it.
     . Using mathematics
sql>select 15 + 12*16 + 8*16*16 + 2*16*16*16 + 1*16*16*16*16
    from dual;
15+12*16+8*16*16+2*16*16*16+1*16*16*16*16
—————————————–
                                    75983
SQL>  col owner for a30
      col object_name for a25
  
      select owner,object_name
      from dba_objects
      where data_object_id = 75983;
OWNER                          OBJECT_NAME
—————————— ——————–
TEST                           T
OR
     . Create a function to convert hexadecimal to decimal and then use the function to find the object
CREATE OR REPLACE FUNCTION HEX2DEC (hexnum IN CHAR) RETURN NUMBER IS
  i                 NUMBER;
  digits            NUMBER;
  result            NUMBER := 0;
  current_digit     CHAR(1);
  current_digit_dec NUMBER;
BEGIN
  digits := LENGTH(hexnum);
  FOR i IN 1..digits LOOP
     current_digit := SUBSTR(hexnum, i, 1);
     IF current_digit IN (‘A’,’B’,’C’,’D’,’E’,’F’) THEN
        current_digit_dec := ASCII(current_digit) – ASCII(‘A’) + 10;
     ELSE
        current_digit_dec := TO_NUMBER(current_digit);
     END IF;
     result := (result * 16) + current_digit_dec;
  END LOOP;
  RETURN result;
END hex2dec;
/
SYS>  select owner,object_name
      from dba_objects
      where data_object_id=hex2dec(upper(‘128cf‘));
OWNER                          OBJECT_NAME
—————————— ——————–
TEST                           T
2. Use DBA_EXTENTS : Identify the object to which the specified  block belongs
SQL>col owner for a10
    col segment_name for a15
    select owner, segment_name
    from dba_extents
    where file_id = 4
      and 190828 between block_id and block_id+blocks-1;
OWNER      SEGMENT_NAME
———- —————
TEST       T

References:

http://www.oracledba.in/Articles/display_article.aspx?article_id=784

————————————————————————————————————

Related links: 

Home

Database Index
Tuning Index 
Buffer Cache Wait Events
Clustering Factor Demystified
Direct Read Enhancements in 11g

Oracle Checkpoints

 

 

———————–

TUNING SHARED POOL : A DEMONSTRATION

Tuning, Uncategorized

In my earlier post, cursor sharing demystified, I had discussed and demonstrated the effects of various values of the

parameter CURSOR_SHARING. In this post, I will demonstrate that hard parsing can be reduced by
  •  – Replacing literals with bind variables
  •  – setting CURSOR_SHARING=SIMILAR

Overview

1. create a procedure to query sh.sales without using bind variables
2. set cursor_sharing to exact
3. Take begin snapshot for awr/statspack
4. run the procedure
5. Take end snapshot for awr/statspack
6. Generate awr/statspack report
7. Both reports indicate low soft parsing
8. Resolve the problem using

– set cursor_sharing to similar

OR

– set cursor_sharing to exact and use bind variables in the procedure

9. Run the procedure and generate awr/statspack report
10. Verify that soft parsing has increased considerably

————————————————————————-

Implementation

SQL>conn sh/sh

— create package test in which we are querying table sh.sales repeatedly (50000 times)  without using bind variables

sql>
CREATE OR REPLACE PACKAGE test AS
     PROCEDURE Workload;
     PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY test AS
PROCEDURE Workload IS
BEGIN
FOR i in 1 .. 50000
LOOP
Foo(i);
END LOOP;
END Workload;
PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
BEGIN
DECLARE
l_stmt VARCHAR2(2000);
BEGIN
l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = ' ||
TO_CHAR(CUSTID);
EXECUTE IMMEDIATE l_stmt;
END;
END Foo;
END;
/

— Create begin snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create begin snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();
    alter system set cursor_sharing = exact scope=memory;

— execute the workload —

sql>conn sh/sh
       exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat
      exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
       exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
    @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % is very low as cursor sharing is exact   and bind variables have not been used.

— To solve this issue, we can take two actions :

  1.  Change cursor_sharing to similar
  2.  Use bind variables in the procedure

-—————– Solution 1 —————————

       Change cursor_sharing to similar (if application code cannot be changed)

sql>conn / as sysdba
    alter system set cursor_sharing=similar scope=memory;

— create begin snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();

— execute the workload —

sql>conn sh/sh
    exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat

       exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
    exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
    @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % has increased substantially as cursor  sharing has been set to similar

—————— Solution 2 —————————

      . Use bind variables in the procedure

— Reset cursor_sharing to exact

sql>conn / as sysdba
    alter system set cursor_sharing=exact scope=memory;

— alter the Foo procedure to use bind variables :

CREATE OR REPLACE PACKAGE BODY test AS
      PROCEDURE Workload IS
       BEGIN
             FOR i in 1 .. 50000     LOOP
                 Foo(i);
             END LOOP;
         END Workload;


       PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
       BEGIN
           DECLARE
              l_stmt VARCHAR2(2000);
            BEGIN
                   l_stmt := ‘SELECT * FROM sh.sales s WHERE s.cust_id = :p_cust_id';
                  EXECUTE IMMEDIATE l_stmt USING CUSTID;
            END;
         END Foo;
END;
/

— Create begin snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create begin snapshot for awr report —

sql>conn / as sysdba
 exec dbms_workload_repository.create_snapshot();
        alter system set cursor_sharing = exact scope=memory;

— execute the workload —

sql>conn sh/sh
      exec test.workload;

— Create end snapshot for statspack report —

sql>conn perfstat/perfstat
       exec statspack.snap;

— create end snapshot for awr report —

sql>conn / as sysdba
       exec dbms_workload_repository.create_snapshot();

— create statspack report —

sql>conn perfstat/perfstat
    @?/rdbms/admin/spreport.sql

— create awr report —

sql>conn / as sysdba
      @?/rdbms/admin/awrrpt.sql

— Instance efficieny section of both awr and statspack report  show that soft parse % has increased substantially as bind variables  have been used.
————————————————————————————————————————-

Related links:
                                                                                                                                   ————–

CURSOR SHARING DEMYSTIFIED

Database, Tuning, Uncategorized

As I discussed in my earlier post on parent and child cursors, multiple child cursors may be created for the same parent cursor if bind variables have different values.
In this post I will discuss about the parameter CURSOR_SHARING which controls the sharing of child cursors if bind variables have different values.

The parameter CURSOR_SHARING can take 3 values :

  •  – EXACT
  •  – SIMILAR
  •  – FORCE

Let’s see the impact of different values :

CURSOR_SHARING = EXACT

– In this case when the same statement is issued with different literals, multiple parent cursors will be created.

— create a test table with

1 record with id1 = id2 = 1

1000 records with id1 = id2 = 2

2000 records with id1 = id2= 3

— create an index on the table

HR> drop table test purge;
create table test (id1 number, id2 number, txt char(1000));
insert into test values (1,1, ‘one’);

begin
for i in 1..1000 loop
insert into test values (2,2, ‘two’);
insert into test values (3,3, ‘three’);
end loop;
end;
/

    insert into test select * from test where id1=3;
commit;

    create index test_idx1 on test(id1);
create index test_idx2 on test(id2);

    select id1,id2, count(*)
from test
group by id1,id2;


CURSOR_SHARING=EXACT 

 

Parent   Parent  Parent
       |               |             |
 Child     Child  Child
 

— Flush the shared pool

Set cursor_sharing=exact

SYS>alter system set CURSOR_SHARING=’EXACT';
alter system flush shared_pool;
sho parameter CURSOR_SHARING

NAME                                 TYPE        VALUE
—–                                        —-        —–

cursor_sharing                       string      EXACT
— Issue identical statements with different values of literals

HR>conn hr/hr
select count(*) from test where id1=1;
select count(*) from test where id1=2;
select count(*) from test where id1=3;

— Check that the 3 parent cursors have been created

— Note that   there is one record for each statement in v$sqlarea as   one parent cursor is created for each sql statement since  each of these statements differ in their text.

  •     Each statement has different SQL_ID/HASH_VALUE
  •    There is one child per parent cursor (version_count=1)
  •     Execution plans for id = 2,3 is same (full table scan) (same PLAN_HASH_VALUE)
  •     Execution plan for id = 1 is different (indexed access)

SYS>col sql_text for a30 word_wrapped

         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE    PLAN_HASH_VALUE
—————————— ————- ————- ———-               —————

select count(*) from test      1n09m564gh0q3          1  2297955011       4192825871
where id1=3

select count(*) from test      20nhaap8uxf7s             1   1370405112       3507950989
where id1=2

select count(*) from test      bavqx2mw26wg0         1  4163072480    3507950989
where id1=1

— Note that 3 child cursors have been created for the 3 statements

SYS>col child_number for 99
         SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE, 
                        PLAN_HASH_VALUE
         FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE    PLAN_HASH_VALUE

—————————— ————- ———- ———-   —————

select count(*) from test      1n09m564gh0q3          0 2297955011   4192825871
where id1=3

select count(*) from test      20nhaap8uxf7s          0 1370405112       3507950989
where id1=2

select count(*) from test      bavqx2mw26wg0          0 4163072480   3507950989
where id1=1

— We can see that in all 6 cursors have been created :
– 3 parent cursors and
– 3 child cursors

Each of the cursor occupies memory. Parent cursors contain sql text whereas
child cursor contains execution plan, execution statistics and execution
environment. If we replace literal with a bind variable, all the 3 statements
will be identical and hence only parent cursor needs to be created. Multiple
child cursors can be created for different values of the bind variables.

That’s what CURSOR_SHARING=SIMILAR does. It replaces literals in the otherwise
identical SQL statements with bind variables and only one parent cursor is
created.

If histogram on a column is created with only one bucket,i.e. it does not know about the skew
in data, only one child cursor will be created.

If histogram is created on a column with >1 buckets i.e. it knows about skew in data in that
column, it  will create one child cursor for each statement even of the execution plan is same.

Thus CURSOR_SHARING=SIMILAR reduces the no. parent cursors.

If there is skew in data
If histogram on the column containing skewed data is there
multiple child cursors may be created – one for each value of the bind variable
else (histogram is not available)
only one child cursor will be created.
else (Data is not skewed)
only one child cursor will be created.

Now, since there is identical skewed data in id1 and id2 , we will create histogram  on id1
with one bucket and on id2 with 4 buckets and see the difference.

CURSOR_SHARING=SIMILAR  WITHOUT HISTOGRAM

Parent  
  |        
 Child    

— create histogram only on id1 with one bucket so that optimizer does not
know about the skew —

HR>exec dbms_stats.gather_table_stats(OWNNAME => ‘HR’,-
TABNAME => ‘TEST’,-
ESTIMATE_PERCENT =>null,-
METHOD_OPT => ‘FOR COLUMNS SIZE 1 ID1′);

— Set cursor_sharing = similar —

— Flush the shared pool

SYS>alter system set CURSOR_SHARING=’SIMILAR';
alter system flush shared_pool;
sho parameter CURSOR_SHARING

— Issue identical statements with different values of literals for the column on which histogram is not there (id1)

HR>conn hr/hr
select count(*) from test where id1=1;
select count(*) from test where id1=2;
select count(*) from test where id1=3;

— Check that the only 1 parent cursor has been created and literal has been replaced by bind variable. ( 1 record in v$SQLAREA)

.There is only one child  cursor (version_count=1) since the optimizer does not know about skew in data

SYS>col sql_text for a30 word_wrapped

         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE    PLAN_HASH_VALUE

—————————— ————- ————- ———-  —————

select count(*) from test      07tpk6bm7j4qm             1   3866661587   3507950989
where id1=:”SYS_B_0″

— Note there is only one child cursor created i.e. same execution plan will be used for different values of the bind variable

SYS>col child_number for 99
         SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
                       PLAN_HASH_VALUE
         FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE  PLAN_HASH_VALUE

—————————— ————- ———- ———-  —————

select count(*) from test      07tpk6bm7j4qm          0   3866661587    3507950989
where id1=:”SYS_B_0″

CURSOR_SHARING=SIMILAR  WITH HISTOGRAM

               Parent

                   +
   +—- —+——–+
   |                |               |
  Child    Child    Child

— create histogram  on id2 with  4 buckets so that optimizer knows about  the skew in data —

HR>exec dbms_stats.gather_table_stats(OWNNAME => ‘HR’,-
TABNAME => ‘TEST’,-
ESTIMATE_PERCENT =>null,-
CASCADE => TRUE,-
METHOD_OPT => ‘FOR COLUMNS SIZE 4 ID2′); 

— Issue identical statements with different values of literals for the column  on which histogram is there (id2)

SYS>alter system flush shared_pool;

HR>conn hr/hr
select count(*) from test where id2=1;
select count(*) from test where id2=2;
select count(*) from test where id2=3;

— Check that the only 1 parent cursor has been created and literal has been replaced by bind variable. ( 1 record in v$SQLAREA)
.There are 3 child cursors (version_count=3)

SYS>col sql_text for a30 word_wrapped

         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE  PLAN_HASH_VALUE

—————————— ————- ————- ———-  ————–

select count(*) from test      3tcujqmqnqs8t             3   3981140249  2432738936
where id2=:”SYS_B_0″

— Note that 3 child cursors have been created as optimizer realizes that data is skewed and different execution plans will be more efficient for different values of the bind variable.
—  2 children have same execution plan (PLAN_HASH_VALUE)      (for id=2 and 3 (Full table scan )

SYS>col child_number for 99
    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,  
                   PLAN_HASH_VALUE
     FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE  PLAN_HASH_VALUE

—————————— ————- ———- ———-   ————–

select count(*) from test      3tcujqmqnqs8t          0   3981140249   2432738936
where id2=:”SYS_B_0″

select count(*) from test      3tcujqmqnqs8t          1   3981140249   2432738936
where id2=:”SYS_B_0″

select count(*) from test      3tcujqmqnqs8t          2 3981140249   1489241381
where id2=:”SYS_B_0″

Hence, it can be seen that setting CURSOR_SHARING=SIMILAR
– replaces literals with bind variables in otherwise identical sql statements

  • - Only one child cursor is created if optimizer does not know about skew in   data
  • - If optimizer is aware of the skew in data, Multiple child cursors are created   for each distinct value of the bind   variable even if they have the same   executiion plan.

Ideally we would like one child cursor to be created if execution plan is same for different values of the bind variable.

Setting CURSOR_SHARING=FORCE IN 11G does precisely this but only if the optimizer is

aware about the skew in the data. Let’s see:

CURSOR_SHARING=FORCE IN 11G WITHOUT HISTOGRAM

Parent  

     |        
 Child    
– Flush the shared pool and issue query using the column without histogram on
it so that optimizer is not aware of the skew.
SYS>alter system set CURSOR_SHARING=’FORCE';

          alter system flush shared_pool;

HR>conn hr/hr
select count(*) from test where id1=1;
select count(*) from test where id1=2;
select count(*) from test where id1=3;

— Note that only one parent cursor is created

One child cursor has been created (version_count=1)

SYS>col sql_text for a30 word_wrapped
         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
         FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE   PLAN_HASH_VALUE

—————————— ————- ————- ———-    ————–

select count(*) from test      07tpk6bm7j4qm             1   3866661587   3507950989
where id1=:”SYS_B_0″

— Note that 1 child cursor has been created

SYS>col child_number for 99
    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,
                   PLAN_HASH_VALUE
     FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE   PLAN_HASH_VALUE

—————————— ————- ———- ———-   —————

select count(*) from test      07tpk6bm7j4qm          0   3866661587    3507950989
where id1=:”SYS_B_0″

CURSOR_SHARING=FORCE IN 11G WITH HISTOGRAM
      Parent

           |

   +—+—-+
   |              |
  Child    Child

– Flush the shared pool and issue query using the column with histogram on it so that optimizer is aware of the skew.

SYS> alter system flush shared_pool;

HR>conn hr/hr
select count(*) from test where id2=1;
select count(*) from test where id2=2;
select count(*) from test where id2=3;

— Note that only one parent cursor is created

Two child cursors have been created (version_count=2)

SYS>col sql_text for a30 word_wrapped

         SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE   PLAN_HASH_VALUE
—————————— ————- ————- ———-   —————

select count(*) from test      3tcujqmqnqs8t             2   3981140249   2432738936
where id2=:”SYS_B_0″

— Note that 2 child cursors have been created and    each child has a distinct execution plan (PLAN_HASH_VALUE)

SYS>col child_number for 99
         SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE,  
                        PLAN_HASH_VALUE
         FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE  PLAN_HASH_VALUE

—————————— ————- ———- ———-  —————

select count(*) from test      3tcujqmqnqs8t          0  3981140249   2432738936
where id2=:”SYS_B_0″

select count(*) from test      3tcujqmqnqs8t          1  3981140249  1489241381
where id2=:”SYS_B_0″

Hence, setting CURSOR_SHARING=FORCE in 11g will use the same child cursor if   execution plan is same for different values of the bind variables which means  saving in memory in the shared pool and saving in the time for scanning the  hash chains in the library cache . This new feature of 11g is called ADAPTIVE CURSOR SHARING.

Note: The behaviour of CURSOR_SHARING=FORCE in 11g is different from 9i/10g. Earlier, it would peek the value of the bind variable during the first execution and decide on the eexcution plan. On subsequent execution of the same statement with different values of the bind variable, it would reuse the same plan irrespective of the skew in the data.

CURSOR_SHARING=FORCE IN 10G WITH/WITHOUT HISTOGRAM

Parent  

  |        

 Child    
  Let’s demonstrate this by simulating 10g optimizer by setting the parameter optimizer_geatures_enable to 10.2.0.0.

SYS> alter system set optimizer_features_enable=’10.2.0.3′;

— Flush the shared pool and issue query using the column with histogram on

it so that optimizer is aware of the skew.

SYS> alter system flush shared_pool;

HR>conn hr/hr
select count(*) from test where id2=1;
select count(*) from test where id2=2;
select count(*) from test where id2=3;

— Note that only one parent cursor is created

Only child cursor has been created (version_count=1)

SYS>col sql_text for a30 word_wrapped

    SELECT SQL_TEXT , SQL_ID, VERSION_COUNT, HASH_VALUE,PLAN_HASH_VALUE
FROM V$SQLAREA
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID        VERSION_COUNT HASH_VALUE PLAN_HASH_VALUE

—————————— ————- ————- ———- —————

select count(*) from test      3tcujqmqnqs8t             1 3981140249      2432738936

where id2=:”SYS_B_0″

— Note that 1 child cursor has been created

SYS>col child_number for 99

    SELECT SQL_TEXT, SQL_ID, CHILD_NUMBER CHILD#, HASH_VALUE, PLAN_HASH_VALUE
FROM V$SQL
WHERE LOWER(SQL_TEXT) LIKE ‘select count(*) from test%’
AND LOWER(SQL_TEXT) NOT LIKE ‘%HASH%';

SQL_TEXT                       SQL_ID            CHILD# HASH_VALUE PLAN_HASH_VALUE

—————————— ————- ———- ———- —————

select count(*) from test      3tcujqmqnqs8t          0 3981140249      2432738936

where id2=:”SYS_B_0″

– cleanup –

SYS>alter system set optimizer_features_enable=’11.2.0.1′;

         drop table hr.test purge;


CONCLUSION:

CURSOR_SHARING = EXACT

– Causes maximum memory usage in library cache as two cursors – one parent and one child cursor are created for each distinct value of the bind variable.

– Gives best performance as optimizer creates different execution plan for each value of the bind variable.

CURSOR_SHARING = SIMILAR
- Reduces memory usage in library cache as only one parent cursor is created .
- If data is not skewed or the optimizer is not aware of the skew, optimizer peeks at the value of the bind variable on the first execution of the statement and that plan is used for all the  values of the bind variable. Thus only one child cursor is created resulting in minimum memory usage by child cursors. In this case performance will be affected if there is skew in the data.
- If data is skewed and the optimizer is aware of the skew, multiple child cursor are created – one for each distinct value of the bind variable. In this case performance will be the best as optimizer creates different execution plan for each value of the bind variable. But in this case we will have multiple child cursors created for the same execution plan.
CURSOR_SHARING = FORCE IN 10g
- Causes minimum memory usage in library cache as only one parent cursor and only one child cursor are created .
- In this case performance will be affected if there is skew in the data.
CURSOR_SHARING = FORCE IN 11g (ADAPTIVE CURSOR SHARING)
- Reduces  memory usage in library cache as only one parent cursor and only one child cursor are created .
- If data is not skewed or the optimizer is not aware of the skew, optimizer peeks at the value of the bind variable on the first execution of the statement and that plan is used for all the  values of the bind variable. Thus only one child cursor is created resulting in minimum memory usage by child cursors. In this case performance will be affected if there is skew in the data. (same scenario as cursor_sharing=similar )
- If data is skewed and the optimizer is aware of the skew, multiple child cursor are created for different values of the bind variable – one for each distinct execution plan . In this case performance will be the best as optimizer creates different execution plans for different values of the bind variable. But in this case we will have only child cursor created for the same execution plan thereby resulting in optimum memory usage by child cursors.In my next post on Tuning Shared Pool , I will demonstrate how can we reduce hard parsing by- replacing literals with bind variables- setting cursor_sharing = similar

————————————————————————————
 
Related links:

TUNING RMAN PART-II

11g R2 RMAN, Tuning, Uncategorized
  In continuation with my earlier post Tuning RMAN Part-I,, in this post, I will demonstrate the usage of asynchronous i/o to improve the performance of RMAN operations.
RMAN can perform i/o in two ways : Synchronous and Asynchr nous.
We are aware that synchronous i/o is a bottleneck and we should always aim at having asynchronous i/o for better performance. Some platforms support asynchronous by default while others don’t. On the platforms which do not support asynchronous i/o, we can simulate asynch i/o by setting parameter dbwr_io_slaves.
If i/o is performed asynchronously, we will have records in v$backup_async_io
If i/o is performed synchronously, we will have records in v$backup_sync_io
 Let’s have a demonstration :
The parameter disk_asynch_io decides whether asynchronous I/O is enabled at O/S level. By default its value is true if OS supported asynch i/o.
Currently my database is runing on windows server 2008 and it supports asynchronous i/o.
Let’s verify by looking at the default value of  disk_asynch_io parameter.
SYS>sho parameter disk_asynch_io;NAME
TYPE        VALUE
———————————————– ———–
disk_asynch_io     boolean     TRUE
When asynchronous i/o is performed at the O/S level, the buffers needed by RMAN are allocated from PGA. Let’s verify this by checking the session pga memory as a backup progresses.
– Take backup using multiplexing level = 4 – this needs to allocate 16m buffers from pga
–
– While backup is going on , issue the next query repeatedly which monitors pga usage by rman session. Note that pga consumption increases as backup progresses.
rman>RUN
 {
     allocate channel c1 device type disk;
   backup datafile 1,2,3,4 filesperset 4 ;
 }
– Query the pga memory usage by rman  session .
– Note that pga consumption increases as backup progresses.
– ALso note that size of buffers allocated = 18 MB (41-23) which is slightly > 16 MB
SQL> col name for a30
     set line 500
     select s.sid, n.name , s.value/1024/1024 session_pga_mb
      from  v$statname n, v$sesstat s
      where s.sid = (select sess.SID
                                  FROM V$PROCESS p, V$SESSION sess
                                 WHERE p.ADDR = sess.PADDR
                                       AND CLIENT_INFO LIKE ‘%rman%’)
        and n.name = ‘session pga memory’
        and s.statistic# = n.statistic#;
 

SID NAME    SESSION_PGA_MB
—————————————- ————–

14 session pga memory     23.1208076
SQL> /

SID NAME  SESSION_PGA_MB
—————————————- ————–

14 session pga memory      41.1833076
  If OS does not support asynchronous I/O, we can simulate by setting parameter dbwr_io_slaves to a non zero value.
4 slave processes will be allocated irrespective of the value of the parameter dbwr_io_Slaves. IN this case, buffers for RMAN will be allocated from large pool. 
If large pool is sized to a value lower than the size of the buffers required, RMAN will switch to synchronous I/O and write a message to the alert log.
Let’s verify this.
– Check current values of sga_target/memory_target which will be used later to return to the original state.
- Note the values
SQL>sho parameter sga_target
          sho parameter memory_target 
– Disable AMM/ASMM
– Disable asynchronous I/O at OS level –
– SImulate async IO by configuring slave processes –
– Size large pool to a size smaller than 16M (say 8M)
SQL>alter system set “_memory_imm_mode_without_autosga”=false scope=both;
            alter system set memory_target = 0;
           alter system set sga_target = 0;

alter system set disk_asynch_io=false scope=spfile;
alter system set dbwr_io_slaves=4 scope=spfile;
alter system set large_pool_size=8m scope = spfile;
startup force;
– Check that the parameters have been set to the specified values
               

SQL> sho parameter sga_targetsho parameter memory_target

sho parameter disk_asynch_io

sho parameter dbwr_io_slaves

sho parameter large_pool_size

NAME TYPE        VALUE
———————————————– ———–
sga_target big integer 0
memory_target big integer 0
disk_asynch_io boolean     FALSE
dbwr_io_slaves integer     4
large_pool_size big integer 8M
– Take backup using multiplexing level = 4
– this needs to allocate 16m buffers from large pool –
rman>backup datafile 2,3,4,5 filesperset 4;
– Check that i/o is not asynchronous –

SQL>select filename, buffer_size, buffer_count, type, status from v$backup_async_io
where status=’IN PROGRESS';

no rows selected
– check that synchronous i/o is taking place although async i/o was simulated –
SQL> col filename for a30 word_wrapped
               select FILENAME, BUFFER_SIZE, BUFFER_COUNT, TYPE,
STATUS from v$backup_sync_io

where status = ‘IN PROGRESS';

FILENAME BUFFER_SIZE BUFFER_COUNT TYPE      STATUS
—————————————– ———— ——— ———–
C:\APP\ADMINISTRATOR\ORADATA\O   1048576            4    INPUT     IN PROGRESS
RCL\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS
RCL\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS
RCL\EXAMPLE01.DBF
C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS
RCL\USERS01.DBF
– check that alert log indicates that sync i/o will be used –
                                                
ORA-04031: unable to allocate 1056812 bytes of shared memory (“large pool”,”unknown
object”,”large  pool”,”KSFQ Buffers”)
ksfqxcre: failure to allocate shared memory means sync I/O will be used
Now let us increase the size of large pool to a value > 16M (say 20M) and the issue the
same backup. This time asynchronous i/o should be used. we will verify this by checking
that records appear in  v$backup_async_io
SQL>alter system set large_pool_size=20m scope=spfile;
startup force;

sho parameter large_pool_size;

NAME   TYPE        VALUE
———————————————– ————-
large_pool_size  big integer 20M
– Take backup using  multiplexing level = 4
- this needs to allocate 16m buffers from large   pool –
rman>backup datafile 2,3,4,5 filesperset 4;
– Check that i/o is not asynchronous –
SQL> select filename, buffer_size, buffer_count, type, status from v$backup_async_io
              where status=’IN PROGRESS';
FILENAME  BUFFER_SIZE BUFFER_COUNT TYPE      STATUS
—————————————– ———— ——— ———–
C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS
RCL\SYSAUX01.DBF
C:\APP\ADMINISTRATOR\ORADATA\O  1048576            4  INPUT     IN PROGRESS
RCL\UNDOTBS01.DBF
C:\APP\ADMINISTRATOR\FLASH_REC  1048576            4  OUTPUT    IN PROGRESS
OVERY_AREA\ORCL\BACKUPSET\2012
_09_14\O1_MF_NNNDF_TAG20120914
T100407_855DY50Z_.BKP
Conclusion:
- The parameter disk_asynch_io decides whether asynchronous I/O is enabled at O/S level. By default its value is true if OS supported asynch i/o.
- When asynchronous i/o is performed at the O/S level, the buffers needed by RMAN are allocated from PGA.
- If OS does not support asynchronous I/O, we can simulate it by setting parameter dbwr_io_slaves to a non zero value. 4 slave processes will be allocated irrespective of the value of the parameter dbwr_io_Slaves.
- In the case  of simulated asynchronous i/o, , buffers for RMAN will be allocated from large pool. 
  If large pool is sized to a value more than the required buffers, asynchronous i/o will be performed.
  If large pool is sized to a value lower than the size of the buffers required, RMAN will switch to synchronous I/O and write a message to the alert log.
-  The no. of buffers allocated by RMAN per file depends upon the level of multiplexing.
Multiplexing level        Allocation rule
  
   Level <= 4           1 MB buffers are allocated so that the total buffer size for all input files is 16 MB.
   4 < Level <= 8       512 KB are allocated so that the total buffer size for all files is less than 16 MB.
   Level > 8            RMAN allocates four 128 KB disk buffers per channel for each file, so that the total size is 512 
                                 KB per channel for each file.
-
- We can calculate the size of the buffers required by RMAN as follows:
    Multiply the total bytes for each data file by the number of data files that are being concurrently accessed by the channel, and then multiply this number by the number of channels.
Assume that you use one channel to back up four data files, and use the settings that are shown above. In this case, multiply as follows to obtain the total size of the buffers that are allocated for the backup:
4 MB per data file * 1 channel  *  4 data files per channel = 16 MB
- If asynchronous i/o is supported by the O/s, we should size PGA to a value more than the size of the buffers required by RMAN.
 
- If asynchronous i/o is not supported by O/S and we are simulating it, we should size large pool to a value more than the size of the buffers required by RMAN.
 
— CLEANUP —
SQL>drop tablespace rman1 including contents and datafiles;
    drop tablespace rman2 including contents and datafiles;
    drop tablespace rman3 including contents and datafiles;
    drop tablespace rman4 including contents and datafiles;
    alter system set disk_asynch_io=true scope=spfile;
    alter system set memory_target = <> scope=spfile;
    alter system set sga_target = <> scope=spfile;
    startup force;
 
————————————————————————————————

Related links:

Home

                                                               —-

 

SINGLE TABLE INDEX CLUSTERS DEMYSTIFIED

Database, Uncategorized

 

Clusters can be employed to physically co-locate records for specified key/keys on disk. Based on access mechanism, there can be two types of clusters :- Index or B-tree* clusters : Data is accessed using index.- Hash clusters : Data is accessed using hashing algorithm.We can store single/multiple tables in a cluster.   

                  

In this post, I will explain and demonstrate the use of single table index cluster tables .
 
What is a single table index cluster table?
 
In a regular heap table,, the rows for a key value are scattered across multiple blocks and hence multiple blocks have to be visited to get all the rows for a key value. Here is the pictorial representation of the data in a heap organized table with an index. Note that one data block can contain rows with different key values.
 
    Heap Table with Index         
  
Index:
 
    1
    1
    1
    1
    2
    2
    2
  3
  3
  3
  3
   4
   4
   4
   4
     
Data Blocks :
1
1
2
3
4
  
2
4
3
1
3
           
1
2
3
4
4
               
 
Let’s compare it with the way data is stored in single table index cluster table.  Note that all the rows with a key value are placed together in one block  so that they can be retrieved with minimum no. of block visits and  clustering factor of the index approaches the no. of blocks in the table which is desirable.
 
 single table index cluster table
 
  Index
 
    1
    1
    1
    1
    2
    2
    2
  3
  3
  3
  3
   4
   4
   4
   4
     
Data Blocks :
1
1
1
1
 
  
2
2
2
 
 
           
3
3
3
3
 
               
4
4
4
4
 
 
 
How to create a single table index cluster table?
 
Here are steps  to create a single table index cluster table :
 
- – Create a cluster 
SQL> drop cluster my_cluster including tables;
 create cluster my_cluster            (id number(2) )          size 1024;

Here, we have created an index cluster. The cluster key column for this cluster is id. The column in table in this cluster does have to be called ID, but it must be NUMBER(2), to match this definition. Also we have specified a SIZE 1024 option which means that we expect about 1,024 bytes of data to be associated with each cluster key value. Oracle will use it to compute the maximum number of cluster keys that could fit per block.

We also need to index the cluster before we can put data in it. The cluster index takes a cluster key value and returns the block address of the block containing that key. Eeach cluster key value points to a single block in the cluster itself. So, when we ask for the data for id = 1, Oracle will read the cluster key, determine the block address for that, and then read the data.
– Let’s create the cluster key index:

SQL>create index mycluster_idx on cluster my_cluster;

The index can have all of the storage parameters of an index and can also be stored in another tablespace. It is just like a regular index, and may also be on multiple columns; Unlike regular indexes,  it can include an entry for a completely null value. Note that we can not specify a list of columns in this CREATE INDEX statement as it is derived from the CLUSTER definition itself

– Let’s create a table in the cluster:

SQL> drop table t1 purge;
          create table t1 (id number(2), data char(500))
          cluster my_cluster;

Note that the only difference from a normal table is that we have used the CLUSTER keyword and specified the column of the base table that will map to the cluster key. As the cluster is the segment here, this table can never have segment attributes such as TABLESPACE, PCTFREE, and so on. These attributes belong to the cluster segment, and the table inherits them from the cluster. 


– Let’s populate our table:

Given that we have an 8KB block size, Oracle will fit up to seven cluster keys (but maybe less/more if the data is smaller/larger than expected) per database block. This is, the data for ids 1, 2, 3, 4, 5, 6 and 7 would tend to go onto one block, and as soon as we insert id 8, a new block will be used. That does not mean that the data is stored in a sorted manner; it just means that if we inserted the ids in that order, they would naturally tend to be placed together. If we inserted the ids in the order 1, 8, 3, 3, 6, 5 and then 7, the last id, 7 would be located on the newly added block. As we will see ,  both the size of the data and the order in which the data is inserted will affect the number of keys which can be stored per block.

– Let’s insert 8 records with id’s ranging from 1 to 8 
SQL>Begin
            for i in 1..8 loop
              insert into t1 values (i, ‘x’);
           end loop;
           commit;
          end;
          /

– Let’s check the blocks the records have gone to :

- Note that record for id = 8 has gone to a block different from the earlier one’s as we had specified the expected size of each row = 1024 = 1k

– block size = 8k
Considering some space needed for the block header,  oracle expects 7 rows and hence 7 key values to fit in one block.  Hence, row corresponding to the 8th key value is placed in the next block (93322). 

 

SQL>select id, dbms_rowid.rowid_block_number(t1.rowid) t1_block 
           from t1 ;

        ID   T1_BLOCK

———- ———-

         1      93321

         2      93321

         3      93321

         4      93321

         5      93321

         6      93321

         7      93321

         8      93322  


 93321             93322

1, ‘x’

2,’x’

3, ‘x’

4, ‘x’

5, ‘x’

6, ‘x’

7, ‘x’

8,’x‘
  
– Let’s insert another record with id = 1
-
- Note that that record again goes the same block as earlier (93321). The reason is that there is space available in block 93321 since actual row size = 501 (number(2) + char(500)) is less than expected size (1024).  

 

SQL>  
insert into t1 values (1, ‘x’);
           commit;  
            select id, dbms_rowid.rowid_block_number(t1.rowid) t1_block
             from t1
             where id=1;

            ID   T1_BLOCK

       ———- ———-

            1     93321

            1      93321

   93321     93322

1, ‘x’

 

2,’x’

 

3, ‘x’

 

4, ‘x’

 

5, ‘x’

 

6, ‘x’

 

7, ‘x’

 

1, ‘x’
8,’x’

 

– Let’s insert another record for id = 8

- Note than this record goes to block 93322 even though there is space available in block 93321 because rows with same key value will be placed in the same block. 

SQL> insert into t1 values (8, ‘x’);

           commit;

           select id, dbms_rowid.rowid_block_number(t1.rowid) t1_block    from t1    where id=8;

        ID   T1_BLOCK

———- ———-

         8      93322

         8      93322

   93321     93322

1, ‘x’

 

2,’x’

 

3, ‘x’

 

4, ‘x’

 

5, ‘x’

 

6, ‘x’

 

7, ‘x’

 

1, ‘x’
8,’x’

 

8,’x’

 

– As per the actual size of the row, no. of rows that can fit in one block are slightly less than 16.35 ( (8*1024)/501) i.e. 15 or may be even lesser taking into account the space for block header.

– There are already 8 rows in block 93321. Let’s insert 8 more rows with id = 1 to 7.

– Note that 5 more  rows are inserted in the first block. After the  first block is fully occupied rest of the 2 rows are placed in the second block i.e. 93322. Now second block has rows with id’s = 6,7 and 8. 

SQL>Begin

            for i in 1..8 loop

              insert into t1 values (i, ‘x’);
           end loop;
           commit;
          end;
          /

       select id, dbms_rowid.rowid_block_number(t1.rowid) t1_block              from t1;

      ID   T1_BLOCK

———- ———-

         1      93321

         2      93321

         3      93321

         4      93321

         5      93321

         6      93321

         7      93321

         1      93321

         1      93321

         2      93321

         3      93321

         4      93321

         5      93321

         8      93322

         8      93322

         6      93322

         7      93322

         8      93322 

    93321     93322

1, ‘x’

 

2,’x’

 

3, ‘x’

 

4, ‘x’

 

5, ‘x’

 

6, ‘x’

 

7, ‘x’

 

1, ‘x’

 

1,’x’

 

2,’x’

 

3,’x’

 

4,’x’

 

5,’x’
8,’x’

 

8,’x’

 

6,’x’

 

7,’x’

 

8,’x’
– Let’s again insert records with id’s 1 to 8.
– Note that rows with 3 id’s i.e. 6,7 and 8 are already in block 93322. Hence rows for 4 more id’s (1,2,3and 4) can go this block. 5th id (5) again goes to the next block. 

 

S
QL>
Begin
            for i in 1..8 loop
              insert into t1 values (i, ‘x’);
           end loop;
           commit;
          end;
          /
       select distinct id, dbms_rowid.rowid_block_number(t1.rowid) t1_block, count(*) cnt
             from t1
            group by id, dbms_rowid.rowid_block_number(t1.rowid)
            order by dbms_rowid.rowid_block_number(t1.rowid), id;   

        ID   T1_BLOCK        CNT

———- ———- ———-

         1      93322          1

         2      93322          1

         3      93322          1

         4      93322          1

         6      93322          2

         7      93322          2

         8      93322          4

         5      93323          1

 93321    93322   93323

1, ‘x’

2,’x’

3, ‘x’

4, ‘x’

5, ‘x’

6, ‘x’

7, ‘x’

1, ‘x’

1,’x’

2,’x’

3,’x’

4,’x’

5,’x’

 

8,’x’

8,’x’

6,’x’

7,’x’

8,’x’

1, ‘x’

2,’x’

3, ‘x’

4, ‘x

6, ‘x’

7, ‘x’

 

8,’x’

 

 5,’x’
-
Hence we could control the no. of distinct key values for which records are placed in a block. But notice that records for a key value are scattered across variouw blocks. How to cluster together the rows for a key value?
There are two ways of doing it :
– Insert data presorted on the cluster key to the table
– Specify size = block size in the create cluster statement

  Let’s demonstrate both the methods:
Insert data presorted on the cluster key to the table
- Let’s recreate the table in the cluster:
SQL> drop table t1 purge;         
 create table t1 ( id number(2), data char(500) )           cluster my_cluster( id );

– Let’s insert presorted data

SQL>Begin

          for i in 1..8 loop

             for j in 1..13 loop

               insert into t1 values (i, ‘x’);

             end loop;

           end loop;

           commit;

         end;

          /
         select distinct id, dbms_rowid.rowid_block_number(t1.rowid) t1_block, count(*) cnt

         from t1

         group by id, dbms_rowid.rowid_block_number(t1.rowid)

      ID   T1_BLOCK        CNT

——– ———- ———-     

       1      94561         13       

       2      94562         13

       3      94563         13

       4      94564         13

       5      94565         13

       6      94566         13

       7      94567         13

       8      94576         13 

 

    94561      94562    94563       94564        94565    94566      94567       94576                                                    

1, ‘x’
1,’x’
1, ‘x’
1, ‘x’
1, ‘x’
1, ‘x’
1, ‘x’
1, ‘x’
1,’x’
1, ‘x’
1, ‘x’
1, ‘x’
1, ‘x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
2,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
3,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
4,’x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
5, ‘x’
6,’x’
6,’x’
6,’x’
6,’x’
6,’x’
6,’x’
6,’x’
6,’x’
6,’x’6,’x’
6,’x’
6,’x’
6,’x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
7, ‘x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’
8,’x’

It can be seen that if data presorted by the cluster key is added to the table, all the rows for a key value will continue to occupy the same block unless a row with a different key value arrives and the block is not yet full. . Hence to fetch all rows for a key, the no. block visits will be smaller if presorted data is insertde into the table.

- Let’s create an index on table t1 and check its clustering factor

 
– Note that the index a low (desirable) clustering factor = 8 which is same as the no. of blocks in the table

SQL> create index t1_idx on t1(id);

          select index_name, clustering_factor from user_indexes where index_name=’T1_IDX';

INDEX_NAME                     CLUSTERING_FACTOR
—————————— —————–
T1_IDX                                         8

Specify size = block size in the create cluster statement

Suppose we want a block to contain rows with one id only, all we need to do is to specify size equal to the blocksize in the create cluster statement. This effectively means that we are telling oracle we want it to reserve a complete block for  each distinct key value . Hence,  when a record for a different key value is inserted, it goes to the next block. 

– Let’s  recreate our cluster with size = blocksize = 8k;

– Create a cluster and index it 

SQL>drop cluster my_cluster including tables;       
   create cluster my_cluster            ( id number(2) )          size 8192;
    create index my_cluster_idx         on cluster my_cluster;
– Create and populate a table in the cluster
SQL>drop table t1 purge;       
           create table t1 ( id number(2), data char(500) )           cluster my_cluster( id );         
     Begin
            for i in 1..8 loop
                 insert into t1 values (i, ‘x’);
             end loop;
             commit;
            end;
              /  
 
– Let’s check the blocks the records have gone to :
– Note that record for each distinct id has gone to a different block .
 SQL>select id, dbms_rowid.rowid_block_number(t1.rowid) t1_block            from t1 ;

          ID   T1_BLOCK

———- ———-
 1     94561
2      94562
3      94563
4      94564
5      94565
6      94566
7      94567
8      94576

 94561       94562      94563      94564      94565  94566     94567       94576                      
1, ‘x’
2,’x’
3,’x’
4,’x’
5, ‘x’
6,’x’
7, ‘x’
8,’x’
– Now let’s again insert records for id’s 1 to 8 and verify
that row of an id goes to the block already containing  the row with same id. 
 
SQL>Begin
          for i in 1..8 loop
               insert into t1 values (i, ‘x’);
           end loop;
           commit;
        end;
          /         select distinct id, dbms_rowid.rowid_block_number(t1.rowid) t1_block, count(*) cnt
            from t1
            group by id, dbms_rowid.rowid_block_number(t1.rowid)
            order by dbms_rowid.rowid_block_number(t1.rowid), id;
        ID   T1_BLOCK        CNT
———- ———- ———-
         1      94561          2
         2      94562          2
         3      94563          2
         4      94564          2
         5      94565          2
         6      94566          2
         7      94567          2
         8      94576          2
   94561  94562   94563  94564   94565  94566     94567   94576                      
1, ‘x’
1, ‘x’
2,’x’
2, ‘x’
3,’x’
3, ‘x’
4,’x’
4, ‘x’
5, ‘x’
5, ‘x’
6,’x’
6, ‘x’
7, ‘x’
7, ‘x’
8,’x’
8, ‘x’ 
 

– Let’s create an index on table t1 and check its clustering factor

– Note that the index a low (desirable) clustering factor = 8 which is same as the no. of blocks in the table

SQL> create index t1_idx on t1(id);

           select index_name, clustering_factor from user_indexes where index_name=’T1_IDX';

INDEX_NAME                     CLUSTERING_FACTOR

—————————— —————–
T1_IDX                                         8

Hence, it can be seen that  SIZE parameter is the most important parameter for a cluster. It controls the maximum number of cluster keys per block. It influences  the space utilization of the cluster. If we set the size too high, we’ll have very few keys per block and will use more space then we need. If we set the size too low, and we’ll have data for a key value spread across multiple blocks  which defeats the very purpose of the cluster to store all of the data together on a single block. 

Summary:

-  Size clause of create cluster statement is used to compute the maximum number of cluster keys that could fit per block.

- In the create cluster statement, if size < block size, records with different key values may be placed in the same block. 

- A block will contain  the records for one key value only  provided 

    . data presorted by the cluster key is added to the table OR

    . Size  = blocksize is specified in the create cluster statement 

- When any of the above options are used, the index on the cluster key column in the table has a low (desirable) clustering factor approaching the no. of blocks in the table.

– Cluster index has one entry per cluster key and not for each row. Therefore, the index is smaller and less costly to access for finding multiple rows.

Note: 

- An index cluster has negative performance impacts on DML performance, particularly insert statements. Since data has to be put in the right place, it takes longer to insert data.

- Neither the cluster nor the tables in  the cluster can be partitioned.

- Tables in cluster cannot be truncated. You must delete rows from the tables in the cluster.

- Index clusters are also called B*Tree clusters.

In my post Clustering Factor Demystified : Part – III, I have demonstrated the use of single table index and hash clusters to improve the clustering factor of an unorganized table.

I hope you found this post useful.  Thanx for your time !!References:http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm

———————————————————————————————————–

Related links :

Home

Database Index 
Tuning Index
SingleTable hash Clusters
Clustering Factor Demystified Part – I
Clustering Factor Demystified Part – II
Clustering FactorDemystified Part – III

——————————————————————————————————————–

 

                                             ———————-