Monthly Archives: March 2013

AUTOMATIC DEGREE OF PARALLELISM (DOP) – PART – II

Database, Tuning, Uncategorized, , , , , , ,
    

 In my last article , I discussed Automatic DOP computation in Oracle 11g Release 2.  In this  article, I will demonstrate the oracle behaviour for various values of PARALLEL_DEGREE_POLICY i.e. Manual, Limited and Auto.
—–      PARALLEL_DEGREE_POLICY= MANUAL —————-
SQL>conn / as sysdba
          alter system set parallel_adaptive_multi_user=false;
          conn sh/sh
         alter session set parallel_degree_policy=manual;
         alter session set parallel_min_time_threshold=auto;
— CREATE TABLES —
— P5 – DICTIONARY DOP = 5
– PDEF – DICTIONARY DOP = DEFAULT
SQL>drop table p5 purge;
          create table p5 parallel 5
         as select * from sales
        where rownum < 5000;
SQL>drop table pdef purge;
   
         create table pdef parallel
        as select * from sales
        where rownum < 5000;
SQL> select table_name,degree
from user_tables
          where table_name in (‘P5′, ‘PDEF’);
TABLE_NAME                    DEGREE
———————————————————————-
P5                                             5
PDEF                                DEFAULT
– CHECK THAT PARALLELISM USED = DICTIONARY DOP IN BOTH THE TABLES –
SQL> select count(*) from p5;
 
          select * from v$pq_sesstat
          where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      5            0
SQL> select  count(*) from pdef;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      2            0
– PRIOR TO 11G R2, TO DECIDE PARALLELISM, THE DBA COULD SPECIFY DIFFERENT
– DICTIONARY DOP’S FOR DIFFERENT OBJECTS
– TO OVERRIDE THE DICTIONARY DOP, HE COULD USE HINTS AT OBJECT LEVEL –
SQL> select /*+ parallel (p5 8) */ count(*) from p5;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      8            0
SQL> select /*+ parallel (pdef 8) */ count(*) from pdef;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      8            0
—–      PARALLEL_DEGREE_POLICY= LIMITED —————-
SQL> alter session set parallel_degree_policy=limited;
– CHECK THAT DOP IS NOT COMPUTED FOR TABLES WITH NON DEFAULT –
– DICTIONARY DOP – FOR TABLE P5, DOP USED = DICTIONARY DOP = 5
SQL> select count(*) from p5;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                    5            0
– CHECK THAT DOP IS  COMPUTED FOR TABLES WITH DEFAULT –
– DICTIONARY DOP – FOR TABLE PDEF, DOP IS COMPUTED TO BE 0 I.E. PARALLELIZATION
– IS NOT REQUIRED CONSIDERING THE SMALL SIZE OF THE OBJECT
SQL> select  count(*) from pdef;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
—–      PARALLEL_DEGREE_POLICY= AUTO —————-
SQL> alter session set parallel_degree_policy=auto;
– EXECUTE QUERY ON P5
– NOTE THAT DOP USED = 0 I.E. QUERY IS EXECUTED SERIALLY
– BECAUSE WITH CURRENT SIZE OF THE TABLE, SERIAL EXECUTION
– CAN EXECUTE THE STATEMENT WITHIN THE TIME SPECIFIED BY
– PARALLEL_MIN_TIME_THRESHOLD

SQL> select count(*) from p5;

 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                    LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
– RECREATE THE TABLE WITH MORE ROWS SO THAT PARALLELIZATION
– IS NEEDED TO EXECUTE THE QUERY
SQL>drop table p5 purge;
    create table p5 parallel 5
    as select * from sh.sales;
– REEXECUTE THE QUERY AND CHECK THAT PARALLELIZATION
– IS USED AND DOP USED IS NOT 5 (DICTIONARY DOP) I.E. AUTOMATIC
– DOP IS COMPUTED EVEN IN CASE OF TABLE WHERE DICTIONARY DOP IS
– NOT DEFAULT
     select count(*) from p5;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      2            0
– MODIFY
PARALLEL_MIN_TIME_THRESHOLD TO 20 SECS –
SQL>alter session set parallel_min_time_threshold=20;
– EXECUTE THE SAME QUERY AGAIN. CHECK THAT THE QUERY
– IS EXECUTED SERIALLY AS THE TIME REQUIRED TO EXECUTE
– IT SERIALLY IS LESS THAN 20 SECS AND HENCE PARALLELIZATION
– IS NEEDED

SQL>select count(*) from p5;

 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
SQL>alter session set parallel_min_time_threshold=auto;
– DOP IS COMPUTED COMPUTED FOR THOSE TABLES ALSO FOR WHICH
– DICTIONARY DOP IS DEFAULT. NOTE THAT THE FOLLOWING STATEMENT
– EEXCUTES SERIALLY AS DUE TO SMALL SIZE OF THE TABLE, PARALLELIZATION
– IS NOT REQUIRED
SQL>select  count(*) from pdef;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height’;
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
– WE CAN OVERRIDE AUTOMATIC COMPUTATION OF DOP BY
– GIVING OBJECT LEVEL HINT
SQL>select /*+ parallel (p5 8) */ count(*) from p5;
 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      8            0
– IF TABLE SIZE IS SO SMALL THAT PARALLELISM IS NOT REQUIRED
– EVEN SPECIFIED DOP IN THE HINT IS OVERRIDDEN. IF WE EXECUTE
– QUERY ON PDEF WHOSE SIZE IS SMALL, THE QUERY EXECUTES SERIALLY
– EVEN THOUGH WE HAVE SPECIFIED DOP = 8

SQL>select /*+ parallel (pdef 8) */ count(*) from pdef;

 
     select * from v$pq_sesstat
     where statistic = ‘Allocation Height';
STATISTIC                     LAST_QUERY SESSION_TOTAL
—————————————- ————-
Allocation Height                      0            0
References:
http://uhesse.com/2011/10/12/auto-dop-differences-of-parallel_degree_policyautolimited/
http://uhesse.com/2009/11/24/automatic-dop-in-11gr2/
http://www.rittmanmead.com/2010/01/in-memory-parallel-execution-in-oracle-database-11gr2/
http://kerryosborne.oracle-guy.com/2014/01/does-parallel_degree_limit-work-with-parallel_degree_policymanual/
 http://www.oracle.com/technetwork/articles/datawarehouse/twp-parallel-execution-fundamentals-133639.pdf
Returning The Actual DOP When Using Oracle’s Auto DOP
———————————————————————————————

Related links :

Home

Database Index
Automatic Degree Of Paralellism – Part-I
Oracle 11g : Automatic DOP – Parallel Threshold
Oracle 11g :  Parallel Statement Queueing
Parallel_Adaptive_Multi_User
Parallel_Min_Percent
Parallel_Min_Servers

                                                                              ——————

AUTOMATIC DEGREE OF PARALLELISM (DOP) – PART – I

Database, Tuning, Uncategorized, , , , , , , , ,
In a serial – non-parallel – execution environment, a single process or thread undertakes  the operations required to process your SQL statement and each action must complete before  the succeeding action can commence.Parallel SQL allows a SQL statement to be processed by multiple threads or processes simultaneously.Oracle supports parallel processing for a wide range of operations, including queries, DDL and DML:
  •  Queries that involve table or index range scans.
  •  Bulk insert, update or delete operations.
  •  Table and index creation.
In earlier versions of the Oracle Database, we had to determine the DOP more or less  manually, either with a parallel hint or by setting a parallel degree with alter table. 
There was an automatic computation of the DOP available for the objects with dictionary DOP of default, derived from the simple formula:
 CPU_COUNT * PARALLEL_THREADS_PER_CPU. 
If there were insufficient parallel servers to satisfy the requested DOP, one of three things could occur:•
  • The SQL would be run at a reduced DOP (be downgraded)
  • The SQL would run in serial mode (be serialized)
  • If PARALLEL_MIN_PERCENT was specified and less than the nominated percentage of the DOP was achievable, then the the SQL statement might terminate with “ORA-12827: insufficient parallel query slaves available”.
Oracle 11g release 2 introduced Automatic computation of DOP to parallelize
SQL. Automatic DOP whichis – as the name says – a way of having Oracle determine the degree of parallelism depending on the nature of the operations to be performed and the sizes of the objects involved. The decision is made based on various initialization parameter settings.
1. PARALLEL_DEGREE_POLICY. It can have 3 values : MANUAL, LIMITED and AUTO.
  • MANUAL – This is the default. Disables Auto DOP, statement queuing and in-memoryparallel execution. It reverts the behavior of parallel execution to what it was previous to Oracle Database 11g, Release 2 (11.2)..
  • LIMITED- will only assign a system computed  degree of parallelism (DOP) for tables decorated with a parallel degree of
  • DEFAULT
  1. Tables and indexes that have a specific DOP specified use that specified DOP.   
  2.  Parallel statement queuing and in-memory parallel execution are disabled.
  3. The optimizer computes the optimal degree-of-parallelism value based on estimated  execution statistics..
  • AUTO
  1. will consider to assign a system computed DOP to all tables
  2. Enables parallel statement queuing :If the requested or required DOP is not possible because parallel servers are busy, then Oracle will defer statement execution rather than downgrading or  serializing the SQL until enough parallel slaves become available.
  3. Enables in-memory parallel execution.

2. PARALLEL_MIN_TIME_THRESHOLD : Oracle 11gR2 will ascertain if the query’s estimated execution time is likely to run longer than the acceptable value (in seconds) for PARALLEL_MIN_TIME_THRESHOLD and, if sufficient resources for parallel execution exist right now, it will allow the query to execute; otherwise, it will delay its execution until sufficient resources exist. This helps prevent a single parallel query from consuming excessive resources at the cost of other non-parallelizable operations.The default of this parameter is 10 seconds.

3. PARALLEL_ADAPTIVE_MULTI_USER : If it is set to TRUE, then Oracle will adjust the degree of parallel based on the overall load on the system. When the system is more heavily loaded, then the degree of parallelism will be reduced.

4. If PARALLEL_IO_CAP is set to TRUE in 11g or higher, then Oracle will limit the Degree of  Parallelism to that which the IO subsystem can support. The IO subsystem limits can be calculated by using the procedure DBMS_RESOURCE_MANAGER.CALIBRATE_IO.

5. PARALLEL_MAX_SERVERS : To avoid an arbitrary number of parallel processes to be running on a system, which may overload that system, the parameter parallel_max_servers provides a hard upper boundary. Regardless of any other setting, the degree of parallelism cannot exceed that which can be supported by PARALLEL_MAX_SERVERS. For most SQL statements, the number of servers required will be twice the Degree of Parallelism.

