primary key with a multi-column index


DB version: Oracle 12.1

SQL> create table system.test (col1 number,col2 number, col3 number);

Table created.

SQL> create index system.idx1 on system.test (col2,col1);

Index created.

SQL> alter table system.test ADD PRIMARY KEY (col1) using index;

Table altered.

SQL> select count(*) from dba_indexes where table_name=’TEST’ and owner=’SYSTEM’;

COUNT(*)
———-
2
—————————————————————————————

But if we do the same steps but just change the index order
SQL> create index system.idx1 on system.test (col1,col2);

Index created.

we get total no of indexes for table=1

SQL> select count(*) from dba_indexes where table_name=’TEST’ and owner=’SYSTEM’;

COUNT(*)
———-
1

why oracle doesn’t use the existing index in first case ?

Answer is:  Multiple reasons, To use a multi-column index for a primary key, the key should be the leading index columns. Remember, Oracle support only “Index Skip Scan” for non-leading columns. ROWID mapping will be always based on leading columns. (Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.)

For more information about index skip scan please follow: http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#51553

 

 

Advertisements

Index rebuilding in parallel, what could go wrong?


I have one table tab1 as mentioned below

SQL> desc tab1;
Name Null? Type
—————————————– ——– —————————-
ID NUMBER
C1 VARCHAR2(100)
C2 VARCHAR2(200)

SQL> create index idx_tab1 on tab1(ID);

Index created.

SQL> alter index idx_tab1 rebuild parallel 4;

Index altered.

SQL> select degree from dba_indexes where index_name=’IDX_TAB1′;

DEGREE
—————————————-
4

NOTE: Index rebuilding in parallel changes the degree permanently .SO NEXT time  it will use the index with parallel degree of 4, which could lead to high CPU utilization. So we can change it to avoid any unexpected problem..

SQL> alter index idx_tab1 parallel 1;

Index altered.

SQL> select degree from dba_indexes where index_name=’IDX_TAB1′;

DEGREE
—————————————-
1

 

“Touch Count” for Buffer Cache


The paper is written by Craig A. Shallahamer (criag@orapub.com). Thanks to the author for sharing such an interestng information.

Author has explained the algorithm for managing buffers in Oracle buffer cache. Now obviously all the buffers cannot stay in buffer cache because of the limited size of cache. So buffers which are older or non-popular should go out of the buffer cache. The algorithm is all about the criteria for buffer cache to stay or leave buffer cache.

Initially with older release, Oracle started with LRU (Least Recently Used) algorithm. Conceptually LRU list is nothing but a list of pointers to buffer blocks. When a buffer block is touched, the pointer moves towards the most recently end of LRU chain. Remember that its the pointer which moves up the chain, buffer blocks are never moved. The general idea is to keep the more popular blocks in buffer cache and ask less popular block to leave.

But this algorithm was having some issues. For example a full table scan !! This will get all the buffers into the buffer cache making other important buffers to leave the cache. For example if the buffer cache is having 500 blocks and full table scan is getting 600 blocks in buffer cache, all the popular blocks will go away.

To overcome this problem, Oracle came with a modified LRU algorithem. This modified LRU algorithm takes care of full table scan by keep the buffers of full table scan at the LRU end of LRU chain and also it will allow only limited number of blocks to be put in LRU end of LRU chain. This will avoid flooding buffer cache with huge number of buffers from full table scan. Also along with this algorithm, Oracle implemented multiple buffer pools – KEEP BUFFER POOL, RECYCLE BUFFER POOL and DEFAULT BUFFER POOL. As you must be knowing, the data which is used frequently and should remain in buffer cache should be placed in KEEP BUFFER POOL, buffer that should not stay for longer in buffer cache should be placed in RECYCLE BUFFER POOL and if we dont specifically mention any buffer pool duing table creation, it will go in DEFAULT POOL.

With this background lets move towards understanding the touch count algorithm.

Buffer cache Management

