chap11.ppt - Montgomery College

Document Sample
chap11.ppt - Montgomery College Powered By Docstoc
					Oracle9i Developer:
   PL/SQL Programming

Chapter 11

Performance Tuning
• Gain awareness of tuning concepts
  and issues
• Explore SQL statement tuning
• Explore PL/SQL statement tuning
   Brewbean’s Challenge
• The application development group is
  beginning to test the Brewbean’s
• Concerns arise regarding response time
  in some of the operations
• Where do they begin to review
   Tuning Issues
• Identify statements that are causing
  lengthy execution times
• Determine how the statement is being
  processed and identify potential
  modifications for improvement
• Determine if modifications improved
  processing efficiency
   Identifying Coding Problems
• User testing feedback can narrow down
  the slow response areas
• V_$SQLAREA view contains execution
  statistics for all statements
• Number of reads per execution = disk
  reads / number of executions
• High number of reads highlights
  potential candidates for improvement
  V_$SQLAREA View Query

Note: A WHERE clause is used in this query to only display one
      SQL statement.
    SQL TRACE Facility
• Can store statistics for a session in an
  operating system file
• Set server parameters to use
• Turn on and off:

• Use TKPROF command at operating
  system level to convert the file to a
  readable format
SQL_TRACE File (partial listing)
   Oracle Optimizers
• Determine how a statement will be
• Two Optimizers available: rule-based
  and cost-based
• Rule-based Optimizer uses a list of rules
• Cost-based Optimizer uses object
• Object must be analyzed for the cost-
  based Optimizer to work
 Optimizer Mode
Value          Description

CHOOSE         If statistics exist for any of the tables accessed the cost-
               basis is used with a goal of best throughput. If some of
               the tables do not have statistics then estimation is
               used. If no statistics are available, the rule-basis is

ALL_ROWS       Always uses cost-basis with goal of best throughput.

FIRST_ROWS_n   Always uses cost-basis with goal of best response time
               to return the first n number of rows. n can be 1, 10, 100
               or 1000.

FIRST_ROWS     Available for backward compatibility. Uses a mix of
               cost and heuristics for fast delivery of the first few

RULE           Always uses rule-basis.
    Explain Plan
• Execution plan for the statement that is
  generated by the Optimizer
• Display using AUTOTRACE
   SQL*Plus Command               Description

    SET AUTOTRACE ON               Displays explain plan, statistics, and
                                   result set

    SET AUTOTRACE ON EXPLAIN       Displays explain plan and result set

    SET AUTOTRACE ON STATISTICS    Displays statistics and result set

    SET AUTOTRACE TRACEONLY        Displays explain plan and statistics
Explain Plan Example
Explain Plan Example (continued)
   Timing Feature
• Basic tool to measure processing times
• Displays execution time in hundredths of
• Always use second statement execution
  time to exclude statement caching time
Timing Example
      SQL Statement Tuning
• Performance tuning focuses on SQL
  statements as these tend to be more resource
• Overview of several issues:
  –   Column selection
  –   Optimizer used
  –   Index use
  –   Correlated subqueries
  –   Join driving table
  –   Optimizer hints
   Column Selection
• Including unnecessary columns in SQL
  statements causes additional processing
• Could lead to unnecessary table scans
  and joins
• A common error is including a column
  from a table that causes an additional
  join when the column is available from
  tables already in the query
• Different execution plans will be
  produced by each of the optimizers
• Cost-based Optimizer is typically more
  efficient as it uses object statistics to
  determine execution path such as the
  use of indexes
   Index Suppression
• Function on a WHERE clause column
  will suppress the use of an index
   Index Suppression
• Comparing a character column to a
  numeric value will suppress index usage
    Concatenated Indexes
• Concatenated indexes are indexes that
  include more than one column
• If all columns of the concatenated index
  are included in the criteria then the index
  will be used
• The Optimizer uses these indexes only
  when the leading column indexed is
  included in the criteria of the SQL
     Concatenated Index Example
 Concatenated Index:
   CREATE INDEX bb_shopname_idx
     ON bb_shopper (lastname, firstname);

Use of leading column of the index, index is used:
    Concatenated Index Example
Leading column of the index not included, index is
  not used:
    Subquery Considerations
