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

This article shows you the sample analysis of the common table KEEP to memory in Oracle tuning, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The performance of the data migration is affected, so you need to keep the tables from the old database to memory in the new database, using the following method.

The new library sets db_keep_cache_size to the appropriate value, which cannot be smaller than the size of the table that requires keep.

Check the table information in the old library that requires keep:

Select s.owner

S.segment_name

S.partition_name

S.bytes / 1024 / 1024 as "size (m)"

From dba_segments s

Where owner = 'XXX'

And segment_name in

(select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')

Order by 4 desc

Query the total size of the keep table in the old library:

Select sum (s.bytes / 1024 / 1024 / 1024) as "total keep size (G)"

From dba_segments s

Where segment_name in

(select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')

Generate a keep script:

Select 'alter table XXX.' | | s.segment_name | |' storage (buffer_pool keep);'as' script

From dba_segments s

Where owner = 'XXX'

And segment_name in

(select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')

Keep table into memory:

Select 'alter table XXX.' | | s.segment_name | |' cache;' as "script 2"

From dba_segments s

Where owner = 'XXX'

And segment_name in

(select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')

The last two scripts are integrated:

Select 'alter table XXX.' | | s.segment_name | |' storage (buffer_pool keep) cache;' as "script

From dba_segments s

Where owner = 'XXX'

And segment_name in

(select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')

The output of the above three scripts can be executed in plsql.

Appendix: commands related to cache to memory

-- Table cache

Alter table. Storage (buffer_pool keep)

To see which tables are placed in the cache does not mean that the table has been cached

Select table_name from dba_tables where buffer_pool='keep'

-- query whether the table has been cached

Select table_name,cache,buffer_pool from user_TABLES where cache like'% Y'

Tables that have been added to the KEEP area want to move out of the cache, using the

Alter table table_name nocache

-- query the following table of the current user

Select table_name,cache,buffer_pool from user_TABLES

-- cache method for segment of normal LOB type

Alter table T2 modify lob (c2) (storage (buffer_pool keep) cache)

-- canceling caching

Alter table test modify lob (address) (storage (buffer_pool keep) nocache)

-- query segment

Select segment_name,segment_type,buffer_pool from user_segments

-- cache methods for objects based on CLOB types

Alter table lob1 modify lob (c1.xmldata) (storage (buffer_pool keep) cache)

-- query the large fields in all tables under the user

Select column_name,segment_name from user_lobs

-- cancel table caching

Alter table XXX storage (buffer_pool default)

Ps:

View the remaining size of the keep space:

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'

View the size of the keep space:

Select component,current_size from v$sga_dynamic_components

Where component='KEEP buffer cache'

The above is the example analysis of the common table KEEP to memory in Oracle tuning. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.