Library cache is very important part of Oracle Shared pool. Shared Pool controls execution of SQL statements. Shared pool is divided into Data dictionary Cache and Library Cache. Main component of Library cache include Shared SQL Area and Private SQL Area (in case of shared server configuration).In Dedicated server configuration Private SQL area is created in PGA of server process. In oracle database each statement is associated with a shared area and a private area. Both are discussed in detail below.
Shared SQL Area
Shared SQL Area contains parse tree and execution plan of SQL cursors and PL/SQL programs. So executable form of SQL statements is available here which ca be reused. When a query is submitted to oracle server for execution, oracle checks if same query has been executed previously. If the parsed execution plan is found then this event is known as Library cache hit or soft parsing. If pared form of the statement is not found in the shared pool then new statement is parsed and its parsed version is stored in Shared SQL area. This is known as hard parse.
Oracle allocates memory from shared pool when a new statement is submitted. If required, oracle may deallocate memory from previous statements. As a result of this, deallocated statements shall require hard parsing when re-submitted. More resources are used to perform a hard parse. So it is very important to keep the size for shared pool large enough to avoid hard parsing.
Oracle compares the text of two statements to conclude that the statements are identical. Oracle matches each and every character, case and spacing. For examples, following two statement are not identical
Select * from emp;
Select * from Emp;
Above two statements will have different Shared SQL Area as case of the table name is different. So when possible statements should be written identical to avoid hard parsing.
As Library cache is kept inside Shared Pool so use SHARED_POOL_SIZE initialization parameter to increase the size of Shared Pool. It will indirectly increase memory available for Shared SQL Area.
Private SQL Area
Oracle server distinguishes when two users are executing same statement. To manage this every statement has its own private area which contains a copy of statement and other information related to that user.
If the connection is made by a dedicated server, Private SQL area is created in PGA. In case of shared server connection, Private SQL area is kept in SGA.