Oracle OCP-hard parsing the process of obtaining the memory lock in the Shared Pool, oracleocp

Oracle OCP-hard parsing the process of obtaining the memory lock in the Shared Pool, oracleocp

Reprinted please indicate the source: Http://blog.csdn.net/guoyjoe/article/details/38684819

 

1. Obtain the library cache Latch.

(1) The text of the SQL statement is not found in the name handle of the parent cursor: select * from gyj_t1 where id = 1;

2. Release library cache Latch

3. Obtain the shared pool Latch.
(1) Search FreeList idle Chunk
(2) Search for chunks that can be covered in LRU
(3) Search for R-FreeList idle Chunk
(4) If there is no space, direct ORA-04031 Error
4. Release the shared pool Latch.

5. Obtain the library cache Latch again (oracle 11g: library cache mutex)

6. Get the library cache lock Latch (oracle 11g: library cache mutex)

7. Release library cache Latch (oracle 11g: library cache mutex)

8. Get library cache lock ---- protect the name handle of the parent cursor (SQL statement text, address of the parent cursor H0)

9. Release the library cache lock Latch

10. Obtain the library cache Pin Latch.

11. Get library cache Pin -- protect the H0 of the parent cursor (the H0 of the parent cursor mainly records the sub-game subject name handle)

12. Release the library cache Pin Latch (oracle 11g: cursor mutex)

13. Get library cache lock Latch

14. Get library cache lock ---- protect the sub-game subject name (the H0 address of the sub-cursor)

15. Release library cache lock Latch

16. Obtain the library cache Pin Latch

17. Get library cache Pin -- protect the sub-Game's H0 (the Sub-Game's H0 mainly records the environment, statistics, and variable binding)

18. Release the library cache Pin Latch

19. Obtain the library cache Pin Latch

20. Get the library cache Pin -- protect the H6 of the child game subject (the H6 of the child game subject mainly records the Execution Plan)

21. Release the library cache Pin Latch

 

How can I set the oracle shared pool and java pool, and the size of these values?

Comrade, setting the size of these values is a complicated problem, especially in earlier versions of ORACLE.
We recommend that you add the version and operating system information of oracle to your questions.

Let's look back at the memory size allocation.
We will consider general scenarios.

The first thing to consider is how much memory your host has,
Consider whether your host is dedicated to databases.

If your host is dedicated to databases,
Generally, the shared pool + buffer + JAVA pool + large pool should not exceed 55% of the host memory.
In this case, you must note whether your operating system is 64-bit or 32-bit. In either case, the upper limit of memory that ORACLE can use is completely different.

Let's talk about the allocation of large pools in the JAVA pool of the Shared Pool buffer.
The allocation of these pools should be configured according to the application scenarios of your database.
If it is a data warehouse database, the buffer should be set as large as possible.
If it is a transaction-type database, the shared pool can be allocated more evenly.

For details about the value size, please go to the Internet and refer to a large amount of relevant information for further determination.
If you do not have time to study in depth, You can first follow the typical configuration and adjust the percentage to 80% (dedicated database server ).

During normal operation, you must adjust and optimize the database monitoring report (such as statspack and awr.
Not a one-day task.

What is the size of the Oracle OCP certificate?

Similar to A4 paper, which is about 1 cm shorter than A4 paper