OracleSource

 Shared SQL areas
 SQL statement processing phases
 Sharing cursors
 SQL coding standards

Shared SQL Areas
• The shared pool is part of the System Global Area (SGA). The shared pool contains the dictionary cache and the library cache. Shared SQL areas are part of the library cache. The library cache also contains metadata.
• The shared pool is automatically maintained by an aging mechanism. This aging mechanism uses a least recently used (LRU) algorithm to determine what has been there longest and ages it out when space is required.
• The database administrator (DBA) can change the available dictionary and shared SQL areas space by modifying the SHARED_POOL_SIZE initialization parameter. The DBA would do this as part of an overall database tuning effort, which would be subject to various constraints.

Cursors
• Inside the shared SQL area, each SQL statement is parsed in its own part, known as a context area or cursor.
• Each cursor holds the following information:
• The parsed statement (static, dynamic, and recursive SQL, plus program units such as procedures and database triggers)
• The execution plan
• A list of referenced objects
• If two users issue the same SQL statement, then they will use the same cursor.
• The statement is reparsed if the representation in the shared pool is invalid. This happens, for example, if a data definition language (DDL) statement such as ALTER TABLE was used on one of the objects in the statement, or if a dependent table is analyzed.


Processing Phases
• The four most important phases in SQL statement processing are parsing, binding, executing, and fetching.
• The reverse arrows indicate processing scenarios; for example, FETCH—(RE)BIND—EXECUTE—FETCH.
• The FETCH phase only applies to queries.

Note :
• There is no real REBIND phase; the same pointer is simply used for a reEXECUTE. The value found at the corresponding memory address is used; Oracle9i uses a “bind by reference” technique.
• Three more phases are missing from this slide: DEFINE, DESCRIBE, and PARALLELIZE.
• DEFINE and DESCRIBE are not very important for tuning. PARALLELIZE is not mentioned here.

SQL Statement Processing Phases – Parse

PARSE Phase

 Searches for the statement in the shared pool
 Checks syntax
 Checks semantics and privileges
 Merges view definitions and subqueries
 Determines execution plan


The Oracle9i Server onwards does the following:
• Searches for the statement in the shared pool
• Checks the statement syntax, given the grammar and specifications of the SQL language
• Checks the semantics, ensuring that objects referenced in the SQL statement are valid and satisfy security constraints
• Determines whether the process issuing the statement has appropriate privileges to execute it
• Transforms a SQL statement on a view into an equivalent SQL statement on its underlying definition, and attempts to simplify a statement with a subquery by rewriting it into a join
• Determines and stores the execution plan, or uses an existing execution plan, if possible


BIND Phase

• The Oracle Server checks the statement for references of bind variables.
• The Oracle Server assigns or reassigns a value to each variable.

Note: This phase order implies that the Oracle Server does not know bind variable values when optimizing a statement. This enables a fast rebind-execute without the need for reparsing, thus saving time and memory; a disadvantage is that it is impossible for the optimizer to estimate predicate selectivity. This will be discussed in more detail in the “Collecting Statistics” lesson.


EXECUTE Phase

 Applies the execution plan
 Performs necessary I/O and sorts for data manipulation language (DML) statements


• The Oracle9i Server applies the parse tree to the data buffers.
• Multiple users can share the same parse tree.
• The Oracle9i Server performs physical reads or logical reads/writes for DML statements and also sorts the data when needed.


FETCH Phase

 Retrieves rows for a query
 Sorts for queries when needed
 Uses an array fetch mechanism

• The Oracle9i Server retrieves rows for a SELECT statement during the FETCH phase. Each fetch typically retrieves multiple rows, using an array fetch.
• Each Oracle tool offers its own ways of influencing the array size; in SQL*Plus you do so by using the ARRAYSIZE setting:
SQL> show arraysize
arraysize 15
SQL> set arraysize 1
With this setting, SQL*Plus will process one row at a time. The default value is 15.


Sharing Cursors

When a SQL statement is found in the shared SQL area, then the PARSE phase is curtailed and the existing cursor is used.

Benefits

 Reduces parsing and saves time
 Dynamically adjusts memory to the SQL being executed
 Improves memory usage


