SQLProcess Tuning in a Cost Based World

1
SQL/Process Tuning in a Cost Based World

• Dan Matasek
• Database Manager
• Brunswick Corporation (Life Fitness)
• Monday February 28, 2005

2
Please remember to complete survey
Warning This is an E-Business Suite centric
presentation.
3
Agenda

• Introduction
• CBO Overview (Quick)
• Initialization Parameters Effecting CBO
• Gathering Statistics
• Finding Processes to Tune

4
Agenda (contd)

• Gathering Diagnostics Data
• Reading Diagnostics Data
• Fixing the Issue
• Keeping Things Running Smoothly
• Case Studies
• QA

5
Personal Introduction

• 13 years of Oracle Applications implementation
  experience
• Current Environment
• Oracle Applications 11.5.9
• Database version 9.2.0.4
• Financials, OM, MFG, Shipping, CRM
• 500 Users
• IBM AIX O/S
• Updated presentation will be posted on
  www.matasek.com

6
Who is Brunswick ?
7
Cost Based Optimizer Overview

• Introduced in version 7
• Actually worked in 8i
• Uses information about objects to determine
  lowest cost of access
• Initialization parameters effect global decisions
• Rule based optimizer not even part of release 10g

Cost based Optimizer greatly enhanced in version
10G
8
Initialization Parameters Affecting the CBO

• Follow document 216205.1
• optimizer_features_enable
• db_block_size db_file_multiblock_read_count
• optimizer_max_permutations
• query_rewrite_enabled
• hash_area_size sort_area_size

9
Initialization Parameters Affecting the CBO

• Optimizer_index_caching
• Optimizer_index_cost_adj

10
Gathering Statistics

• How often should I gather statistics?
• What percent should I use?
• Gather statistics when your data skew changes
• On a scheduled periodic basis

11
Gathering Statistics - Process
Automatically done in 10G version of DBMS_STATS

• Backup existing statistics
• Can be done for a specific table(s) using
  concurrent request or whole database using
  DBMS_STATS package
• Determine how new statistics will affect
  important processes
• Gather while few users are on the system

Can lock statistics in 10G
12
Gathering Statistics - Process

• Not just running Gather Schema Statistics
  request
• Use highest percentage that completes in time
  allowed
• See Safely Navigating the RBO-to-CBO Minefield
  by John Kanagaraj, at http//www.geocities.com/joh
  n_sharmila/links.htm
• Run each schema independently
• Do not forget custom schemas

11.5.10 now includes support for the
auto-gather feature, which will only gather
statistics that changed greater than a threshold
(default 10)
13
Gather Statistics - Process (contd)

• Never gather statistics for SYS (or SYSTEM)
• Note Several documents that I have seen recently
  seem to suggest that statistics on the SYS and
  SYSTEM schema should be gathered. I asked Oracle
  to clarify this position and they responded with
  the following. If you are having performance
  issues related to data dictionary queries, then
  you can try and gather statistics for SYS. If
  this does not correct the problem, you should
  delete the statistics. Oracle does not support
  gathering statistics on the SYS or SYSTEM schema
  until release 10g.

Do not forget about the effect of system
statistics which are optional in 9i and mandatory
in 10G
14
Gather Statistics - Process (contd)

• Purge data before running gather process
• Standard purges
• Workflow, Concurrent Requests, Alerts, Login
  Audit
• Application specific purges
• ICXDLTMP.sql
• MTL, MRP, BOM temporary tables
• intf tables

15
Gather Statistics - How

• Analyze
• Included for backwards compatibility
• DBMS_STATS
• FND_STATS (Oracle Applications Mandate)
• Wrapper process around DBMS_UTILITY which calls
  DBMS_STATS
• Artificially sets statistics for tables in
  FND_EXCLUDE_TABLE_STATS
• Default (11.5.9) contains 445 tables, across 29
  different applications

16
What Processes to Tune (First)

• High CPU/Memory/Disk Utilization?
• High Usage?
• ROI?
• Political Factors (VP push)?

17
Gathering Diagnostic Data

• Ratio Based Tuning
• Statspack
• SQLTrace
• V Tables

18
SQL Trace

• Level 0 - SQL Trace is not enabled
• Level 1 outputs the session SQL
• Level 4 outputs the session SQL and the values of
  bind variables
• Level 8 outputs the session SQL and wait
  information
• Level 12 outputs the session SQL with bind
  variables and wait information

19
Generating Trace Files
11.5.10 includes ability to generate different
trace level over multiple UIs (forms, self
service, etc)

• Forms Session
• Use Help --gt Diagnostics Menu
• Apps password required
• Only once per session
• Concurrent Request
• Use Enable Trace on concurrent request setup
  (gets Level 1 trace)
