1 / 62

Which new Oracle 9i features are helpful for a SAP customer?

Which new Oracle 9i features are helpful for a SAP customer?. Dr. Stephan Bühne Oracle SAP Solution Center Walldorf. Initialisation: spfile vs. Pfile SGA: Dynamic Resizing Multiple Block Size Support PGA: Automatic Memory Management Automatic UNDO Management Resumable Space Allocations

davidwolfe
Download Presentation

Which new Oracle 9i features are helpful for a SAP customer?

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Which new Oracle 9i features are helpful for a SAP customer? Dr. Stephan Bühne Oracle SAP Solution Center Walldorf

  2. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  3. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  4. Initialisation: spfile vs. pfile • Spfile replaces traditional init.ora file • Allows server to maintain parameter settings • All members of RAC can use the same file • Is maintained by the oracle server

  5. Initialisation: spfile vs. pfile • Binary file • Is created with the command:„Create spfile from pfile;“ • Alter system set parameter = valuescope = Memory | Spfile | Both • Example:Alter system set SORT_AREA_SIZE = 1048676 comment ´Temporary Change´scope = spfile;

  6. Initialisation: spfile vs. pfile • Exporting spfile:„Create pfile from spfile;“ • *.sort_area_size=1048676#Temporary change • prd1. shared_pool_size=200m • prd2. shared_pool_size=150m • *.sessions=200

  7. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  8. Dynamic SGA • Oracle 8i Buffer Cache and Shared Pool are fixed • Recycle Pool and Keep Pool are defined out of all DB_Block_Buffers

  9. SGA Oracle 8i Shared Pool Shared_Pool_Reserved_Size SHARED_POOL_RESERVED_SIZE SHARED_POOL_SIZE = 10240000 Default Buffer Pool DB_BLOCK_BUFFERS = 250000 Recycle BUFFER_POOL_RECYCLE Keep BUFFER_POOL_KEEP

  10. Dynamic SGA • Oracle 9i Buffer Cache and Shared Pool can be dynamically changed • No database restart necessary

  11. SGA Oracle 9i SGA_MAX_SIZE = 2048M Shared Pool Shared_Pool_Reserved_Size SHARED_POOL_SIZE Keep DB_KEEP_CACHE_SIZE Recycle DB_RECYCLE_CACHE_SIZE Default DB_CACHE_SIZE

  12. Dynamic SGA: Parameters • If you want make use of the dynamic SGA resizing you MUST use new parameters: • DB_CACHE_SIZE (DB_BLOCK_BUFFERS) • DB_KEEP_CACHE_SIZE (BUFFER_POOL_KEEP) • DB_RECYCLE_CACHE_SIZE (BUFFER_POOL_RECYCLE)

  13. Dynamic SGA: MONITORING • Efficieny of the buffer cache can be monitored with View V$DB_CACHE_ADVICE • Parameter DB_CACHE_ADVICE must be specified • OFF: Advisory is turned off • ON: Advisory is turned on • READY: Advisory is turned off, but needed memory is allocated

  14. Dynamic SGA: V$DB_CACHE_ADVICE 10 % 20 % 30 % 100 % 110 % 120 % 200 %

  15. Dynamic SGA: Summary • MAX_SGA_SIZE specifies the total amount of memory used for the oracle SGA • Within this specified size all parts of the SGA can be resized dynamically • KEEP and RECYCLE Buffers are specified additionally • More flexibility to adjust SGA to the current needs

  16. Dynamic SGA: Summary • View V$DB_CACHE_ADVICE can be used to estimate optimal buffer cache size • Feature can be switched on/ off dynamically • Partial analysis for specific workload is possible • Dynamic SGA Resize is also possible in RAC environments

  17. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  18. Multiple Block Sizes • Tablespaces within one database can have different Oracle Block Sizes • Is setup on Tablespace creation • Up to five different Sizes can be used • Supported Block Sizes: 2k, 4k, 8k, 16k, 32k • Each block size has an own buffer cache area

  19. Multiple Block Sizes SGA_MAX_SIZE = 2048M DB_CACHE_SIZE = 16K DB_CACHE_SIZE = 16K DB_CACHE_SIZE = 32K DB_CACHE_SIZE = 32K Keep DB_KEEP_CACHE_SIZE Recycle DB_RECYCLE_CACHE_SIZE Default Block Size = 8k DB_CACHE_SIZE

  20. Multiple Block Sizes: Monitoring • View V$BUFFER_POOL displays information about specified buffer pools

  21. Multiple Block Sizes: Summary • A separate Buffer cache must exist, before a tablespace can be created • System TS and Temp TS must have default blocksize • Larger block sizes are useful for tables containing Long Raw fields • BW-Application can benefit (FTS) • Useful for SAP Cluster or Pool Tables

  22. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  23. Automatic PGA Management • Simplifies and improves memory allocation • SQL working areas can be adjusted automatically and dynamically • Ease of memory tuning • Reduction of time to tune memory • Better throughput • Improved query response time

  24. Automatic PGA Management Tunable Memory Untunable Memory SORT_AREA_SIZE HASH_AREA_SIZE BITMAP_MERGE_AREA_SIZE CREATE_BITMAP_AREA_SIZE Process Code Heap Memory Tunable Memory Process PGA Untunable Memory Tunable Memory Process PGA Untunable Memory Tunable Memory Process PGA Untunable Memory Untunable Memory + Tunable Memory <= PGA_AGGREGATE_TARGET

  25. Automatic PGA Management:Standard R/3 Tunable Memory Untunable Memory = SORT_AREA_SIZE Process PGA Process PGA Process PGA Process PGA 95 % + 5 % <= PGA_AGGREGATE_TARGET

  26. Automatic PGA Management:BW R/3 SORT_AREA_SIZE HASH_AREA_SIZE BITMAP_MERGE_AREA_SIZE CREATE_BITMAP_AREA_SIZE Tunable Memory Untunable Memory Process PGA Process PGA Process PGA Process PGA 5 % + 95 % <= PGA_AGGREGATE_TARGET

  27. Automatic PGA Management • New Oracle Parameters introduced: • PGA_AGGREGATE_TARGET 10 MB – 400 GB • WORKAREA_SIZE_POLICY • MANUAL (Default) • AUTO (Default if PGA_AGGREGATE_TARGET is set)

  28. Automatic PGA Management • The AUTO Mode ensures: • The overall size of the PGA memory never exceed PGA_AGGREGATE_TARGET • A Single process never runs out of memory

  29. PGA Management: Monitoring • New Statistics in V$SYSSTAT:

  30. Automatic PGA Management • V$SYSSTAT • WORK_AREA_MEMORY_ALLOCATEDTotal amount of PGA allocated either by a single process or overall • WORK_AREA_EXECUTIONS_OPTIMALOptimal size: No write to disk necessary • WORK_AREA_EXECUTIONS One Pass Query could executed with a single disk pass • WORK_AREA_EXECUTIONS MULTIPASSMultiple pass runs were necessary

  31. PGA Management: Monitoring New View V$PGASTAT:

  32. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  33. Automatic UNDO Management • Simplifies management of undo data • No reasons for create, drop, alter rollback segments • UNDO segments can be managed either manual or automatic • Data is managed by a single UNDO-tablespace

  34. Automatic UNDO Management System TBS UNDO TBS _SYSSMU1$ Auto System Rollback Segment _SYSSMU2$ _SYSSMUn$ System TBS RBS TBS RBS1 Manual System Rollback Segment RBS2 RBSn

  35. Automatic UNDO Management • UNDO_MANAGEMENTSwitches automatic UNDO management on/off • UNDO_TABLESPACEDefines which automatic UNDO tablespace is used • UNDO_SUPPRESS_ERRORSSupresses errors if invalid UNDO command is issued • UNDO_RETENTIONSpecifies the time in seconds Read Consistency should be guarenteed

  36. UNDO Management Monitoring • V$UNDOSTATDisplays the UNDO usage in 10 Minutes intervals Undo space = (UR x UPS) + Overhead UR = Undo Retention Parameter UPS = Undo Blocks per second

  37. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  38. Resumable Space Allocation • Resumable operation are suspended under „Out of space“-errors (e.g. ORA-1653, ORA-1631, ORA-1562, ORA-1628, ...) • A session running in one of these error conditions is not aborted, but waits for a specified amount of time to proceed

  39. Resumable Space Allocation • Resumable space operation is enabled • Transaction starts • Out-Of Space error occurs • Error is written to alert.log • Error is fixed (e.g. Datafile added) • Suspended operation resumes automatically • Operation ends successfully

  40. Resumable Space Allocation • But: Must be activated on session level„Alter session enable resumable timeout nn“ ; • How to activate in SAP environments ?

  41. Resumable Space Allocation • But: Must be activated on session level„Alter session enable resumable timeout nn“ ; • How to activate in SAP environments ? => Logon Trigger

  42. Resumable Space Allocation • Logon Trigger:CREATE OR REPLACE TRIGGER RESUMABLE_TRANSACTIONafter logon on SAPR3.SCHEMABEGIN execute immediate ´alter session enable resumable timeout 14400´;END;/ • Must be created with SYDBA privileg • Can be enabled/ disabled as needed

  43. Resumable Space Allocation • Resumable statements are: • Queries (Sort Area, Hash Area) • DML statements (max. Extents, TBS full, Rollback) • SQL*Loader operations • Import and Export operations • DDL statements (Create Index, Index rebuild, CTAS) • Status can be monitored with DBA_RESUMABLE

  44. Resumable Space Allocation DBA_RESUMABLE:

  45. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  46. Identifying Unused Indexes • Monitoring is done during Parse Time • Helps to identify unused indexes in the system to save space and resources • Only Parsing Step is monitored, not the execution

  47. Identifying Unused Indexes • Alter index <name> monitoring usage • Switches Monitoring on for the specified index • Alter index <name> nomonitoring usage • Switches Monitoring off for the specified index • Switching monitoring ON/ OFF forces reparsing on all SQl statements on the table

  48. Identifying Unused Indexes • A new DBA View V$OBJECT_USAGE exists to view the monitoring results:

  49. Initialisation: spfile vs. Pfile • SGA: Dynamic Resizing • Multiple Block Size Support • PGA: Automatic Memory Management • Automatic UNDO Management • Resumable Space Allocations • Identifying unused indexes • Online-Reorganisation

  50. Online Reorganisation • Tables can be converted: • Non-Partitioned  Partitioned • Columns can be dropped • Columns can be renamed • New Columns can be added • LOB are supported • B UT: Long Raw fields are not supported

More Related