Shared Cursor Requirements

Cursors can be shared only by SQL statements that have the following identical elements:
 Text
• Uppercase and lowercase
• White space (spaces, tabs, carriage returns)
• Comments
 Referenced objects
 Bind variable data types

• Only identical SQL statements can use the same cursor.
• The text of the SQL statements must be exactly the same, including case, spaces, tabs, carriage returns, and comments.
• The objects referenced in the SQL statements must resolve to the same objects in the database.
• The types of the bind variables used in the SQL statements must be the same.
Note: Before sending SQL statements to the Oracle9i Server, most Oracle tools (such as PL/SQL, the precompilers, and Oracle Developer) preprocess SQL statements to make them as identical as possible by removing comments, squeezing white space, and converting to uppercase or lowercase. SQL*Plus, however, sends SQL statements to the Oracle9i Server in the same format as they are entered.

Writing SQL to Share Cursors

If the case or the amount of white space is different, then the statements are not identical.

SQL> select * from employees where emp_id = 70727;

SQL> select * from EMPLOYEES where EMP_ID = 70727;

If the objects belong to different users, then the statements are not identical.

SQL> select * from employees where EMP_ID = 70727;

SQL> select * from employees where EMP_ID = 70727;


SQL statements must be identical to be able to share cursors. Note that sharing statements is unimportant in a decision support system (DSS) environment, because most statements will be different anyway.
• Identical Case : The first two examples in the slide are not identical. Notice the case difference for the table and column names. Because of this case difference, the statements are not identical and thus cannot share a SQL area.

• Identical Objects : Even when two statements look identical, if the objects actually refer to different database objects, then the two statements are not identical. In the last two examples in the slide, the statements are issued by two different users who each have their own EMPLOYEES table. Thus the statements are not identical and cannot share a SQL area.


Bind Variables

Suppose you enter the following two statements:

select * from employees where emp_id = :c

select * from employees where emp_id = :d

Both statements are translated into:

select * from employees where emp_id = :b1


If two bind variables have different data types, then the statements are not identical. If the bind variable data types match but their names are not identical, as in the example above, there is no problem, because bind variables will be renamed internally. The first variable is always called :b1, the second is :b2, and so on.

Note that the script actually uses the PL/SQL environment of SQL*Plus. SQL*Plus itself does not translate bind variable references in SQL statements.
Note that 8.1.6 comes with a powerful (but also dangerous) feature to force cursor sharing, allowing you to set CURSOR_SHARING=FORCE at the session or instance level. This will replace all literals in your SQL statements by system generated bind variables.

Writing SQL to Share Cursors

Create generic code using the following:
 Stored procedures and packages
 Database triggers
 Referenced Oracle Developer Forms procedures
 Any other library routines and procedures

Develop coding conventions for SQL statements in ad hoc queries, SQL scripts, and Oracle Call Interface (OCI) calls.
Using Generic Shared Code
• Write and store procedures that can be shared across applications.
• Use database triggers.
• Write referenced triggers and procedures when using Oracle Developer.
• Write library routines and procedures in other environments.


Writing to Format Standards
• Develop format standards for all statements, including those in PL/SQL code.
• Develop rules for use of uppercase and lowercase.
• Develop rules for use of white space (spaces, tabs, carriage returns).
• Develop rules for use of comments, preferably keeping them out of the SQL statements themselves.
• Use the same names to refer to identical database objects.

Follow SQL coding standards:
 Case
 White space
 Comments
 Object references
 Bind variables

Monitoring Shared Cursors

 V$LIBRARYCACHE provides general information about the library cache.
 Information about individual SQL statements is contained in the following views:
• V$SQLTEXT
• V$SQLTEXT_WITH_NEWLINES
• V$SQL_BIND_DATA
• V$SQL
• V$SQLAREA


When there is no room to parse a statement in the shared SQL area, the oldest cursor is closed and its space is reused. If the original statement is needed again, the server must parse it again. The shared pool should be large enough to keep the number of statements that are parsed more than once to a minimum.
You can monitor your system to see how often the server cannot find a statement in memory. You can use Oracle Enterprise Manager (OEM) or query the appropriate data dictionary views: V$LIBRARYCACHE and V$SQLAREA.

