This article provides an overview of important concepts in Oracle architecture and how they can impact SQL performance tuning.
From login to soft parse
All users have to connect or login to Oracle. The listener is waiting on a predetermined port and ‘listens’ for traffic from the network. When a request is made to login, the listener process verifies the login credentials and if they pass, a dedicated user process (or if MTS…is assigned to the pool of available shared user processes) is started and a Program Global Area (PGA) is established. This PGA contains an area for any sorts the user will request as well as a cursor area for each SQL submitted. This cursor area is where the rows/result set from Oracle will be held while negotiating with the application on how to return the rows/result set.
The SQL is then passed to the library cache. Oracle performs a check sum on the SQL and arrives at a hash value from this check sum. This hash value is used to access a predetermined location in the library cache. Oracle checks to see if the SQL in that slot (if there is SQL in the slot) matches the SQL being submitted.
If it is a match, then the existing explain plan is used. This is known as a ‘Soft Parse.’
How a soft parse can cause problems
It is this process that causes problems for SQL with bind variables where the data might be skewed (maybe using histograms). Oracle ‘peeks’ at the bind variables once in this SQL and produces an explain plan. IF the data submitted would have produced a better explain plan, the better explain plan is not considered because Oracle will use the existing explain plan. This issue is discussed further in the next section of this book.
NOTE
This issue with bind variables has been addressed in Oracle11g+ and is called Adaptive Cursor Sharing.
About the hard parse
If the SQL is not found in the library cache, Oracle assigns it a location in the library cache, parses the SQL (syntax checking, remembering permissions, indexes, statistics if CBO), and then uses either the Rule-Based Optimizer (RBO) or the Cost-Based Optimizer (CBO) to build an execution plan for the SQL. This is known as a ‘Hard Parse’.
NOTE
An Execution Plan is the path that Oracle will use to retrieve the data or to perform the function requested by the SQL. The Explain Plan technically is the exposing of the Execution Plan. The author will use both terms synonymously.
NOTE
This article focuses on the CBO as the RBO has mostly been depreciated and should not be used in Oracle10g or higher. Some shops still have older applications that use the RBO so it will be discussed here but just briefly.
Executing the explain plan
Once the SQL is parsed and an explain plan arrived at, it is then executed. While the SQL is in the library cache, Oracle will now use this execution plan to process the request.
If the blocks requested (index or table blocks) are in the buffer cache, this is known as a logical read. If the blocks being requested are not in the buffer cache, then Oracle will need to read them from the tablespaces. This is a physical read.
Tuning is all about reducing physical reads
The whole tuning process evolves around reducing the physical reads required to process the SQL request. The fewer physical reads, the faster the SQL will execute.
Once the rows/result set is arrived at, it is then returned to the cursor area in the PGA and the application is notified that the result set is ready. There are various ways of retrieving these rows…various techniques are covered in the Advanced PL/SQL Programming Tips and Techniques course.
If the process has disconnected, the PMON will wake up and close the PGA and rollback any work that the SQL performed (releasing any locks, etc).
The MMON process performs a unique process. This process was introduced in Oracle9.2 and is used to monitor the library cache externally to the Oracle RDBMS, not using SQL code (recursive or dictionary SQL).
TIP
Oracle issues SQL to handle your requests, process your SQL as well as to perform a number of housekeeping tasks. This SQL is known as Dictionary SQL or Recursive SQL. Other than gathering dictionary statistics, there is little that can be done to tune it and no attempt should be made to do so.
MMON watches for DML and tracks how many rows are being changed. This is used for the STALE option for DBMS_STATS. MMON can also watch for indexes being used.
The Result Cache is a new memory structure in Oracle11g that holds the returning rows or function result from repeatedly executed SQL and functions. If instructed to do so (hints in SQL, code in functions), Oracle11g will return a previous result set instead of executing the SQL or function again. There are options to check the accessed table for changes, etc.
Leave a Reply