Before understanding the complex logic, we should understand the needs for touch count algorithm. Why does Oracle needs to have such a complex alogrithm in place? We have already seen that modified LRU algorithm takes care of full table scan. But if you think again we have another issue – Large index range scan? Imaging large number of index leaf blocks flowing into the buffer cache. This issue wont be addressed by modified LRU.

Also with growing sizes for buffer cache, better and better performance requirements and more control Oracle introduced touch count algorithm.

For smooth and effective buffer cache operation, a very fast and very flexible algorithm must be implemented that essentially forces every buffer to seriously earn the right to remain in buffer cache. Touch count algorithm makes it very difficult for buffer to simply remain in buffer cache. It’s almost cruel how many hurdles a buffer must continually jump to simply remain in buffer cache.

At the core of touch count algorithm are the harsh requirements placed on each buffer to not only remain in cache, but to remain in MRU end of LRU list.Conceptually touch count algorithm does this by assigning each buffer a counter. Each time a block is touched its counter is incremented. The value of this counter is used to assign popularity to the blocks. But its not staright forward. Keep reading !!

Touch Count Algotrithm

Oracle does lot of nasty things to make it very difficult for a buffer to remain in buffer cache. Lets understand each concepts

Mid-point Insertion

Each LRU is divided into two basic areas or region; a hot region and a cold region. All buffers in hot region is called hot buffers and all buffers in cold region is called cold buffers.There is a midpoint marker between hot region and cold region. This mid point marker moves in order to make sure that correct number of buffers are present in each regions. This mid pointer is not associated with any buffer.

By default Oracle divides LRU equally between hot and cold region. That means, 50% of the buffers will be in hot region and 50% will be in cold region. However we can change the default setting by changing a hidden parameter _db_percent_hot_default. If we increase this parameter then buffers in hot region will increase, that is buffers above mid point wil increase.

When a server process reads a block from disk into the buffer cache, it is placed in the middle of LRU chain, that is, between hot region and cold region. This is called mid-point insertion and is the fundamental concept in touch count algorithm. These buffer has to earn there way into hot region by increasing the touch count. Frequent access to these block will increase there touch count and block will move in the hot region.

Touch Count Increment

Now we know that when a server process reads a new block from disk into the buffer cache, where exactly its going to put the block. Lets now consider how the tocuh count increases for blocks.

Therotically when ever a buffer is touched, its touch count should increase. but practically Oracle does not let that happen. Think of a situation where some processes needs some blocks in buffer very frequently for some period, and after that period that block is not so required. In that case Oracle process might access the block so frequently that within a second its touch count will grow huge and it will become eligible to be placed in hot region. Not only that, it might become eligible to stay in hot region. Since this block wont be used later, we dont want this block to stay just because it was used for initial period of time.

To overcome this problem, Oracle only allows buffer’s touch count to be incremented, at most, once every 3 seconds. Again, thi 3 scond is default timing by Oracle, which can be changed using hidden parameter _db_aging_touch_time.

When a touch count is incremented buffer pointer should move. But movement of buffer pointer is independent of touch count increment. Also for any activity in memory area oracle needs a latch for assuring cache consistency. But there is an exception here !! For updating touch count, Oracle does not use latch and buffer block can be modified while touch count is getting incremented. But more interesting is that, two processes may increment the touch count to same value, and when this happens Oracle assures the worst that could happen is the touch count is not actually incremented every time a buffer is touched and that no cache corruption will result.

Buffer Movement

As mentioned previously, when a buffer is brought into the buffer cache its placed in the middle of hot region and cold region. Unlike LRU algorithm, touch count algorithm will not move the block to hot region just because its touched. Yes, its touch count will probably be incremented.

When a server process if looking for the free buffer to place the content of datafile block into buffer cache or when the DBWR is looking for dirty buffer to write to disk, and if the buffer’s touch count is observed to be greater then 2, its moved to MRU end of LRU list. This default threshold of block movement is controlled by hidden parameter _db_aging_hot_criteria.