6. PARALLEL_DEGREE_LIMIT : It is the maximum DOP that can be used. It can take various values :

  •  CPU (Default) : the total CPU count of the system
  •  IO : the limit depends on the I/O load on the database host
  •  any number greater than  0.

Note that even if you increase the parameter value, the automatically computed degree of parallelism may not increase, because the database may determine that the computed degree of parallelism is sufficient.The DOP that we run the statement with is the minimum value of the computed DOP (or ideal DOP) and that parallel_degree_limit parameter.

• A degree of parallelism can be specified at the table or index level by using the PARALLEL clause of CREATE TABLE, CREATE INDEX, ALTER TABLE or ALTER INDEX.

• The PARALLEL hint can be used to specify the degree of parallelism for a specific table within a query.

In my next article in this series , I will practically demonstrate oracle behaviour for various values of the parameter PARALLEL_DEGREE_POLICY i.e. MANUAL, LIMITED AND AUTO

References:
http://searchitchannel.techtarget.com/feature/Using-parallel-SQL-to-improve-Oracle-database-performance
http://docs.oracle.com/cd/E11882_01/server.112/e25523/parallel002.htm

http://kerryosborne.oracle-guy.com/2014/01/does-parallel_degree_limit-work-with-parallel_degree_policymanual/
http://www.databasejournal.com/features/oracle/oracle-parallel-processing-new-and-improved.html
http://www.pythian.com/blog/secrets-of-oracles-automatic-degree-of-parallelism/
http://hemantoracledba.blogspot.in/2015/02/parallel-execution-1-parallel-hint-and.html?utm_source=feedburner&utm_medium=email&utm_campaign=Feed:+blogspot/JcnHK+(Hemant%27s+Oracle+DBA+Blog)
https://hourim.wordpress.com/2015/02/24/parallel-query-broadcast-distribution-and-temp-space/
http://www.oracle.com/technetwork/articles/datawarehouse/twp-parallel-execution-fundamentals-133639.pdf
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1657271217898
What Is Auto DOP?
Configuring And Controlling Auto DOP
https://blogs.oracle.com/datawarehousing/entry/parallel_degree_limit_parallel_max
In Memory Parallel execution in Oracle Database 11g R2
Parameter Changes For Parallel Execution In Oracel Database 12cR2

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

Related links :


Home

Database Index
Automatic Degree Of Paralellism – Part-II
Oracle 11g : Automatic DOP – Parallel Threshold
Oracle 11g :  Parallel Statement Queueing
Parallel_Adaptive_Multi_User
Parallel_Min_Percent
Parallel_Min_Servers

                                           —————-

SINGLE TABLE HASH CLUSTERS DEMYSTIFIED

Database, Uncategorized, , , , ,

In this post, I will discuss single table hash clusters which we can fetch a row requiring only a single block get most of the time. Hash clusters are quite similar to index clusters, except that the cluster index is missing. The data in the cluster itself acts like an index.
First, let us see how is the data stored in a hash cluster.
Let’s say we have a hash cluster table with two columns id (number) and text (char) where  id is the key column. On  inserting a record with id = 1 into the table- The key column value (1 here) is hashed and  converted into a diskaddress.- The record is placed at the disk address returned above.Now, if  another record is inserted with id = 2, , the key column value 2 is hashed to return a different address. Thus records having different key values will be placed at different disk addresses whereas  all the records with same key value will go to the same address on the disk.
Now, let’s see how the data is retrieved from  a hash cluster as compared to a conventional indexed cluster  table.In a conventional indexed heap table , if there is a unique index on the column being queried, the algorithm followed is :- Perform unique scan of the index to locate the key and get the rowid (i.e. at least 2 I/O’s – one for index root block, one for index leaf block and may be more as blevel of the index increases)

– Using the rowid obtained above, access the table by rowid (i.e. one I/O to get the data)

whereas, the procedure followed in  a hash cluster is :

– Hash the primary key to get the physical location of the record

– Perform single I/O to read the block.

Thus, hash clusters can  reduce I/O on  tables.

How to create a single table hash cluster?

create cluster <cluster_name> (cluster_key <datatype>)

size <size_number> single table hashkeys <hash_keys_number> hash is
<expr>;

Let’s understand various clauses in create cluster command:

CLUSTER_KEY <datatype>

The cluster key should have a single column containing only integers.

Hash clusters having composite cluster keys or cluster keys made up of non integer columns use the internal hash function.

If a non-integer cluster key value is supplied and internal hash function is bypassed , the operation (INSERT or UPDATE statement) is rolled back and an error is returned.

SIZE <size_number>

Specifies the amount of space in bytes reserved in a block to store all rows having the same cluster key value or the same hash value. This space decides the maximum number of cluster or hash values stored in a data block.

SINGLE TABLE

SINGLE TABLE indicates that this  hash cluster can contain only one
table.. However, you may drop the table and create another  table in the
same cluster.

HASHKEYS <hash_keys_number>

Specify the HASHKEYS clause to create a hash cluster and specify the number of hash values for the hash cluster.The HASHKEYS value specifies and limits the number of unique hash values that can be generated by the hash function used by the cluster. (how many distinct values you anticipate for the cluster key over time)Oracle Database rounds up the HASHKEYS value to the nearest prime number to obtain the actual number of hash values. The minimum value for this parameter
is 2. If you omit the HASHKEYS parameter, the database creates an indexed cluster by default.

When you create a hash cluster, the database immediately allocates space for the cluster based on the values of the SIZE and HASHKEYS parameters.

It allocates a hash table to hold HASHKEY number of cluster keys of SIZE bytes each.

HASH IS <expr>

Specify an expression to be used as the hash function for the hash cluster. Must evaluate to a positive value

If you omit the HASH IS clause, then Oracle Database uses an internal hash function for the hash cluster.

—- Overview –

– create two single table hash clusters with
size = 8k , hashkeys = 4

cluster HSH_CLUSTER_ROWSIZ_8K and HSH_CLUSTER_ROWSIZ_4K

— Create  table hash_cluster_tab_ROWSIZE_8k in cluster HSH_CLUSTER_ROWSIZ_8K  with row size such that only one record fits one block

— Create  table hash_cluster_tab_ROWSIZE_4k in cluster HSH_CLUSTER_ROWSIZ_4K  with row size such that two records fit one block

— insert records for 5 distinct key values to both the tables

— Each record goes to a different block as hashkeys has been set to 5 (next prime no.) and each block can have records with only one hash key.

— Try to access record with a key value – single I/O

— Insert records for 6th key values although provision has made for 5 keys only and check that hash collision takes place as id = 6 is mapped to one of the already existing hash values (id=1)

— Add another record for id = 1 and check that multiple blocks containing rows with same hash value are chained together.

– Create 3 clusters with size = 2K, hashkeys = 4  

HSH_CLUSTER_SIZ_2K_ROW_1K,  HSH_CLUSTER_SIZ_2K_ROW_2K and HSH_CLUSTER_SIZ_2K_ROW_4K

— Create a table HSH_TAB_SIZ_2K_ROWSIZ_1K in cluster HSH_CLUSTER_SIZ_2K_ROW_1K with row size such that row size =1/2( specified size) = 1/2(2K) = 1K

— Create a table HSH_TAB_SIZ_2K_ROWSIZ_2K in cluster HSH_CLUSTER_SIZ_2K_ROW_2Kwith row size such that row size =  specified size 2K

— Create a table HSH_TAB_SIZ_2K_ROWSIZ_4K in cluster HSH_CLUSTER_SIZ_2K_ROW_4K with row size such that row size = 2( specified size) = 2(2K) = 4K

— Insert records for 5 distinct key values in the three tables

— Check that each block contains records for a maximum of 3 hashkeys.
— Insert 3 records with id = 1 in all the 3 tables

— Add records for id’s = 6 to 9 i.e. four keys more than what we have defined the cluster for (5).

— Note that it takes time to add these records

— Check that there is collision for hash keys – Multiple key values correspond to the same  hash key – the hash chain for a hash key becomes longer and contains records with different key values.

— Check that overallocation takes place i.e. a block holds rows for hashkeys more than it is expected to hold (3).
– Implementation –

– create two single table hash clusters with
size = 8k , hashkeys = 4

cluster HSH_CLUSTER_ROWSIZ_8K and HSH_CLUSTER_ROWSIZ_4K

– The cluster key column for the clusters is id. The column in table in this cluster does not have to be called ID, but it must be NUMBER(2), to match this definition.

– Also we have specified a SIZE 8K option which means that we expect about 8K 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. Here, maximum no. of hash keys per block = Block size/SIZE = 8K/8K = 1 i.e. each block can contain records having only one hash key.

– The value of HASHKEYS limits the number of unique hash values that can be generated by the hash function used for the cluster. Oracle rounds the number you specify for HASHKEYS to the nearest prime number (5 here as we have set  HASHKEYS to 4 and 5 is the next prime number) i.e.  for any cluster key value, the hash function generates a maximum of 5 values.

SQL>Create tablespace mssm datafile
‘/u01/app/oracle/oradata/orcl/mssm01.dbf’ size 100m
segment space management manual;

    alter system set deferred_segment_creation=FALSE;

    drop  cluster  HSH_CLUSTER_ROWSIZ_8K including tables;

  create cluster HSH_CLUSTER_ROWSIZ_8K     ( id number(2) )
size 8192 single table hash is id hashkeys 4 tablespace mssm;

      drop cluster HSH_CLUSTER_ROWSIZ_4K including tables;

create cluster HSH_CLUSTER_ROWSIZ_4K ( id number(2) )
size 8192 single table hash is id hashkeys 4 tablespace mssm;

Let’s create  tables in  these clusters ..

– Create  table
hash_cluster_tab_ROWSIZE_8k in cluster HSH_CLUSTER_ROWSIZ_8K  with row
size such that only one record fits one block

drop table hash_cluster_tab_rowsize_8k purge;

CREATE TABLE HASH_CLUSTER_TAB_ROWSIZE_8K
(id          number(2) ,
txt1        char(2000),
txt2        char(2000),
txt3        char(2000)
 ) CLUSTER HSH_CLUSTER_ROWSIZ_8K( id );

– Create  table
hash_cluster_tab_ROWSIZE_4k in cluster HSH_CLUSTER_ROWSIZ_4K  with row
size such that two records fit one block

drop table hash_cluster_tab_rowsize_4k purge; 

