Notes

Outline

SQL Issues and Tuning Considerations for Different Applications

Objectives After completing this lesson, you should be able to do the following: Identify the role of the DBA in application tuning Use optimizer modes to enhance SQL statement performance Manage stored outlines to store execution paths as a series of hints

Objectives Use the available data access methods to tune the physical design of the database Identify the demands of online transaction processing (OLTP) systems Identify the demands of decision support systems (DSS) Reconfigure systems on a temporary basis for particular needs

The Role of the DBA Application tuning is the most important part of tuning. DBAs may not be directly involved in application tuning. DBAs must be familiar with the impact that poorly written SQL statements can have upon database performance.

Diagnostic Tools Overview EXPLAIN PLAN SQL Trace and TKPROF SQL*Plus AUTOTRACE Oracle SQL Analyze

Explain Plan Can be used without tracing To use the explain plan: 1. Create PLAN_TABLE with utlxplan.sql. 2. Run the EXPLAIN PLAN SQL command. 3. Query PLAN_TABLE to display the execution plans.

SQL Trace and TKPROF 1. Set the initialization parameters. 2. Invoke SQL Trace. 3. Run the application. 4. Turn off SQL Trace. 5. Format the trace file with TKPROF. 6. Interpret the output.

Enabling and Disabling SQL Trace Instance level: SQL_TRACE = {TRUE|FALSE} Session level:

Formatting the Trace File with TKPROF

TKPROF Statistics Count: Number of execution calls CPU: CPU seconds used Elapsed: Total elapsed time Disk: Physical reads Query: Logical reads for consistent read Current: Logical reads in current mode Rows: Rows processed

SQL*Plus AUTOTRACE Create PLAN_TABLE Run plustrce.sql from the ORACLE_HOME/sqlplus/admin directory AUTOTRACE syntax:

Optimizer Modes Rule-based: Uses a ranking system Syntax- and data dictionary–driven Cost-based: Chooses least-cost path Statistics-driven

Setting the Optimizer Mode Instance level: optimizer_mode = {choose|rule|first_rows|all_rows} Session level: alter session set optimizer_mode = {choose|rule|first_rows|all_rows} Statement level: Using hints

Managing Statistics Use the ANALYZE command to collect or delete statistics. Use the DBMS_STATS package: GATHER_TABLE_STATS GATHER_INDEX_STATS GATHER_SCHEMA_STATS GATHER_DATABASE_STATS

Table Statistics Number of rows Number of blocks and empty blocks Average available free space Number of chained or migrated rows Average row length Last ANALYZE date and sample size Data dictionary view: DBA_TABLES

Index Statistics Index level (height) Number of leaf blocks and distinct keys Average number of leaf blocks per key Average number of data blocks per key Number of index entries Clustering factor Data dictionary view: DBA_INDEXES

Column Statistics Number of distinct values Lowest value, highest value Last ANALYZE date and sample size Data dictionary view: USER_TAB_COL_STATISTICS

Histograms Describe the data distribution of a particular column in more detail Better predicate selectivity estimates for unevenly distributed data Create histograms with ANALYZE TABLE ... FOR COLUMNS ... Data dictionary view: DBA_HISTOGRAMS

Copying Statistics Between Databases

Example: Copying Statistics

Optimizer Plan Stability Allows applications to force the use of a desired SQL access path Maintains  consistent execution path through database changes Is implemented using a stored outline consisting of hints

Plan Equivalence SQL statement text must match Plans are maintained through: New Oracle versions New statistics on objects Initialization parameter changes Database reorganization Schema changes

Creating Stored Outlines

Using Stored Outlines Set the USE_STORED_OUTLINES parameter to TRUE or to a category name Both CREATE_STORED_OUTLINES and USE_STORED_OUTLINES can be set at the instance or session level

Maintaining Stored Outlines Use the OUTLN_PKG package to: Drop outlines or categories of outlines Rename categories Use the ALTER OUTLINE command to: Rename an outline Rebuild an outline Change the category of an outline Outlines are stored in OUTLN schema

Data Access Methods To enhance performance, you can use the following data access methods: Indexes (B-tree, bitmap, reverse key) Index-organized tables Clusters Histograms Materialized views