• Subquery processes inner query first and
  treats result set as an IN list
• A correlated subquery executes the outer
  query first and executes the inner query for
  each row returned in the outer query
• Correlated subqueries tend to be more
• EXISTS operator will stop execution when
  match is found, IN does not stop
Correlated Subquery Example
    Join Driving Table
• The driving table is the table used first to
  begin the join operation
• Goal for efficiency – smallest table to be
  the driving table
• Rule-based Optimizer sets the last table
  in the FROM clause as the driving table
• Cost-based Optimizer sets the first table
  in the FROM clause as the driving table
    Optimizer Hints
• The execution plan of an SQL statement can
  be altered by including hints
• Hints include a comment with + sign
• Numerous hints are available and are
  grouped by the following categories:
  – The optimization approach for an SQL statement
  – The goal of the cost-based Optimizer for an SQL
  – The access path for a table accessed by the
  – The join order for a join statement
  – A join operation in a join statement
    Optimizer Hint Examples
• Hint on optimization approach:
  SELECT /*+RULE*/ firstname, lastname
     FROM bb_shopper
     WHERE lastname = ‘Parker’;

• Hint on table access path:
SELECT /*+FULL(b)*/ idshopper, firstname, lastname
 FROM bb_shopper b
 WHERE lastname = 'Parker';
   PL/SQL Statement Tuning
• Attention to a number of common
  PL/SQL coding issues can lead to
  greater efficiency:
  – Minimize loop iterations
  – Use ROWID in updates
  – Perform variable comparisons
  – Order conditional statements
  – Use PLS_INTEGER data type
  – Pin program units
   Minimize Loop Iterations
• Minimize both number of iterations and
  time for a single iteration
• Be sure that statements that do not have
  to be repeated for each iteration are
  located outside of the loop
• Be sure to minimize rows that need
  processing by loop by using WHERE
  and HAVING clauses
• ROWID – physical address of row
• Fastest path to row retrieval
  2 CURSOR prod_cur IS
  3    SELECT idproduct, price, ROWID
  4       FROM bb_product;
  5 lv_new NUMBER(7,2);
  7   FOR prod_rec IN prod_cur LOOP
  8      lv_new := ROUND(prod_rec.price * :g_pct,2);
  9      UPDATE bb_product
 10        SET price = lv_new
 11        WHERE rowid = prod_rec.ROWID;
 12   END LOOP;
 14 END;
   Variable Comparisons
• When comparing values of different data
  types, Oracle must implicitly convert
  values to the same type
• The conversion will add to the
  processing time
• Numeric to character comparison:
    FOR i IN 1..100000 LOOP
      IF i IN('5','10','15','20') THEN
        lv_cnt := lv_cnt + 1;
      END IF;
   Ordering Conditions
• Developers tend to order conditional
  statements in IF or CASE statements in
  logical order which may not be most
• If information is known regarding data
  values, place the most frequent true
  conditions first to minimize processing
   PLS_INTEGER Data Type
• A numeric data type that can hold
  whole number values ranging from
  negative to positive 2,147,483,647
• More efficient than the NUMBER data
   Pinning Program Units
• Stored program units that are executed
  are placed into the shared pool memory
  which serves as a cache
• Code is moved out of cache when
  memory space runs out
• Heavily used program units can be
  pinned into memory
  enables pinning
• The goal of tuning is to minimize resources
  needed by an application to improve response
• Performance tuning ranges from hardware
  configuration and database configuration to
  application code
• This section only addresses application code
• The V_$SQLAREA view and SQL Trace
  facility enable the review of processing
• Two optimizers exist: Rule-based and
• The explain plan outlines the execution
  path the Optimizer generates
• AUTOTRACE enables display of the
  explain plan
• The TIMING command allows the
  display of execution time
• SQL tuning includes a review of: selected
  columns, indexes, order of tables in the
  FROM clause, correlated subquery use, and
  optimizer identification
• PL/SQL tuning includes a review of: loop
  iterations, use of ROWID, variable data types
  in comparisons, order of conditional
  statements, use of PLS_INTEGER data type,
  and potential pinning opportunities
• This section is only an introduction to
  tuning to expose you to the topic

Shared By: