It holds the executable PL/SQL code and SQL statements, as well as information regarding the data dictionary tables.
Whenever a new user requests for a data through SQL session, Oracle first determines whether that SQL is already in Shared Pool or not. If Oracle finds in Shared pool then it just simply do Soft Parse to that SQL but when it doesn't find ,It has to do Hard Parse.
Also Shared Pool should be size properly.It should be neither over-sized nor under-sized.
Both the I/O rates and the CPU usage will diminish when the database uses its shared pool memory effectively.
Shared Pool components:
Library Cache:
All application code has to be parsed first and executed later. Oracle stores all compiled SQL statements in the library cache component of the shared pool. The library cache component of the shared pool memory is shared by all users of the database. Every time when a SQL statement is fired,Oracle first checks the library cache to see if there is an already parsed and ready-to-execute form of the statement in there. If there is, Oracle uses soft parse otherwise it will do hard parse. Hard parsing involves the use of critical system resources, such as processing power, and internal Oracle structures, such as latches; so we should try to avoid its occurrence. High hard-parse counts will lead to resource contention and a consequent slowdown of the database when responding to user requests.
Data Dictionary Cache
It contains object definitions, usernames, roles, privileges, and other such information. When a segment of SQL code run, Oracle first determines whether user has the privilege to perform the planned operation, for that It checks the data dictionary cache.
Whenever a new user requests for a data through SQL session, Oracle first determines whether that SQL is already in Shared Pool or not. If Oracle finds in Shared pool then it just simply do Soft Parse to that SQL but when it doesn't find ,It has to do Hard Parse.
Also Shared Pool should be size properly.It should be neither over-sized nor under-sized.
Both the I/O rates and the CPU usage will diminish when the database uses its shared pool memory effectively.
Shared Pool components:
Library Cache:
All application code has to be parsed first and executed later. Oracle stores all compiled SQL statements in the library cache component of the shared pool. The library cache component of the shared pool memory is shared by all users of the database. Every time when a SQL statement is fired,Oracle first checks the library cache to see if there is an already parsed and ready-to-execute form of the statement in there. If there is, Oracle uses soft parse otherwise it will do hard parse. Hard parsing involves the use of critical system resources, such as processing power, and internal Oracle structures, such as latches; so we should try to avoid its occurrence. High hard-parse counts will lead to resource contention and a consequent slowdown of the database when responding to user requests.
Data Dictionary Cache
It contains object definitions, usernames, roles, privileges, and other such information. When a segment of SQL code run, Oracle first determines whether user has the privilege to perform the planned operation, for that It checks the data dictionary cache.
No comments:
Post a Comment