CREATE TABLE hash_cluster_tab_rowsize_4k
(id          number(2) ,
txt1        char(2000),
txt2        char(1000)) CLUSTER HSH_CLUSTER_ROWSIZ_4K( id );

– check the blocks assigned to the clusters –

SQL> col segment_name for a30
select segment_name, extent_id, block_id, blocks  
 from dba_extents where segment_name like ‘%HASH_CLUSTER%';

SEGMENT_NAME    EXTENT_ID   BLOCK_ID     BLOCKS

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

HSH_CLUSTER_ROWSIZ_8K        0       128          8

HSH_CLUSTER_ROWSIZ_4K       0       136          8

– Insert 5 records each with a  distinct
key value to both the tables

SQL>Begin
    for i in 1..5 loop
     insert into hash_cluster_tab_rowsize_8k values (i,  ‘x’, ‘x’, ‘x’);
     insert into hash_cluster_tab_rowsize_4k values (i,  ‘x’, ‘x’);
       end loop;
     commit;
    end;
/

Let’s find out the blocks the records have
gone to ..

– Each record goes to a different block as hashkeys has been set to 5 (next prime no.)
First block assigned to the cluster(128, 136) is used to store header info.

Records occupy blocks second block onwards

Note that record for id=5 goes to the first block as mod (id, hashkeys) decides the block no. for a key and mod (5,5) = 0

0 denotes the first block to accommodate records for the table

Note that records for id= 1  to 4  go to the subsequent  blocks as mod (1,5) = 1, mod(2,5) = 2 …

In hash_cluster_tab_rowsize_4k, even though two rows can fit one block, still each row has gone to a different block   as one block has been assigned to a hash value. and

SQL>select ‘HASH_CLUSTER_TABLE_8K’
TAB_NAME , id,    dbms_rowid.rowid_block_number(t1.rowid) hash_cluster_block
from hash_cluster_tab_rowsize_8k t1 
    union
select ‘HASH_CLUSTER_TABLE_4K’, id,
dbms_rowid.rowid_block_number(t2.rowid) hash_cluster_block
from hash_cluster_tab_rowsize_4k t2
     order by 1,2,3;

TAB_NAME
ID HASH_CLUSTER_BLOCK

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

HASH_CLUSTER_TABLE_4K
1
138

HASH_CLUSTER_TABLE_4K
2
139

HASH_CLUSTER_TABLE_4K
3
140

HASH_CLUSTER_TABLE_4K
4
141

HASH_CLUSTER_TABLE_4K
5
137

HASH_CLUSTER_TABLE_8K
1
130

HASH_CLUSTER_TABLE_8K
2
131

HASH_CLUSTER_TABLE_8K
3
132

HASH_CLUSTER_TABLE_8K
4
133

HASH_CLUSTER_TABLE_8K
5
129

– Try to access record with a key value –
single I/O is needed (cr=1) as hash access is used

SQL>conn / as sysdba


alter session set tracefile_identifier=’hash_cluster';


alter session set sql_trace=true;

       


select * from hash_cluster_tab_rowsize_8k where id = 1;


select * from hash_cluster_tab_rowsize_4k where id = 1;


alter session set sql_trace=false;


col value for a75


select value from v$diag_info where upper(name) like ‘%TRACE FILE%';

VALUE

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

/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14452_hash_cluster.trc

ho
rm hash_cluster.out


ho tkprof
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_14452_hash_cluster.trc
hash_cluster.out


ho vi hash_cluster.out

********************************************************************************

select * from

hash_cluster_tab_rowsize_8k where id = 1

call     count
cpu    elapsed
disk      query
current        rows

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

Parse
1      0.00
0.00
0          2
0           0

Execute      1
0.00
0.00
0
0
0           0

Fetch
2      0.00
0.00
0          1 
0           1

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

total
4      0.00
0.01
0          3
0           1

Rows     Row Source Operation

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

1  TABLE ACCESS HASH
HASH_CLUSTER_TAB_ROWSIZE_8K (cr=1 pr=0
pw=0 time=0 us)

********************************************************************************

select * from

hash_cluster_tab_rowsize_4k where id = 1

call     count
cpu    elapsed
disk      query
current        rows

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

Parse
1      0.00
0.00
0          2
0           0

Execute      1      0.00
0.00
0
0
0           0

Fetch
2      0.00
0.00
0          1  
0           1

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

total
4      0.00       0.00
0
3
0           1

Rows     Row Source Operation

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

1  TABLE ACCESS HASH
HASH_CLUSTER_TAB_ROWSIZE_4K (cr=1 pr=0
pw=0 time=0 us)

********************************************************************************

– Insert records for 6th key values although
provision has made for 5 keys only

SQL> insert into
hash_cluster_tab_rowsize_8k values (6,  ‘x’, ‘x’, ‘x’);


insert into hash_cluster_tab_rowsize_4k values (6,
‘x’, ‘x’);


commit;

– Let’s find out the blocks the records have
gone to .

–    In hash_cluster_tab_rowsize_4k, id = 6 is hashed
to one of the existing hash values and record is placed in the same block (138)
as the earlier containing record with same hash key as two records can fit one
block. Hence block 138 becomes overflow block as it contains more hashvalues
than it is configured for.

Here id = 6 has been mappped to same hash
value as id = 1 and has occupied the same block as id = 1

–    In hash_cluster_tab_rowsize_8k, since one row can
fit one block, Newly added rows have  gone to a new  block (147) as
the earlier blocks can’t accommodate new rows.

But it has again been mapped to one of the
already existing hash values and is chained to the blocks containing those key
values.

SQL>select ‘HASH_CLUSTER_TABLE_8K’
TAB_NAME , id, dbms_rowid.rowid_block_number(t1.rowid) hash_cluster_block


from hash_cluster_tab_rowsize_8k t1

    union

select ‘HASH_CLUSTER_TABLE_4K’, id,
dbms_rowid.rowid_block_number(t2.rowid) hash_cluster_block


from hash_cluster_tab_rowsize_4k t2

     order by 1,2,3;

TAB_NAME
ID HASH_CLUSTER_BLOCK

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

HASH_CLUSTER_TABLE_4K        
1
138

HASH_CLUSTER_TABLE_4K
2
139

HASH_CLUSTER_TABLE_4K
3
140

HASH_CLUSTER_TABLE_4K
4
141

HASH_CLUSTER_TABLE_4K
5
137

HASH_CLUSTER_TABLE_4K        
6
138

HASH_CLUSTER_TABLE_8K
1
130

HASH_CLUSTER_TABLE_8K
2
131

HASH_CLUSTER_TABLE_8K
3
132

HASH_CLUSTER_TABLE_8K
4
133

HASH_CLUSTER_TABLE_8K
5
129

HASH_CLUSTER_TABLE_8K        
6
147

– Let’s add another record for id = 1

In hash_cluster_tab_rowsize_2k,  although we have space for another row
in blocks containing id = 2,3,4,5 , the new record goes to a new block(142) as
one block can contain only one hashvalue.  From now onwards, a block which
contains id = 1 may also contain an entry for id=6 and vice versa since both of
them hash to the same value.

In hash_cluster_tab_rowsize_8k also , the new  record goes to a new
block (135) as a block can contain only one row. Presuming that id = 6 and 1
hash to the same value, blocks containing id = 1 (130 and 135) have been
chained to block containing id=6 (135).

From now onwards, when we search for id =1 or id = 6 we will have to scan a
larger no. of blocks.Hence, if actual no. of cluster keys exceeds the specified
value for HASHKEYS,  the likelihood of a collision (two cluster
key values having the same hash value) increases and performance degrades.

SQL>

insert into
hash_cluster_tab_rowsize_8k values (1,  ‘x’, ‘x’, ‘x’);

     insert into
hash_cluster_tab_rowsize_4k values (1,  ‘x’, ‘x’);

     commit;

   

  select ‘HASH_CLUSTER_TABLE_8K’
TAB_NAME , id, dbms_rowid.rowid_block_number(t1.rowid) hash_cluster_block


from hash_cluster_tab_rowsize_8k t1

    union all

select ‘HASH_CLUSTER_TABLE_4K’, id,
dbms_rowid.rowid_block_number(t2.rowid) hash_cluster_block


from hash_cluster_tab_rowsize_4k t2

     order by 1,2,3;

TAB_NAME
ID HASH_CLUSTER_BLOCK

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

HASH_CLUSTER_TABLE_4K          1
138

HASH_CLUSTER_TABLE_4K          1
142

HASH_CLUSTER_TABLE_4K
2
139

HASH_CLUSTER_TABLE_4K
3
140

HASH_CLUSTER_TABLE_4K
4
141

HASH_CLUSTER_TABLE_4K
5
137

HASH_CLUSTER_TABLE_4K          6
138

HASH_CLUSTER_TABLE_8K          1
130

HASH_CLUSTER_TABLE_8K          1
135

HASH_CLUSTER_TABLE_8K
2
131

HASH_CLUSTER_TABLE_8K
3
132

HASH_CLUSTER_TABLE_8K
4
133

HASH_CLUSTER_TABLE_8K
5
129

HASH_CLUSTER_TABLE_8K        
6
134

Therefore, the distribution of rows in a hash cluster is directly
controlled by the value set for the HASHKEYS parameter. With a larger number of
hash keys for a given number of rows, the likelihood of
a collision (two cluster key values having the same hash value)
decreases. Minimizing the number of collisions is important because overflow
blocks (thus extra I/O) might be necessary to store rows with hash values that
collide.

Now let’s play around with SIZE clause...

The maximum number of hash keys assigned per data block is determined by the
SIZE parameter of the CREATE CLUSTER command. SIZE is an estimate of the total
amount of space in bytes required to store the average number of rows associated
with each hash value. For example, if the available free space per data block
is 1700 bytes and SIZE is set to 500 bytes, three hash keys (round(1700/500))
are assigned per data block.

Note: The importance of the SIZE parameter of hash clusters is analogous
to that of the SIZE parameter for index clusters. However, with index clusters,
SIZE applies to rows with the same cluster key value instead of the same hash
value.

Although the maximum number of hash key values per data block is determined
by SIZE, Oracle does not actually reserve space for each hash key value in the
block. For example, if SIZE determines that three hash key values are allowed
per block, this does not prevent rows for one hash key value from taking up all
of the available space in the block. If there are more rows for a given hash
key value than can fit in a single block, the block is chained, as necessary.

– Create 3 clusters with size = 2K, hashkeys
= 4  

HSH_CLUSTER_SIZ_2K_ROW_1K,  HSH_CLUSTER_SIZ_2K_ROW_2Kand
HSH_CLUSTER_SIZ_2K_ROW_4K

