Thomas E' Canty, Senior DBA ServerCare, Inc' Session

1
Thomas E. Canty, Senior DBAServerCare,
Inc.Session 442
A Tuning Methodology using SQL Modeling
2
Speaker Qualifications

• Thomas E. Canty, Senior Oracle DBA, ServerCare,
  Inc.
• 18 years of Oracle experience, starting with
  version 5
• Has been using this technique for nearly 15 years
• Has presented at IOUG, OpenWorld, and NoCOUG
• Has been a DBA, Developer, Architect, and IT
  Manager

• Has worked with Fortune 100 companies in
  Healthcare, Technology, Pharmaceuticals, and
  Telecom, as well as Major Universities

888-918-6309 http//www.ServerCare.com
3
Areas of Tuning

• Operating System, Hardware, I/O, Network
• Database, Schema, Indexes
• Application, SQL

From your friendly DBADont Neglect the SQL!
4
Why Tune SQL Anymore?

• Doesnt the Cost-Based Optimizer take care of
  poor SQL now?

NO!!
5
The CBO does not do everything

• Logic Errors
• Redundant Joins
• Missing Indexes
• Nested Views
• Old or Missing stats
• Poor Design

6
Introducing a Modeling Method

• Identify bad SQL
• Draw a model of the SQL
• Dig deeper
• Identify problems and solutions
• Test solution(s)
• Start again

7
Our Example SQL Statement

• select d.dname, m.ename mgr, e.ename,
  e.salnvl(e.comm,0)nvl(b.comm,0) total_compfrom
  dept d, emp m, emp e, bonus bwhere d.deptno
  e.deptno and e.mgrm.empno () and
  e.enameb.ename () and e.jobb.job () and
  e.sal gt 1000order by 1,2,3

Show total compensation for each employee that
has a base salary gt 1000, for each department and
manager
8
Objects (Tables, Views, IOTs, etc.)
9
Our Example
Same Table
10
Joins
11
Our Example
Outer Join
12
Operators (Where Clauses)
13
Our Example
Greater Than operator
14
Indexes
15
Our Example
Compound, Nonunique Index
16
Select List
17
Selectivity and Sizes
Rows fromSelect
Rows in Table
18
Special Cases

• Unions Split up
• Partitioned Tables Treat as View
• Index Organized Tables (IOTs) Just an Index
• Materialized Views Treat as Table
• Subqueries
• Normal (In) Subqueries
• Correlated Subqueries
• Not In/Not Exists Subqueries

19
Subqueries New Example

• select d.dname, m.ename mgr, e.ename,
  e.salnvl(e.comm,0)nvl(b.comm,0) total_compfrom
  dept d, emp m, emp e, bonus bwhere d.deptno
  e.deptno and e.mgrm.empno () and
  e.enameb.ename () and e.jobb.job () and
  e.sal gt 1000 and exists (select 1 from salgrade
  s where e.sal between s.losal and
  s.hisal)order by 1,2,3

Show total compensation for each employee that
has a base salary gt 1000 and the salary falls
within a known salary grade, for each department
and manager
20
Subqueries
Between Operator
21
Gathering Information

• Tools TOAD, Statspack, OEM, SQLPlus
• Look at all_objects, esp. object_type
• Look at all_synonyms, then repeat
• Look at all_tab_partitions
• Look at all_views, then repeat
• Look at all_indexes all_ind_columns
• Look for stats must be up to date!
• Otherwise, select count(pk) from tables

22
Case Study 1 Video on Demand

• SELECT c.ID, m.ID, s.service_idFROM content c,
  movie m, movie_to_service sWHERE c.filename
  'file_name' AND (c.ID m.title_content_id
  OR c.ID m.clip_content_id) AND m.movie_active
  'Y' AND s.movie_id m.ID
• Execution Plan-----------------------------------
  -----------------0 SELECT STATEMENT
  OptimizerCHOOSE (Cost27 1 0 HASH JOIN
  (Cost27 Card2 Bytes90)2 1 NESTED LOOPS
  (Cost25 Card2 Bytes78)3 2 TABLE ACCESS
  (FULL) OF 'CONTENT' (Cost21 4 2 TABLE
  ACCESS (FULL) OF 'MOVIE' (Cost4 5 1 INDEX
  (FAST FULL SCAN) OF 'PK_MOVIE_TO_SERVICE'

23
Case Study 1 contd
24
Additional Case Studies - Live

• Author will show the modeling technique in
  action, with live examples

25
Challenges with the Model

• Intersection Minus become Subquery?
• Updates, esp. correlated updates
• Only models individual SQL, not programs
• Many newer features need to be defined, such as
  Object Orientation (varrays, nested tables,
  etc.), XML schemas, Subquery reuse, Cubes
  Stars, LOBs

26
Alternative Resource

• Dan Tows excellent book, SQL Tuning
• Takes a more mathematical approach
• Developers perspective
• Uses different technique standards
• Tow, Dan SQL Tuning. Sebastopol, CA O'Reilly
  Media, Inc., 2003.

27
Items Learned in this Session

• Although this was just an introduction, attendees
  have learned
• How to model tables views
• How to diagram relationships, where clauses,
  indexes, and select lists
• How to identify missing indexes
• How to identify redundancy logic issues
• From here it should be easy to see how a model
  can be used to address performance.

28
Questions?

• Lots of things we didnt cover
• If we dont cover something you wanted to hear,
  please contact me.

29
Session 442A Tuning Methodology using SQL
Modeling
THANK YOU!
Please fill out evaluations!
Email Tom Canty tom.canty_at_servercare.com
Or Call 888-918-6309 http//www.ServerCare.com