[PDF] Oracle® Database - SQL Tuning Guide

View - Download Oracle® Database - SQL Tuning Guide

Previous PDF

Next PDF

boldfaceBoldface type indicates graphical user interface elements associated

with an action, or terms defined in text or the glossary.italicItalic type indicates book titles, emphasis, or placeholder variables for

which you supply particular values. monospaceMonospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter.

DBMS_STATS

DBMS_STATS

DBMS_STATS

AUTO_STAT_EXTENSIONS

ON OFF

DBMS_STATS

im_imcu_count im_block_countscanrate

DBMS_STATS

PREFERENCE_OVERRIDES_PARAMETER

PREFERENCE_OVERRIDES_PARAMETER

FLUSH_DATABASE_MONITORING_INFO

DBA_TAB_STATISTICS

DBA_IND_STATISTICS

DBA_TAB_MODIFICATIONS

OPTIMIZER_ADAPTIVE_PLANS

OPTIMIZER_ADAPTIVE_STATISTICS

WITH

ȑOR

CONCATENATION

OR

UNION-ALL

LTRIM

TO_NUMBER

V$

OPTIMIZER_ADAPTIVE_FEATURES

GROUP BY

VECTOR

GROUP BY

SYS_AUTO_SPM_EVOLVE_TASK

DBMS_SPM

SYSAUX

AUTO_SAMPLING

JOB_QUEUE_PROCESSES

DBMS_STATS

DBMS_STATS

DBMS_STATS

CREATE TABLE AS SELECT

INSERT INTO ... SELECT

ALTER TABLE EXCHANGE

INCREMENTAL

true

CUBE JOIN

SIMILAR

CURSOR_SHARING

FORCE

Database architectureDatabase architecture is not

the domain of administrators alone. As a developer, you want to develop applications in the least amount of time against an Oracle database, which requires exploiting the database architecture and features. For example, not understanding

Oracle Database concurrency

controls and multiversioning read consistency may make an application corrupt the integrity of the data, run slowly, and decrease scalability.Oracle Database Concepts explains the basic relational data structures, transaction management, storage structures, and instance architecture of Oracle Database.SQL and PL/SQLBecause of the existence of

GUI-based tools, it is possible

to create applications and administer a database without knowing SQL. However, it is impossible to tune applications or a database without knowing SQL.Oracle Database Concepts includes an introduction to Oracle

SQL and PL/SQL. You must also

have a working knowledge of

Oracle Database SQL Language

Reference, Oracle Database

PL/SQL Packages and Types

Reference, and Oracle Database

PL/SQL Packages and Types

Reference.SQL tuning toolsThe database generates

performance statistics, and provides SQL tuning tools that interpret these statistics.Oracle Database 2 Day +

Performance Tuning Guide

provides an introduction to the principal SQL tuning tools.

ȄUNION

UNION ALLȄ

DBMS_STATS

WHERE

SELECT

DBMS_SPM

DBMS_XPLAN

DBMS_XPLAN

EXPLAIN PLAN

V$SQL_PLAN

EXPLAIN PLAN

ȑV$SQL_PLAN

AUTOTRACE

AUTOTRACE

V$SQL_MONITOR

V$SQL_PLAN_MONITOR

V$ACTIVE_SESSION_HISTORYȑ

V$SESSIONȑ

V$SESSION_LONGOPSȑ

V$SQLȑ

V$SQL_PLAN

V$

DBMS_MONITOR

DBMS_SESSION

ȑTKPROF

trcsess trcsess

TKPROF

trcsess

DBMS_MONITOR

DBMS_SESSION

SELECT /*+ INDEX (employees emp_department_ix) */ employee_id, department_id FROM employeesWHERE department_id > 50;

DBMS_SQLTUNE

SELECT * FROM employees WHERE last_name LIKE ©KING©; SELECT * FROM employees WHERE last_name LIKE :1;

Test #Users SupportedNo Parsing all statements 270 Soft Parsing all statements 150

Hard Parsing all statements 60Re-Connecting for each Transaction 30

Generation of