— Available free space is slightly less that blocksize of 8K

— No. of hashkeys that can be stored in the block < 4 (blocksize/size =
8k/2K)

= 3

SQL>  drop cluster HSH_CLUSTER_SIZ_2K_ROW_1K
including tables;


create cluster HSH_CLUSTER_SIZ_2K_ROW_1K


( id number(2) )


size 2K single table hash is id hashkeys 4 tablespace mssm;

      drop cluster
HSH_CLUSTER_SIZ_2K_ROW_2Kincluding tables;


create cluster hash_cluster_size_2k_row_2k


( id number(2) )


size 2K single table hash is id hashkeys 4 tablespace mssm;

     drop cluster
HSH_CLUSTER_SIZ_2K_ROW_4K including tables;


create cluster HSH_CLUSTER_SIZ_2K_ROW_4K


( id number(2) )


size 2K single table hash is id hashkeys 4 tablespace mssm;

– Create a table HSH_TAB_SIZ_2K_ROWSIZ_1K in
cluster HSH_CLUSTER_SIZ_2K_ROW_1K with row size such that row size =1/2(
specified size) = 1/2(2K) = 1K 

SQL>drop table HSH_TAB_SIZ_2K_ROWSIZ_1K
purge;

CREATE TABLE HSH_TAB_SIZ_2K_ROWSIZ_1K

(
id          number(2) ,


txt1        char(400),


txt2        char(400)

)

CLUSTER HSH_CLUSTER_SIZ_2K_ROW_1K( id );

– Create a table HSH_TAB_SIZ_2K_ROWSIZ_2K in
cluster HSH_CLUSTER_SIZ_2K_ROW_2Kwith row size such that row size =
specified size 2K  

SQL>drop table HSH_TAB_SIZ_2K_ROWSIZ_2K
purge;

CREATE TABLE HSH_TAB_SIZ_2K_ROWSIZ_2K

(
id          number(2) ,


txt1        char(900),


txt2        char(900)

)

CLUSTER HASH_CLUSTER_SIZE_2K_row_2k( id );
– Create a table HSH_TAB_SIZ_2K_ROWSIZ_4K in
cluster HSH_CLUSTER_SIZ_2K_ROW_4K with row size such that row size = 2(
specified size) = 2(2K) = 4K 

SQL>drop table HSH_TAB_SIZ_2K_ROWSIZ_4K
purge;

CREATE TABLE HSH_TAB_SIZ_2K_ROWSIZ_4K

(
id          number(2) ,


txt1        char(1900),


txt2        char(1900)

)

CLUSTER HSH_CLUSTER_SIZ_2K_ROW_4K( id );

– Insert records for 5 distinct key values in
the three tables

SQL>Begin

    for i in 1..5 loop

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

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

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

       end
loop;

     commit;

    end;

/

– Check that each block contains records for
a maximum of 3 hashkeys.   
– For row size = 1K

8 rows can fit one block

Max no. of hash keys per block = 3

Max no. of records of distinct key values per block = 3

Records for id = 1,2 and 5 (3 hash keys) go to block 161

Records for id = 3 and 4 (only records left) go to block
162

 – For row size = 2K

3 rows can fit one block

Max no. of hash keys per block = 3

Max no. of records of distinct key values per block = 3

Records for id = 1,2 and 5 (3 hash keys) go to block 169

Records for id = 3 and 4 (only records left) go to block
170

 – For row size = 4K

1 row can fit one block

Max no. of hash keys per block = 3

Max no. of records possible per block = 1

Records for each id  go to different block

SQL>select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t1.rowid) hash_cluster_block


from HSH_TAB_SIZ_2K_ROWSIZ_1K t1

    union

    select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t2.rowid) hash_cluster_block


from HSH_TAB_SIZ_2K_ROWSIZ_2K t2

    union

    select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t3.rowid) hash_cluster_block


from HSH_TAB_SIZ_2K_ROWSIZ_4K t3  

     order by 1,2,3;

TABLE_NAME
ID HASH_CLUSTER_BLOCK

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

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K          1
161

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K         
2
161

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
3
162

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K         
4
162

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K        
5
161

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
1
169

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K        
2
169

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K       
3
170

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
4
170

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K         
5
169

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
1
177

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
2
179

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
3
178

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
4
180

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
5
181

 — Let’s insert 3 records with id = 1 in
all the 3 tables 

- For row size = 1K

No. of rows already there = 3 in block 161 containing id = 1

Since 8 rows can fit one block, all the 3 rows inserted go to
block 161

Now block 161 has 6 rows

i.e. although the maximum number of hash key values per data
block as determined by SIZE is 3, Oracle does not actually reserve space for
each hash key value in the block. Rows for one hash key value(id=1) can take up
all of the available space in the block.

 – For row size = 2K

3 rows can fit one block

Block 169 containing id=1 already has 3 rows

Although 1 more row can fit in block 170 , newly added
rows for id=1 go to anothet block 172

i.e. when key value is inserted for the first time, its
blockmate keys are decided or let’s say that it is     decided
records for which hash values will stay together. From then onwards, records
for its blockmate keys will always reside on the same block. Hence, once hash
values occupying a block have been grouped (1,2,5 and 3,4) further records will
occupy only the blocks along with their blockmates.

Block 172 is chained to block 169 as both contain id=1.

 – For row size = 4K

1 row can fit one block

Each newly added record goes to new block

All the blocks containing id=1 (177, 184, 185, 186) will
be chained

SQL>select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t1.rowid) hash_cluster_block, count(*)


from HSH_TAB_SIZ_2K_ROWSIZ_1K t1

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

    union

    select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t2.rowid) hash_cluster_block, count(*)


from HSH_TAB_SIZ_2K_ROWSIZ_2K t2

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

    union

    select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t3.rowid) hash_cluster_block, count(*)


from HSH_TAB_SIZ_2K_ROWSIZ_4K t3 

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

     order by 1,3,2;

TABLE_NAME
ID HASH_CLUSTER_BLOCK   COUNT(*)

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

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
1
161          4

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
2
161          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K         
5
161          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
3
162          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
4
162          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
1
169          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
2
169          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
5
169          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
3
170          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
4
170          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K         
1
172          3

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
1
177          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
3
178          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
2
179          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
4
180          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
5
181          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K         
1
184          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K         
1
185          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K         
1
186          1
– Add records for id’s = 6 to 9 i.e. four
keys more than what we have defined the cluster for (5).

— Note that it takes time to add these records

SQL>Begin

    for i in 6..9 loop

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

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

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

       end
loop;

     commit;

    end;

/

– Check that there is collision for hash keys
- Multiple key values correspond to the same  hash key – the
hash chain for a hash key becomes longer and contains records with different
key values.

– Check that overallocation takes place i.e.
a block holds rows for hashkeys more than it is expected to hold (3).

 – For row size = 1K

In block 161, no. of rows already there = 6  containing id
= 1

Since 8 rows can fit one block, rows for id = 6 and 7 go to block
161 i.e. now block 161 contains records for id = 1,2,5,6,7

i.e. it contains 2 hashkeys more than it has configured to hold
. This is called overallocation and block 161 is called overflow block.

From now onwards whenever a record is added with any of id =
1,2,5,6,7, it will be chained to block 161   .

All the blocks chained to block 161  will be searched whenever any of
these id’s is searched.

 – For row size = 2K

3 rows can fit one block

Block 169 containing id=1 already has 3 rows with id =
1,2,5

Block 172 has 3 rows with id = 1 – It is already full –
it is chained to block 169

Block 170 has 2 rows with id = 3,4

Record for id=8 goes to block 170 i.e. from now onwards all
the blocks containing of id=3,4,8 will be chained to block 170.

All the blocks chained to block 170  will be
searched whenever any of these id’s (3,4,8) is searched.

Rows with id’s 7 and 9  go to block 173 . This block
will be chained with block 172 or 170

 – For row size = 4K

1 row can fit one block

Each newly added record goes to new block

Blocks with id’s > 5 will be chained with any of the
earlier blocks with matching hash value.

e.g. mod(1,5) = mod (6,5) = 1

Hence block containing id = 6 i.e. block 183  will
chain with the all the blocks containing id = 1 (177, 184, 185, 186)

i.e. to search for id = 6, 5 blocks will be searched.

– With a larger number of hash keys for a given number of rows, the
likelihood of a collision (two cluster key values having the same
hash value) decreases.

SQL>select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t1.rowid) hash_cluster_block, count(*)


from HSH_TAB_SIZ_2K_ROWSIZ_1K t1

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

    union

    select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t2.rowid) hash_cluster_block, count(*)


from HSH_TAB_SIZ_2K_ROWSIZ_2K t2

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

    union

    select
‘HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K’ TABLE_NAME, id,
dbms_rowid.rowid_block_number(t3.rowid) hash_cluster_block, count(*)


from HSH_TAB_SIZ_2K_ROWSIZ_4K t3 

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

     order by 1,3,2;

TABLE_NAME
ID HASH_CLUSTER_BLOCK   COUNT(*)

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

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
1
161          4

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
2
161          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
5
161          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K         
6
161          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
7
161          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
3
162          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
4
162          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K
8
162          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_1K         
9
162          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
1
169          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
2
169          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
5
169          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
3
170          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
4
170          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K         
8
170          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
6
171          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
1
172          3

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
7
173          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_2K
9
173          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
1
177          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
3
178          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
2
179          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
4
180          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
5
181          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
9
182          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
6
183          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
1
184          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
1
185          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
1
186          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K
7
187          1

HASH_CLUSTER_TABLE_SIZE_2K_ROWSIZE_4K         
8
188          1
Specifying a User-Defined Hash Function

You can also specify any SQL expression as the hash function for a hash
cluster. If your cluster key values are not evenly distributed among the
cluster, you should consider creating your own hash function that more
efficiently distributes cluster rows among the hash values.

For example, if you have a hash cluster containing employee information and
the cluster key is the employee’s home area code, it is likely that many
employees will hash to the same hash value. To alleviate this problem, you can
place the following expression in the HASH IS clause of the CREATE CLUSTER
command:

MOD((emp.home_area_code + emp.home_prefix + emp.home_suffix), 101)

The expression takes the area code column and adds the phone prefix and
suffix columns, divides by the number of hash values (in this case 101), and
then uses the remainder as the hash value. The result is cluster rows more
evenly distributed among the various hash values.

CREATE CLUSTER address

(postal_code NUMBER, country_id CHAR(2))

HASHKEYS 20

HASH IS MOD(postal_code + country_id, 101);

Summary:

