Oracle9 i Performance Tuning

1. Oracle9iPerformance 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 Chapter 4: Tuning the Shared Pool Memory

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 Chapter 4: Tuning the Shared Pool Memory

4. Oracle Architecture Chapter 4: Tuning the Shared Pool Memory

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 Chapter 4: Tuning the 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 Chapter 4: Tuning the Shared Pool Memory

7. Data Classification (continued) Chapter 4: Tuning the Shared Pool Memory

8. Processing SQL Statements Chapter 4: Tuning the Shared Pool Memory

9. Processing PL/SQL Blocks Chapter 4: Tuning the Shared Pool Memory

10. SQL Statement Processing Tasks Chapter 4: Tuning the Shared Pool Memory

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 SQL*Plus, Oracle Forms, Oracle Reports, and other Oracle development tools Chapter 4: Tuning the Shared Pool Memory

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 Chapter 4: Tuning the Shared Pool Memory

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 Chapter 4: Tuning the Shared Pool Memory

14. Shared Pool Memory Internal Structure Chapter 4: Tuning the Shared Pool Memory

15. V$LIBRARY_CACHE_MEMORY Chapter 4: Tuning the Shared Pool Memory

16. V$LIBRARY_CACHE_MEMORY (continued) Chapter 4: Tuning the Shared Pool Memory

17. Shared Pool Size Advice • Provides advisory statistics for the shared pool memory • Use the dynamic performance view V$SHARED_POOL_ADVICE Chapter 4: Tuning the Shared Pool Memory

18. V$LIBRARYCACHE Chapter 4: Tuning the Shared Pool Memory

19. Library Cache Hit Ratio Chapter 4: Tuning the Shared Pool Memory

20. Library Cache Diagnosis Chapter 4: Tuning the Shared Pool Memory

21. Library Cache Diagnosis (continued) GETHITRATIO value: PINHITRATIO value: RELOADS Ratio: Chapter 4: Tuning the Shared Pool Memory

22. Library Cache Diagnosis (continued) • INVALIDATIONS Ratio: • RELOADS to PINS Ratio: Chapter 4: Tuning the Shared Pool Memory

23. Data Dictionary Diagnosis Chapter 4: Tuning the Shared Pool Memory

24. Data Dictionary Diagnosis (continued) Chapter 4: Tuning the Shared Pool Memory

25. Shared Pool Memory Usage Chapter 4: Tuning the Shared Pool Memory

26. Shared Pool Memory Usage (continued) Chapter 4: Tuning the Shared Pool Memory

27. Shared Pool Free Memory Chapter 4: Tuning the Shared Pool Memory

28. Shared Pool Free Memory (continued) Chapter 4: Tuning the Shared Pool Memory

29. Shared Pool Free Memory (continued) Chapter 4: Tuning the Shared Pool Memory

30. Using Oracle Enterprise Manager Chapter 4: Tuning the Shared Pool Memory

31. TopSQL Chapter 4: Tuning the Shared Pool Memory

32. Looking Inside Shared Pool MemoryUsing V$DB_OBJECT_CACHE Chapter 4: Tuning the Shared Pool Memory

33. Looking Inside Shared Pool MemoryUsing V$DB_OBJECT_CACHE (continued) Chapter 4: Tuning the Shared Pool Memory

34. Looking Inside Shared Pool Memory • V$OBJECT_USAGE • V$SQL • V$SQLAREA • V$SQLTEXT • V$SQLTEXT_WITH_NEWLINES Chapter 4: Tuning the Shared Pool Memory

35. Managing Shared Pool Memory Flushing the Shared Pool Memory Pinning Objects Chapter 4: Tuning the Shared Pool Memory

36. CURSOR_SHARING Parameter Chapter 4: Tuning the Shared Pool Memory

37. CURSOR_SPACE_FOR_TIME Parameter Chapter 4: Tuning the Shared Pool Memory

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 Chapter 4: Tuning the Shared Pool Memory

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 Chapter 4: Tuning the Shared Pool Memory

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 Chapter 4: Tuning the Shared Pool Memory

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 V$LIBRARYCACHE performance dynamic view • You can use V$SESSION_OBJECT_CACHE to get a full statistics report on cached objects for the current session Chapter 4: Tuning the Shared Pool Memory