Simplified SQL Performance Management in Oracle Database 11g

1
(No Transcript)
2
(No Transcript)
3
Simplified SQL Performance Management inOracle
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 TuningSQL 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
CorrectionFactors
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 11gBetter 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 10gEnd-to-end
Workflow

Workload
one hour
AWR
A good end-to-end solution, but manual
intervention is required
18
Improvements in Oracle Database
11gFully-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
  recentlyDont re-tune SQLs if nothing has
  changed!

21
Tuning FlowTuning 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 DefaultsSensible 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
ProblemManaging 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 MonitoringLooking
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 AdditionsNew 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 DetailsCore concepts

42
SQL Monitoring DetailsCore concepts

43
SQL Monitoring DetailsCore concepts

44
SQL Monitoring DetailsCore 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 DetailsBig Plans

49
SQL Monitoring DetailsBig Plans

50
SQL Monitoring DetailsBig Plans

51
SQL Monitoring DetailsBig Plans

52
SQL Monitoring DetailsBig Plans

53
SQL Monitoring DetailsPoor Indexing

54
SQL Monitoring DetailsPartially Parallelized

55
SQL Monitoring DetailsPartially Parallelized

56
SQL Monitoring DetailsPartially Parallelized

57
SQL Monitoring DetailsFORCE PARALLEL QUERY
PARALLEL 4

58
SQL Monitoring DetailsFORCE PARALLEL QUERY
PARALLEL 4

59
SQL Monitoring DetailsAdvanced PQ Skews

60
SQL Monitoring DetailsAdvanced PQ Skews

61
SQL Monitoring DetailsAdvanced PQ Skews

62
SQL Monitoring DetailsAdvanced 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