multiple execution plans

Generation of

query plan

Parsing

Optimization

Row Source

Generation

Execution

Hard ParseSoft Parse

Semantic

Check

Syntax

Check

Shared Pool

Check

SQL Statement

FROM FORM

SQL> SELECT * FORM employees;SELECT * FORM employees *ERROR at line 1:ORA-00923: FROM keyword not found where expected

SQL> SELECT * FROM nonexistent_table;SELECT * FROM nonexistent_table *ERROR at line 1:ORA-00942: table or view does not exist

V$SQL.SQL_ID

UPDATE

Comparison of hash valuesUser

Server

ProcessClient

Process

Private SQL Area

UserUpdate ...

PGA

SQL Work Areas

Session Memory

3967354608

System Global Area (SGA)

Shared Pool

Private

SQL Area

Shared SQL Area

3667723989

3967354608

2190280494Library Cache

Data

Dictionary

Cache

Server

Result

Cache

Other Reserved

Pool CREATE TABLE my_table ( some_col INTEGER );SELECT * FROM my_table;

SELECT

my_table

ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;ALTER SYSTEM FLUSH SHARED_POOL; # optimizer environment 1SELECT * FROM sh.sales;ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS; # optimizer environment 2SELECT * FROM sh.sales;ALTER SESSION SET SQL_TRACE=true; # optimizer environment 3SELECT * FROM sh.sales;

SELECT

SELECT

AUTOTRACE

A

SELECT e.last_name, j.job_title, d.department_name FROM hr.employees e, hr.departments d, hr.jobs jWHERE e.department_id = d.department_idAND e.job_id = j.job_idAND e.last_name LIKE ©A%©; Execution Plan----------------------------------------------------------Plan hash value: 975837011---------------------------------------------------------------------------| Id| Operation | Name |Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------| 0| SELECT STATEMENT | | 3 |189 |7(15)|00:00:01 ||*1| HASH JOIN | | 3 |189 |7(15)|00:00:01 ||*2| HASH JOIN | | 3 |141 |5(20)|00:00:01 || 3| TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 3 | 60 |2 (0)|00:00:01 ||*4| INDEX RANGE SCAN | EMP_NAME_IX | 3 | |1 (0)|00:00:01 || 5| TABLE ACCESS FULL | JOBS |19 |513 |2 (0)|00:00:01 || 6| TABLE ACCESS FULL | DEPARTMENTS |27 |432 |2 (0)|00:00:01 |--------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------

1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") 2 - access("E"."JOB_ID"="J"."JOB_ID") 4 - access("E"."LAST_NAME" LIKE ©A%©) filter("E"."LAST_NAME" LIKE ©A%©)

Id

Operation

SELECT

TABLE ACCESS

FULL jobs 53

TABLE ACCESS

BY INDEX ROWID

employees 4

INDEX RANGE

SCAN emp_name_ix 6

TABLE ACCESS

FULL departments 1

HASH JOIN

2

HASH JOIN

Ȅdepartments

jobs emp_name_ix A

Atkinson

AAAPzRAAFAAAABSAAe

employees

AAAPzRAAFAAAABSAAe

Atkinson

Atkinson

Stock Clerk

Atkinson

Shipping

emp_name_ix employees jobs jobs

CREATETABLE

CREATE TABLE mytable (mycolumn INTEGER);

ȑCOMMIT

CREATE TABLE

COMMIT

ROLLBACK

Parsed Representation

of SQL Statement

Input Output

Final Plan with

Lowest Cost

GB HJHJ

Optimizer

1 0 1 1 0 0 1 0 0

StatisticsGenerates Multiple Plans and Compares Them Plan 1 GB NLNL Plan 2 GB HJHJ Plan 2

SELECT

SELECT first_name, last_nameFROM hr.employeesWHERE department_id IN (SELECT department_id FROM hr.departments WHERE location_id = 1800);

FROM Query

Transformer

Estimator

Plan

GeneratorParsed Query

(from Parser)

Query Plan

(to Row Source Generator)Transformed query

Query + estimates

Data

Dictionarystatistics

