The methodology of SQL optimization

• Find the most resource-intensive SQL statement –v$sqlarea (Shared_pool) –v$session_longops (6 seconds) –statspack Report–sql*trace + tkprof–10g ADDM–Toad, Quest Data center–... problem location How to find Bad Sql–v$sqlarea (shared_pool) –statspack–sql*trace + tkprof–10g ADDM optimized SQL statement – Understanding Optimizer, CBO & Rbo and Execution Plan –explain plan, Tkprof & Sql_trace, Autotrace, Toad–tune join (Sort Merge, Nest Loop, Hash join) –tune Index, MV (summery and Join Table) – partitioning, parallelism, optimization using hint and special SQL---Finding a large number of logical read statements

Select Buffer_gets, Sql_text

From V$sqlarea

where Buffer_gets > 200000

ORDER BY buffer_gets Desc;

Optimization of SQL statements:

• Index and data access index classifications:

Logically

– Single or combined index – unique non-unique index

On the physical

– Partitioned and non-partitioned indexes –b-tree and bitmap normal or reverse (B-tree only) – Function index join mode • Optimization hint hint partitioning • materialized view • Parallel • Other things about SQL optimization How Oracle accesses data single Table Access path–full Table scan–rowid unique scan–index unique Scan–index range scan–unique Index range Scan–index Skip scan–i Ndex (FAST) Full Scan–bitmap Index Table Join1, Nested Loop

The driving table should be small

The other tables is indexed.

2, Sort Merge

For large data sets (Sort_area_size)

The row sources is sorted already.

A sort operation does not has A to is done.

3, Hash Join

For large data sets (Hash_area_size)

Optimizer_mode=cbo

Pga_aggregate_target is big enough

The methodology of SQL optimization