— Hash Cluster tables are appropriate for data that is read frequently via
an equality comparison on the key. If an index scan is used for a key
value, as more no. of users search for the same record, they hit the same index
block which becomes the “hot block” leading to more contention for
the cache buffers chains (cbc) latch. Replacing indexed tables with hash cluster
tables in this case can resolve the problem of contention for CBC latches.

— you cannot range scan a table in a hash cluster without adding a
conventional index to the table.  In an index cluster, the query for range of values will  be able to make use of the cluster key index to find these rows. In a hash cluster, this query would result in a full table scan unless you had an index on the key column. Only exact equality searches (including in lists and subqueries) may be made on the hash key without using an index that supports range scans.

— When you create a hash cluster  table, you must determine in advance,  the number of hash keys your table will ever have. The number of HASHKEYs in a hash cluster is a fixed size. You cannot change the size of the hash table without a rebuild of the cluster. This does not in any way limit the amount of data you can store in this cluster; it simply limits the number of unique hash keys that can be generated for this cluster. That may affect performance due to unintended hash collisions if the value was set too low. Getting the size of the HASHKEYs and SIZE parameters right is crucial to avoid a rebuild.

— With a hash cluster, the tables will start out big and will take longer
to create, as Oracle must initialize each block, an action that normally takes
place as data is added to the table. They have the potential to have data in
their first block and their last block, with nothing in between. Full scanning
a virtually empty hash cluster will take as long as full scanning a full hash cluster.

• The hash cluster is allocated right from the beginning. Oracle will take
your HASHKEYS/ trunc(blocksize/SIZE) and allocate and format that space right away. As soon as the first table is put in that cluster, any full scan will hit every allocated block. This is different from every other table in this respect.

• Updates to hash cluster tables do not introduce significant overhead,
unless you update the HASHKEY, which would not be a good idea, as it would
cause the row to migrate

. Hash clusters allocate all the storage for all the hash buckets when the cluster is created, so they may waste space.. Full scans on single table hash clusters will cost as much as they would in a heap table.

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.

——————————–

References :

http://www.relationaldbdesign.com/extended-database-features/module3/oracle-hash-cluster.php

http://docs.oracle.com/cd/B19306_01/server.102/b14231/hash.htm

http://simpleoracledba.blogspot.in/2009/05/oracle-single-table-hash-clusters-are.html

http://jonathanlewis.wordpress.com/2009/10/05/hash-clusters-2/

http://www.iselfschooling.com/mc4articles/mc4cluster.htm

http://dbaforums.org/oracle/index.php?showtopic=18947

http://www.orafaq.com/tuningguide/bad%20hash%20cluster.html

http://www.riddle.ru/mirrors/oracledocs/server/scn73/ch507.html

http://www.dba-oracle.com/t_hash_tables_clusters.htm

http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:707440500346653259

http://stackoverflow.com/questions/2714099/oracle-hash-cluster-overflow-blocks

http://www.orafaq.com/forum/t/141142/2/

https://forums.oracle.com/forums/thread.jspa?threadID=2512488&start=0&tstart=0

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

Related links :

Home

Database Index

Tuning Index

Single Table Index Clusters

Clustering Factor Demystified : Part – I

Clustering Factor Demystified : Part – II

Clustering Factor Demystified : Part – III

 

 

——————-

 

FLUSH BUFFER CACHE

Database, Uncategorized,
                   

  In this post I will demonstrate that flushing the buffer cache causes dirty blocks to be written to disk .
– Create simple table whose one row occupies one block
HR1> drop table t1 purge;
    create table t1
    (c1 int, c2 char(2000), c3 char(2000), c4 char(2000));
– Insert one row
HR1> insert into t1 values(1, ‘pre update’, ‘x’, ‘x’);
     commit;
– Get the block address and browse X$BH view for that block.
HR1> select dbms_rowid.rowid_relative_fno(rowid) as file#,
           dbms_rowid.rowid_block_number(rowid) as block#
    from t1;
 
File#         Block#
——        ——
    4        550
– Check in x$bh that We have one CURRENT block(state=xcur).
   Enter file#/block# found above when prompted
SYS1>select b.dbarfil, b.dbablk, b.class,
  decode(state,0,’free’,1,’xcur’,2,’scur’,3,’cr’,
4,’read’,5,’mrec’,6,’irec’,7,’write’,8,’pi’, 9,’memory’,10,’mwrite’,11,’donated’) as state,
  cr_scn_bas, cr_scn_wrp, cr_uba_fil, cr_uba_blk, cr_uba_seq,
  (select object_name from dba_objects where object_id = b.obj) as object_name
from sys.x$bh b
where dbarfil = &file_no and
      dbablk = &block_no;
Enter value for file_no: 4

old   6: where dbarfil = &file_no and

new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
         4        550          1 xcur                0          0          0          0
   0 T1
– Update the table so that the block becomes dirty.
   Do not commit the transaction so that info about the change is not written to redo logs.
HR1>update t1 set c2=’updated';
– Check that two versions(current and cr clone) of the block are there in buffer cache
SYS1>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
  DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
         4        550          1 xcur                0          0          0          0
   0 T1
         4        550          1 cr           24279860          0          0          0
   0 T1
– Flush the buffer cache –
SYS2>alter system flush buffer_cache;
– Check that there are not any versions of the block (state=free) in buffer cache
SYS1>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
         4        550          1 free                0          0          0          0
   0 T1
         4        550          1 free                0          0          0          0
   0 T1
– Query the table from the same session from where update was issued
   Autotrace the query to verify that blocks were read from the disk (physical reads > 0)
   Note that the updated row is displayed.
HR1>set autotrace on
    col c2 for a20;
    select c2 from t1;
C2
——————–
updated
Statistics
———————————————————-
          0  recursive calls
          0  db block gets
          7  consistent gets
          6  physical reads
          0  redo size
       2423  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
– Check that current version of the block (state=xcur) has been read into the buffer cache
SYS1>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
         4        550          1 xcur                0          0          0          0
   0 T1
         4        550          1 free                0          0          0          0
   0 T1
– Query the table from another session. This session should see the pre update value of the
column.
   Check that physical reads = 0 as current block is already there in buffer cache
              Consistent reads > 0 as undo tablespace has been read to construct cr block
HR2>set autotrace on
    col c2 for a20
    set line 80
    select c2 from t1;
C2
——————–
pre update
Statistics
———————————————————-
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
        108  redo size
       2423  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
– Check that cr version of the block has been created in buffer cache (state=cr)
   after reading the undo blocks (CR_UBA_FIL = 3, CR_UBA_BLK = 189980)
SYS1>/
Enter value for file_no: 4
old   6: where dbarfil = &file_no and
new   6: where dbarfil = 4 and
Enter value for block_no: 550
old   7:       dbablk = &block_no
new   7:       dbablk = 550
   DBARFIL     DBABLK      CLASS STATE      CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK
CR_UBA_SEQ OBJECT_NAME
———- ———- ———- ———- ———- ———- ———- ———-
———- ——————–
         4        550          1 cr           24294089          0          3     189980
 941 T1
         4        550          1 xcur                0          0          0          0
   0 T1
Conclusion:
     Flushing the buffer cache causes dirty blocks to be written to disk .
———————————————————————————————————-
Related links:

 

TUNING PGA : PART – III

Tuning, Uncategorized

In
my first article on PGA tuning I had explained the concept of
manual/automatic tuning of PGA. In my second article, I had given
guidelines to size the PGA in case of automatic tuning.

In the third and last article on PGA tuning, I will demonstrate both manual and automatic tuning of PGA.

BRIEF OVERVIEW OF THE DEMONSTRATION:

MANUAL TUNING : EFFECT OF VARIOUS SORT AREA SIZES ON SORTING OPERATIONS

— Disable AMM

— Enable manual PGA memory management

— Set sort area size to  different values (64k, 1M, 1G)

— Note the effect on sorts : As sort area size is increased, temporary tablespace usage decreases

AUTOMATIC TUNING

— Enable automatic PGA memory management with pga_aggregate target set to minimum (10M)

— execute query

— Sort spills to disk

— use Pga advisor to estimate correct size of pga

— Increase size og PGA as per advice

— Re execute query

— Sort does not spill to disk

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

– SAVE CURRENT PARAMETER SETTINGS IN PFILE

conn / as sysdba

create pfile=’/u01/app/oracle/initorcl.ora’ from spfile;

– DISABLE AMM

sho parameter memory_target;

alter system set memory_target=0;

sho parameter memory_target;

– ENABLE MANUAL PGA MEMORY MANAGEMENT

alter system set workarea_size_policy=manual;

alter system set pga_aggregate_target=0 scope=spfile;

startup force;

sho parameter workarea

sho parameter pga_agg

@?/sqlplus/admin/plustrce.sql

grant plustrace to hr;

– CREATE TEST TABLE, GATHER STATS

conn hr/hr

create table t as select * from all_objects;

exec dbms_stats.gather_table_stats( user, ‘T’ );

– END CURRENT SESSION AND START A NEW SESSION TO GET A CONSISTENT ENVIRONMENT IN WHICH NO WORK HAS BEEN DONE YET

conn hr/hr

– FIND OUT SID FOR CURRENT SESSION

HR>select sid from v$mystat where rownum = 1;

sid = 37

– START ANOTHER SESSION FROM WHERE WE CAN MONITOR

MEMORY USAGE FOR SORTING IN EARLIER SESSION

sqlplus / as sysdba

— NOTE DOWN CURRENT MEMORY USED FOR UGA/PGA

AND TEMPORARY TABLESPACE USAGE BY HR’s session

 NOTE THAT TEMPORARY TABLESPACE USAGE = 0

SYSDBA>

select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = 37
and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’);

NAME                                                                  VALUE

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

session uga memory                                                   147180

session uga memory max                                               147180

session pga memory                                                   841832

session pga memory max                                               841832

physical reads direct temporary tablespace                                0

physical writes direct temporary tablespace                               0

– NOTE THAT DISK SORTS = 0

SYSDBA>

select name, value
from v$sysstat
where name like ‘%sort%';

NAME                                                                  VALUE

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

sorts (memory)                                                         3687
sorts (disk)                                                              0
sorts (rows)                                                          22489

– NOTE THAT TEMPORARY SEGMENT USAGE BY HR = 0 AS NO SORTS HAVE BEEN MADE IN HR SESSION.

SYSDBA>

select username, segtype, blocks, tablespace
from   v$tempseg_usage
where username=’HR';

no rows selected

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

– SET SORT AREA SIZE = 64K IN HR SESSION AND PERFORM THE SORT

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

