Downloaded 2,025 times
Uploaded byGuy Harrison
Oracle sql high performance tuning
This document discusses techniques for optimizing SQL performance in Oracle databases. It covers topics like optimizing the optimizer itself through configuration changes and statistics collection, detecting poorly performing SQL, and methods for improving plans such as indexing, partitioning, hints and baselines. The goal is to maximize the optimizer's accuracy and ability to handle edge cases, while also knowing how to intervene when needed to capture fugitive SQL and ensure acceptable performance.
In this document
Powered by AI
Introduces Oracle SQL High Performance Tuning, presenting the speaker, Guy Harrison, and agenda covering methodology and optimization techniques.
Covers analogies for SQL tuning, detailing optimizer configurations, and necessary structures for optimizing SQL performance. Key components include cardinality and system statistics.
Discusses histograms in SQL tuning, addressing their limitations, and advising on realistic expectations regarding their effectiveness.
Focuses on finding tunable SQLs and detecting high resource costs through tools like V$SQL and DBMS_XPLAN for better execution plans.
Explains how to refine SQL plans through indexing and configuration, highlighting the cautious use of hints and the advantages of SQL baselines.
Details various indexing methods, including single table lookups, bitmap indexes, and vertical partitioning for optimizing query performance.
Discusses join methodologies, optimal ordering, and performance effects of different join types, including nested loops and bitmap join indexes.
Explores sorting expectations, memory management, and adjustments necessary for optimal sort operations within Oracle databases.
Focuses on grouping techniques, including the new hash group by functionality introduced in 11g, as well as analytic functions.
Discusses DML tuning techniques including indexes, multi-table inserts, and merge optimizations for enhanced SQL performance.
Summarizes the importance of maximizing optimizer effectiveness, acknowledging that some sub-optimal conditions are unavoidable in SQL tuning.
More Related Content
Similar to Oracle sql high performance tuning
More from Guy Harrison
Oracle sql high performance tuning
- 1. Oracle SQL HighPerformance TuningGuy HarrisonDirector, R&D [email protected]@guyharrison
- 2.
- 3. AgendaPhilosophy and methodologyOptimizingthe optimizerDetecting errant SQLsChanging the plan Table lookupsJoinsSortsGroup BYOther topics
- 4. Prison guard analogyKeepthe SQLs contained Indexing and clustering Optimizer configuration Detect break outsMonitor and detect errant SQLsRe-capture the fugitivesTraditional SQL tuningOutlines, baselines, indexing, denormalization, hints
- 5.
- 6. Optimizer inputsCardinality EstimatesTableand indexStructureObject StatisticsIO and CPUEstimates DB parametersAnd configCost estimateSystem Statistics
- 7. Optimizing the optimizerCreatenecessary physical structures for optimal plans Indexes, partitions, clusters Collect object statistics Histograms, extended statistics Optimizer configuration parametersMemory_target, db_block_size, etcOptimizer_index_caching, optimizer_index_cost_adjSystem statisticsDBMS_STATS.gather_system_stats
- 8.
- 10. 11g Extended StatisticsSelect * from people Where gender=‘boy’ And name=‘Sue’BoysGirlsPeople named SuePeople named Sue
- 11. Histogram limitationsHeight balancedhistograms don’t have the granularity we might want or expect.
- 12. Be realistic abouthistograms.....Histograms often fail to push cardinalities through multi-table SQLsDefault histogram collections are not always optimal
- 13.
- 14. Detecting break outsV$SQL & V$SQL_PLANFind SQLs with high resource costs EXPLAIN PLAN & DBMS_STATDetermine the execution plan SQL Trace/TkprofBest drilldown at the session level
- 15.
- 16. DBMS_XPLANSQL> SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('at6ss8tmxm5xz', '0', 'TYPICAL -BYTES')); PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------SQL_ID at6ss8tmxm5xz, child number 0-------------------------------------SELECT department_name, last_name, job_title FROM hr.employees JOINhr.departments USING (department_id) JOIN hr.jobs USING (job_id)ORDER BY department_name, job_title Plan hash value: 3225241925 --------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 26 (100)| || 1 | SORT ORDER BY | | 106 | 26 (8)| 00:00:01 || 2 | NESTED LOOPS | | 106 | 25 (4)| 00:00:01 || 3 | MERGE JOIN | | 107 | 24 (5)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 20 (0)| 00:00:01 || 5 | INDEX FULL SCAN | EMP_JOB_IX | 107 | 12 (0)| 00:00:01 ||* 6 | SORT JOIN | | 19 | 4 (25)| 00:00:01 || 7 | TABLE ACCESS FULL | JOBS | 19 | 3 (0)| 00:00:01 || 8 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 1 (0)| 00:00:01 ||* 9 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 0 (0)| |-------------------------------------------------------------------------------------- Predicate Information (identified by operation id):--------------------------------------------------- 6 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID") filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID") 9 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
- 17. SQL Trace &tkprofTrace in current session: DBMS_SESSIONIn other session: DBMS_MONITORSession_trace_enable – specific session Serv_mod_act_trace_enable – service, module or action nameAnalyze with tkprofOr third party tools (Toad, Spotlight, others)
- 19. The best laidplans of Mice and Oracle....
- 20. Returning to captivity– changing the plan Options for improving the plan:IndexingConfiguration changes (esp. Memory)Stored outlines (stability)SQL Tuning sets and profiles 11g SQL Baselines (flexibility)Hints and re-writes (last resort)
- 21. Use hints withextreme cautionHints reduce optimizer flexibility and can lead to bad plansEg: USE_NL can force a nested loops join without an indexUSE_NL_WITH_INDEX is safer..
- 22. Exploit baselines andplan management
- 23. SQL Baselines inSQL Optimizer
- 24. Indexing and singletable lookups
- 25. Single table lookupIndexor table scan?Avoid accidental table scans Optimize indexesbest combination of concatenated indexesOptimize necessary table scans Vertical/Horizontal partitioningCompressionParallel Query
- 27. Concatenated Index EffectivenessSELECTcust_idFROM sh.customers cWHERE cust_first_name = 'Connor'AND cust_last_name = 'Bishop'AND cust_year_of_birth = 1976;
- 28.
- 29.
- 30.
- 31.
- 32. Optimizing joinsBest joinorder Eliminate rows as early as possibleJoin Type: Nested loops Optimize the join indexSort mergeAvoid, esp. if memory scarce Hash join Avoid multi-pass executions
- 33.
- 34. Sort-merge and hashjoin In MemoryIn MemorySingle pass disk sortMulti pass disk sortDisk Sort
- 35.
- 36. Bitmap join performanceSELECT SUM (amount_sold)FROM customers JOIN sales s USING (cust_id) WHERE cust_email='[email protected]';
- 37.
- 38. 38Sorting – whatwe expectMulti-passDisk SortMemory Sort Single PassDisk Sort
- 39. 39Flash drive tothe rescue?Multi-passDisk SortSingle PassDisk Sort
- 40. Less memory thanyou may think....
- 41. Memory and sortingNothingmatters as much as PGA to sort operationsAutomatic work area management restricts you to a subset of PGAFor big sorts, “opt out” of automatic work area managementALTER SESSION SET workarea_size_policy = manual;ALTER SESSION SET sort_area_size = 524288000;
- 42.
- 43. Hash Group by11gintroduced the Hash GROUP BYUsing an ORDER BY can suppress the hash GROUP BY....Can override with USE_HASH_AGGREGATION hint
- 44.
- 45.
- 46.
- 47. DML tuning -indexes
- 48.
- 49.
- 50.
- 52. Merge optimization Theoptimizer usually can’t determine the overlap between Tables
- 53. Forcing a Nestedloops Merge outer join may be significantOther DML optimizationsArray insertDirect pathNOLOGGINGCommit frequencyNOWAIT and BATCH redo logging
- 54. Other topicsDatabase logicaldesign Clustering and exotic physical optionsPL/SQL Parallel SQLApplication optimization (Arrays, bind variables)
- 55. ConclusionMaximizing optimizer accuracyprovides the best return on investmentSub-optimal optimizations are unfortunately inevitableKey SQL tuning skills are therefore:Configuring Oracle to maximize optimizer effectivenessDetection of sub-optimal SQLsTechniques for coercing SQLs to acceptable performance
Editor's Notes
- #3 Apologies, I’m a database type.....Quest is best known for toad, but we also have enterprise monitoring across all levels of the stackIn Melbourne, SQL Navigator + the spotlights. It’s not a complete co-incidence about the star trek theme.
- #7 Garbage In Garbage OutCreate necessary physical structures for optimal plans Indexes, partitions, clusters Collect object statistics Histograms, extended statistiOptimizer configuration parametersMemory_target, db_block_size, etcOptimizer_index_caching, optimizer_index_cost_adjSystem statisticsDBMS_STATS.gather_system_stats
- #21 Robert Burns
- #33 Stupid SQL Joke:An SQL statement walks into a bar and sees two tables.It approaches, and asks “may I join you?”