B-Tree Index

Bitmap Index

Bitmap Indexes Used for low-cardinality columns Good for multiple predicates Use minimal storage space Best for read-only systems Good for very large tables

Creating and Maintaining Bitmap Indexes

Comparing B-Tree and Bitmap Indexes

Reverse Key Index

Creating Reverse Key Indexes

Index-Organized Tables

Index-Organized Tables Compared with Regular Tables Faster key-based access to table data Reduced storage requirements Secondary indexes and logical ROWIDs Main restrictions: Must have a primary key Cannot use unique constraints Cannot be clustered

Creating Index-Organized Tables

IOT Row Overflow

IOT Dictionary Views

Clusters

Cluster Types

Situations in Which Clusters Are Useful

Materialized Views Are instantiations of a SQL query Can be used for query rewrites Refresh types: Complete or fast Force or never Refresh modes: Manual Automated (synchronous or asynchronous)

Materialized Views: Manual Refresh Refresh-specific MVs: MVs based on one or more base tables: All MVs due for refresh:

Query Rewrites To use MVs instead of the base tables, a query must be rewritten. Query rewrites are transparent and do not require any special privileges on the MV. MVs can be enabled or disabled for query rewrites.

Query Rewrites The initialization parameter QUERY_REWRITE_ENABLED must be set to TRUE. The QUERY REWRITE privilege allows users to enable materialized views. The DBMS_OLAP package has options to use materialized views.

Materialized Views and Query Rewrites: Example

Materialized Views and Query Rewrites: Example

Enabling and Controlling Query Rewrites

Disabling Query Rewrites: Example

OLTP Systems High throughput, insert- and update-intensive Large, continuously growing data volume Concurrent access by many users Tuning goals: Availability Speed Concurrency Recoverability

OLTP Requirements Explicit space allocation Indexes: Not too many (prefer B-tree to bitmap) Reverse key for sequence columns Rebuilt regularly Clusters for tables in join queries: Index clusters for growing tables Hash clusters for stable tables

OLTP Requirements Short transactions do not require big rollback segments; multiple rollback segments prevent contention. A large MINEXTENTS value is required.

OLTP Application Issues Use database constraints instead of application code. Make sure that code is shared. Use bind variables rather than literals for optimally shared SQL.

DSS Systems Queries on large amounts of data Heavy use of full table scans Tuning goals: Fast response time Accuracy Availability Parallel Query is particularly designed for DSS environments

DSS Requirements Storage allocation: Set db_block_size and db_file_multiblock_read_count  carefully. Ensure that extent sizes are multiples of this parameter value. Run ANALYZE regularly.

DSS Requirements Evaluate the need for indexes: Use bitmap indexes when possible. Use index-organized tables for (range) retrieval by PK. Generate histograms for indexed columns that are distributed nonuniformly. Clustering: Consider hash clusters for performance access.

DSS Application Issues Parse time is less important. The execution plan must be optimal. Use the Parallel Query feature. Tune carefully, using hints if appropriate. Test on realistic amounts of data. Consider using PL/SQL functions to code logic into queries. Bind variables are problematic.

Multipurpose Applications Combination of OLTP and DSS Hybrid systems rely on several configurations

Hybrid Systems

Parameters for Hybrid Systems Memory use: SHARED_POOL_SIZE LARGE_POOL_SIZE DB_BLOCK_BUFFERS SORT_AREA_SIZE Parallel Query: Reconfigure parameters for DSS

Hybrid Systems Configuration Online rollback segments: More small ones during the day Fewer, large ones at night Multithreaded server (MTS): For peak-time use, not for DSS

Summary In this lesson, you should have learned that: Application tuning often results in the greatest performance benefits. You tune CBO with parameters and hints. You use stored outlines for plan stability. You should apply available data access methods appropriately.

Summary For OLTP, try to reach the following goals: Immediate access to small amounts of data (indexing, hashing) Immediate concurrent access to transaction tables Shared code to cut down parse time No space allocation during peak hours

Summary For DSS, try to reach the following goals: Data tightly packed into large blocks Careful tuning of queries Histograms generation Query rewrites using materialized views and dimensions Well-configured Parallel Query support