Oracle9i Performance Tuning

1
Oracle9i Performance Tuning

• Chapter 4
• Tuning the Shared Pool Memory

2
Chapter Objectives

• Understand the role of the Shared Pool Memory
• Learn Shared Pool Advice terms
• Learn terms for the internal structures of the
  Shared Pool Memory
• Learn the role of the library cache and data
  dictionary cache
• Configure the Shared Pool Memory
• Use the Shared Pool Size Advice feature

3
Chapter Objectives (continued)

• Diagnose the Shared Pool Memory configuration
• Look inside the Shared Pool Memory using
  performance dynamic views
• Flush the Shared Pool Memory
• Pin objects in Shared Pool Memory
• Understand and configure the Large Pool
• Understand and configure Java Pool

4
Oracle Architecture
5
Data Classification

• User and system data is stored and retrieved in a
  data file and cached in the buffer cache
• Transaction data consists of all the DMLs and
  DDLs issued against the database
• Data is cached in the log buffer and ultimately
  stored in the redo log files
• SQL statements and PL/SQL blocks data consists of
  the SQL and PL/SQL code issued against the
  database
• It is cached in shared pool memory

6
Data Classification (continued)

• Database objects definition data is retrieved
  from system data files and cached in the shared
  pool
• Database objects definition data contains
  metadata about the database object structures and
  privileges
• Java code data consists of Java-related code,
  which is loaded and executed by different
  sessions
• It is cached in the Java pool
• Buffered data can be from any of the above
  classifications, but is buffered in the large
  pool

7
Data Classification (continued)
8
Processing SQL Statements
9
Processing PL/SQL Blocks
10
SQL Statement Processing Tasks
11
Shared Pool Memory Performance Terms

• Hard parse When a statement is submitted and is
  not found in memory, a hard parse is performed
• Hard parses use considerably more resources than
  soft parses
• Soft parse Occurs when a SQL statement is found
  in memory and can be reused
• Execute call A call to execute a SQL statement.
• If the statement is already parsed, a soft parse
  occurs, but if the statement has been aged out
  from memory, a hard parse occurs
• Parse call A call to parse a SQL statement
  because it was not found in memory
• Bind variable The process of passing a variable
  from the calling environment such as SQLPlus,
  Oracle Forms, Oracle Reports, and other Oracle
  development tools

12
Shared Pool Memory Performance Terms

• Hash function An algorithm used to convert the
  submitted SQL statement to a hash value, which
  can be compared to hash values stored in memory
  to determine if the statement is already in
  memory
• Reloads The number of times a cached SQL
  statement was reloaded or reparsed because the
  statement was aged out
• Invalidations The number of times a cached SQL
  statement became invalid and could not be shared
  because there was a modification to the database
  objects used by the statement
• Library cache hit Synonymous with soft parse
• Library cache miss Synonymous with hard parse

13
Shared Pool Memory Internal Structure

• Library cache A major memory space of the shared
  pool memory used to cache SQL statements, PL/SQL
  blocks, and other object code used by the
  application
• Data dictionary cache A major memory space of
  the shared pool memory used to store database
  object definitions temporarily
• Character set structure A space in memory used
  to store the character set used by the Oracle
  instance
• Locks structures Data structures used to
  synchronize and coordinate access to database
  objects
• Latches structures Data structures used as
  mechanisms to protect memory while it is in use
• Enqueues structures Data structures used for
  serial access to the database in a Real
  Application Cluster (RAC) or in a standalone
  instance

14
Shared Pool Memory Internal Structure
15
VLIBRARY_CACHE_MEMORY
16
VLIBRARY_CACHE_MEMORY (continued)
17
Shared Pool Size Advice

• Provides advisory statistics for the shared pool
  memory
• Use the dynamic performance view
  VSHARED_POOL_ADVICE

18
VLIBRARYCACHE
19
Library Cache Hit Ratio
20
Library Cache Diagnosis
21
Library Cache Diagnosis (continued)

• GETHITRATIO value
• PINHITRATIO value
• RELOADS Ratio

22
Library Cache Diagnosis (continued)

• INVALIDATIONS Ratio
• RELOADS to PINS Ratio

23
Data Dictionary Diagnosis
24
Data Dictionary Diagnosis (continued)
25
Shared Pool Memory Usage
26
Shared Pool Memory Usage (continued)
27
Shared Pool Free Memory
28
Shared Pool Free Memory (continued)
29
Shared Pool Free Memory (continued)
30
Using Oracle Enterprise Manager
31
TopSQL
32
Looking Inside Shared Pool MemoryUsing
VDB_OBJECT_CACHE
33
Looking Inside Shared Pool MemoryUsing
VDB_OBJECT_CACHE (continued)
34
Looking Inside Shared Pool Memory

• VOBJECT_USAGE
• VSQL
• VSQLAREA
• VSQLTEXT
• VSQLTEXT_WITH_NEWLINES

35
Managing Shared Pool Memory
Flushing the Shared Pool Memory
Pinning Objects
36
CURSOR_SHARING Parameter
37
CURSOR_SPACE_FOR_TIME Parameter
38
Large Pool Memory

• The large pool memory is an optional structure of
  the SGA
• It is configured by the LARGE_POOL_SIZE parameter
• It is used as a temporary placeholder for special
  programs and functionality as follows
• Recovery Manager (RMAN)
• Shared server, formerly known as Multithreaded
  server (MTS)
• PARALLEL_AUTOMATIC_TUNING option
• Parallel query

39
Java Pool

• The Java pool is an optional structure of the SGA
• It is configured by the JAVA_POOL_SIZE parameter
• It is used to cache executed Java programs, Java
  classes, and other Java-related objects

40
Summary

• The shared pool memory is an important structure
  of the SGA used to cache SQL statements, PL/SQL
  blocks, and other memory objects to reduce CPU
  consumption and I/O trips to data files
• SQL statements are processed in three steps
• The statements are parsed for syntax validity,
  user privileges are verified, and a plan for
  retrieving data is created
• The plan created in the first step is executed
• The data is retrieved and submitted to the user
• The parsing process comprises six main tasks that
  ensure the validity of the statement as well as
  the validity of the selected columns and
  determines the best method for retrieving the
  data

41
Summary (continued)

• The library cache is a major structure of the
  shared pool and is used to store application code
  that is in use
• The library cache is divided into pieces of
  memory structures called namespaces
• You can look at the library cache namespaces by
  displaying the contents of the VLIBRARYCACHE
  performance dynamic view
• You can use VSESSION_OBJECT_CACHE to get a full
  statistics report on cached objects for the
  current session