Querying V$ views is usually a DBA task. Tell the participants that they may not have access to these data dictionary views on their home systems. The following pages give examples of V$LIBRARYCACHE and V$SQLAREA usage only.
Another interesting view is V$DB_OBJECT_CACHE.
Note that V$SQL lists all copies of identical SQL statements (child cursors) on a separate line, whereas V$SQLAREA groups those statements together.


The V$LIBRARYCACHE View

NAMESPACE The name of the library cache area
GETS Total number of requests (lookups)
GETHITS The number of times that an object's handle
was found in memory
GETHITRATIO The ratio of GETHITS to GETS
PINS The number of times that a PIN was requested
PINHITS The number of times that all the pieces of the
object were found in memory
PINHITRATIO The ratio of PINHITS to PINS
RELOADS The number of library cache misses

This view holds information about library cache management. Values for PINHITRATIO and GETHITRATIO close to 1 indicate a good library cache performance. Note that this slide does not show all columns of the V$LIBRARYCACHE view.
The V$LIBRARYCACHE view is used in the query below to check on the amount of caching.
SQL> select gethitratio, pinhitratio
2 from v$librarycache
3 where namespace = 'SQL AREA';
GETHITRATIO PINHITRATIO
----------- -----------
0.94 0.95

Instead of the PINHITRATIO, you could also use sum(reloads)/sum(pins) ratio to determine when you should increase the size of the shared pool.

Shared Cursor Use

SQL_TEXT Text of the SQL statement
VERSION_COUNT Number of versions of this cursor
LOADS Number of times the cursor has been loaded
INVALIDATIONS Number of times the contents have been invalidated
PARSE_CALLS Number of times a user has called this cursor
SORTS Number of sorts performed by the statement
COMMAND_TYPE Command type
PARSING_USER_ID Parsing user ID (SYS = 0)


The V$SQLAREA view holds information about all shared cursors in the cache.
VERSION COUNT >1 Indicates that the same text is used by different users on their own version of a table
LOADS > 1 Indicates cursor reloads after aging out or cursor invalidation
COMMAND_TYPE 1: CREATE TABLE
2: INSERT
3: SELECT
6: UPDATE
7: DELETE
Note: Only the most important columns of the V$SQLAREA view are listed above.
V$SQL provides more detailed information than V$SQLAREA, because it does not group by the SQL statement text.
For a full list of command type values, see description of the V$SESSION view in Oracle9i Server Reference.

Monitoring Shared Cursor Use

 One load per statement is ideal.
 One load per version/invalidation is acceptable.
 More than one load per version indicates a potential benefit from increasing the shared pool size.

In the best case scenario, there should be one version of each statement that is never invalidated or aged out.
If the number of loads is significantly higher than the sum of the versions and invalidations, especially if the number of loads is similar to the number of calls, then the cursor has probably been reloaded because of aging, and the system may benefit from increasing the size of the shared pool.

The statement above excludes information about recursive SQL (parsing user SYS: user_id=0) and displays only SELECT commands (command type 3).
There are two versions of the first statement, probably because they reference two different EMPLOYEES objects. However, each version has been loaded only once. The statement has been issued three times (PARSE_CALLS).
There is only one version of the second statement, but it has been loaded twice, having been invalidated once (probably by some DDL on the table or related index).

Note: Oracle SQL Analyze, a component of the Oracle Enterprise Manager (OEM) Tuning Pack, offers an excellent graphical interface on top of V$SQLAREA. This diagnostic tool is introduced in the “Oracle SQL Analyze” lesson.
V$SQL would distinguish between the two versions of the first SQL statement.

SQL> select sql_text, version_count, loads
2 , invalidations, parse_calls, sorts
3 from v$sqlarea
4 where parsing_user_id > 0
5 and command_type = 3
6 order by sql_text;


version invali parse
sql_text count loads dations calls sorts
-------------------- ------- ------- ---------- ------- --------
select * 2 2 0 3 0
from employees
where EMP_ID = 70727

select * 1 2 1 4 0
from employees
where emp_id = 70727