Simplified SQL Performance Management in Oracle Database 11g - PowerPoint PPT Presentation

Loading...

PPT – Simplified SQL Performance Management in Oracle Database 11g PowerPoint presentation | free to download - id: 5b6ef-NmU5M



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Simplified SQL Performance Management in Oracle Database 11g

Description:

Tournament competition. Tune SQL. Fix potential regressions. Look for indexes, statistics, ... Update execution statistics in PGA continuously ... – PowerPoint PPT presentation

Number of Views:1409
Avg rating:3.0/5.0
Slides: 65
Provided by: noc8
Learn more at: http://nocoug.org
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Simplified SQL Performance Management in Oracle Database 11g


1
(No Transcript)
2
(No Transcript)
3
Simplified SQL Performance Management in Oracle
Database 11g
  • Pete Belknap

4
The 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
Oracles products remains at the sole discretion
of Oracle.
5
Agenda
  • SQL Tuning Challenges
  • Oracle Database 11g Solutions
  • Automatic SQL Tuning
  • Improvements to SQL Tuning Advisor
  • Fully automating SQL tuning
  • Real-time SQL Monitoring
  • Track high response-time SQL
  • Find the most expensive plan operation
  • Q A

6
SQL 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
  • Oracle Database 11g solutions
  • Automatic SQL Tuning
  • Real-time SQL Monitoring

7
Automatic SQL Tuning
ltInsert Picture Heregt
The Self-Managing Database
8
Challenges 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 regressions


9
Simplifying SQL Tuning SQL Tuning Advisor, since
Oracle Database 10g
SQL Tuning Recommendations
Automatic Tuning Optimizer
SQL Tuning Advisor

Gather Missing or Stale Statistics
Statistics Analysis
SQL Profiling
Create a SQL Profile
DBA
Add Missing Indexes
Access Path Analysis
SQL Structure Analysis
Modify SQL Constructs
10
SQL Profiling Technology Transparent SQL tuning
No SQL Profile
SQL Profiling
Use SQL Profile (future executions)

use
?
?
add
?
?
?
?
?
Search Space
  • Validates estimates using dynamic sampling and
    partial execution
  • Validates only relevant estimates

11
SQL Profiling Technology (2) Trying alternative
plans (new in 11g)
Profile Selection
(3)
(1)
Estimate Correction
Correction Factors
Corrections O_F_E 10.2.0.3
Corrections O_F_E 10.1.0.4
(2)
Corrections O_F_E 8.1.7
Alternate Plan Set
  • Try some interesting alternatives plans from old
    releases
  • Feed correction factors into alternative plan
    selection
  • But which is the best?

12
Testing SQL Profiles (1) Measuring actual benefit
with test-execution (new in 11g)
Naïve Execute in Order
P2
P1
Finish, P2 wins!
But then I take 2 CPUs, and N in the general case…
13
Testing SQL Profiles (2) Measuring actual benefit
with test-execution
Solution Tournament Execution
Your winner, with a knockout in the second round,
P2!
14
Testing SQL Profiles (3) Choosing appropriate
metrics, comparison strategy
  • Winner and Loser depends on your point of
    view
  • Need a statistic that is repeatable and
    comprehensive.
  • ELAPSED_TIME comprehensive, but includes row
    lock waits I/O time depends on buffer cache
    state. Not repeatable.
  • CPU_TIME very repeatable, but not comprehensive
    for I/O.
  • BUFFER_GETS very repeatable, but ignores CPU
    expense overly pessimistic for some plans
  • Combine the best elements of each
  • CPU_TIME should improve (most reliable statistic)
  • Benefit Reported ratio of CPU_TIME
    BUFFER_GETS10ms
  • Conservatively consider every buffer get to be
    an I/O, but allow large CPU improvements to
    overrule small buffer gets regressions


15
Improvements in Oracle Database 11g Better SQL
Profiling
SQL Tuning Recommendations
Automatic Tuning Optimizer
SQL Tuning Advisor

Gather Missing or Stale Statistics
Statistics Analysis
SQL Profiling
  • Create a SQL
  • Profile
  • show verified benefit
  • Fix potential regression after upgrade
  • Verify benefit through test-execution

DBA
Access Path Analysis
Add Missing Indexes
SQL Structure Analysis
Modify SQL Constructs
16
Agenda
  • SQL Tuning Challenges
  • Oracle Database 11g Solutions
  • Automatic SQL Tuning
  • Improvements to SQL Tuning Advisor
  • Fully automating SQL tuning
  • Real-time SQL Monitoring
  • Track high response-time SQL
  • Find the most expensive plan operation
  • Q A

17
SQL Tuning in Oracle Database 10g End-to-end
Workflow

Workload
one hour
AWR
A good end-to-end solution, but manual
intervention is required
18
Improvements in Oracle Database
11g Fully-Automated Tuning Workflow

Workload
Its Automatic!
19
Picking Candidate SQL (1)
But I will miss SQLs with important hotspots!
OK, but where do I start?
20
Picking Candidate SQL (2)
AWR

10
20
5
65
  • Eventually we need one list to tune from merge
    the buckets.
  • All buckets are not created equal focus on the
    week, but dont forget about the others.
  • Focus on the SQLs we have not seen
    recently Dont re-tune SQLs if nothing has
    changed!