1Query TransformerThe optimizer determines whether it is helpful

to change the form of the query so that the optimizer can generate a better execution plan." "2EstimatorThe optimizer estimates the cost of each plan

based on statistics in the data dictionary.""3Plan GeneratorThe optimizer compares the costs of plans

and chooses the lowest-cost plan, known as the execution plan, to pass to the row source generator."" OR

UNION ALL

Query Transformer

SELECT *

FROM sales

WHERE prod_id=136

UNION ALL

SELECT *

FROM sales

WHERE promo_id=33

AND LNNVL(prod_id=136);

SELECT *

FROM sales

WHERE promo_id=33

OR prod_id=136;

0 1

WHERE last_name LIKE ©A%©

0 1

DBMS_STATS

DISTINCT

GROUP BY

Rows

Estimator

1 0 1 0 0

0 0 0 1 1

0 1 1 0 1 Statistics

PlanGB

HJHJTotal Cost

Cardinality

SelectivityCost

---------------------------------------------------------------------------|Id| Operation |Name |Rows|Bytes|Cost %CPU|Time|---------------------------------------------------------------------------| 0| SELECT STATEMENT | |10|250|3 (0)|00:00:01|| 1| NESTED LOOPS | | | | | || 2| NESTED LOOPS | |10|250|3 (0)|00:00:01||*3| TABLE ACCESS FULL |DEPARTMENTS | 1| 7|2 (0)|00:00:01||*4| INDEX RANGE SCAN |EMP_DEPARTMENT_IX|10| |0 (0)|00:00:01|| 5| TABLE ACCESS BY INDEX ROWID|EMPLOYEES |10|180|1 (0)|00:00:01|---------------------------------------------------------------------------

last_name

©Smith©

last_name = ©Smith© AND job_id = ©SH_CLERK©

OPTIMIZER_DYNAMIC_SAMPLING

last_name = ©Smith© last_name > ©Smith© last_name = ©Smith© n last_name last_name

OPTIMIZER_DYNAMIC_SAMPLING

Rows WHERE hr employees SELECT first_name, last_nameFROM employeesWHERE salary=©10200©; employees salary 58
2

107/58=1.84

employees departments employees departments

Optimizer

Hash Join

departments 0, employees 1

SELECT e.last_name, d.department_name

FROM hr.employees e, hr.departments d

WHERE e.department_id = d.department_id;

Transformer

Lowest Cost Plan

Join Method

Hash, Nested

Loop, Sort Merge

Access Path

Index

Full Table Scan

Join Order

departments 0 employees 1 employees 0 departments 1

GENERAL PLANS***************************************Considering cardinality-based initial join order.Permutations for Starting Table :0Join order[1]: DEPARTMENTS[D]#0 EMPLOYEES[E]#1 ***************Now joining: EMPLOYEES[E]#1***************NL Join Outer table: Card: 27.00 Cost: 2.01 Resp: 2.01 Degree: 1 Bytes: 16Access path analysis for EMPLOYEES. . . Best NL cost: 13.17. . .SM Join SM cost: 6.08 resc: 6.08 resc_io: 4.00 resc_cpu: 2501688 resp: 6.08 resp_io: 4.00 resp_cpu: 2501688. . .SM Join (with index on outer) Access Path: index (FullScan). . .HA Join HA cost: 4.57 resc: 4.57 resc_io: 4.00 resc_cpu: 678154 resp: 4.57 resp_io: 4.00 resp_cpu: 678154Best:: JoinMethod: Hash Cost: 4.57 Degree: 1 Resp: 4.57 Card: 106.00 Bytes: 27. . .***********************Join order[2]: EMPLOYEES[E]#1 DEPARTMENTS[D]#0. . . ***************Now joining: DEPARTMENTS[D]#0***************. . .HA Join HA cost: 4.58 resc: 4.58 resc_io: 4.00 resc_cpu: 690054 resp: 4.58 resp_io: 4.00 resp_cpu: 690054Join order aborted: cost > best plan cost***********************

