Optimization, where the Oracle optimizer evaluates how to process the statement with the Parsing, which includes syntactic and semantic verification of SQL statements and checking of object privileges to perform the actions. As you may recall from Chapter 21, all SQL statements undergo the following steps during their processing: The library cache holds the parsed and executable versions of SQL and PL/SQL code. Let’s look at these two important components of the shared pool in detail. Similarly, when you decrease the total shared pool memory, both components will decrease in size.
If you increase the total shared pool memory size, both components will increase in some ratio that Oracle determines. You can’t allocate or decrease memory specifically for one of these components. The shared pool consists of two major areas: the library cache and the data dictionary cache. Sometimes the entire database seems to hang. If there are excessive latches the result might be a higher wait time and a slower response time. The general increase in shared pool waits and other waits observed during a severe slowdown of the production database is the result of SQL code that fails to use bind variables.Īs the number of users increases, so does the demand on shared pool memory and latches, which are internal locks for memory areas. Higher CPU usage because of unnecessary parsing of SQL code.Greater I/O because executable forms of SQL aren’t present in the shared pool.Increased latch contention with the resulting demand for more CPU resources.An improperly tuned shared pool leads to problems such as the following: Proper shared pool configuration leads to dramatic improvements in performance. In addition to caching program code, the shared pool caches the data dictionary information that Oracle needs to refer to often during the course of program execution. The shared pool is a part of the SGA that holds almost all the necessary elements for execution of the SQL statements and PL/SQL programs. The most important part of memory resource when considering the production system is “shared pool”.
0 kommentar(er)
