Up to now, we talked about
- Selectivity / Cardinality
- Skewed Data / Histograms
- Displaying Execution Plans with Explain Plan, dbms_xplan package, autotrace feature
- Introduced bind variables
I believe, that for most of you it was quite familiar..
But I also stronlgy believe, that we should talk about the process of executing SQL query and components of Oracle Database that take part in it…
Shared Pool
Oracle Database architekcture is quite complicated …
But for you, we can start with this simple figure
You connect with eg. sqlplus from your PC. Sqlplus is User Process. On server side, Shadow Process (called also Server Process) is created, and is serving all the requests for your connection. Session is path/connection between User Process and its Shadow Process. Shadow Process that serves you, can allocate some memory from PGA (Private Global Area) for sorting , join, bitmap merge operations – so called Working Area. It also keeps there values of your variables, session informations and so on …
Server process works directly on preallocated memory structures called SGA, which keeps a lot of things.. But most important areas in SGA are Buffer Cache – cached blocks from datafiles and Shared Pool – place for all dictionary information and … INTERNAL REPRESENTATION OF SQL QUERIES that You issue…
In Shared Pool there is a special place called Library Cache…Querying for SQL_TEXT, for Execution Plans, for statistics from query execution , is querying of Library Cache..
Why it is so important to understand thing like that ? .. It’s simple.. to make you understand, when DBA tells you “you are overwhelming Shared Pool” .. Or you do to many Hard Parses.
SQL Execution process and Library Cache
Internally Yours SQL commands are called Cursors. The simple figure below shows in very basic way processing of SQL Query
SQL Statement Processing Phases:
Execute and Fetch
• Execute phase:
– Executes the SQL statement
– Performs necessary I/O and sorts for data manipulation language (DML) statements
• Fetch phase:
– Retrieves rows for a query (from block images stored in Buffer Cache)
– Sorts for queries when needed (in PGA)
– Uses an array fetch mechanism
SQL> show arraysize arraysize 15 SQL> set arraysize 50
When you send SQL, it must be parsed. Dealing with it in simple way we can say that:
Parse phase checks:
- Syntax
- Semantics and privileges
Types of parses:
- Soft parse:
- Searches for the statement in the shared pool
- Hard parse:
- Merges view definitions and subqueries
- Determines execution plan basing on objects, system statistics and instance parameters
During this operation, some memory must be allocated from Library Cache where this binary representation of your SQL will be stored among with child cursors (cursors for the same query, but with different execution plan or just created when main Cursor cannot be shared) , execution plan , cardinality statistics feedback and so on …
Allocating memory must be protected by Latches , not to allow allocation of the same memory for different sessions/cursors.
As you can see parsing is quite resource and time consuming operation..So the most efficient way to work with database is to share already parsed cursors among sessions that issue the same Query, not to allow parsing to happen.. And keeping them in memory as long as possible.
Remeber…parsing uses memory, cpu cycles, and all the operation on memory structrures demands Latches, and Mutexes (another lightweight serialization mechanizm) for search and execution operations…And these are serialization points..So smaller shared pool, less serialization and less resources usage..
So , when application runs for a while and users are issuing queries , there are many Cursors in Library Cache…
So how it works ?
- For each query, SQL Hash value is calculated. Hash value is not unique, and Cursors Contexes with the same Hash value are stored in the same “Hash Chain“
- Session can cache handles to Cursors in its private memory
- If query is issued, Hash value is calculated, Server Process looks for handles to open Cursor in PGA (if it finds it, it executes it), if it is not an open Cursor, it looks in session “cache” for handle to Cursor – just to omit searching through Hash Chains..If it finds it, it directly open Cursor and executes it (since 12c it is not accounted for “Soft Parse“)
- If there was no cached Cursor handle, it searches for existing Cursor through Hash Chains (take into account many mechanisms that take place here, like protecting Hash Chain from being modified with eg. new Cursor entry, when we look through it or ageing it out). When it finds already parsed Cursor it uses it
- If no Cursor was found in Library Cache, all the steps, from allocating memory , through checking semantic, syntax, privileges, object definitions , statistics and generating executioin plan has to be done
Hope…Now You know, why sharing Cursors is so important and resource consuming..
In some systems with Huge Shared Pool, internal work on allocating/deallocating memory, latching, searching for Cursors causes the systems to Hang or being not responsive !
So , now it is time to come back, to what is interesting for You directly … Fun with parsing, cursor sharing, observing what execution plans are created and why … So Part 3 will give you some information about Bind Variables Peeking, Adaptive Cursor Sharing, Result Set Cache.
Below some summarization of mechanisms and components we talked over
The shared pool is managed by an LRU algorithm.
• New objects require memory allocations.
• Re-creatable objects are aged out of the cache.
• Objects are made up of chunks of memory.
• A memory allocation is a whole chunk.
• A chunk is contiguous.
• LRU operations are protected by a latch or mutex.
LIBRARY CACHE
• Stores complex object metadata associated with cursors
• Stores SQL statements and PL/SQL blocks that are to be
shared by users
• Allows cursor sharing
Latch and Mutex
• A latch is a memory object that allows:
– Sharing of a resource without corruption
– Exclusive access for update and allocation
– Shared access for reads
• A mutual exclusion object (mutex) allows:
– Sharing of a resource without corruption
– Shared access for reads
– Exclusive access for update
– Each object can have its own mutex
Latch wait events:
– latch latchname
Mutex wait events:
– cursor:mutex – cursor:mutex S
– cursor:pin X
– cursor:pin S
– cursor:pin S wait on X
Views for monitoring Shared Pool and Cursors
V$SGASTAT
V$SGAINFO
V$LIBRARYCACHE
V$LIBRARY_CACHE_MEMORY
V$SHARED POOL RESERVED
V$SQLSTATS
V$SQL
V$SQLAREA (V$SQL Group By SQL_ID)
V$SQLTEXT
V$DB_OBJECT_CACHE
