serverjas.blogg.se - Quest sql tuning for oracle

If two steps are indented at the same level, the uppermost statement is executed first.The more heavily indented an access path is, the earlier it is executed.SQL> SELECT RTRIM (LPAD (' ', 2 * LEVEL) || RTRIM (operation) ||' '||RTRIM (options) || ' ' || object_name) query_plan 2 FROM plan_table 3 CONNECT BY PRIOR id = parent_id 4* START WITH id = 0 QUERY_PLAN - SELECT STATEMENT SORT AGGREGATE TABLE ACCESS FULL SALES

Suitably formatted queries can be used to extract the execution plan from the plan table.Ī simple EXPLAIN PLAN SQL> EXPLAIN PLAN FOR select count(*) from sales where product_id=1 Explained.

The execution plan is stored in an Oracle table called the “plan table”.The execution plan reveals the exact sequence of steps that the Oracle optimizer has chosen to employ to process the SQL.The EXPLAIN PLAN reveals the execution plan for an SQL statement.Effective SQL tuning requires either familiarity with these tools or the use of commercial alternatives such as SQLab.The foundation tools for SQL tuning are:.

Make sure your developers understand EXPLAIN PLAN and tkprof, or equip them with commercial tuning tools.

Make sure you establish a reasonable sub-set of production data that is used during development and tuning of SQL.

A significant portion of SQL that performs poorly in production was originally crafted against empty or nearly empty tables.

Hint #2: Establish a tuning and development environment

Consider de-normalization and other performance by design features early on.

Consider prototyping critical portions of the applications.

Measure performance as early as possible.

Test the SQL for these transactions against simulations of production data.

Explicitly identify performance targets.

Hint #1: Design and develop with performance in mind

Take advantage of Cost Based Optimizer.

Design and develop with performance in mind.

Top tips for Oracle SQL tuning BUY GUY’S BOOK BUY QUEST PRODUCTS BUY QUEST PRODUCTS Guy Harrison Senior Software Architect, Quest Software