|
Real Application Clusters
|
Throughout my analysis of Real Application Clusters, an index-related process
has always been a bottleneck and as a result, scalability decreased to less than 1.
I've been thinking if there is any method of tuning an index.
Luckily, some subscribers sent us their ideas. I'll pick up one of their ideas,
and in this issue, I mainly focus on index partitioning.
Comments from a subscriber
-quote-
In Vol.142, you added NODE_NO column in index key. But I think
you don't need to add NODE_NO. I think it may be a good idea if you partition
the index with NODE_NO instead.
This may prevent access contention among instances at insertion process
and range scan should operate properly.
(B*Tree exists for the number of partitions and range scan operates
per B*Tree.)
-end quote-
First, I prepare a testing environment.
TEST1. Current environment
TEST2. NODE_NO column is added to a table and to an index.
NODE_NO is actually added to the header column of the index.
TEST3. Partition the table with NODE_NO and create INS_DATE
index as local index.
I prepare 200,000 of data for a month and simulate a process for searching the data registered
today.
Now, let's check the index configuration of each environment.
I execute analyze index aaaa validate structure.
*****************************************************************************
[TEST1.]
SQL> select * from index_stats where name = 'IDX_LOGTBL1_1_1';
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS(1) . .
-------- ------ --------------- ------- -------
3 512 IDX_LOGTBL1_1_1 200000 478 . .
[TEST2.]
SQL> select * from index_stats where name = 'IDX_LOGTBL1_2_1';
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS(2) . .
-------- ------ --------------- ------- -------
3 896 IDX_LOGTBL1_2_1 200000 814 . .
[TEST3.]
SQL> select * from index_stats where name = 'IDX_LOGTBL1_3_1';
HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS(3-1) . .
-------- ------ --------------- -------------------- ------- -------
2 256 IDX_LOGTBL1_3_1 IDX_LOGTBL1_3_1_PT00 100000 239 . .
SQL> select * from index_stats where name = 'IDX_LOGTBL1_3_1';
HEIGHT BLOCKS NAME PARTITION_NAME LF_ROWS LF_BLKS(3-2) . .
-------- ------ --------------- -------------------- ------- -------
2 256 IDX_LOGTBL1_3_1 IDX_LOGTBL1_3_1_PT01 100000 239 . .
*****************************************************************************
|
I see a difference in number of blocks. In TEST2 environment, I have added NODE_NO column
to the index and that explains the increase in number of blocks. (2)
In addition to the increase in number of blocks, the number of blocks where
range scan is executed may also increase.
But still, blocks are too many. This may be a good topic to cover in future issues.
In TEST3 environment, on the other hand, total number of leaf blocks in each partition
(3-1) (3-2) is same as the number of leaf blocks in TEST1 environment.
In this case, range scan should not cause the increase in number of read blocks.
Now, I actually execute a scan in each environment.
To tell you the truth, I have analyzed current topic in RAC Linux but I didn't see
any difference in EPS value. (EPS=Executions Per Second)
So, I view SQL TRACE to check if there is any difference in EPS value.
Let's check the SQL TRACE.
********************************************************************************
[TEST1.]
select action_cd from logtbl1_1 where ins_date > sysdate -1 and ins_date <= sysdate
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 448 0.04 0.03 0 1042 0 6666
Rows Row Source Operation
------- ---------------------------------------------------
6666 TABLE ACCESS BY INDEX ROWID LOGTBL1_1 (cr=1042 r=0 w=0 time=23913 us)
6666 INDEX RANGE SCAN IDX_LOGTBL1_1 (cr=464 r=0 w=0 time=10146 us)
(object id 9755)
********************************************************************************
[TEST2.]
select /*+ index_ss(logtbl1_2 idx_logtbl1_2) */ action_cd
from logtbl1_2 where ins_date 'gt; sysdate -1 and ins_date <= sysdate
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 448 0.11 0.07 0 1954 0 6666
Rows Row Source Operation
------- ---------------------------------------------------
6666 FILTER (cr=1954 r=0 w=0 time=67470 us)
6666 TABLE ACCESS BY INDEX ROWID LOGTBL1_2 (cr=1954 r=0 w=0 time=61333 us)
6666 INDEX SKIP SCAN IDX_LOGTBL1_2 (cr=1377 r=0 w=0 time=44602 us)
(object id 9757)
********************************************************************************
[TEST3.]
select sction_cd from logtbl1_3 where ins_date > sysdate -1 and ins_date <= sysdate
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 448 0.05 0.04 0 1026 0 6666
Rows Row Source Operation
------- ---------------------------------------------------
6666 FILTER (cr=1026 r=0 w=0 time=34667 us)
6666 PARTITION RANGE ALL PARTITION: 1 3 (cr=1026 r=0 w=0 time=28877 us)
6666 TABLE ACCESS BY LOCAL INDEX ROWID LOGTBL1_3 PARTITION: 1 3
(cr=1026 r=0 w=0 time=23322 us)
6666 INDEX RANGE SCAN IDX_LOGTBL1_3 PARTITION: 1 3
(cr=450 r=0 w=0 time=10312 us)(object id 9762)
********************************************************************************
|
Check query column. The least number of blocks is read in TEST3.
cpu and elapse are also decreased in number.
Why?
Check an access path cr value as follows.
***************************************************
TEST1. TEST2. TEST3.
--------------------------------------------
INDEX 464(4) 1377(5) 450(6)
TABLE 578 577 576
TOTAL 1042 1954 1026
CPU 0.04 0.11 0.05
ELAPS 0.03 0.07 0.04
***************************************************
|
In this analysis, I take range scan as precondition because my objective
is to scan a log table to analyze. That is why index range scan is executed in every
test environment.
The most remarkable difference among test environments is the number of blocks. (4), (5), and (6)
If unique index scan is executed, result may come out differently.
As I have mentioned earlier, the number of blocks increases in TEST2 because
index contains large number of blocks. I think there is an alternative cause for this.
Index skip scan may cause the increase in the number of blocks.
Index partitioning may help improving performance in RAC up to the same level
as a single instance environment. If no updates are made to logs,
GCS and GES regarding RAC may not affect much.
I think index partitioning is quite useful. If you have a chance, try this in your
environment.
New topic will start next week.
Tadashi Yamashita
|
|
Ora! Ora! Oracle