departments Best:: JoinMethod: Hash Cost: 4.57 Degree: 1 Resp: 4.57 Card: 106.00 Bytes: 27 employees

Adaptive

Statistics

SQL Plan

Directives

Dynamic

Statistics

Automatic

Reoptimization

Bitmap

Index Pruning

Join

Methods

ParallelDistributionMethods

Adaptive

Plans

Adaptive QueryOptimization

Dynamic Plan

Subplan Group

Subplan

Subplan

GB HJHJ GB NLNL

Subplan Group

Subplan

Subplan

GB HJHJ GB NLNL

Dynamic Plan

Coordinator

order_items prod_info

SELECT product_name FROM order_items o, prod_info p WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id

SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(FORMAT => ©ADAPTIVE©));SQL_ID 7hj8dwwy6gm7p, child number 0

-------------------------------------SELECT product_name FROM order_items o, prod_info p WHEREo.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_idPlan hash value: 1553478007-----------------------------------------------------------------------------| Id | Operation | Name |Rows|Bytes|Cost (%CPU)|Time|-----------------------------------------------------------------------------| 0| SELECT STATEMENT | | | |7(100)| || * 1| HASH JOIN | |4| 128 | 7 (0)|00:00:01||- 2| NESTED LOOPS | |4| 128 | 7 (0)|00:00:01||- 3| NESTED LOOPS | |4| 128 | 7 (0)|00:00:01||- 4| STATISTICS COLLECTOR | | | | | || * 5| TABLE ACCESS FULL | ORDER_ITEMS |4| 48 | 3 (0)|00:00:01||-* 6| INDEX UNIQUE SCAN | PROD_INFO_PK |1| | 0 (0)| ||- 7| TABLE ACCESS BY INDEX ROWID| PROD_INFO |1| 20 | 1 (0)|00:00:01|| 8| TABLE ACCESS FULL | PROD_INFO |1| 20 | 1 (0)|00:00:01|-----------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID") 5 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1)) 6 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")Note----- - this is an adaptive plan (rows marked ©-© are inactive)

prod_info order_items order_items

NestedLoopsStatisticsCollectorTable scanorder_itemsIndex scanprod_info_pkHashJoinTable scanprod_infoDefault plan is a nested loops joinThe optimizer buffers rows coming from the order_items table up to a point. If the row count is less than the threshold, then use a nested loops join. Otherwise, switch to a hash join. NestedLoopsStatisticsCollectorTable scanorder_itemsIndex scanprod_info_pkHashJoinTable scanprod_infoFinal plan is a hash joinThe optimizer disables the statistics collector after making the decision, and lets the rows pass through. Threshold exceeded, so subplan switches7KHNoteVHFWLRQRIWKHH[HFXWLRQSODQLQGLFDWHVZKHWKHUWKHSODQLVDGDSWLYHDQGZKLFKURZVLQWKHSODQDUHLQDFWLYH6HH$OVR&RQWUROOLQJ$GDSWLYH2SWLPL]DWLRQ5HDGLQJ([HFXWLRQ3ODQV$GYDQFHGIRUDQH[WHQGHGH[DPSOHVKRZLQJDQDGDSWLYHTXHU\SODQ&KDSWHU$ERXW$GDSWLYH4XHU\2SWLPL]DWLRQ

departments employees departments employees P1quotesdbs_dbs6.pdfusesText_12

[PDF] advanced oracle sql tuning the definitive reference pdf free download

[PDF] advanced oracle sql tutorial

[PDF] advanced php book pdf

[PDF] advanced piano chords pdf

[PDF] advanced piano chords. pdf

[PDF] advanced placement french: preparing for the language examination

[PDF] advanced powerpoint 2010 tutorial pdf

[PDF] advanced powerpoint 2010 tutorial pdf free download

[PDF] advanced powerpoint 2013 tutorial pdf

[PDF] advanced powerpoint animation

[PDF] advanced powerpoint animation free download

[PDF] advanced powerpoint charts

[PDF] advanced powerpoint exercises

[PDF] advanced powerpoint presentation examples

[PDF] advanced powerpoint presentation free download