Oracle touch count implementation is tough on buffers !! When buffer is moved to MRU end of LRU list, its touch count is set to zero. So this buffer which is newly brought into the hot region has hit touch count reset to 0 immediately. For this block to remain in the hot region its touch count should be incremented significantly. So if this block is really accessed frequenctly, its touch count will increase automatically and it will servive in hot region.

Hot and Cold Movement

If a buffer is moved from cold region to hot region, the mid point marker has to shift to accomodate correct number of blocks in hot a cold region. So 1 block from hot region will be forced into the cold region which is least frequently used and which belongs to LRU end of LRU list. When this happens the touch count of that block is reset to 1. Even if the buffer’s touch count is 250, after moving to cold region its touch count is reset to 1. This threshold crossing touch count reset value is controlled by the instance parameter _db_aging_cool_count. This means the buffer must be all touched again to make its move to the hot region. This is how the blocks are moved and managed in buffer cache.

For more information on performance tuning buffer cache using the above parameters check the reference section below.

References:

ALL ABOUT ORACLE’S TOUCH COUNT ALGORITHM – Criag A. Shallahamer (Version 4a, January 5, 2004)

ORA-01919: role ‘PLUSTRACE’ does not exist


The autotrace provides instantaneous feedback including the returned rows, execution plan, and statistics. The user doesn’t need to be concerned about trace file locations and formatting since the output is displayed instantly on the screen. This is very important data that can be used to tune the SQL statement.

Problem: 

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> GRANT PLUSTRACE TO scott;
GRANT PLUSTRACE TO scott
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist

solution:

Run plustrce.sql through SYS schema if it did not run before
<pre>
@$ORACLE_HOME/sqlplus/admin/plustrce.sql
</pre>

Grant PLUSTRACE to <USER WHO WANTS TO RUN AUTOTRACE>

Also PLAN_TABLE must exist in the USER’s Schema ,if user want to do autotrace with explain plan . For creation of plan_table ,UTLXPLAN.sql have to be in user’s schema.

SQL> @/u01/app/oracle/product/11.1.0/db/sqlplus/admin/plustrce.sql
This script in turn will execute below sql statements:
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role ‘PLUSTRACE’ does not exist
SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL>
SQL> set echo off

For any specific SQL query you can see SQL execution performance statistics. In SQL*Plus you can issue the “set autotrace” command and then issue your SQL statement:

SQL> conn scott/scott
Connected.
SQL> set autot TRACE STAT
SQL> select * from t1;

14 rows selected.
Statistics
———————————————————-
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed

Library cache lock


The library cache lock event is also associated with library cache concurrency.
A session must acquire a library cache lock on an object handle to prevent other sessions from accessing it at the same time,
or to maintain a dependency for a long time, or to locate an object in the library cache.
Both locks and pins are provided to access objects in the library cache.

Locks manage concurrency between processes, whereas pins manage cache coherence. In order to access an object, a process must first lock the object handle, and then pin the object heap itself.
Requests for both locks and pins will wait until granted. This is a possible source of contention, because there is no NOWAIT request mode.

By acquiring a lock on the object handle, a process can prevent other processes from accessing the object, or even finding out what type it is.
It can even maintain a dependency on an object without preventing other processes from accessing the object.
Acquiring a lock is also the only way to locate an object in cache—a process locates and locks an object in a single operation.

If the process wants to actually examine or modify the object, then it must acquire a pin on the object itself (after acquiring a lock on the handle).
Pinning the object causes information about the object and its heaps to be loaded into memory if they were not already there.
This information is guaranteed to remain in memory at least until the pin is released. Locks and pins are externalized in X$KGLLK and X$KGLPN, respectively.

Oracle needs Library Cache Lock and Library Cache Pin for the compilation/Parsing of Package/Procedure/Function/View.
This is to ensure that no one is using the object during compilation/parsing as we are making some changes to the definition of the object
and need to drop and recreate the object with the new definition.

