华为技术培训教程-Oracle性能调整.pptVIP

Oracle性能调整 lq@ 怎样使用Oracle 需要了解 Oracle如何存储数据 Oracel如何访问数据 Oracle怎样管理并发性 Oracle怎样做select/DML Oracle怎样存储数据 Data is stored in tables Heap table (Most commonly used) Partitioned table Index organized table Cluster Hash Cluster Index Materialized View External Tables Oracle如何存取数据 单表存取路径 Full Table Scan ROWID unique scan Index unique scan Index range scan Index (fast) full scan Bitmap Index Index and-equal 表连接 Hash Join Nested loop join Sort-merge join Oracle如何管理并发性 行级锁 SCN(System Change Number) 和回滚段 读不阻塞写（Readers don’t block writers） 写不阻塞读（Writers don’t block readers） 写不阻塞写 (不在同一行) --Row Level Lock 支持更多的并发访问 Oracle如何处理SQL Stage 1: Create a Cursor Stage 2: Parse the Statement (很消耗资源) Stage 3: Bind Any Variables （绑定变量） Stage 4: Run the Statement (很消耗资源) Stage 5: Fetch Rows of a Query (也可能很消耗资源) Stage 6: Close the Cursor Oracle的优化模式 Optimizer decide the SQL execution plan, which is critical to SQL performance. RBO (Rule Based Optimizer) Since oracle 5(maybe?) No change since oracle 7.3,desupported in oracle 10 Stable, generate the execution plan according fixed rules Not necessary good Stable Do not support new oracle features(like IOT, partition, materialized view,…) CBO (Cost based optimizer) Since oracle 7.3 Support all oracle features Better than RBO in most case, Can be bad in some case Choose the best execution plan according to its internal 算法 Can use event 10053 to find why oracle choose this Need to analyze schema to get the best execution plan SQL调整 Requirements: Know How Oracle Store the data Know how oracle process SQL Know how oracle access the data Target: Use less resource, let oracle do less work Make application runs faster SQL调整 When to tune SQL Need speedup: Application running too slow Need scale up: Oracle using too much resource, Cannot support more users How we tune SQL Understand the business requirement Analyze the schema design Tune the SQL Explain plan AUTOTRACE(SQL*Plus) SQL Trace+Tkprof Event 10046 detailed trace Try hint/rewr