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