HR>

alter session set sort_area_size = 65536;

– NOTE THAT SORT HAS BEEN PERFORMED ON DISK

set autotrace traceonly statistics

select * from t order by 1, 2, 3, 4;

Statistics

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

8  recursive calls
296  db block gets
808  consistent gets
3737  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
0  sorts (memory)
    1  sorts (disk)

55666  rows processed

— NOTE DOWN CURRENT MEMORY USED FOR UGA/PGA AND TEMPORARY TABLESPACE USAGE BY HR’s SESSION

– NOTE THAT TEMPORARY TABLESPACE HAS BEEN USED

. UGA expanded and then shrank (session uga memory < session uga memory max)

. PGA (1406016) is more than earlier value (841832)

SYSDBA>

select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = 21
and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’);

NAME                                                                  VALUE

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

session uga memory                                                   274344

session uga memory max                                               331568
session pga memory                                                  1406016
session pga memory max                                              1406016
physical reads direct temporary tablespace                             3737
physical writes direct temporary tablespace                            3737
– NOTE THAT DISK SORTS HAVE INCREASED (FROM 0 TO 10)

select name, value
from v$sysstat
where name like ‘%sort%';

NAME                                                                  VALUE

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

sorts (memory)                                                         8495
sorts (disk)                                                             10
sorts (rows)                                                         221576

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

– SET SORT AREA SIZE = 1M IN HR SESSION AND PERFORM THE SORT

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

HR>

alter session set sort_area_size = 1048576;

– NOTE THAT SORTS HAVE AGAIN SPILLED TO DISK AS SORT AREA SIZE IS STILL INSUFFIEICNT.

HR>

set autotrace traceonly statistics;

select * from t order by 1, 2, 3, 4;

Statistics

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

8  recursive calls
9  db block gets
808  consistent gets
825  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
0  sorts (memory)
  1  sorts (disk)

55666  rows processed
— NOTE DOWN CURRENT MEMORY USED FOR UGA/PGA AND TEMPORARY TABLESPACE USAGE BY HR’s SESSION

– NOTE THAT TEMPORARY TABLESPACE USAGE IS REDUCED

( current value – earlier value = 4562 – 3737 = 825

which is less that ealier value (3737))

— UGA EXPANDED FROM EARLIER VALUE OF 274344 TO 1309592 (= MAX VALUE)

— PGA EXPANDED FROM EARLIER VALUE OF 1406016 TO 2483236(= MAX VALUE)

SYSDBA>

select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = 21
and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’);

NAME                                                                  VALUE

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

session uga memory                                                  1309592
session uga memory max                                              1309592
session pga memory                                                  2483236
session pga memory max                                              2483236
physical reads direct temporary tablespace                             4562
physical writes direct temporary tablespace                            4562

– NOTE THAT TOTAL DISK SORTS (SINCE INSTANCE STRATUP) HAVE INCREASED AS CURRENT SORT SPILLED TO DISK.

SYSDBA>

select name, value
from v$sysstat
where name like ‘%sort%';

NAME                                                                  VALUE

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

sorts (memory)                                                         9781
sorts (disk)                                                             11
sorts (rows)                                                         280487

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

– SET SORT AREA SIZE = 1G IN HR SESSION AND PERFORM THE SORT

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

HR>

alter session set sort_area_size = 1073741820;

– NOTE THAT ALL SORTS HAVE BEEN PERFORMED IN MEMORY AS SORT AREA SIZE IS SUFFICIENT

HR>

set autotrace traceonly statistics

select * from t order by 1, 2, 3, 4;

Statistics

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

1  recursive calls
0  db block gets
808  consistent gets
0  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
1  sorts (memory)

  0  sorts (disk)

55666  rows processed
— NOTE DOWN CURRENT MEMORY USED FOR UGA/PGA AND TEMPORARY TABLESPACE USAGE BY HR’s SESSION

– NOTE THAT TEMPORARY TABLESPACE USAGE IS NOT AT ALL USED AND SORT IS COMPLETELY PERFORMED IN PGA

(Reads/Writes to temporary tablespace is same as earlier

value (4562))

— UGA AND PGA EXPANDED

SYSDBA>

select a.name, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = 21
and (a.name like ‘%ga %’
or a.name like ‘%direct temp%’);

NAME                                             VALUE

————————–                       ———-
session uga memory           7404100
session uga memory max     7404100
session pga memory          8556012
session pga memory max     8556012
physical reads direct temporary tablespace   4562
physical writes direct temporary tablespace      4562
– DISK SORTS HAVE SAME VALUE (11) AS EARLIER AS SORTING COMPLETELY IN MEMORY ITSELF.

SYSDBA>

select name, value
from v$sysstat
where name like ‘%sort%';

NAME               VALUE

—————–  ———

sorts (memory)     10641
sorts (disk)       11
sorts (rows)       337538

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

– ENABLE AUTOMATIC PGA MEMORY MANAGEMENT

SET pga_aggregate target to minimum valie i.e. 10M

SYSDBA>

alter system set pga_aggregate_target=10m;

alter system set workarea_size_policy=auto;



sho parameter pga_agg

sho parameter workarea

— EXECUTE QUERY FROM HR SESSION WITH NEW SETTING

NOTE THAT SORT IS SPILLING TO DISK AS THE VALUE OF 10M IS INSUFFICIENT

HR>

select * from t order by 1, 2, 3, 4;

Statistics

———————————————————-
8  recursive calls
9  db block gets
808  consistent gets
827  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
0  sorts (memory)
          1  sorts (disk)

55666  rows processed

— FIND OUT WHETHER ABOVE QUERY EXECUTION WAS OPTIMAL/ONE PASS/MULTI PASS

– NOTE THAT QUERY WAS NOT EXECUTED IN OPTIMAL MODE (ONE PASS / MULTIPASS EXECS > 0)

HR>

SQL> col sql_text for a25 word_wrapped

      Select SQL_text, sum(OPTIMAL_EXECUTIONS) OPTIMUM_EXECS,
             sum(ONEPASS_EXECUTIONS) ONE_PASS_EXECS, sum(MULTIPASSES_EXECUTIONS) MULTI_PASS_EXECS
      from v$sql s, v$sql_workarea w
      where s.sql_text like ‘select * from t order by 1%’
       and s.sql_id=w.sql_id
      group by sql_text;

– LOOK AT PGA ADVISOR TO FIND OUT IF THE CURRENT SIZE OF PFA IS APPROPRIATE

SQL> SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
FROM   v$pga_target_advice;

TARGET_MB   CACHE_HIT_PERC   ESTD_OVERALLOC_COUNT

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

10                     92                                         3

12                     92                                         3

14                     92                                         3

16                     92                                         3

18                     92                                         3

20                     92                                         3

30                     92                                         3

40                     92                                         3

60                     92                                         1

80                    100                                        0
set the PGA_AGGREGATE_TARGET parameter to a value where we avoid any over

allocation, so lowest PGA_AGGREGATE_TARGET value we can set is 80m (where

ESTD_OVERALLOC_COUNT is 0). After eliminating over-allocations, the goal is

to maximize the PGA cache hit Percentage based on your response-time

requirement and memory constraints.

SYSDBA>alter system set pga_aggregate_target=80m;

– AGAIN EXECUTE QUERY IN HR SESSION AND NOTE THAT SORTS HAVE NOT SPILLED TO DISK.

HR> select * from t order by 1, 2, 3, 4;

55666 rows selected.

Statistics

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

1  recursive calls
0  db block gets
808  consistent gets
0  physical reads
0  redo size
3064341  bytes sent via SQL*Net to client
41240  bytes received via SQL*Net from client
3713  SQL*Net roundtrips to/from client
1  sorts (memory)

          0  sorts (disk)

55666  rows processed

— FIND OUT WHETHER ABOVE QUERY EXECUTION WAS OPTIMAL/ONE PASS/MULTI PASS

– NOTE THAT QUERY WAS NOT EXECUTED IN OPTIMAL MODE (OPTIMAL EXECS > 0)

SQL> col sql_text for a25 word_wrapped

      Select SQL_text, sum(OPTIMAL_EXECUTIONS) OPTIMUM_EXECS,
             sum(ONEPASS_EXECUTIONS) ONE_PASS_EXECS,
             sum(MULTIPASSES_EXECUTIONS) MULTI_PASS_EXECS
      from   v$sql s, v$sql_workarea w
      where  s.sql_text like ‘select * from t order by 1%’
       and   s.sql_id=w.sql_id
      group by sql_text;
– RESTORE EARLIER STATUS

SYSDBA>

drop table hr.t purge;

create spfile from pfile=’/u01/app/oracle/initorcl.ora';

startup force;

 

References:

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

Related links:

Tuning Index
Tuning PGA : Part – I
Tuning PGA : Part – II

——————–

TUNING PGA : PART – II

Tuning, Uncategorized, ,

In continuation with my earlier article (Tuning PGA Part-I), in this article I will discuss how we can decide on an appropriate value of PGA_AGGREGATE_TARGET for our database.

When configuring a brand new instance, it is hard to know precisely the appropriate setting for PGA_AGGREGATE_TARGET. You can determine this setting in three stages:
1. Make a first estimate for PGA_AGGREGATE_TARGET. By default, Oracle Database uses 20% of the SGA size. However, this initial setting may be too low for a large DSS system.
2. Run a representative workload on the instance and monitor performance, using PGA statistics collected by Oracle Database, to see whether the maximum PGA size is under-configured or over-configured.
3. Tune PGA_AGGREGATE_TARGET, using Oracle PGA advice statistics.

Setting PGA_AGGREGATE_TARGET Initially

The value of the PGA_AGGREGATE_TARGET initialization parameter  should be set based on the total amount of memory available for the Oracle database instance. This value can then be tuned and dynamically modified at the instance level. illustrates a typical situation.
Let total physical memory available on the system = 4 GB
Memory  left for the OS and other non-Oracle applications running on the same system = 20% i.e. .8 GB
Memory available for Oracle instance = 80% i.e. 3.2 GB
You must then divide the resulting memory between the SGA and the PGA.
For OLTP systems, the PGA memory = 20% i.e. 3.2 * .2 = 0.64 GB leaving 80% for the SGA.
For DSS systems running large, memory-intensive queries, PGA memory can
typically use up to 70% of that total i.e. 3.2 * 0.7 = 2.2 GB ).
Good initial values for the parameter PGA_AGGREGATE_TARGET might be:
For OLTP: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
For DSS: PGA_AGGREGATE_TARGET = (total_mem * 80%) * 70%
where total_mem is the total amount of physical memory available on the system.
In this example, with a value of total_mem equal to 4 GB, you can
initially set PGA_AGGREGATE_TARGET to 1600 MB for a DSS system and to
655 MB for an OLTP system.