21
Tuning Flow Tuning activities per SQL
Candidate SQLs
  • Fetch next SQL
  • Store reporting data

Accept Profile
Tune SQL
  • If lt 3X benefit, recommend for DBA consideration
  • Fix potential regressions
  • Look for indexes, statistics, as with standard
    tuning
  • Require 3X benefit
  • Both CPU and I/O times must improve

Evaluate Profile
  • Tournament competition

22
Focus 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 well!


23
Automatic 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 implements


24
Automatic SQL Tuning Task

25
Automatic SQL Tuning Configuration
26
Automatic SQL Tuning Result Summary
27
Automatic SQL Tuning Result Recommendations
28
Automatically Tuned SQL Details Drilldown
29
Conclusions
  • 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 wants


30
Q

A
31
Real-Time SQL Monitoring
ltInsert Picture Heregt
Shining new light on SQL Performance
32
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?
  • ? What is going on inside a SQL execution???


33
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 multiple levels
  • Global execution level
  • Plan operation level (Plan Tuning)
  • Parallel Execution level (PX Tuning)
  • Guides your tuning efforts


34
How does it work?
t 5
t 6
t 7

PGA
SGA
  • Update execution statistics in PGA continuously
  • After 5 seconds for serial / immediately for
    parallel, target for monitoring (reserve SGA
    space)
  • Push statistics to SGA every second
  • Separate entries for each Parallel Execution
    Server
  • Each execution of each SQL identifiable in ASH
    via execution key
  • PX Servers share an execution key, but have a
    different Session ID
  • Statistics available for at least 5 minutes
  • Not vulnerable to cursor age-outs

35
New Statistics Exposed
  • For each SQL Execution (VSQL_MONITOR)
  • Resource Consumption ELAPSED_TIME, CPU_TIME,
    FETCHES, BUFFER_GETS, DISK_READS, DIRECT_WRITES,
    APPLICATION/CONCURRENCY/CLUSTER/USER_IO_WAIT_TIME,
    PLSQL/JAVA_EXEC_TIME
  • For each Plan Operation (VSQL_PLAN_MONITOR)
  • Production STARTS (executions), OUTPUT_ROWS
  • Memory/Temp usage WORKAREA_MEM, WORKAREA_TEMPSEG
  • For each second of session activity
    (VACTIVE_SESSION_HISTORY)
  • SQL Execution Key SQL_ID, SQL_EXEC_START,
    SQL_EXEC_ID
  • Row source information SQL_PLAN_LINE_ID/OPERATION
    /OPTIONS


36
V Additions New V Views added existing views
supplemented
VACTIVE_SESSION_HISTORY (SAMPLE_TIME,
SESSION_ID, SESSION_SERIAL) With Execution Key,
Plan Line ID/Operation
DBA_HIST_ACTIVE_SESS_HISTORY (DBID, SNAP_ID,
INSTANCE_NUMBER, SAMPLE_TIME, SESSION_ID,
SESSION_SERIAL)
VSQL_MONITOR (SQL_ID, SQL_EXEC_START,
SQL_EXEC_ID)
VSESSION (SID, SERIAL) With Execution Key
VSQL_PLAN_MONITOR (SQL_ID, SQL_EXEC_START,
SQL_EXEC_ID, PLAN_LINE_ID)
VSESSION_LONGOPS (SID, SERIAL, OPNAME) With
Execution Key, Plan Line ID/Operation
VSQL_PLAN (SQL_ID, CHILD_NUMBER,
PLAN_HASH_VALUE, ID)
37
How do I use it?
  • 11g Enterprise Manager Grid Control (11.1.0.7 DB
    Control)
  • Additional reporting (available today)
    DBMS_SQLTUNE.REPORT_SQL_MONITOR
  • Get reports in HTML, XML, or Text


38
Enterprise Manager Flow (1)
SQL Details
Monitoring Details
Top Activity
Session Details
39
Enterprise Manager Flow (2)
Monitoring Details
Monitoring List

40
SQL Monitoring List
41
SQL Monitoring Details Core concepts

42
SQL Monitoring Details Core concepts

43
SQL Monitoring Details Core concepts

44
SQL Monitoring Details Core concepts

45
SQL Monitoring Details (Parallelism) Core concepts

46
SQL Monitoring Details (Parallelism) Core concepts

47
SQL Monitoring Details (Parallelism) Core concepts

48
SQL Monitoring Details Big Plans

49
SQL Monitoring Details Big Plans

50
SQL Monitoring Details Big Plans

51
SQL Monitoring Details Big Plans

52
SQL Monitoring Details Big Plans

53
SQL Monitoring Details Poor Indexing

54
SQL Monitoring Details Partially Parallelized

55
SQL Monitoring Details Partially Parallelized

56
SQL Monitoring Details Partially Parallelized

57
SQL Monitoring Details FORCE PARALLEL QUERY
PARALLEL 4

58
SQL Monitoring Details FORCE PARALLEL QUERY
PARALLEL 4

59
SQL Monitoring Details Advanced PQ Skews

60
SQL Monitoring Details Advanced PQ Skews

61
SQL Monitoring Details Advanced PQ Skews

62
SQL Monitoring Details Advanced PQ Skews
63
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 whats 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 it!


64
Q

A
About PowerShow.com