Analysis of Oracle sharing pool

The Oracle Shared Pool (Share Pool) belongs to SGA and is composed of library cache and data words.
Data dictionary cache. Database cache www.2cto.com Oracle import to database the purpose of High-speed cache is to share SQL and PL/SQL code. When the server process executes SQL and PL/SQL, it first enters the cache to check whether the same SQL exists,
If any, the compiled SQL and execution plan will not be used for subsequent compilation. Oracle compares the bodies of the two SQL statements to determine whether the two SQL statements are the same,
You must bind the variable. For example, select * from emp where sal> 100 and select * from emp where sal> 101 are different,
When a variable is bound, Oracle considers that select * from emp where sal> & v_sal is the same even if the v_sal value is different. Oracle uses LRU queues and algorithms to manage the high-speed cache of databases. recently used SQL statements are placed at the beginning of the queue, while unused SQL statements are placed at the end of the queue for a long time, when the database cache requires memory space and there is no idle memory space, the SQL statements in the memory at the end of the team will be cleared and placed into the latest SQL statements, and the Team will first point to the next memory segment. Oracle does not provide a method to directly modify the cache size of a database. You can only modify the cache size of a database by modifying the size of the Shared Pool. Www.2cto.com data dictionary high-speed cache when Oracle executes SQL, the definitions and permissions of related data files, tables, indexes, columns, users, and other data objects are stored in the data dictionary cache. After that, Oracle extracts the same data from the data dictionary cache. Oracle does not provide a direct method to modify the data dictionary cache size. You can only modify the size of the Shared Pool to indirectly modify the data dictionary cache size. Modify the size of the Shared Pool 1 alter system set shared_pool_size = XXXM the size of the Shared Pool is limited by the size of the SGA_MAX_SIZE parameter. Author LiJIaming