J2EE Design PatternsTo Introduce some of the performance tuning
techniques used
*
*
Optimizer determines how Oracle will retrieve data to satisfy a
given SQL statement - known as the execution plan
Types of optimizers – Rule/Cost
Rule based - outdated and should be avoided , execution plan based
on predefined rules
*
Choosing the “best” execution plan
first_rows_n – pagenated report with sum at the last page
all_rows – calculation process like balance sheet where complete
calculations matters
Choose – use the best possible available
*
Old method : analyze table <table_name> estimate
statistics;
New method – use dbms_stats package for in depth and better
statistcs.
The following query can be used in order to check the the last
analyzed date of the tables :
select table_name, last_analyzed from user_tables order by
last_analyzed;
*
select lpad(' ',2*level)||operation||' '||options||' '||
object_name q_plan
from plan_table
start with id=1;
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
4 2 INDEX (RANGE SCAN) OF 'IDX_GAM_CUST_ID' (NON-UNIQUE)
*
Create plan_table if it doesn‘t exist using the script available at
(%ORACLE_HOME%/rdbms/admin/utlxplan.sql ) and use it to know
execution plan
Evaluating Large-table, full-table scans
Index Usage Analysis
Enable Usage of Index using : - alter index <index_name>
monitoring usage, and query v$object_usage in order to check the
same.
Reports on system-wide SQL execution with v$sqlarea
*
*
Using hints (index,full,rule,etc)
SELECT /*+ index(A) index(b) */ FROM A,B WHERE A.ind_col =
B.ind_col AND A.value = 1 AND B.value = 2;
Changing the system-wide optimizer mode.
Alter system set optimizer_mode=all_rows;
Changing optimizer mode for specific statements
SELECT /*+ first_rows(100) */ FROM A,B WHERE A.ind_col = B.ind_col
AND A.value = 1 AND B.value = 2;
Re-writing SQL queries
Table join order evaluation
Place the most limiting tables for the join first in the FROM
clause.
Using the ordered hint
*
*
B-Tree indexes - Normal index used for High cardinality data, eg.
Employee number. Uses optimised binary search algorithum
Bitmap Indexes - for low cardinality data, eg. Gender column.
Uses a bitmap to trace the data, number of columns = distinct
values + rowid
Function-based Indexes – to_char(sysdate,‘mmddyyyy‘)
Index-only tables - all columns are indexed
*
Identify killing statements (disk reads, cpu utilization,user
feedback, debug messages), query the dictionary views like
v$sqlarea (disk_reads, buffer_gets, cpu_time)
Identify killing clause – comment out where conditions 1 by 1 and
check execution time and execution plan
Hints : (SET AUTOTRACE ON/ ON EXPLAIN/ ON STATISTICS
/TRACEONLY)
Try with adding hints/indexes, refresh table statistics, table
reorganization, even try with dropping index
*
Use OR rather than UNION, union all instead of union
Avoid ‘NOT IN’
Avoid Cartesian products on tables with large numbers of rows
Use Truncate command when deleting all the rows
*
*