• Modify code to get higher level trace (event
  10046)
• Doing both will result in level 1 trace

11.5.10 can specify trace at request submit time.
Using debug button, enabled by profile option
(Concurrent Allow Debug).
20
Generating Trace Files - New
OAM.H with Rollup 1 (delivered with 11.5.10) -
can use debug workbench to conditionally start
logging and/or tracing.
21
How to Trace SQLPlus Scripts

• Your Script
• Line 1
• Line 2
• Line 3
• exit

• alter session set timed_statistics TRUE
• alter session set max_dump_file_size UNLIMITED
• alter session set tracefile_identifier token
• alter session set events 10046 trace name
  context forever, level 12

22
How to Trace Oracle Reports

• Locate .rdf file
• Make a backup copy
• Use Report Builder tool to Open Before Parameter
  Form Report Trigger
• Add
• srw.do_sql(alter session set timed_statistics
  TRUE)
• srw.do_sql(alter session set max_dump_file_size
  UNLIMITED)
• srw.do_sql(alter session set events 10046
  trace name context forever, level 12)
• Recompile the .rdf file

23
Tracing Self-Service Forms

• Note the Diagnostics icon at the top of the
  page. Click on it to turn on trace
• Two options
• Show Log
• Set Trace Level
• Select Set Trace Level
• Click Go

24
Tracing Concurrent Requests Another Method

• Logon to Applications
• Select Profile
• Query profile option Initialization SQL Statement
• This option can contain any legal SQL or PL/SQL
  statement