When a SQL statement is hard parsed by a session, the session has to acquire a library cache lock so that the other sessions can be prevented from accessing or modifying the same object.
If this event accumulates a lot of time, then it may indicate that the shared pool is too small or is being flushed regularly.
Otherwise, it indicates that database object definitions are being changed regularly.
Apart from hard parsing, if the session wants to change the definition of the object specified in the SQL or do any modifications,
then it has to acquire a library cache lock along with the library cache pin.
It is pinning because it needs the dictionary information to be loaded in the memory to access the same to modify/change the object.

You may use X$KGLLK to find the session which holds the lock:

SELECT SID,
USERNAME,
TERMINAL,
PROGRAM
FROM V$SESSION
WHERE SADDR IN
(SELECT KGLLKSES
FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS
(SELECT LOCK_B.KGLLKHDL
FROM X$KGLLK LOCK_B
WHERE     KGLLKSES = ‘C0000006D1AFE550’ –saddr of blocked session from v$session
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0));
References: Metalink Note 444560.1

DBMS_STATS.SET_*_PREFS procedure in Oracle 11gR2


In previous Database releases you had to use the DBMS_STATS.SET_PARM procedure to change the default value for the parameters used by the DBMS_STATS.GATHER_*_STATS procedures. The scope of any changes that were made was all subsequent operations. In Oracle Database 11g, the DBMS_STATS.SET_PARM procedure has been deprecated and it has been replaced with a set of procedures that allow you to set a preference for each parameter at a table, schema, database, and Global level. These new procedures are called DBMS_STATS.SET_*_PREFS and offer a much finer granularity of control.

However there has been some confusion around which procedure you should use when and what the hierarchy is among these procedures. In this post we hope to clear up the confusion. Lets start by looking at the list of parameters you can change using the DBMS_STAT.SET_*_PREFS procedures.

AUTOSTATS_TARGET (SET_GLOBAL_PREFS only)
CASCADE
DEGREE
ESTIMATE_PERCENT
METHOD_OPT
NO_INVALIDATE
GRANULARITY
PUBLISH
INCREMENTAL
STALE_PERCENT

As mentioned above there are four DBMS_STATS.SET_*_PREFS procedures.

SET_TABLE_PREFS

SET_SCHEMA_PREFS

SET_DATABASE_PREFS

SET_GLOBAL_PREFS
The DBMS_STATS.SET_TABLE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for the specified table only.

The DBMS_STATS.SET_SCHEMA_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the existing objects in the specified schema. This procedure actually calls DBMS_STATS.SET_TABLE_PREFS for each of the tables in the specified schema. Since it uses DBMS_STATS.SET_TABLE_PREFS calling this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF values for all parameters.

The DBMS_STATS.SET_DATABASE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the user defined schemas in the database. This procedure actually calls DBMS_STATS.SET_TABLE_PREFS for each of the tables in each of the user defined schemas. Since it uses DBMS_STATS.SET_TABLE_PREFS this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREF values for all parameters. It is also possible to include the Oracle owned schemas (sys, system, etc) by setting the ADD_SYS parameter to TRUE.

The DBMS_STATS.SET_GLOBAL_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless there is a table preference set or the parameter is explicitly set in the DBMS_STATS.GATHER_*_STATS command. Changes made by this procedure will affect any new objects created after it has been run as new objects will pick up the GLOBAL_PREF values for all parameters.

With GLOBAL_PREFS it is also possible to set a default value for one additional parameter, called AUTOSTAT_TARGET. This additional parameter controls what objects the automatic statistic gathering job (that runs in the nightly maintenance window) will look after. The possible values for this parameter are ALL,ORACLE, and AUTO. ALL means the automatic statistics gathering job will gather statistics on all objects in the database. ORACLE means that the automatic statistics gathering job will only gather statistics for Oracle owned schemas (sys, sytem, etc) Finally AUTO (the default) means Oracle will decide what objects to gather statistics on. Currently AUTO and ALL behave the same.

In summary, DBMS_STATS obeys the following hierarchy for parameter values, parameters values set in the DBMS_STAT.GATHER*_STATS command over rules everything. If the parameter has not been set in the command we check for a table level preference. If there is no table preference set we use the global preference.