Monitoring the Performance of the Automatic PGA Memory Management

Before starting the tuning process, you need to know how to monitor and
interpret the key statistics collected by Oracle Database to help in assessing the performance of the automatic PGA memory management component. Several dynamic performance views are available for this purpose:
 V$PGASTAT
This view gives instance-level statistics on the PGA memory usage and the
automatic PGA memory manager. Various columns of interest are:
over allocation count
: This statistic is cumulative from instance startup. Over-allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small to accommodate the untunable  component of PGA  plus the minimum memory
required to execute the work area workload. When this happens, Oracle Database cannot honor the initialization parameter PGA_AGGREGATE_TARGET, and extra PGA memory must be allocated. If over-allocation occurs, you should increase the value of PGA_AGGREGATE_TARGET using the information provided by the advice view V$PGA_TARGET_ADVICE.
extra bytes read/written:
When a work area cannot run optimally, one or more extra passes is performed over the input data. extra bytesread/written represents the number of bytes processed during these extra passes since instance startup. This number is also used to compute thecache hit percentage. Ideally, it should be small compared to total bytes processed.
cache hit percentage:
This metric is computed by Oracle Database to reflect the performance of the PGA memory component. It is cumulative from instance startup. A value of 100% means that all work areas executed by the system since instance startup have used an optimal amount of PGA memory.  When a work area cannot run optimally, one or more extra passes is performed over the input data. This reduces the cache hit percentage in proportion to the size of the input data and the number of extra passes performed.
 V$PROCESS
This view has one row for each Oracle process connected to the instance. The
columns PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM andPGA_MAX_MEM can be used to monitor the PGA memory usage of these processes.
 V$SQL_WORKAREA_HISTOGRAM
This view shows the number of work areas executed with optimal memory size,
one-pass memory size, and multi-pass memory size since instance startup.
Statistics in this view are subdivided into buckets that are defined by the optimal memory requirement of the work area. Each bucket is identified by a range of optimal memory requirements specified by the values of the columns LOW_OPTIMAL_SIZE and HIGH_OPTIMAL_SIZE.
V$SQL_WORKAREA_ACTIVE
You can use this view to display the work areas that are active (or executing) in the instance. Small active sorts (under 64 KB) are excluded from the view. Use this view to precisely monitor the size of all active work areas and to determine if these active work areas spill to a temporary segment.
 V$SQL_WORKAREA
Oracle Database maintains cumulative work area statistics for each loaded
cursor whose execution plan uses one or more work areas. Every time a work area is deallocated, the V$SQL_WORKAREA table is updated with execution statistics for that work area.

Tuning PGA_AGGREGATE_TARGET

To help you tune the initialization parameter PGA_AGGREGATE_TARGET, Oracle Database provides the V$PGA_TARGET_ADVICE  view.
V$PGA_TARGET_ADVICE
This view predicts how the statistics cache hit percentage and over
allocation count in V$PGASTAT will be impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET. A typical query of this view:
SELECT round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
       ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
       ESTD_OVERALLOC_COUNT
  FROM V$PGA_TARGET_ADVICE;
How to Tune PGA_AGGREGATE_TARGET ?
1.  If over allocation count > 0, it indicates that PGA_AGGREGATE_TARGET is too small to even meet the minimum PGA memory needs. If PGA_AGGREGATE_TARGET is set within the over allocation zone, the memory manager will over-allocate memory and actual PGA memory consumed will be more than the limit you set. Set PGA_AGGREGATE_TARGET so that there is no memory over-allocation; avoid setting it in the over-allocation zone.
2. After eliminating over-allocations, aim at maximizing the PGA cache hit percentage, based on your response-time requirement and memory constraints.
   – If available memory >= optimal memory for 100% PGA cache hit %,
        set PGA_AGGREGATE_TARGET = optimal memory as in PGA_TARGET_ADVICE ,
         if AMM is enabled
             Rest of the available memory will be allocated to SGA
         else
              Rest of the available memory will be returned to Operating system
   – If available memory < optimal memory for 100% PGA cache hit %,
        set PGA_AGGREGATE_TARGET = available memory and settle for a lower PGA cache hit %.
V$PGA_TARGET_ADVICE_HISTOGRAM 
This view predicts how the statistics displayed by the performance view will
be impacted if you change the value of the initialization parameter PGA_AGGREGATE_TARGET. You can use the dynamic view V$PGA_TARGET_ADVICE_HISTOGRAM to view detailed information on the predicted number of optimal, one-pass and multi-pass work area executions for the set of PGA_AGGREGATE_TARGET values you use for the prediction.
V$SYSSTAT and V$SESSTAT
Statistics in the V$SYSSTAT and V$SESSTAT views show the total number of work areas executed with optimal memory size, one-pass memory size, and
multi-pass memory size. These statistics are cumulative since the instance or the session was started.
The following query gives the total number and the percentage of times work areas were executed in these three modes since the instance was started:
SELECT name profile, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
 FROM (SELECT name, value cnt, (sum(value) over ()) total 
               FROM V$SYSSTAT 
               WHERE name like ‘workarea exec%’);


In the third and last article on PGA tuning, I will demonstrate both manual and automatic tuning of PGA.
References:
———————————————————————————————————–

Related links:
Home

Tuning Index
Tuning  PGA : Part – I
Tuning  PGA : Part – III

                                                   ——————

TUNING PGA : PART – I

Tuning, Uncategorized

 In this article, I will discuss about tuning of PGA. First, we will discuss the scenario when PGA was tuned manually , find out what were the problems and how the problems were resolved using automatic tuning of PGA.

Program Global Area or PGA as it is popularly called contains data and control information about a server process. This memory is specific to a single server process and is not accessible by any other server process. Because the PGA is process-specific, it is never allocated in the SGA.

Various  components of PGA are as follows:

  • Private SQL Area:  Holds information about a parsed SQL statement and other session-specific information for processing. A private SQL area is further  divided into the following areas:
    • Run time Area:  Contains query execution state information for example,  the number of rows retrieved so far in a full table scan.
    • Persistent area: Contains bind variable values.
  • SQL Work Areas:  : Used for sorting, hash operations etc.
  • User Global Area (UGA) : It essentially stores the session state.  It contains session information such as logon information, buffers read as a result of direct path reads and other information required by a database Session. The UGA must be available to a database session for the life of the session. In a dedicated server connection, since each session is associated with a dedicated server process having its own PGA, the UGA is stored in the PGA only. In a shared server connection, since a session can use any one of the shared servers, the UGA should be accessible to each one of the shared server processes and hence it cannot be stored in the PGA . Therefore,  when using shared server connections, the UGA is stored in the SGA .

  For complex queries, work areas in PGA are required to perform memory intensive operations.  For example, a sort operator uses the sort area to sort a set of rows. Similarly, a hash join operator uses a hash area to build a hash table from its left input, whereas a bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.

Memory intensive operations which make use of PGA are

– Sort based operators (Order by, group by, rollup, Distinct etc.)
– Hash join
– Bitmap related operations
– Write buffers used by bulk load operations (Direct Path Load)

                              HOW DOES SIZE OF PGA AFFECT APPLICATION’S PERFORMANCE

If amount of data to be processed fits the work area i.e. size of work area is optimal, all the operations are performed in memory.

If memory required by the amount of data to be processed is more than the available workarea, the input is divided into smaller pieces. Then, some pieces pf data are processed in memory while the rest are spilled to temporary tablespace.

When one extra pass is performed on all or some of the input data, the corresponding size of the available work area is called one-pass size i.e. if the data having size equal to one-pass size is sorted with currently available PGA workarea, an extra pass on the data will have to be made.

When the available work area size is even less than one pass threshold, multiple passes over the imput data are needed causing dramatic increase in response time. This is referred to as multipass size of the workarea.

In an OLTP system, size of input data is small and hence they mostly run in optimal mode.

In DSS  systems, where input data is very large , it is important to size the workarea for good performance as in-memory operations are m uch faster than temporary disk operations. Generally, bigger workareas can significantly improve the performance of an operation at the cost of higher memory consumption.

Optimally, the size of workarea should be enough to accommodate the input data.

                        MANUAL PGA MEMORY MANAGEMENT

Earlier releases required DBA to manually specify the maximum workarea size for each type of SQL operator.

PGA has two components : Tunable and untunable

– Untunable PGA : consists of

  • Context information of each session
  • Each open cursor
  • PL/SQL, OLAP or Java memory

This component of PGA can’t be tuned i.e. whatever is memory is needed it will be consumed else the operation fails.

– Tunable PGA : consists of memory available to SQL work areas (used by various sort operations)

  •  approx. 90% of PGA in DSS systems
  •  approx. 10% of PGA in OLTP systems

This component is tunable in the sense that memory available and hence consumed may be less than what is needed  and the operation will still complete but may spill to disk. Hence, increasing available memory may improve performance of such operations.

                   MANUAL PGA MEMORY MANAGEMENT

In manual management, DBA manually specifies the maximum work area size for each type of SQL operator (Sort, hash ,etc.) i.e. when WORKAREA_SIZE_POLICY = Manual

SORT_AREA_SIZE, HASH_AREA_SIZE, CREATE_BITMAP_AREA_SIZE, BITMAP_MERGE_AREA_SIZE parameters decided the size of memory available for each of these operations per server process.

e.g. SORT_AREA_SIZE = Memory available for sorting to each server process (each user in dedicated server connection)

Let SORT_AREA_SIZE = 1M

As no. of users performing sort operations increase, the total PGA memory for the instance increases linearly as n * 1 M where n = no. of sessions

Implication :

– Each user session will use 1M of PGA for sorting irrespective of the size of the data.

If size of the data is > 1M, his sorts will spill to the disk even though we might be having PGA memory available to the instance i.e. a session won’t be able to use the available memory because the memory allocated to his session has been fixed by SORT_AREA_SIZE.

IF SIZE OF THE DATA IS << 1M, the sorting will take place completely in memory but will still consume 1M of memory which is more than what is needed. The extra memory can’t be transferred to another session needing it.

– If SORT_AREA_SIZE is small, sorts spill to disk and

If SORT_AREA_SIZE is made large and no. of sessions performing sorts is so large that the total memory required by them (n * 1M) is more than the available memory ( Total memory available to oracle instance – SGA ), paging and swapping will take place to satisfy that memory requirement resulting in heavily degraded performance.

