Question? Leave a message!




Improvements in Oracle Database 11g

Improvements in Oracle Database 11g 26
Insert Picture Here DBA’s New Best Friend: Advanced SQL Tuning Features of Oracle Database 11g Peter Belknap, Sergey Koltakov, Jack RaittoThe following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.Agenda • SQL Tuning Challenges • Oracle Database 11g Solutions • Automatic SQL Tuning • Real-time SQL Monitoring • Partition Advisor • Q & ASQL Tuning Challenges • Oracle Database 10g introduced SQL advisors to simplify application and SQL tuning • Remaining challenges • SQL Tuning still reactive • Painful to find and investigate long-running SQL • Partitioning excluded from schema optimization advice • Oracle Database 11g solutions • Automatic SQL Tuning • Real-time SQL Monitoring • Partition Advisor component of SQL Access AdvisorAutomatic SQL Tuning Insert Picture Here The Self-Managing DatabaseChallenges of Manual SQL Tuning • Requires expertise in several domains • SQL optimization: adjust the execution plan • Access design: provide fast data access • SQL design: use appropriate SQL constructs • Time consuming • Plans are complicated • Each SQL statement is unique and each execution can be different • Potentially large number of statements to tune • Testing proposed changes is labor-intensive • Many possible ways to a solution • Never ending task • SQL workload always evolving • Plan regressionsSimplifying SQL Tuning SQL Tuning Advisor, since Oracle Database 10g SQL Tuning Automatic Tuning Optimizer Recommendations SQL Tuning Advisor Statistics Gather Missing or Analysis Stale Statistics SQL Create a SQL Profiling Profile DBA Access Path Add Missing Analysis Indexes SQL Structure Modify SQL Analysis ConstructsImprovements in Oracle Database 11g Better SQL Profiling SQL Tuning Automatic Tuning Optimizer Recommendations SQL Tuning Gather Missing or Stale Statistics Advisor Statistics Analysis SQL Profiling Create a SQL Profile • Fix potential regression – show verified after upgrade benefit • Verify benefit through DBA test-execution Access Path Add Missing Indexes Analysis SQL Structure Modify SQL Constructs AnalysisTesting SQL Profiles (1) Measuring actual benefit with test-execution Naïve: Execute in Order Finish, P2 wins P P 2 1 But what if P1 never completes? Timeout P 1 It would be great to run them concurrently…. P 1 P P2 wins, kill P1 2 But then I take 2 CPUs, and N in the general case…Testing SQL Profiles (2) Measuring actual benefit with test-execution Solution: Tournament Execution P Round 1: P 2 1 15 sec 15 sec Round 2: P P 2 1 16 sec 30 sec Your winner, with a knockout in the second round, P 2SQL Tuning in Oracle Database 10g End-to-end Workflow Evaluate Implement Recommendations Workload DBA DBA Generate Recommendations one hour DBA Invoke ADDM Advisor SQL Tuning AWR Advisor SQL Tuning Candidates A good end-to-end solution, but manual intervention is requiredAutomatic SQL Tuning in Oracle 11g The Self-Managing Database Implement Test SQL Profiles SQL Profiles Workload Generate Recommendations Choose Candidate SQL Tuning SQL Candidates one It’s Automatic week View Reports / Control AWR Process DBAPicking Candidate SQL (1) S1, 10 minutes Week’s Top SQL, S2, 8 minutes Ordered by DB AWR S3, 5 minutes Time S4, 1 minute I could just pick from the top down… But I will miss SQLs with important hotspots Let’s try a more balanced approach: AWR Weekly Daily Hourly Average Exec OK, but where do I start? yyy Picking Candidate SQL (2) AWR Weekly Daily Hourly Average Exec 20% 10% 5% 65% Candidate List Eventually we need one list to tune from: merge the buckets. All buckets are not created equal: focus on the week, but don’t forget about the others. Focus on the SQLs we have not seen recently: Don’t re-tune SQLs if nothing has changedTuning Flow Tuning activities per SQL Candidate SQLs – Store findings, exec stats – Fetch next SQL Accept Profile Tune SQL – Fix potential regressions – Require 3X benefit in CPU and IO time – Look for indexes, statistics, as with standard tuning – Still recommend if 3X Test Profile – Tournament competitionFocus on SQL Profiles First step in automating SQL tuning Auto-testing/implementing is limited to profiles because: • No lengthy, expensive set-up process (building an index takes time) • Private to the current compilation • No change to user SQL (does not change semantics) • SQL-level recommendation, can be effectively tested • Easily reversed by the DBA Testing is done for regular SQL Tuning Advisor tasks as wellAutomatic SQL Tuning Defaults Sensible defaults with flexible configurations • Out-of-the-box defaults: • Runs in each maintenance window (MAINTENANCE_WINDOW_GROUP) • SQL profiles are tested but not implemented • DBA can configure using EM: • Whether / When / How long it runs • Resources it uses • Whether it implements profiles • How many profiles it implementsAutomatic SQL Tuning Task Automatic SQL Tuning Configuration Automatic SQL Tuning Result SummaryAutomatic SQL Tuning Result Recommendations Automatically Tuned SQL Details DrilldownConclusions • Manual SQL tuning is painful even for the experts • Oracle 10g SQL Tuning Advisor quickly gives DBA good choices • Oracle 11g Automatic SQL Tuning automates the process by making the easy decisions • DBA can control as much of the process as he wantsJust when you thought it was safe to run your SQLs… Single SQL Execution There’s a lot more to SQL performance than bad plans • Potential run-time issues • Finding high response-time SQL is no piece of cake • Keeping tabs on Parallel SQL is even harderReal-Time SQL Monitoring Insert Picture Here Shining new light on SQL PerformanceÎ Problem: Managing High Response-Time SQLs • Monitoring: tracking high response-time SQL • What is that expensive SQL (ETL, DDL, batch, report, …) I started up to? • Do I have any high response-time SQL running on my OLTP system? • Any SQL executing parallel? • Investigating: why is this execution so expensive? • Plan has hundreds of operations where is the time being spent? • Why is a particular operation so expensive? • SQL runs parallel, is DOP appropriate? is there a skew? Single SQL Execution What is going on inside a SQL execution???Solution: Real-time SQL Monitoring Looking inside the SQL • Enabled out-of-the-box with no performance impact • Automatically monitors SQL executions that: • consume more than 5 seconds of CPU or I/O time • are running parallel: PQ, PDML, PDDL • Monitors each execution independently • Exposes monitoring statistics at Single multiple levels SQL • Global execution level Execution • Plan operation level (Plan Tuning) • Parallel Execution level (PX Tuning) • Guides your tuning effortsHow does it work? • Exposes monitoring statistics in: • VSQL_MONITOR • Cumulative DB time breakdown (CPU, IO, Application, etc) • PL/SQL, Java Exec Times • VSQL_PLAN_MONITOR • rows, executions, memory, temp space per plan operation • Plan operation begin and end times • VACTIVE_SESSION_HISTORY (ASH) • Each execution of each SQL identifiable in ASH execution key: (SQL_ID, SQL_EXEC_START, SQL_EXEC_ID) • Parallel Execution Servers share an execution key with QC, but use a separate Session ID • Separate entries for each Parallel Execution Server • Refreshes statistics every second, during query execution • Statistics available for at least 5 minutes, even with cursor age-outsHow do I use it? • 11g Enterprise Manager Grid Control • Additional reporting (available today): DBMS_SQLTUNE.REPORT_SQL_MONITOREnterprise Manager Flow (1) SQL Details Monitoring Details Top Activity Session DetailsEnterprise Manager Flow (2) Monitoring Details Monitoring ListSQL Monitoring ListSQL Monitoring DetailsSQL Monitoring Details (Parallelism)Conclusion • Real-Time SQL Monitoring is • Monitoring and tuning for high response-time SQLs • New, fine-grained SQL statistics • tracked automatically • updated while the SQL runs • highly visible and accessible • at no cost to your production system • The only way to know what’s happening inside single SQL execution • The quickest way to the root cause of a performance problem: If you can find the problem, you can fix itInsert Picture Here Partition AdvisorProblem • SQLs on large tables run too long or timeout • High I/O counts • Too much pressure on buffer pool • Disgruntled users • Low transaction rates • Too many complex SQLs to figure out on my own • Put out a fire here, another starts over thereSolution • Get new 11g partition advice along with other advice from the new 11g SQL Access Advisor • Recommendations targeted at partition elimination in query processing • Recommendations to aid certain join processingInterval Partitioning 100 200 300 400 500 600 CREATE TABLE emp (empno NUMBER(6), first_name VARCHAR(20), last_name VARCHAR(20), deptno NUMBER(6)) PARTITION BY RANGE (deptno) INTERVAL 100 PARTITION p1 VALUES LESS THAN 100Interval Partitioning 100 200 300 400 500 600 CREATE TABLE emp (empno NUMBER(6), first_name VARCHAR(20), last_name VARCHAR(20), deptno NUMBER(6)) PARTITION BY RANGE (deptno) INTERVAL 100 PARTITION p1 VALUES LESS THAN 100 Interval partition is a new, automated form of range partitioning.Partition Elimination 100 200 300 400 500 600 CREATE TABLE emp (empno NUMBER(6), first_name VARCHAR(20), last_name VARCHAR(20), deptno NUMBER(6)) PARTITION BY RANGE (deptno) INTERVAL 100 PARTITION p1 VALUES LESS THAN 100 SELECT empno, last_name, first_name FROM emp WHERE deptno = 123Partition Elimination 100 200 300 400 500 600 CREATE TABLE emp (empno NUMBER(6), first_name VARCHAR(20), last_name VARCHAR(20), deptno NUMBER(6)) PARTITION BY RANGE (deptno) INTERVAL 100 PARTITION p1 VALUES LESS THAN 100 SELECT empno, last_name, first_name FROM emp WHERE deptno = 123Partition-wise Join When joining two tables that are partitioned on the join- key, Oracle may choose to join on a per-partition basis. Lineitem Orders Lineitem Orders 05-Apr 05-Apr Sub-1 Sub-1 Sub-1 Node 1 Sub-1 Sub-2 Sub-2 Sub-2 Sub-2 Node 2 Sub-3 Sub-3 Sub-3 Sub-3 Node 3How does SAA work? Standard STS SQL cache, Workload user defined, Analyzes access etc. patterns, column usage, etc. SQL Access Advisor w/new Partition Advice Determines best partitioning strategy for the entire Recommendations: workload in concert with New partition best index and materialized view solutions index mvHow does SAA work? Workload Expensive Qs Rank Qs on BIG tables Partition Table+Index annotated Partition workload Analysis Index MV MV Partition Analysis Analysis Analysis feedback output RecommendationsHow does SAA work? Workload Expensive Qs Rank Qs on BIG tables Partition Table+Index annotated Partition workload Analysis Index MV MV Partition Analysis Analysis Analysis feedback output RecommendationsPartition Advisor Problem Space • Fact: If I partition table T1, all Qs referencing T1 are affected (+ or -) • Fact: If I also partition table T2, the same applies • Fact: Lots of Qs reference multiple tables forming a network of inter-relationships • Therefore: A potential partitioning scheme on each different table affects each potential partitioning scheme on other tables in that networkPartition Advisor Enumeration Pruning Heuristics EvaluationPartition Advisor Enumeration Pruning Heuristics EvaluationPartition Advisor Enumeration Pruning Heuristics EvaluationPartition Advisor Enumeration Pruning Heuristics EvaluationPartition Advisor Enumeration Pruning Heuristics Evaluation Partition AnnotationsHow does SAA work? Workload Expensive Qs Rank Qs on BIG tables Partition Table+Index annotated Partition workload Analysis Index MV MV Partition Analysis Analysis Analysis feedback output RecommendationsHow does SAA work? Workload Expensive Qs Rank Qs on BIG tables Partition Table+Index annotated Partition workload Analysis Index MV MV Partition Analysis Analysis Analysis feedback output RecommendationsIndex Analysis: MV & Index Advisor predicates MV Analysis: group bys joins joins group bys index-only access dimensions bitmap access MV candidates Index candidates Evaluate MV Partition Optimizer / Query Rewrite Advisor Associated groups of access candidates Recommendations Global access optimizationWhat does SAA do?What does SAA do? Recommends: PartitioningWhat does SAA do? Recommends: Partitioning Tables What does SAA do? Recommends: Partitioning Tables Materialized ViewsWhat does SAA do? Recommends: Partitioning Tables Materialized Views Indexes Supported What does SAA do? Partitioning Types: Interval Recommends: Recommends: Hash Partitioning Partitioning Tables Tables Materialized Views Materialized Views Indexes Indexes Supported What does SAA do? Partitioning Types: Interval Recommends: Hash Partitioning Tables Materialized Views Indexes Supported Partition Key Types: Date NumberWhat does SAA do? Recommends: Partitioning Tables Materialized Views Indexes Creating What does SAA do? Recommends: Partitioning Tables Materialized Views Indexes Creating Materialized Views What does SAA do? Recommends: Partitioning Tables Materialized Views Indexes Creating Materialized Views Indexes What does SAA do? Recommends: Partitioning Tables Materialized Views Indexes Creating Materialized Views Indexes MV Logs What does SAA do? Recommends: Partitioning Tables Materialized Views Indexes Holistic Advice Creating Materialized Views Indexes MV Logs Choosing Partition Advice NewRecommendation summary NewPartition recommendations NewPartition RecommendationConclusions • SAA now covers your data access problems with all possible access solutions • New for 11g: • Partition advice, including hash and new interval on date and number • Incremental advice • Partition recommendations are holistically generated, simultaneously considering all possible access solutions across an entire SQL workload • SAA is easy to use as ever – partition advice is yours for click of a checkboxNavigating to SQL Access Advisor EM Home Page Advisor Central Page SQL Advisor PageUsing SQL Access Advisor Choose initial options Select a workloadRunning advisor job Select job options Review & submit Review results
Website URL
Comment