|
Real Application Clusters
|
Last time, I mainly talked about an index in order to perform a tuning to
improve scalability.
Following are my idea to improve scalability:
1. I let the index be used per instance to prevent contention among instances.
2. I prevent contention of index leaf blocks caused by heavy transactions.
Reduce the index contention among instances
INS_DATE was indexed, which was required for satisfying specifications of
search engine system checking the transaction status. This column is used
to summarize the amount of data newly inserted or updated and the date (time) when the user
performs operations.
As any user can add or update the data, the latest index block encounters contention
when some user attempts to insert a log record.
I start with a simple method to prevent index contention.
I think the index contention can be resolved by adding instance number for each instance
before the date column. B-TREE index consists of branch blocks and leaf blocks.
Thus, if the header of the column of the index has a column that identifies an
instance, index blocks (i.e. branch blocks and leaf blocks) are considered
unique to each instance.
If index scan is performed in each instance, the scan may extend among instances.
But, wait such as ITL waits at insertion process can be avoided.
Following is a configuration of the column.
*****************************************************
SQL> desc logtbl1
Name NULL? Type
----------------------- -------- -------------------
NODE_NO NUMBER(2)
INS_DATE TIMESTAMP(6)
CONNECT_ID CHAR(64)
ACTION_CD NUMBER(1)
USER_ID CHAR(50)
NOTE VARCHAR2(200)
SQL> select INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION
2 from user_ind_columns where table_name = 'LOGTBL1';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION
------------ ------------ ------------ ---------------
IDX_LOGTBL1 LOGTBL1 NODE_NO 1
IDX_LOGTBL1 LOGTBL1 INS_DATE 2
*****************************************************
|
Reduce contention of index leaf block
Another method is to reduce the contention within instances.
This method can be applied to any environments besides RAC.
That is, Reverse key index. Even though data are inserted in ascending
order, the last digits are not necessarily inserted in the same way.
Reverse key index can prevent the contention of the same block which
occurs at insertion process.
I'm wondering which method I should cover in this issue.
"Purpose of index" is a key.
In this issue, I focus on the block contention that occurs when data are inserted.
Index is supposed to function to improve the efficiency of scan. If scan is not performed
efficiently, it is a waste of time to create an index.
What is the purpose of log-related index? Is this to determine the following?
1. The date and the amount of which the data are newly inserted.
2. The number of updates performed
3. The time when the operation performed
Now, you know what this is; index range scan. We cannot use reverse key index.
If I use reverse key index, time data are inserted to index leaf block randomly.
As I cannot determine the range, index range scan is not useful.
It can perform as an application but it overloads system, which is of course the last thing
we want to do.
This time, I add a column node_no that identifies an instance. With this column,
I should be able to prevent the contention of the same block occurring when data are inserted.
As I have changed the index column, I also need to change the application to scan.
Well, I perform an index scan anyway to check the current execution plan.
******************************************************
(Table (or index) where node_no is added)
SQL> select * from logtbl1
2 where ins_date > sysdate -1 and ins_date <= sysdate;
6666 rows selected
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'LOGTBL1'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4524 consistent gets
0 physical reads
0 redo size
926564 bytes sent via SQL*Net to client
5387 bytes received via SQL*Net from client
446 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6666 rows processed
******************************************************
|
As you see, index is not used. Following is the execution plan of existing table (or index).
Index is used and consistent gets is four times less than that of the execution plan above.
******************************************************
(Existing table (or index))
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'LOGTBL1'
2 1 INDEX (RANGE SCAN) OF 'IDX_LOGTBL1' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1007 consistent gets
0 physical reads
(skipped)
******************************************************
|
I need to let the index be used. To do so, I add a hint clause.
Result is as follows.
******************************************************
SQL> select /*+ index(logtbl1 idx_logtbl1) */ *
2 from logtbl1
3 where ins_date > sysdate -1 and ins_date <= sysdate;
6666 rows selected
Execution plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=832
Bytes=215488)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'LOGTBL1' (Cost=29
Card=832 Bytes=215488)
3 2 INDEX (FULL SCAN) OF 'IDX_LOGTBL1' (NON-UNIQUE)
(Cost=26 Card=832)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1624 consistent gets
0 physical reads
0 redo size
926564 bytes sent via SQL*Net to client
5387 bytes received via SQL*Net from client
446 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6666 rows processed
******************************************************
|
consistent gets decreases remarkably, which looks good.
BUT, take a close look at the result. Full index scan is performed.
Why? If the compound index contains a column at the head of the column
which is not included in scan condition, it is impossible to find the data
unless full index scan is performed.
That's it for today.
Tadashi Yamashita
|
|
Ora! Ora! Oracle