Shulou(Shulou.com)05/31 Report--

This article mainly introduces the process of "Oracle Rac uses keep pool to solve the problem of index high clustering factor". In daily operation, I believe many people have doubts in the process of solving index high clustering factor problem by Oracle Rac using keep pool. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for everyone to answer the doubt that "Oracle Rac uses keep pool to solve the problem of index high clustering factor". Next, please follow the editor to study!

What is the clustering factor?

Tables in Oracle use heap tables most often, which are unordered, while indexes are ordered. The degree of disorder between the two is the clustering factor. Generally speaking, the ideal clustering factor should be close to the number of blocks on the table, while the poor clustering factor is very close to the number of rows in the table. After the index clustering factor is high, the cost of using the index will be on the high side, resulting in a full table scan of the execution plan that should be followed by the index, which does great harm to the efficiency of sql.

How to solve the problem of high index clustering factor?

1. Rebuild the table in indexed order. However, the disadvantages of this method should be carefully considered, if there are other indexes on the table, then this behavior of rebuilding the table may increase the clustering factor of other indexes. It is equivalent to tearing down the east wall to make up for the west wall, so it is necessary to make a detailed analysis of the sql and discuss the fetching logic with the development, and have a clear idea of the importance of the index, which may have a negative impact on other indexes, but the use of other indexes is relatively less, and the decline in efficiency is acceptable, so this method is feasible.

two。 Keep pool is used to optimize the index with high clustering factor. This method puts the index with high clustering factor into keep pool, which avoids the problem of clustering factor of the index. Because the problem of order and disorder naturally does not exist in memory. Here is the process of using keep pool to solve this problem in rac.

Note that the following behavior does not solve the clustering factor problem:

1. Rebuild the index

2.move or shrink table

3. Data pump import and export

Sometimes some people will say that it works a little bit, and if it has a positive effect, it can only be because the fragmentation rate of the table or index is too high. The problem of clustering factor still exists.

Actual combat process: setting keep pool in rac

Two index sizes with high clustering factor

15:45:11 SQL > select sum (bytes / 1024 / 1024) M_size

15:45:31 2 from dba_segments

15:45:31 3 where segment_name in

15:45:31 4 ('PK_LG_FSPE_YEJIBIAOXIAN',' IDX_FSPE_YEJIBIAOXIAN_CHGDATE')

M_SIZE

-

two hundred and eight

1 row selected.

Elapsed: 00.19 set the keep pool size:

15:16:31 SQL > alter system set db_keep_cache_size=250m scope=both sid='*'

Alter system set db_keep_cache_size=250m scope=both sid='*'

*

ERROR at line 1:

ORA-32018: parameter cannot be modified in memory on another instance Elapsed: 00:00:00.00

15:17:15 SQL >! oerr ora 32018

32018, 00000, "parameter cannot be modified in memory on another instance"

/ / * Cause: Parameter adjustment can take a very long time

/ / * Action: Modify the parameter individually on each instance using

/ / the SID clause of the alter system command

You can't use sid='*'.

Alter system set db_keep_cache_size=250m scope=both sid='ECAC2'

Alter system set db_keep_cache_size=250m scope=both sid='ECAC1'

Query keep pool size

Show parameter db_keep_cache_size

15:20:11 SQL > show parameter db_keep_cache_size

NAME TYPE VALUE

-

Db_keep_cache_size big integer 512M

Select component,current_size from v$sga_dynamic_components

Where component='KEEP buffer cache'

Although 250m is set, 512m is actually allocated. This is good, because with the addition and deletion of the table, the fragmentation rate of the index will inevitably become higher and higher, if it is 250m, there may not be a situation in which the index can not be fully accommodated.

Cache index into keep pool

Alter / * source only*/ index EMDB.PK_LG_FSPE_YEJIBIAOXIAN storage (buffer_pool keep)

Alter / * source only*/ index EMDB.IDX_FSPE_YEJIBIAOXIAN_CHGDATE storage (buffer_pool keep)

Read the index block into keep pool, and both nodes execute

Select / * + index (IDX_FSPE_YEJIBIAOXIAN_CHGDATE,t1) * / count (CHGDATE) from EMDB.FSPE_YEJIBIAOXIAN T1

Select / * + index (PK_LG_FSPE_YEJIBIAOXIAN,t1) * / count (CLFCODE) from EMDB.FSPE_YEJIBIAOXIAN T1; view the execution plan at this time:

15:26:37 SQL > set line 200

15:26:39 SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 3009983618

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 8 | 2082 (3) | 00:00:01 |

| | 1 | SORT AGGREGATE | | 1 | 8 |

| | 2 | TABLE ACCESS INMEMORY FULL | FSPE_YEJIBIAOXIAN | 3986K | 30m | 2082 (3) | 00:00:01 |

-

9 rows selected.

Elapsed: 00:00:00.02

Query the remaining size of keep pool

Select p.namemena.cnumnumrepl "total buffers", a.anum_repl "free buffers" from x$kcbwds a, v$buffer_pool p

Where a.set_id=p.LO_SETID and P. nameplate KEEP'

At this point, the study on "the process of Oracle Rac using keep pool to solve the problem of index high clustering factor" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.