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

This article mainly explains "how to fix objects to the shared pool in oracle". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to fix objects to a shared pool by oracle".

Generally speaking, PL/SQL is very large. To load such objects into a shared pool, you need to find a large amount of contiguous memory space. If you load it continuously, it is bound to age out a lot of hot memory out of the shared pool. This is bound to affect database performance.

Therefore, fixing the commonly used PL/SQL or SQL cursors to the shared pool can solve the above problems. Including possible ORA-04031 errors.

1. Pin PL/SQL to shared pool

SYS@cdbtest1 (MING) > exec sys.dbms_shared_pool.keep ('ming.p_hello','P')

PL/SQL procedure successfully completed.

You can check whether it is fixed in the shared pool by using the keep field of the following sql.

SELECT * FROM v$db_object_cache where owner='MING' and TYPE = 'PROCEDURE'

The way to fix it:

SYS@cdbtest1 (MING) > exec sys.dbms_shared_pool.unkeep ('ming.p_hello','P')

PL/SQL procedure successfully completed.

two。 The method of fixing sql cursor to shared Pool

For fixed sql cursors, you need to understand the following:

a. Fixed cursors need to get the address and hash_value of the cursor

b. For multiple child cursors under a parent cursor, their address and hash_value are the same

MING@ming (MING) > variable p number

MING@ming (MING) > exec: P: = 10

PL/SQL procedure successfully completed.

MING@ming (MING) > select / * test2*/ count (*) from T1 where a set line 100

MING@ming (MING) > select sql_id,sql_text from v$sql where sql_text like'% test2%'

SQL_ID SQL_TEXT

-

1anf7skpasz1a select / * test2*/ count (*) from T1 where a select sql_id,address,hash_value,version_count from v$sqlarea where sql_id='1anf7skpasz1a'

SQL_ID ADDRESS HASH_VALUE VERSION_COUNT

--

1anf7skpasz1a 000000007267DA48 2863430698 1

Fixed cursor

Exec sys.dbms_shared_pool.keep ('000000007267DA48 2863430698)

Unfix the cursor

Exec sys.dbms_shared_pool.unkeep ('000000007267DA48 2863430698)

At this point, I believe you have a deeper understanding of "how to fix objects to the shared pool in oracle". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.