• Add the following statement
• BEGIN FND_CTL.FND_SESS_CTL('','','TRUE','TRUE','',
  'ALTER SESSION SET MAX_DUMP_FILE_SIZE 8000000
  EVENTS'''''' 10046 TRACE NAME CONTEXT
  FOREVER, LEVEL 12 ''''')END

Careful using application specific tracing. (OM
Debug Level and QP Debug)
25
Reading Trace Data

• Metalink Resources
• Optimizing Oracle Performance
• by Cary Millsap with Jeff Holt
• Hotsos Profiler (www.hotsos.com)
• TKPROF

Trace files created in 11.5.10 will contain the
name of the user in the trace file name.
26
Reading Trace Data - TKPROF

• Use same version of tkprof to read data that was
  used to create the trace file (9i vs. 8i/8.0)

SELECT COUNT() FROM FND_CONCURRENT_PROGRAM_SERIA
L WHERE RUNNING_APPLICATION_ID b1 AND
RUNNING_CONCURRENT_PROGRAM_ID b2 call
count cpu elapsed disk query
current rows ------- ------ --------
---------- ---------- ---------- ----------
---------- Parse 1 0.03 0.02
0 0 0 0 Execute
1 0.00 0.00 0 0
0 0 Fetch 1 0.00
0.01 2 2 0
1 ------- ------ -------- ----------
---------- ---------- ----------
---------- total 3 0.03 0.03
2 2 0 1 Misses
in library cache during parse 1 Optimizer goal
CHOOSE Parsing user id 28 Elapsed times include
waiting on following events Event waited on
Times Max. Wait Total
Waited ----------------------------------------
Waited ---------- ------------ SQLNet
message to client 3
0.00 0.00 SQLNet message from client
3 11.79 11.79
db file sequential read 2
0.01 0.01


27
Reading Trace Data - TKPROF

• 8i/8.0.6 Output

SELECT
COUNT() FROM FND_CONCURRENT_PROGRAM_SERIAL
WHERE RUNNING_APPLICATION_ID b1 AND
RUNNING_CONCURRENT_PROGRAM_ID b2 call
count cpu elapsed disk query
current rows ------- ------ --------
---------- ---------- ---------- ----------
---------- Parse 1 300.00 201.70
0 0 0 0 Execute
1 0.00 8.56 0 0
0 0 Fetch 1 0.00
159.47 2 2 0
1 ------- ------ -------- ----------
---------- ---------- ----------
---------- total 3 300.00 369.73
2 2 0 1 Misses
in library cache during parse 1 Optimizer goal
CHOOSE Parsing user id 28


28
Fixing the Issue

• Tuning the Process
• Tuning the System
• Tuning the SQL

Only 2 reasons for SQL performance issues Doing
to much of something, or not allowed to do
something.
29
Tuning the Process

• Eliminate non value added tasks
• Run static reports once and post
• Tune the concurrent manager
• Separate queues
• Using queue caching
• Use workshifts
• User training

30
Tuning the System

• Do not ignore easy changes made by O/S level
  upgrades, etc
• Instance migrated between servers of same make,
  O/S level and database level. New server was 3x
  more powerful than old server. Moving instance
  brought business processing to a standstill.
  UNIX administrators insisted Everything is the
  same.

31
CPU Utilization - JFS2
BAD !!!
32
CPU Utilization - JFS
GOOD!
33
Tuning SQL
By any means possible, including setting
optimizer_features_enable to lower level

• Determine bad performing SQL
• Duplicate SQL in controlled environment
• Force a better execution plan
• Determine why CBO chooses its path and not yours
  (event 10053)
• Change the behavior of the CBO
• Make sure you have not caused more problems than
  benefit

34
Changing behavior of the CBO

• Adjust object statistics
• Modifying init.ora parameters
• Modify code to add hint
• Used a stored outline

35
Isolating your fix

• Use test/UAT environment
• Look at Laredo by Hotsos (www.hotsos.com)

36
Keeping it Running Smooth
Metalink Doc 244040.1

• Stay current on patches
• Tune custom programs
• Keep init.ora at recommended settings (document
  any reason that they are not at recommended
  settings)
• Make sure all objects are analyzed
• Understand consequences of system changes
• Follow a structured approach to gathering
  statistics
• Purge data when available
• Rebuild indexes when needed

37
Case Studies
38
CS 1 - Tuning the Process

• Problem - Numerous slow running requests clogging
  concurrent manager queue. Poorly tuned SQL
  dominating the system.
• Solution - Run only required requests. Single
  stream requests when possible. Develop required
  and optional queues for important processes.

39
CS 2 - Tuning the Developers

• Problem - During upgrade project report that used
  to run in 45 minutes, now running is over 12
  hours.
• Environment
• Running as concurrent request, not able to get
  level 8 trace, trace file would be too big even
  if we could

40
CS 2 - Tuning the Developers

• SQLgt _at_whatsql 123
• SQL_TEXT
• --------------------------------------------------
  ----------------------------
• SELECT sum(mmt.transaction_quantity -1)
• FROM mtl_material_transactions mmt
• WHERE mmt.organization_id b2
• AND mmt.inventory_item_id b1
• AND mmt.transaction_date gt (sysdate - 365)
• AND mmt.transaction_type_id in (1, 32,
  33,35)
• GROUP BY mmt.inventory_item_id
• ID OPERATION
• ---------- ---------------------------------------
  ---------------------
• 0 SELECT STATEMENT () cost4
• 1 SORT (GROUP BY NOSORT) cost4
• 2 TABLE ACCESS (BY INDEX ROWID)
  MTL_MATERIAL_TRANSACTIONS cost4
• 3 INDEX (RANGE SCAN)
  MTL_MATERIAL_TRANSACTIONS_N1 cost3

26 Million Rows
Query will run 32000 times
Why?
Selectivity 4.68
41
CS 2 - Tuning the Developers

• SQLgt _at_sesswait 123
• EVENT TOTAL_WAITS
  TIME_WAITED
• ------------------------------ -----------
  -----------
• SQLNet message to client 40
  0
• log file sync 2
  2
• db file scattered read 2
  3
• SQLNet message from client 40
  21
• latch free 24
  28
• db file sequential read 2776670
  7349833
• 6 rows selected

Usually means poorly tuned SQL
42
CS 2 - Tuning the Developers

• INDEX_NAME LAST_ANAL
  NUM_ROWS DISTINCT_KEYS Sel
• ------------------------------ ---------
  ---------- ------------- ----------
• MTL_MATERIAL_TRANSACTIONS_N1 17-MAY-04
  26192778 1227139 4.68503
• MTL_MATERIAL_TRANSACTIONS_N10 17-MAY-04
  686 5 .72886
• MTL_MATERIAL_TRANSACTIONS_N11 17-MAY-04
  26930896 1 .00000
• MTL_MATERIAL_TRANSACTIONS_N12 17-MAY-04
  26864770 60689 .22591
• MTL_MATERIAL_TRANSACTIONS_N13 17-MAY-04
  0 0 100.00000
• MTL_MATERIAL_TRANSACTIONS_N14 17-MAY-04
  16337234 31040 .19000
• MTL_MATERIAL_TRANSACTIONS_N15 17-MAY-04
  27223077 32880 .12078
• MTL_MATERIAL_TRANSACTIONS_N16 17-MAY-04
  750829 95081 12.66347
• MTL_MATERIAL_TRANSACTIONS_N17 17-MAY-04
  750834 372045 49.55090
• MTL_MATERIAL_TRANSACTIONS_N18 17-MAY-04
  1385674 1211899 87.45917
• MTL_MATERIAL_TRANSACTIONS_N19 17-MAY-04
  427241 427241 100.00000
• MTL_MATERIAL_TRANSACTIONS_N2 17-MAY-04
  26988401 35489 .13150
• MTL_MATERIAL_TRANSACTIONS_N20 17-MAY-04
  0 0 100.00000
• MTL_MATERIAL_TRANSACTIONS_N21 17-MAY-04
  0 0 100.00000
• MTL_MATERIAL_TRANSACTIONS_N22 17-MAY-04
  0 0 100.00000
• MTL_MATERIAL_TRANSACTIONS_N23 17-MAY-04
  1879586 1833571 97.55185
• MTL_MATERIAL_TRANSACTIONS_N3 17-MAY-04
  27268918 38775 .14219

Useless index
In previous version this index had a
selectivity of 76
43
Monitoring Index Usage

• Metalink Docs 136642.1 and 144070.1
• Alter ltindexgt monitoring usage
• Check vobject_usage (column USED)
• Note Some degradation in performance (extra DML)

44
CS 2 - Tuning the Developers

• Suggestions
• 1) Redesign the process to get all of the data
  once, place it into a temporary table and query
  that table numerous times.
• 2) Why is a group by statement even used/needed.
• 3) Move the multiplication outside of the SUM
  operator.

45
CS 3 - Tuning Using V Data

• Problem - ONT Copy order functionality running
  multiple hours
• Consequences
• Department has 3 extra workstations devoted to
  doing order copies (RMAs)
• Copy orders had to be scheduled around peak
  processing
• Extra workstations locked up frequently

46
CS 3 - Tuning Using V Data

• Determine SQL Running

7.6 Million Rows
SELECT DISTINCT U.SERIAL_NUMBER FROM
MTL_UNIT_TRANSACTIONS_ALL_V U, MTL_MATERIAL_TRANSA
CTIONS M WHERE M.TRANSACTION_ID
U.TRANSACTION_ID AND U.SERIAL_NUMBER
NVL(b1,U.SERIAL_NUMBER) AND M.TRANSACTION_SOURC
E_TYPE_ID 2 AND M.TRX_SOURCE_LINE_ID b2
26 Million Rows
Useless Index (N15) selectivity .12
Selectivity 15
47
CS 3 - Tuning Using V Data

• Solution
• Create index on TRX_SOURCE_LINE_ID on
  MTL_MATERIAL_TRANSACTIONS
• Results
• Copy order functionality now completing in less a
  minute
• Any time of the day or month

48
CS 4 - Tuning with Trace Data

• Problem - After gathering statistics Pick Slip
  Report running over an hour, previously ran in
  less than a minute.
• Process
• Attempt 1 used Enable Trace checkbox on
  Concurrent Request
• Attempt 2 inserted trace statement in report

49
CS 4 - Tuning with Trace Data

• Results
• Run 1 - Duration 1 hr 3 min - Level 4 trace used
• Changed instance level optimizer parameters
  (specifically db_file_multiblock_read_count)
• Run 2 - Duration 7 min - Level 8 trace used
• Gathered statistics on WSH and INV schemas at 30
• Run 3 - Duration 5 seconds

50
CS 4 - Tuning with Trace Data

• Solution(s)
• Make sure init.ora parameters are in line with
  Oracle recommendations and mandates
• Make sure to backup statistics before generating
  new statistics
• Try generating statistics with higher estimate
  percent

51
CS 5 - Keeping Current with Patches

• Problem - Transact move orders form taking a long
  time to query when only order number is supplied
• Process
• Worked on tuning the SQL for over 10 man hours
• Analyst found direct hit on Metalink for that
  form Slow performance when querying by order
  number
• Applied patch and problem went away

52
CS 6 - Dealing with Oracle Reports

• Problem - Customized version of Inventory
  Transaction report was running very long.
• Process
• Ran report with level 4 trace enabled extracted
  SQL.
• Unable to duplicate the problem in SQLPlus.

53
CS 6 - Dealing with Oracle Reports

• Findings
• Query was coded in Reports 6i using a place
  holder variable, that gets populated during the
  before report trigger.
• Optimizer was not seeing the value of that
  variable when determining path
• Using a hint in program code eliminated the
  problem

54
CS 6 - Dealing with Oracle Reports

• WHERE p_catg_where
• AND p_item_where
• AND mmt.reason_id mtr.reason_id ()
• AND mmt.created_by fndu.user_id ()
• AND mmt.transaction_type_id
  mtxt.transaction_type_id ()
• AND mmt.locator_id mil.inventory_location_id
  ()
• AND mmt.organization_id mil.organization_id
  ()
• AND mmt.transaction_source_type_id ! 11
• AND mmt.transaction_id mtln.transaction_id
  ()
• AND mmt.transaction_id mut.transaction_id
  ()
• C_reason_where
• C_txn_type_where
• C_subinv_where
• C_type_break_where
• C_source_type_where
• C_source_where
• C_zero_dollar
• lp_transaction_id

Limiting Criteria
55
Questions and (hopefully) Answers
56
(No Transcript)