Hence, DBA had to continuously monitor the user workload and decide on the appropriate value of SORT_AREA_SIZE such that neither the sorts spilled to disk nor swapping/paging took place.

                   AUTOMATIC PGA MEMORY MANAGEMENT

Automatic PGA memory management resolved above mentioned issues by allowing DBA to allocate an aggregate PGA to all the server processes for all the SQL operations which could be distributed as per the requirement. In this case, Oracle dynamically adapts the SQL memory allocation based on

  • - PGA memory available
  • - SQL operator needs
  • - System workload

With automatic PGA memory management, sizing of SQL work areas for all dedicated server sessions is automatic and all *_AREA_SIZE initialization parameters are ignored for these sessions. At any given time, the total amount of PGA memory available to active work areas on the instance is automatically derived from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of PGA_AGGREGATE_TARGET minus the PGA memory allocated for other purposes (for example, session memory). The resulting PGA memory is then allotted to individual active work areas based on their specific memory requirements.

  To implement it, two parameters need to be set.

WORKAREA_SIZE_POLICY = AUTO
PGA_AGGREGATE_TARGET = Target size of PGA for the total instance.

Given the specified target PGA, Oracle itself tunes the size of individual PGA’s depending upon the workload.

IMPLICATIONS:

– As the workload changes, memory available to each session changes dynamically while keeping the sum of all PGA allocations under the threshold PGA_AGGREGATE_TARGET.

e.g.

If PGA_AGGREGATE_TARGET = 10G,
let Tunable PGA = 5G
If one session is performing sort needing 5G workarea,
he will get workarea = 5G
If two sessions performing the same sort,
They will get 5/2 = 2.5 G each
and so on..

i.e. Sort area for each session is not fixed. Rather it changes dynamically.

– PGA_AGGREGATE_TARGET is a goal of an upper limit. It is not a value that is allocated when instance is started i.e. memory will be allocated only when there is a sorting operation which needs it. Else, it will be returned to the Operating System or transferred to SGA if AMM is enabled.

– HOW IS MEMORY ALLOCATED TO PGA – PRACTICAL IMPLEMENTATION

OVERVIEW

– Setup
– Disable AMM
– Create  3 test tables
. hr.small(9M),
. scott.medium(17M),
. sh.large (33M)
– Set workarea_size_policy = AUTO
– Check current value of  PGA_AGGREGATE_TARGET
– Set PGA_AGGREGATE_TARGET to current value
– To check that PGA memory allocated to the instance can exceed even the specified PGA_AGGREGATE_TARGET if fixed PGA requirement is more

– Check  current allocation/PGA used/overallocation count
– Create a  PL/SQL array requiring large fixed PGA
– Check that  PGA allocated > PGA_AGGREGATE_TARGET
PGA allocated > PGA in use
over allocation count increased

– To check that overallocation count increases if \fixed memory requirement of SQL work area is not satisfied by the extra PGA memory allocated to the instance  (PGA allocated > PGA in use)

– Execute queries on 3 test tables
– Check that overallocation count increases as specific requirement of the SQL workareas is not met by the extra memory allocated in PGA.

—————— IMPLEMENTATION ———————–

- SETUP

- Check if AMM enabled

SQL>CONN / AS SYSDBA

sho parameter memory_target

  – Disable AMM

ALTER SYSTEM SET MEMORY_TARGET = 0;

  – Create 3 test tables
. hr.small(9M),
. scott.medium(17M),
. sh.large (33M)

SQL>ALTER USER SCOTT IDENTIFIED BY tiger account unlock;

ALTER USER Sh IDENTIFIED BY sh account unlock;
GRANT SELECT_CATALOG_ROLE TO HR, SCOTT, SH;
GRANT EXECUTE ON DBMS_STATS TO HR, SCOTT, SH;

CONN HR/hr
drop table hr.small purge;
CREATE TABLE SMALL AS SELECT * FROM dba_OBJECTS;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘SMALL’);

col segment_name for a30
SELECT SEGMENT_NAME, BYTES/1024/1024 MB
FROM   USER_SEGMENTS
WHERE  SEGMENT_NAME = ‘SMALL';

SEGMENT_NAME                           MB
—————————— ———-
SMALL                                   9

CONN SCOTT/tiger

drop table scott.medium purge;
CREATE TABLE MEDIUM AS SELECT * FROM dba_OBJECTS;
INSERT INTO MEDIUM SELECT * FROM MEDIUM;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘MEDIUM’);

col segment_name for a30

SELECT SEGMENT_NAME, BYTES/1024/1024 MB
FROM   USER_SEGMENTS
WHERE  SEGMENT_NAME = ‘MEDIUM';
SEGMENT_NAME                           MB
—————————— ———-
MEDIUM                                 17

CONN Sh/sh

drop table sh.large purge;
CREATE TABLE LARGE AS SELECT * FROM dba_OBJECTS;
INSERT INTO LARGE SELECT * FROM LARGE;
INSERT INTO LARGE SELECT * FROM LARGE;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, ‘LARGE’);

col segment_name for a30
SELECT SEGMENT_NAME, BYTES/1024/1024 MB
FROM   USER_SEGMENTS
WHERE  SEGMENT_NAME = ‘LARGE';
SEGMENT_NAME                           MB
—————————— ———-
LARGE                                  33

 – Set WORKAREA_SIZE_POLICY = AUTO

SQL>CONN / AS SYSDBA
ALTER SYSTEM SET WORKAREA_SIZE_POLICY = AUTO;

- Check current value of PGA_AGGREGATE_TARGET

SHO PARAMETER PGA_AGGREGATE_TARGET

NAME                                 TYPE        VALUE
———————————— ———–
pga_aggregate_target                 big integer 496M

– Note that current PGA allocated <  PGA_AGGREGATE_TARGET

as currently only the PGA required for fixed areas has been allocated
COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB
FROM   V$PGASTAT
WHERE NAME IN (‘aggregate PGA target parameter’, ‘total PGA allocated’);

NAME                                  VALUE_MB
———————————– ———-
aggregate PGA target parameter             496
total PGA allocated                 56.3554688
- Set PGA_AGGREGATE_TARGET to current value
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 496M;
———————

– To check that PGA memory allocated to the instance can exceed even the specified PGA_AGGREGATE_TARGET if fixed PGA requirement is more
———————–

  – Check current allocation /  PGA used / overallocation count

– Note that
– PGA allocated (57M) > PGA in use (44M) i.e. some extra memory is allocated
– Overallocation count  = 0 because currently
the fixed memory requirement  < PGA_AGGREGATE_TARGET (496M)

COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB
FROM   V$PGASTAT
WHERE NAME IN (‘aggregate PGA target parameter’,
‘total PGA allocated’,
‘total PGA inuse’,
‘over allocation count’);

NAME                                  VALUE_MB
———————————– ———-
aggregate PGA target parameter             496
total PGA inuse                     44.9199219
total PGA allocated                 57.2304688
over allocation count                        0

   – Create a  PL/SQL array requiring large fixed PGA

SQL>create or replace package demo_pkg    As
type array is table of char(2000) index by binary_integer;
g_data array;
end;
/

- Fill up the chararray (a CHAR datatype is blank-padded so each of     these array elements is exactly 2,000 characters in length):

SQL> begin
for i in 1 .. 200000
loop
demo_pkg.g_data(i) := ‘x';
end loop;
end;
/

    – Check that 

      PGA allocated (509M)  > PGA_AGGREGATE_TARGET (496M)

       PGA allocated (509M) > PGA in use (493M)

over allocation count increased (0 to 2)

COL NAME FOR A35

SELECT NAME, VALUE/1024/1024 VALUE_MB
FROM   V$PGASTAT
WHERE NAME IN (‘aggregate PGA target parameter’,
‘total PGA allocated’,
‘total PGA inuse’)
union
SELECT NAME, VALUE
FROM   V$PGASTAT
WHERE NAME IN (‘over allocation count’);

NAME                                  VALUE_MB
———————————– ———-
aggregate PGA target parameter             496
over allocation count                        2
total PGA allocated                 509.044922
total PGA inuse                     493.198242

- To check that overallocation count increases if fixed memory requirement of SQL work area is not satisfied by the extra PGA memory allocated to the instance  (PGA allocated > PGA in use)

- Execute queries on 3 test tables

– Check that overallocation count increases as specific requirement of the SQL workareas is not met by the extra memory allocated in PGA.

HR>CONN HR/hr

set autotrace traceonly
select * from small order by 1,2,3,4,5,6;
SYS>COL NAME FOR A35
SELECT NAME, VALUE/1024/1024 VALUE_MB
FROM   V$PGASTAT
WHERE NAME IN (‘aggregate PGA target parameter’,
‘total PGA allocated’,
‘total PGA inuse’)
union
SELECT NAME, VALUE
FROM   V$PGASTAT
WHERE NAME IN (‘over allocation count’);

SCOTT>CONN scott/tiger

set autotrace traceonly
select * from medium order by 1,2,3,4,5,6;
SYS>COL NAME FOR A35
SELECT NAME, VALUE/1024/1024 VALUE_MB
FROM   V$PGASTAT
WHERE NAME IN (‘aggregate PGA target parameter’,
‘total PGA allocated’,
‘total PGA inuse’)
union
SELECT NAME, VALUE
FROM   V$PGASTAT
WHERE NAME IN (‘over allocation count’);

SH>CONN sh/sh

set autotrace traceonly
select * from large order by 1,2,3,4,5,6;

SYS>COL NAME FOR A35
SELECT NAME, VALUE/1024/1024 VALUE_MB
FROM   V$PGASTAT
WHERE NAME IN (‘aggregate PGA target parameter’,
‘total PGA allocated’,
‘total PGA inuse’)
union
SELECT NAME, VALUE
FROM   V$PGASTAT
WHERE NAME IN (‘over allocation count’);

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

— cleanup —

sql>conn / as sysdba

drop table hr.small purge;
drop table scott.medium purge;
drop table sh.large purge;

– enable AMM if it was initially enabled

SQL>ALTER SYSTEM SET MEMORY_TARGET= <inital value>;

In my next article (Tuning PGA Part-II)  in this series, I will discuss how to set an appropriate value for PGA_AGGREGATE_TARGET.

References:

Expert Oracle Database Architecture by Thomas Kyte
Oracle database operating system memory allocation management for PGA
https://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#CNCPT803
http://ksun-oracle.blogspot.in/2015/09/limit-pga-memory-usage.html

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

Related links:

Home

Tuning Index
Tuning PGA : Part – II
Tuning PGA : Part – III

                                                  ——————