oracle 11g training note 02

training, note, oracle

Enhanced features

一．Storage Enhancements
1.1　ASM fast mirror resync
      The ASM won't resync by settup reparing time, which save performance time and resource to resync.
1.2  Use ASM preferred mirror read
   You can specify user to access specific mirror, e.g user from site A can only access primary AU(allocation unit), and users from siteB access secondary AU. which can improve access effeciency.
ASM supports variable extent sizes to:
– Raise the maximum possible file size
– Reduce memory utilization in the shared pool
1.3  Use the SYSASM role
   Using the SYSASM role to manage ASM instances avoids overlap between DBAs and storage administrators.
1.4  Use the ASMCMD md_backup, md_restore, and repair extensions
1.5  ASM Scalability in Oracle Database 11g
      ASM imposes the following limits:
      • 63 disk groups
      • 10,000 ASM disks
      • 4 petabytes per ASM disk
      • 40 exabytes of storage
      • 1 million files per disk group
      • Maximum file size:
      – External redundancy: 140 PB
      – Normal redundancy: 42 PB
      – High redundancy: 15 PB
二．Change Management in Oracle Database 11g
2.1 SQL Performance Analyzer

      • New 11g feature
      • Targeted users: DBAs, QAs, application developers
      • Helps predict the impact of system changes on SQL workload response time
      • Builds different versions of SQL workload performance (that is, SQL execution plans and execution statistics)
      • Executes SQL serially (concurrency not honored)
      • Analyzes performance differences
      • Offers fine-grained performance analysis on individual SQL
      • Integrated with SQL Tuning Advisor to tune regressions

2.2  SQL Performance Analyzer is beneficial in the following  use cases:
      • Database upgrades
      • Implementation of tuning recommendations
      • Schema changes
      • Statistics gathering
      • Database parameter changes
      • OS and hardware changes

   2.3 Summary:SQL Performance Analyzer
      1. Capture SQL workload on production.
      2. Transport the SQL workload to a test system.
      3. Build “before-change” performance data.
      4. Make changes.
      5. Build “after-change” performance data.
      6. Compare results from steps 3 and 5.
      7. Tune regressed SQL.

三.  SQL Plan Management
   3.1 SQL Plan Management: Overview
      • SQL Plan Management is automatically controlled SQL plan evolution.
      • Optimizer automatically manages SQL plan baselines.
         – Only known and verified plans are used.
      • Plan changes are automatically verified.
         – Only comparable or better plans are used going forward.
      • Can pre-seed critical SQL with STS from SQL Performance Analyzer
3.2 SQL Plan Baseline: any new plan won't be applied if there is planline existing, and it could be a baseline in the second round.
四. Database Replay
4.1 Why Use Database Replay?
         • System changes (such as hardware and software upgrades) are a fact of life.
         • Customers want to identify the full impact of changes before going live.
         • Extensive testing and validation can be expensive in time and money.
         • Despite expensive testing, success rates are low:
         – Many issues go undetected.
         – Changes can negatively affect system availability and performance.
         • Cause of low success rate:
            – Inability to properly test with real-world production workloads, with many issues going undetected.
         • The Database Replay feature makes it possible to do real-world testing.
   4.2 Database Replay
         • Re-create actual production database workload in test environment.
         • Identify and analyze potential instabilities before making changes to production.
         • Capture workload in production:
            – Capture full production workload with real load & concurrency
            – Move the captured workload to test system
         • Replay workload in test:
            – Make the desired changes in test system
            – Replay workload with production load & concurrency
            – Honor commit ordering
            • Analyze and report:
            – Errors
            – Data divergence
            – Performance divergence
   4.3 Pre-Change Production System
               Supported changes
               • Database upgrades, patches
               • Schema, parameters
               • RAC nodes, interconnect
               • OS platforms, OS upgrades
               • CPU, memory
               • Storage
   4.4 Supported Workloads
               •  Supported
               – All SQL (DML, DDL, PL/SQL) with practically all types of binds
               – Full LOB functionality (cursor-based and direct OCI)
               – Local transactions
               – Logins and logoffs
               – Session switching
               – Limited PL/SQL RPCs
               • Limitations
               – Direct path load, import/export
               – OCI-based object navigation (ADTs) and REF binds
                  – Streams, non-PL/SQL-based AQ
                  – Distributed txns, remote describe/commit operations
               – Flashback (Database and Query)
               – Shared Server
   4.5 Capture Considerations
         Planning
         • Adequate disk space for captured workload (binary files)
         • Database restart:
         – Only way to guarantee authentic replay
         — Startup restrict
         — Capture will un-restrict
         – May not be necessary depending on the workload
         • A way to restore database for replay purposes:
         – Physical restore (scn/time provided)
         – Logical restore of application data
         – Flashback/snapshot-standby
         • Filters can be specified to capture subset of the workload.
         • SYSDBA or SYSOPER privileges and appropriate OS privileges    Overhead
         • Performance overhead for TPCC is 4.5%
         • Memory overhead : 64 KB per session
         • Disk space
4.5 Replay Considerations
         • Preprocess captured workload
         – One-time action
         – On same DB version as replay
         – Can be performed anywhere (production, test system, or other system) if versions match
         • Restore database, and then perform. the change:
         – Upgrade
         – Schema changes
         – OS change
         – Hardware change
         – Add instance
         • Manage external interactions
         – Remap connection strings to be used for the workload:
         — One-to-one: For simple instance-to-instance remapping
         — Many-to-one: Use of load balancer (e.g., single node to RAC)
         – Modify DB Links and directory objects that point to production systems
         • Set up one or more replay clients
         – Multithreaded clients that can each drive multiple workload sessions
4.6 Replay Options
         • Synchronized replay:
         – Ensures minimal data divergence
         – Commit-based synchronization
         • Unsynchronized replay:
         – Useful for load/stress testing
         – Original commit ordering not honored
         – High data divergence
         • Think time options:
         – Auto (default)
         – Adjust think time to maintain the captured request rate:
         — 0%: No think time (highest possible request rate)
         — <100%: Higher request rate
         — 100%: Exact think time
         — >100%: Lower request rate
         • Login time options
         – Percentage (default is 100%)
4.6 Replay Analysis
         • Data divergence
         – Number of rows compared for each call (queries, DML)
         • Error divergence
         – New errors
         – Mutated errors
         – Errors that have disappeared
         • Performance
         – Capture and Replay report
         – ADDM report
         – ASH report for skew analysis
         – AWR report
4.7 Database Replay Workflow in EntERPrise Manager
      1. Capture the workload on a database. (Task 1)
      2. Optionally export the AWR data. (Task 1)
      3. Restore the replay database on a test system.
      4. Make changes to the test system as required.
      5. Copy the workload to the test system.
      6. Preprocess the captured workload. (Task 2)
      7. Configure the test system for the replay.
      8. Replay the workload on the restored database. (Task 3)

收藏分享评分

---- 吃得苦中苦，方为人上人！
---- 不想当将军的士兵不是个好兵！

回复引用

订阅 TOP

下载狂