ORACLE调优性能.ppt

Performance Tuning By Example Zhu Chao Chao_Ping@ How to use oracle Know The database How Oracle Store the data How Oracle Access the data How Oracle manage concurrency How Oracle Do Select/DML Know the application How application use the data Store/Manage the data as it is used How oracle store the data Data is stored in tables Heap table (Most commonly used) Partitioned table Index organized table Cluster Hash Cluster Index Materialized View External Tables How oracle access the data Single Table Access Path Full Table Scan ROWID unique scan Index unique scan Index range scan Index (fast) full scan Bitmap Index Index and-equal Table Join Hash Join Nested loop join Sort-merge join How oracle manage concurrency Row Level Lock SCN(System Change Number) and rollback segment Readers don’t block writers Writers don’t block readers Writers don’t block writers (not the same row) --Row Level Lock Support tens of thousands of users How Oracle process SQL Stage 1: Create a Cursor Stage 2: Parse the Statement (Most expensive step) Stage 3: Bind Any Variables Stage 4: Run the Statement (Most expensive step) Stage 5: Fetch Rows of a Query (can be expensive) Stage 6: Close the Cursor Optimizer 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 Tuning Requirements: Know How Oracle Store the data Know how oracle process SQL Know how oracle access the data Target: Use less resource, let oracle d