QUERY TUNING

About This Presentation

Title:

QUERY TUNING

Description:

... table AND what type of scan is being done on the ... for FULL TABLE SCAN and attempt to ... FULL VEHICLE Notice that the full table scan is performed ... – PowerPoint PPT presentation

Number of Views:20

Avg rating:3.0/5.0

Slides: 56

Provided by: JKE94

Category:

more less

Transcript and Presenter's Notes

Title: QUERY TUNING

1
QUERY TUNING As the Oracle Developer- Dont
be a target for slow performance
2

Agenda
Basic trouble spots for SQL queries
Tools used for identifying trouble spots
EXPLAIN PLAN
Tuning of SQL queries
Real life examples of SQL queries
Tuning of PL-SQL
Real life examples of PL-SQL
Discussion of Real Life Examples.
The goal is to be able to identify basic SQL
trouble spots , to understand
The explain plan tool for identifying trouble
spots, and to go through real
Life examples of tuning SQL queries and PL-SQL
code.

Basic trouble spots for sql queries

Creating the Oracle Table The basics of an
index Indexes help you to retrieve your data
quickly They do a binary search on your
Oracle tables. To choose what columns to
index Choose a column, or combination of
columns Which have the most unique values. Do
not choose A column, or columns which have mostly
the same Values. If a column has mostly the
same values, An index will hurt performance.
Know how the Table is going to be used, and what
data you are Going after in the table. For
example, for an employee table, you want the
index on social Security number (a unique value).
BUT, if you were going to search on name, you
would Want an index specifically on the NAME
column. Querying the Oracle table Set up your
query in such a way that Oracle Will use the
index of the columns that you Are querying.
4

Basic trouble spots for sql queries

When processing a query, Oracle ranks the
indexes, and determines which one
to use. When setting a column equal to a
literal string, this is the highest ranking,
And chances are that the index to that column
will be used.
The next highest ranking is greater than or less
than (gt, lt)
A literal.
There are several factors in THE WHERE CLAUSE of
a query that can cause
An index not to be used
Using a function on the left side.
Substr(plant_code,1,5) AP05A, ? Since there
is a function around the column,
the index will not be used. This includes
Oracle functions such as to_char,
to_number, ltrim, rtrim, instr, trunc, rpad ,
lpad
Comparing incompatible data.
Employee_num 1
? There will be an implicit to_char conversion
used.
plant_code 1234
? There will be an implicit to_num conversion
used.
Using is null and is not null .
Select

5
Basic trouble spots for sql queries
(continued) (3) Using is null and is not null.
(Continued) Select FROM s_emp WHERE Title is
not null ? Since the column title has null
values, and is compared to a null value,
the index can not be used. (4) Adding
additional criteria in the where clause for a
column name that is of a different index
Select from s_emp where title
Manager and Department 500 ? Column title
and department have separate indexes on these
columns.
6

Solutions to
Basic trouble spots for sql queries
Problem
Using a function on the left side.
Select from s_emp
Where substr(title,1,3) Man
Select from s_emp
Where trunc(hire_date) trunc(sysdate)
Solution
Use like
Select from s_emp
Where title like Man
Use gt, lt
Select from s_emp
where hire_date gt sysdate

7
Solutions to Basic trouble spots for sql
queries (continued) Problem 2. Comparing
incompatible data types. Select from
s_emp Where employee_number 3 Select from
s_emp Where hire_date 12-jan-01 Solution
Select from s_emp Where employee_number
3 Select from s_emp Where hire_date
to_date(12-jan-01)

8
Solutions to Basic trouble spots for sql
queries (continued) Problem 3. Using null
and not null Select from s_emp Where title is
not null Select from s_emp Where title is
null Solution Select from s_emp where
title gt Use an Oracle hint select
/ index (s_emp) / ? Oracle hints are
always enclosed in / / and must come from
s_emp directly after the select
clause. The index hint causes indexes to
be where title is null used.
9
Solutions to Basic trouble spots for sql
queries (continued) Problem 4. Adding
additional criteria in the where clause for a
column name that is of a different index
Select from s_emp where title
Manager and salary 100000 Solution Use
an Oracle hint select / index (s_emp) / ?
Oracle hints are always enclosed in / / and
must come from s_emp directly after the
select clause. The index hint causes
indexes where title Manager to be used.
S_EMP is the Oracle table and salary
100000
10

(2) Explain plan
Explain plan is a utility that will show you what
indexes are being used
In the table AND what type of scan is being done
on the table
(full table scan, index range scan etc..)
Make sure you have the plan_table
desc plan_table.
If you do not run the explain plan utility
go to the
/usr/oracle/product/8.1.6.64/rdbms/admin
or
/oracle_home/rdbms/admin
(3) Run utlxplan.sql in sql (I.e. _at_ utlxplan.sql )

11
(2) Explain plan (continued)
Do an explain plan on your statement such
as DELETE FROM plan_table WHERE statement_id
'TEST' EXPLAIN PLAN SET statement_id 'TEST'
FOR query Put this in an sql file called
explain.sql Then do _at_explain from SQL Set up a
query to go after the plan_table select lpad('
',2level) operation ' ' Options ' '
object_name execution_path from
plan_table where statement_id 'TEST connect by
prior id parent_id and statement_id
'TEST start with ID1 Put this in an sql file
called _at_explain2.sql Then do _at_explain2 from SQL
12

(2) Explain plan (continued)
Sample result from explain plan -- explain2.sql
Example 1
EXECUTION_PATH
--------------------------------------------------
------------------------------
TABLE ACCESS BY INDEX ROWID VEHICLE
INDEX RANGE SCAN FKI_VEHICLE_1
SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID VEHICLE
INDEX RANGE SCAN FKI_VEHICLE_1
EXECUTION_PATH
--------------------------------------------------
------------------------------
SORT AGGREGATE
TABLE ACCESS FULL VEHICLE
SORT AGGREGATE
SORT AGGREGATE

13
(3) Tuning of SQL queries

Find out what indexes are on the tables
Run explain plan
Make sure most restrictive indexes are being
used by avoiding SQL trouble spots.
Make sure most restrictive indexes are being
used by using Oracle hints.
For multi-table joins, join everything that can
be joined together.
Use unions instead of outer joins
Use exists subquery if only selecting items
from 1 table.
The query
select count() from vehicle
Where assembly_location_code 'AP24A'
and production_date '06-apr-01'
This will select vin (vehicle ID , Market
information,
and other information about a vehicle

(3) Tuning of SQL queries (cont)
Find out what indexes are on this table---
CRUCIAL
Select column_name, index_name from
all_ind_columns
Where table_name VEHICLE
Samle output
Column_name Index name
Assembly_location_code FKI_VEHICLE_1
Production_date FK_PRODUCTION_DATE
Vehicle id
FKI_PRODUCTION_DATE
Vehicle_id PK_VEHICLE
Run an explain plan on this query
In explain.sql
DELETE FROM plan_table
WHERE statement_id 'TEST'

(3) Tuning of SQL queries (cont)
Results of explain plan
EXECUTION_PATH
--------------------------------------------------
------------------------------
SORT AGGREGATE
TABLE ACCESS FULL VEHICLE
SORT AGGREGATE
SORT AGGREGATE
TABLE ACCESS FULL VEHICLE ? Notice that
the full table scan is performed
(3) Enable indexes to be used ELIMINATE TABLE
ACCESS FULL
TRY AN ORACLE HINT. Index(vehicle) means that
Oracle will choose to use an index on the table
name
vehicle.
Explain.sql looks like
DELETE FROM plan_table

16
(3) Tuning of SQL queries (cont)

(3) Enable indexes to be used ELIMINATE TABLE
ACCESS FULL (continued)
Results of explain plan
EXECUTION_PATH
--------------------------------------------------
------------------------------
TABLE ACCESS BY INDEX ROWID VEHICLE
INDEX RANGE SCAN FKI_PRODUCTION_DATE
SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID VEHICLE
INDEX RANGE SCAN FKI_PRODUCTION_DATE
(4) Force the use of the most restrictive index
FKI_VEHICLE_1
TRY AN ORACLE HINT. Index(vehicle FKI_VEHICLE_1)
means
that Oracle will use the FKI_VEHICLE
index on the table name vehicle.
Explain.sql looks like
DELETE FROM plan_table
WHERE statement_id 'TEST'

17
(3) Tuning of SQL queries (cont) (3) Enable
indexes to be used ELIMINATE TABLE ACCESS FULL
(continued) Results of explain
plan EXECUTION_PATH ----------------------------
--------------------------------------------------
-- TABLE ACCESS BY INDEX ROWID VEHICLE INDEX
RANGE SCAN FKI_VEHICLE_1 SORT AGGREGATE TABLE
ACCESS BY INDEX ROWID VEHICLE INDEX RANGE SCAN
FKI_VEHICLE_1 ? Notice that the most restrictive
index is used now. Results of query time
differences -------------------gt This does not
use an index select count() from
vehicle where assembly_location_code
'AP24A' and production_date '06-apr-01' SQLgt
_at_tt COUNT() ---------- 787 Elapsed
000010.00 ------gt Notice it is 10 seconds.
18
(3) Tuning of SQL queries (cont) (3) Enable
indexes to be used ELIMINATE TABLE ACCESS FULL
(continued) --------------------gt This is
where the query does use an index SQLgt get
tt.sql 1 select / index (vehicle
FKI_VEHICLE_1) / count() from vehicle 2
where 3 assembly_location_code 'AP24A' 4
and production_date '06-apr-01' 5 SQLgt set
timing on SQLgt _at_tt COUNT() ----------
787 Elapsed 000000.88 ------gt Notice it is
less than 1 second USE THE MOST SELECTIVE INDEX
that will return the fewest records.
19
CONTINUING ON about INDEXES Look at the
following query (vehicle material cost
summarizes The cost of the vehicles) The vehicle
table is information about the vehicle) select
/ index(t1 PK_VEHICLE_MATERIAL_COST) /
count() from vehicle_material _cost t1
where vehicle_id in (select / index(t2
fki_vehicle_1) -- SAME INDEX AS BEFORE
vehicle_id from vehicle t2 where t1.vehicle_id
t2.vehicle_id and assembly_location_code
'G9W1A' and production_date '22-apr-03')
and vehicle_id gt 0 and currency_code gt
' '
This query takes a while because the proper index
is NOT used on the vehicle table
20
WE NEED AN INDEX that uses BOTH production
date And ASSEMBLY_LOCATION_CODE
SQLgt list 1 select column_name, index_name
from all_ind_columns 2 where index_name
'I_VEHICLE_5' 3 and table_name
'VEHICLE' SQLgt / COLUMN_NAME --------------------
--------------------------------------------------
---------- INDEX_NAME ----------------------------
-- PRODUCTION_DATE I_VEHICLE_5 ASSEMBLY_LOCATION_
CODE I_VEHICLE_5
21
Now, if we use the proper index (I_VEHICLE_5) the
query Will be FAST
SQLgt set timing on SQLgt list 1 select /
index(t1 PK_VEHICLE_MATERIAL_COST) / count()
from vehicle_ma1 2 where vehicle_id in
3 (select / index(t2 I_VEHICLE_5) /
vehicle_id 4 from vehicle t2 where 5
t1.vehicle_id t2.vehicle_id 6 and
assembly_location_code 'G9W1A' 7
and production_date '22-apr-03' 8
and rownum lt 1) 9 and vehicle_id gt
0 10 and currency_code gt ' ' SQLgt
/ COUNT() ---------- 1803 Elapsed
000000.46
22

(3) Tuning of SQL queries (cont)
MUTI-Table queries
Make sure everything that can be joined is joined
(for 3 or more tables)
Instead of
select from t1, t2, t3
where t1.emp_id t2.emp_id
and t2.emp_id t3.emp_id
add
select from t1, t2, t3
where t1.emp_id t2.emp_id
and t2.emp_id t3.emp_id
and t1.emp_id t3.temp_id
Make sure smaller table is first in the from
clause
Use unions instead of outer-joins.
Use not exists instead of not in. USE a
correlated
sub-query.

23
(4) Tuning of SQL queries Real life example THE
QUERY SELECT mfal_vl_code,
mod_yr,

effective_out_date,

mfal_gvw_mkt_der_code,
mfal_market_code,

mfal_series_trim_code,

mfal_bs_code,mfal_eng_code,
mfal_trans_code,

mfal_drv_code,

mfal_feature_code,
sum(decode(ucc_qtrl
y_volume, 0, 0, (weight/ucc_qtrly_volume))) rate
from cpat_gvp_extract_main
where
substr(mfal_feature_code, 1, 1) not in
( '1', '2',
'3', '4', '5', '6', '7', '8', '9', '0', 'P', 'X'
) and substr( mfal_feature_code, 1,
3) not in
( 'EN ', 'TR ', 'DR ', 'MD ', 'SE ', 'BS ', 'CA
', 'VS ', 'AAA', 'AAG', 'AAF', 'AAH' )

and substr( mfal_feature_code, 1, 2)
not in ( 'WA', 'WS' ) and
mfal_feat_family_code not like 'PTR'
and (
mfal_series_trim_code like 'AAA'
or
mfal_series_trim_code like 'VS ' )
and ( substr(
mfal_feature_code, 4, 1) not in
( '1', '2', '3', '4', '5',
'6', '7', '8', '9')
or substr( mfal_feature_code, 5, 1) not in
( '1', '2',
'3', '4', '5', '6', '7', '8', '9')
)
Group
by mfal_vl_code,
mod_yr,

effective_out_date,

mfal_gvw_mkt_der_code,
mfal_market_code,

mfal_series_trim_code,

mfal_bs_code,
mfal_eng_code,

mfal_trans_code,

mfal_drv_code,
mfal_feature_code

24

(4) Tuning of SQL queries Real life example
(continued) 1) The indexes are Select
column_name, index_name from all_ind_columns Where
table_name CPAT_GVP_EXTRACT_MAIN Samle
output Column_name Index
name MFAL_FEATURE_CODE CGEM_CFC_IND_X MFAL_VL_COD
E CGEM_VL_IND_X MOD_YR CGEM_VL_IND_X MFAL_FEAT
_FAMILY_CODE CGEM_VL_IND_X MFAL_VL_CODE CGEM_VL_
IND_X MOD_YR CGEM_VL_IND_X
25
(4) Tuning of SQL queries Real life example
(continued)
The tuned query set timing on SELECT

/ index (cpat_gvp_extract_main CGEM_CFC_IND_X)
/ -gt Notice index hint mod_yr,

effective_out_date,

mfal_gvw_mkt_der_code,
mfal_market_code,

mfal_series_trim_code,

mfal_bs_code,mfal_eng_code,
mfal_trans_code,

mfal_drv_code,

mfal_feature_code,
sum(decode(ucc_qtrl
y_volume, 0, 0, (weight/ucc_qtrly_volume))) rate
from cpat_gvp_extract_main

where mfal_feature_code not like '1' and
mfal_feature_code not like '2' and
mfal_feature_code not like '3' and
mfal_feature_code not like '4' and
mfal_feature_code not like '5 ? Notice the use
of like instead of substr. and mfal_feature_code
not like '6' and mfal_feature_code not like
'7' and mfal_feature_code not like '8' and
mfal_Feature_code not like '9' and
mfal_feature_code not like '0' and
mfal_feature_code not like 'P' and
mfal_feature_code not like 'X'
26
(4) Tuning of SQL queries Real life example
(continued)
and mfal_feature_code not like 'EN' and
mfal_feature_code not like 'TR' and
mfal_Feature_code not like 'DR' and
mfal_feature_code not like 'MD' and
mfal_feature_code not like 'SE' and
mfal_feature_code not like 'BS' and
mfal_Feature_code not like 'CA' and
mfal_feature_code not like 'VS' and
mfal_Feature_code not like 'AAA' and
mfal_feature_code not like 'AAG' and
mfal_feature_code not like 'AAF' and
mfal_Feature_code not like 'AAH' and
mfal_feature_code not like 'WA' and
mfal_feature_code not like 'WS' and
mfal_feat_family_code not like 'PTR'
and ((
mfal_series_trim_code like 'AAA'
or
mfal_series_trim_code like 'VS ' )
and (mfal_feature_code not like
'___1' and mfal_feature_code not like '___2'
and mfal_feature_code not like '___3' and
mfal_feature_code not like '___4' and
mfal_feature_code not like '___5' and
mfal_feature_code not like '___6' and
mfal_feature_code not like '___7' and
mfal_feature_code not like '___8' and
mfal_feature_code not like '___9')
27
(4) Tuning of SQL queries Real life example
(continued)
OR ( mfal_feature_code not like '___1' and
mfal_feature_code not like '____2' and
mfal_feature_code not like '____3' and
mfal_feature_code not like '____4' and
mfal_feature_code not like '____5' and
mfal_feature_code not like '____6' and
mfal_feature_code not like '____7' and
mfal_feature_code not like '____8' and
mfal_feature_code not like '____9')) Group by
mfal_vl_code,
mod_yr,

effective_out_date,

mfal_gvw_mkt_der_code,
mfal_market_code,

mfal_series_trim_code,

mfal_bs_code,
mfal_eng_code,

mfal_trans_code,

mfal_drv_code,
mfal_feature_code
Time improved from 4 hours to 30 minutes.
28
(4) Tuning of SQL queries Real life example
(continued)
Multi-table join example SELECT
raw_part_number, price_type_code, IB.BOM_RECNO
RECNO,vV.VEHICLE_ID,IB.PART_ID,IB.PARENT_PART_ID,
BOM_LEVEL PART_LEVEL,IB.PART_USING_LOCA
TION_CODE,IB.SHIP_FROM_LOCATION_CODE, LPAD('
',2(BOM_LEVEL-1)) PART_PREFIX '-'
PART_BASE '-' PART_SUFFIX
'-' PART_CONTROL_NUMBER INDENTED_PART_NUMBER
, IB.FORD_AFFILIATE_CODE,IB.MISSING_S
TRUCTURE_CODE,IB.BOM_ID VIN,
IB.EFFECTIVE_DATE PRODUCTION_DATE,IB.CPSC_CODE,IB
.TOP_CPSC_CODE,PART_BASE, PART_PREFIX,PART_S
UFFIX,PART_CONTROL_NUMBER,ULP.PART_DESCRIPTION,
ULP.PART_TYPE,PI.COMMODITY_CODE,L.LOC
ATION_NAME SUPPLIER_NAME,
ULP.PART_UNIT_OF_MEASURE,IB.USAGE_QUANTITY,IB.WEI
GHT, IB.NOTES,IB.ALTERN
ATE_PART_PERCENT, IB.NEXT_AVAIL_BURDEN_DEPT,
IB.PERCENT_OF_BUSINESS,IB.RAW_PERCENT
_OF_BUSINESS,
ULP.PSEUDO_ALTERNATE_PART_CODE,
ULP.WAREHOUSE_PART_FLAG, ULP.BURDEN_DEPARTMENT_NUM
BER, IB.SUPPLIER_TYPE, IB.MANUFACTURING_PLANT_CODE
, PO.PRICE PO_PRICE,

PO.CURRENCY_CODE PO_CURRENCY_CODE,

PO.PO_EFFECTIVE_DATE, PO.CHARGE_FROM_DATE
PO_CHARGE_FROM_DATE,
PO.PURCHASING_MANAGER_CODE PO_PURCHASING_MANAGER_
CODE, PO.PURCHASING_MANA
GER_CDSID PO_PURCHASING_MANAGER_CDSID,
PO.BUYER_CODE PO_BUYER_CODE,

PO.BUYER_CDSID PO_BUYER_CDSID,
PO.RETURN_CODE
PO_RETURN_CODE,
PO.PRICING_RULE
PO_PRICING_RULE,PO.PRICING_SUBRULE
PO_PRICING_SUBRULE, ICS.TRANSFER_PRICE_E
FFECTIVE_DATE,
ICS.TRANSFER_PRICE ICS_PRICE,ICS.CURREN
CY_CODE ICS_CURRENCY_CODE,
ICS.RETURN_CODE ICS_RETURN_CODE,
IB.ROLLED_UP_COST
ROLLED_UP_PRICE,
29
(4) Tuning of SQL queries Real life example
(continued)
Multi-table join example (continued)
IB.PRICE_USED BOM_PRICE, IB.PRICE_SYSTEM_USED
FROM INDENTED_BOM
IB, VEHICLE_VIN VV, PART_INFO PI,
USING_LOCATION_PART ULP, LOCATION L,
PURCHASE_ORDER PO, INTERCOMPANY_SETTLEMENT ICS
WHERE IB.PART_IDPI.PART_ID
AND
IB.PART_IDULP.PART_ID AND
IB.PART_USING_LOCA
TION_CODEULP.PART_USING_LOCATION_CODE AND
IB.SHIP_FROM_LOCATION_CODEL.LOCATION
_CODE AND
IB.PART_IDPO.PART_ID() AND
IB.PART_USING_LOCA
TION_CODEPO.PART_USING_LOCATION_CODE() AND
IB.SHIP_FROM_LOCATION_CODEPO.SHIP_FR
OM_LOCATION_CODE() AND
IB.PO_EFFECTIVE_DATEPO.PO_EFFECTIVE_DATE() AND
IB.PO_CHARGE_FROM_
DATEPO.CHARGE_FROM_DATE() AND
IB.PART_IDICS.PART_ID() AND

IB.PART_USING_LOCATION_CODEICS.PART_USING_LOCATI
ON_CODE() AND IB.SHIP_FROM_LOCAT
ION_CODEICS.SHIP_FROM_LOCATION_CODE() AND
IB.TRANSFER_PRICE_EFFECTIVE_DATEICS.
TRANSFER_PRICE_EFFECTIVE_DATE() AND
IB.BOM_IDvV.VIN()

30
(4) Tuning of SQL queries Real life example
(continued)
RESULTS OF Explain plan
EXECUTION_PATH -----------------------------------
---------------------------------------------
TABLE ACCESS FULL LOCATION HASH JOIN OUTER
HASH JOIN OUTER HASH JOIN
TABLE ACCESS FULL PART_INFO HASH JOIN
OUTER HASH JOIN TABLE
ACCESS FULL USING_LOCATION_PART
TABLE ACCESS FULL INDENTED_BOM TABLE
ACCESS FULL VEHICLE_VIN TABLE ACCESS FULL
INTERCOMPANY_SETTLEMENT ?NOTICE FULL TABLE
SCANS EXECUTION_PATH ----------------------------
--------------------------------------------------
-- TABLE ACCESS FULL PURCHASE_ORDER HASH
JOIN TABLE ACCESS FULL LOCATION HASH JOIN
OUTER HASH JOIN OUTER HASH JOIN
TABLE ACCESS FULL PART_INFO HASH
JOIN OUTER HASH JOIN
TABLE ACCESS FULL USING_LOCATION_PART
TABLE ACCESS FULL INDENTED_BOM EXECUTION_PATH -
--------------------------------------------------
----------------------------- TABLE
ACCESS FULL VEHICLE_VIN TABLE ACCESS FULL
INTERCOMPANY_SETTLEMENT TABLE ACCESS FULL
PURCHASE_ORDER
31
(4) Tuning of SQL queries Real life example
(continued)

Modify multi table join in the where
clause WHERE IB.PART_IDPI.PART_ID AND
ulp.part_idpi.part_id ? add this part to the
where clause and
IB.PART_IDULP.PART_ID AND

IB.PART_USING_LOCATION_CODEULP.PART_USING_LOCATI
ON_CODE AND IB.SHIP_FROM_LOCAT
ION_CODEL.LOCATION_CODE AND
IB.PART_IDPO.PART_ID() AND

IB.PART_USING_LOCATION_CODEPO.PART_USING_LOCATIO
N_CODE() AND IB.SHIP_FROM_LOCAT
ION_CODEPO.SHIP_FROM_LOCATION_CODE() AND
IB.PO_EFFECTIVE_DATEPO.PO_EFFECTIVE_
DATE() AND
IB.PO_CHARGE_FROM_DATEPO.CHARGE_FROM_DATE()
AND
IB.PART_IDICS.PART_ID() AND
IB.PART_USING_LOCA
TION_CODEICS.PART_USING_LOCATION_CODE() AND
IB.SHIP_FROM_LOCATION_CODEICS.SHIP_F
ROM_LOCATION_CODE() AND
IB.TRANSFER_PRICE_EFFECTIVE_DATEICS.TRANSFER_PRI
CE_EFFECTIVE_DATE() AND IB.BOM_IDvV.VIN(
)
32
(4) Tuning of SQL queries Real life example
(continued)
RESULTS OF Explain plan
EXECUTION_PATH -----------------------------------
---------------------------------------------
NESTED LOOPS NESTED LOOPS OUTER
NESTED LOOPS OUTER NESTED LOOPS
HASH JOIN TABLE ACCESS FULL
PART_INFO TABLE ACCESS FULL
INDENTED_BOM TABLE ACCESS BY INDEX
ROWID LOCATION INDEX UNIQUE SCAN
PK_LOCATION TABLE ACCESS BY INDEX ROWID
INTERCOMPANY_SETTLEMENT INDEX UNIQUE
SCAN PK_INTERCOMPANY_SETTLEMENT EXECUTION_PATH --
--------------------------------------------------
---------------------------- TABLE ACCESS
BY INDEX ROWID PURCHASE_ORDER INDEX
UNIQUE SCAN PK_PURCHASE_ORDER TABLE ACCESS
BY INDEX ROWID USING_LOCATION_PART ?NOTICE
INDEXES USED INDEX UNIQUE SCAN
PK_USING_LOCATION_PART TABLE ACCESS BY INDEX
ROWID VEHICLE_VIN INDEX RANGE SCAN
UK_VEHICLE_VIN NESTED LOOPS OUTER NESTED
LOOPS NESTED LOOPS OUTER NESTED
LOOPS OUTER NESTED LOOPS
33
(4) Tuning of SQL queries Real life example
(continued)
RESULTS OF Explain plan continued
EXECUTION_PATH -----------------------------------
---------------------------------------------
HASH JOIN TABLE ACCESS FULL
PART_INFO TABLE ACCESS FULL
INDENTED_BOM TABLE ACCESS BY INDEX
ROWID LOCATION INDEX UNIQUE SCAN
PK_LOCATION TABLE ACCESS BY INDEX ROWID
INTERCOMPANY_SETTLEMENT INDEX UNIQUE
SCAN PK_INTERCOMPANY_SETTLEMENT TABLE
ACCESS BY INDEX ROWID PURCHASE_ORDER
INDEX UNIQUE SCAN PK_PURCHASE_ORDER TABLE
ACCESS BY INDEX ROWID USING_LOCATION_PART ?NOTICE
INDEXES USED INDEX UNIQUE SCAN
PK_USING_LOCATION_PART EXECUTION_PATH -----------
--------------------------------------------------
------------------- TABLE ACCESS BY INDEX
ROWID VEHICLE_VIN INDEX RANGE SCAN
UK_VEHICLE_VIN
34
Real life example (continued)
Suppose you have to select all of the employees
that are Not account representatives Table 1
Table2 S_emp s_account_rep Soc_number
soc_number last_name
last_name first_name first_name
salary
region This query is slower select
soc_number from s_emp minus
select soc_number from s_account_rep because the
minus has to select distinct values from both
tables. This query is a little faster
select soc_number from s_emp where
soc_number not in (select soc_number from
s_account_rep) Faster, but still not as fast
because we are not joining And are not using
indexes.
35
Real life example (continued)
Suppose you have to select all of the employees
that are Not account representatives Table 1
Table2 S_emp s_account_rep Soc_number
soc_number last_name
last_name first_name first_name
salary
region This query is fast Select /
index(t1) / soc_number from s_emp t1 Where Not
exists (select / index(t1) index(t2) /
from s_account_rep t2 where T1.soc_number
t2.soc_number)
36

(5) Tuning of PL SQL code
Everything said about tuning SQL queries holds
with the addition of
(1) Using explicit instead of implicit cursors
(2) Eliminating cursors where ever possible
Using rowid to update
Use truncate instead of delete IF you want to
delete ALL of the
data from the table.
(5) Use PL/SQL tables for iterations.
(6) Eliminate excessive use of execute
immediate (Oracle 8i).

(5) Tuning of PL SQL code (continued)
Using explicit instead of implicit cursors
Implicit cursors always take longer than
explicit cursors because they are doing
an extra to make sure that there is no more
data.
Instead of
select count() into tot from s_emp
where emp_id v_emp_id
Declare a cursor for the count Or if just
checking for existence
cursor cnt_emp_cur(v_emp_id number) is
cursor cnt_emp_cur(v_emp_id number) is
select count() emp_total from s_emp select
emp_id from s_emp where emp_id v_emp_id
where emp_id v_emp_id and rownum 1
cnt_emp_rec cnt_emprowtype
And then do the fetch from this cursor
open cnt_emp(v_emp_id)
fetch cnt_emp into cnt_emp_rec

38
(5) Tuning of PL SQL code (continued)
(2) Eliminating cursors where ever
possible Instead of INSERT INTO
TEMP_PARTSOURCE_DIFFER(PART_ID,PART_USING_LOCATION
_CODE, SHIP_FROM_LOCATION_CODE,PART_SOURCE_EFFEC
TIVE_DATE) (SELECT A.PART_ID,
A.PART_USING_LOCATION_CODE, A.SHIP_FROM_LOCATIO
N_CODE,A.PART_SOURCE_EFFECTIVE_DATE
FROM PART_SOURCE A WHERE A.PART_USING_LOCATION_
CODEV_PLANT AND A.PART_ID V_PART_ID AND
A.PERCENT_OF_BUSINESSgt0 AND A.PART_SOURCE_EFFEC
TIVE_DATE (SELECT MAX(PART_SOURCE_EFFECTIVE_DAT
E) FROM PART_SOURCE B
WHERE B.PART_IDA.PART_ID AND
B.PART_USING_LOCATION_CODE A.PART_USING_LOCATIO
N_CODE AND B.SHIP_FROM_LOCATION_COD
EA.SHIP_FROM_LOCATION_CODE)) MINUS
(SELECT PART_ID, PART_USING_LOCATION_CODE,
SHIP_FROM_LOCATION_CODE,PART_SOURCE_EFFECTIVE_
DATE FROM TEMP_PARTSOURCE WHERE
PART_IDV_PART_ID AND PART_USING_LOCATION_CODEV_P
LANT) And then having a cursor to go off the
temp_partsource_differ table CURSOR
CS_TEMP_PART_SOURCEDIFFER IS SELECT
PART_ID, PART_USING_LOCATION_CODE, SHIP_FROM_L
OCATION_CODE, APP_PEND_INDICATOR FROM
TEMP_PARTSOURCE_DIFFER
39
(5) Tuning of PL SQL code (continued) (2)
Eliminating cursors where ever possible
(continued) Have the cursor go directly off of
this query Cursor temp_partsource_differ
(v_plant varchar2, v_part_id number) SELECT
A.PART_ID, A.PART_USING_LOCATION_CODE,
A.SHIP_FROM_LOCATION_CODE,A.PART_SOURCE_EFFECTI
VE_DATE FROM PART_SOURCE A WHERE
A.PART_USING_LOCATION_CODEV_PLANT AND
A.PART_ID V_PART_ID AND A.PERCENT_OF_BUSINESSgt
0 AND A.PART_SOURCE_EFFECTIVE_DATE (SELECT
MAX(PART_SOURCE_EFFECTIVE_DATE) FROM
PART_SOURCE B
WHERE B.PART_IDA.PART_ID AND
B.PART_USING_LOCATION_CODE A.PART_USING_LOCATIO
N_CODE AND B.SHIP_FROM_LOCATION_COD
EA.SHIP_FROM_LOCATION_CODE) MINUS
(SELECT PART_ID, PART_USING_LOCATION_CODE,
SHIP_FROM_LOCATION_CODE,PART_SOURCE_EFFECTIVE_D
ATE FROM TEMP_PARTSOURCE WHERE
PART_IDV_PART_ID AND PART_USING_LOCATION_CODEV_P
LANT)
40
The PL/SQL code set serveroutput on size
1000000 set linesize 500 set pagesize 0 set
feedback off set termout off spool
check_vinbom_result_052201.txt declare v_exchang
e_date DATE'01-MAY-2001' v_start_date
DATE'16-MAY-2001' v_end_date
DATE'22-MAY-2001' v_plant VARCHAR2(5) v_curr_s
tart_date DATE v_vehicle VARCHAR2(3) v_bulk_mate
rial NUMBER v_st_line_bom NUMBER v_all_parts_bom
NUMBER v_vin NUMBER v_vin1 NUMBER0 v_vin2
NUMBER0 v_count NUMBER v_bulk_eff_date
DATE v_vin_all_perc_diff NUMBER v_vin_st_perc_di
ff NUMBER TYPE t_plant_table IS TABLE OF
VARCHAR2(5) INDEX BY BINARY_INTEGER v_plant_table
t_plant_table v_veh_line_count NUMBER0
41
CURSOR c_iterate_veh_lines IS SELECT DISTINCT
WERS_9270_CODE FROM VEHICLE V, MKT_MDL MM
WHERE ASSEMBLY_LOCATION_CODEv_plant AND
PRODUCTION_DATE gt v_start_date
AND PRODUCTION_DATE lt v_end_date
AND mm.mkt_mdl_sakey gt 0 and
MM.MKT_MDL_SAKEYV.MKT_MDL_SAKEY AND
MM.WERS_9270_CODE IS NOT NULL cursor
max_bulk (v_plant varchar2, vehicle_line_code
varchar2, v_start_date date) is select
max(bulk_material_cost) bulkmat from
bulk_material_cost t1 where
assembly_location_codev_plant and
vehicle_line_codev_vehicle and
effective_date (select max(effective_date) from
bulk_material_cost t2 where
t1.assembly_location_code t2.assembly_location_c
ode and t1.vehicle_line_code
t2.vehicle_line_code and
t2.vehicle_line_code v_vehicle and
t2.assembly_location_code v_plant and
effective_date lt v_start_date) cursor
sum1(v_curr_start_date date, v_plant varchar2,
v_vehicle varchar2 , v_exchange_date date)
is select sum(part_currency_cost(rate_mult/ra
te_div)) summ from vehicle_material_cost vmc,
ps_rt_rate_tbl_mv er, vehicle v, mkt_mdl
MM where V.production_date
v_curr_start_date and V.assembly_location_cod
ev_plant and vmc.vehicle_idv.vehicle_id
and MM.mkt_mdl_sakeyV.mkt_mdl_sakey
and mm.wers_9270_codev_vehicle
and from_curvmc.currency_code
and to_cur'USD' and rt_type'CRRNT'
and er.effdtv_exchange_date ? Notice we
are replacing implicit with explicit
42
sum1rec sum1rowtype max_bulk_rec
max_bulkrowtype begin v_plant_table(1)'AP01A'
v_plant_table(2)'AP02A' v_plant_table(3)'
AP03A' v_plant_table(4)'AP04A' v_plant_table
(5)'AP05A' v_plant_table(6)'AP06A' v_plant
_table(7)'AP07A' v_plant_table(8)'AP09A' v
_plant_table(9)'AP10A' v_plant_table(10)'AP1
1A' v_plant_table(11)'AP12A' v_plant_table(1
2)'AP13A' v_plant_table(13)'AP14A' v_plant
_table(14)'AP15A' v_plant_table(15)'AP16A'
v_plant_table(16)'AP17A' v_plant_table(17)'
AP20A' v_plant_table(18)'AP21A' v_plant_tabl
e(19)'AP22A' v_plant_table(20)'AP23A' v_pl
ant_table(21)'AP24A' v_plant_table(22)'0096A
' v_plant_table(23)'0097A' v_plant_table(24)
'0118A' v_plant_table(25)'0128A' v_plant_t
able(26)'0129A' v_plant_table(27)'0134A' v
_plant_table(28)'0145A' v_plant_table(29)'02
64F' v_plant_table(30)'0264A' v_plant_table(
31)'02641' dbms_output.put_line('Plant,Veh
Line,Bulk Matl Cost,Date,Straight,All,VIN,(VIN-All
),(VIN-Straight),Total VINs,Cost Per
VIN') for v_plant_index in 1..31 loop
v_plantv_plant_table(v_plant_index) v_veh_
line_count0 for v_veh_rec in
c_iterate_veh_lines loop v_veh_line_countv_ve
h_line_count1 v_curr_start_datev_start_date
v_vehiclev_veh_rec.wers_9270_code
43
for v_plant_index in 1..31 loop
v_plantv_plant_table(v_plant_index) v_veh_
line_count0 for v_veh_rec in
c_iterate_veh_lines loop v_veh_line_countv_ve
h_line_count1 v_curr_start_datev_start_date
v_vehiclev_veh_rec.wers_9270_code
open max_bulk (v_plant,
v_vehicle, v_start_date) fetch max_bulk into
max_bulk_rec v_bulk_material
max_bulk_rec.bulkmat close max_bulk IF
v_bulk_material is null then v_bulk_material
0 end if dbms_output.put_line('-') dbm
s_output.put_line('-') loop exit when
v_curr_start_date gt v_end_date open sum1
(v_curr_start_date , v_plant , v_vehicle ,
v_exchange_date ) fetch sum1 into sum1rec
v_vin1 sum1rec.summ close
sum1 select sum(part_currency_cost
(rate_mult/rate_div)) into v_vin2 from
vehicle_material_cost vmc, ps_rt_rate_tbl_mv er,
vehicle v, mkt_mdl MM, vehicle_vin VV,
vehicle_suspense VS where V.production_da
te v_curr_start_date and
44
V.assembly_location_codev_plant
and vv.vehicle_idv.vehicle_id
and MM.mkt_mdl_sakey()V.mkt_mdl_sa
key and MM.wers_9270_code is null
and vmc.vehicle_idv.vehicle_id
AND vv.vehicle_id vmc.vehicle_id and
vs.plant_code v.assembly_location_code and
vs.plant_code v_plant and
vs.vinvv.vin and ? Notice making all
joins possible vs.record_id(SELECT
MAX(RECORD_ID) FROM VEHICLE_SUSPENSE vss
where vinvv.vin and vss.plant_code
v.assembly_location_code and plant_code
v_plant and vss.vehicle_line_code
v_vehicle) and vs.vehicle_line_codev_vehicle
and from_curvmc.currency_code
and to_cur'USD' and rt_type'CRRNT'
and er.effdtv_exchange_date v_vinnvl
(v_vin1,0)nvl(v_vin2,0) select count()
into v_count from (select distinct v.vehicle_id
from vehicle_material_cost vmc,
ps_rt_rate_tbl_mv er, vehicle v, mkt_mdl
MM, vehicle_vin VV, vehicle_suspense
VS where V.production_date
v_curr_start_date and V.assembly_location_cod
ev_plant and MM.mkt_mdl_sakey()V.mkt_mdl
_sakey and (MM.wers_9270_code is null or
(mm.wers_9270_codev_vehicle)) and vmc.vehi
cle_idv.vehicle_id AND
45
vv.vehicle_idv.vehicle_id
and vs.vin()vv.vin and (vs.vehicle_lin
e_code is null or (vs.vehicle_line_codev_vehicle
and vs.record_id(SELECT MAX(RECORD_ID)
FROM VEHICLE_SUSPENSE where vinvv.vin)))
and (vs.vehicle_line_code is not null or
MM.wers_9270_code is not null) and from_cur
vmc.currency_code and to_cur'USD'
and rt_type'CRRNT' and er.effdtv_excha
nge_date) v_vinv_vin-(v_countv_bulk_mater
ial) select sum(price (rate_mult/rate_div)
accumulated_supplier_quantity) into
v_st_line_bom from daily_flat_bom dfb,
ps_rt_rate_tbl_mv er where production_date
v_curr_start_date and assembly_location_code
v_plant and vehicle_line_codev_vehicle
and er.from_curdfb.currency_code
and er.to_cur'USD' and rt_type'CRRNT'
and er.effdtv_exchange_date select
sum(ext_purc_material_costaccumulated_supplier_qu
antity) into v_all_parts_bom from
daily_assembly_end_item_bom where production_
date v_curr_start_date and assembly_locatio
n_codev_plant and vehicle_line_codev_vehicle
v_st_line_bomnvl(v_st_line_bom,0) v_all_p
arts_bomnvl(v_all_parts_bom,0) v_vinnvl(v
_vin,0) IF v_vin0 THEN v_vin_all_perc
_diff0
46
ELSE v_vin_all_perc_diffabs((v_vin-v_all
_parts_bom)/(v_vin)) END IF IF v_vin0
THEN v_vin_st_perc_diff0 ELSE v_v
in_st_perc_diffabs((v_vin-v_st_line_bom)/(v_vin)
) END IF IF v_count gt 0
THEN dbms_output.put_line(v_plant','v_veh
icle','v_bulk_material',' v_curr_star
t_date ',' v_st_line_bom ','
v_all_parts_bom','v_vin','v_vin_all_perc
_diff ','v_vin_st_perc_diff','v_count
','v_vin/v_count) v_curr_start_datev_c
urr_start_date1 ELSE dbms_output.put_li
ne(v_plant','v_vehicle','v_bulk_material
',' v_curr_start_date ','
v_st_line_bom ',' v_all_parts_bom','v_vi
n','v_vin_all_perc_diff ','v_vin_st_p
erc_diff','v_count','v_count) v_curr
_start_datev_curr_start_date1 END
IF end loop end loop IF
v_veh_line_count0 THEN dbms_output.put_line('N
o vehicles with vehicle lines found for plant
'v_plant) END IF end loop end / spool
off set feedback on set termout on
47
Real life applications
We need to extract DISTINCT plant, plant
description, , vehicle line (TAURUS), Vehicle
line description, assembly plant 0096A,
assembly plant description (Genk), commodity code
(B100), commodity code description , Market code
, market code description (Australia) (batteries)
from a table of data (called TVM_market_flat_bom).
This is used for a WEB screen drop
down Previous Method Create a
snapshot Problem Refresh takes too long
because Query with
DISTINCT AND the function takes much
too long. Took about 7
hours.

TVM LOOKUP SCREEN
COMMODITY_CODE
Snapshot
ASSEMBLY_LOCATION_CODE
TVM_MARKET_FLAT_BOM
43
48
DESC TVM_MARKET_FLAT_BOM
REPORT_CONTROL_SAKEY NOT
NULL NUMBER CALENDAR_YEAR_MONTH
NOT NULL VARCHAR2(6) ASSEMBLY_LOCATION_CODE
NOT NULL VARCHAR2(5)
WERS_9270_CODE NOT
NULL VARCHAR2(5) PART_USING_LOCATION_CODE
NOT NULL VARCHAR2(5)
SHIP_FROM_LOCATION_CODE NOT
NULL VARCHAR2(5) CONFIG_STRING
NOT NULL VARCHAR2(50) CPSC_CODE
NOT NULL VARCHAR2(6)
PART_ID NOT
NULL NUMBER(9) WERS_MARKET_CODE
NOT NULL VARCHAR2(5) PART_PREFIX
VARCHAR2(12)
PART_BASE NOT
NULL VARCHAR2(12) PART_SUFFIX
VARCHAR2(12) PART_DESC
VARCHAR2(34)
CPSC_USAGE_QTY
NUMBER(25,15) CONFIG_USAGE_QTY
NUMBER(25,15)
WERS_9270_USAGE_QTY
NUMBER(25,15) MARKET_USAGE_QTY
NUMBER(25,15)
CONFIG_VOLUME_COUNT
NUMBER(38) MARKET_VOLUME_COUNT
NUMBER WERS_9270_VOLUME_COUNT
NUMBER(38)
PERCENT_OF_BUSINESS
NUMBER(3,2) PERCENT_OF_BUSINESS_9270
NUMBER(3,2) COMMODITY_CODE
VARCHAR2(4) PRICE

NUMBER(20,6) PRICE_SOURCE
VARCHAR2(240) CURRENCY_CODE
VARCHAR2(3)
EXCHANGE_RATE
NUMBER(15,8) SUPPLIER_TYPE
VARCHAR2(240) PRICE_EFFECTIVE_DAT
E DATE
PRICING_RULE
VARCHAR2(240) PRICING_SUBRULE
VARCHAR2(240) BUYER_CODE
VARCHAR2(240)
PART_UNIT_OF_MEASURE
VARCHAR2(3) CREATE_DTS
NOT NULL DATE CREATE_USERID
NOT NULL VARCHAR2(8) UPDATE_DTS
NOT NULL DATE
UPDATE_USERID NOT
NULL VARCHAR2(8)
49
The query
CREATE SNAPSHOT MV_TVM_MKT_LOC_LOOKUP TABLESPACE
"GCOR7_TABLE" BUILD
IMMEDIATE USING INDEX TABLESPACE "GCOR3_INDEX"
PCTFREE 10 INITRANS 2 MAXTRANS 255 REFRESH
COMPLETE WITH ROWID AS SELECT distinct
PART_uSING_LOCATION_CODE, fn_plant_description(pa
rt_Using_location_code) part_using_location_name,
wers_9270_code, fn_wers9270_description(wers_9270
_code) wers_9270_name, assembly_location_code, fn_
plant_description(assembly_location_code)
assembly_location_name, commodity_code, fn_commodi
ty_code_desc(commodity_code) commodity_desc, wers
_market_code, fn_wers_market_desc(wers_market_code
) wers_market_name from tvm_market_flat_bom
50

The solution
Instead of Using the slow query from
TVM_MARKET_FLAT
BOM in creating a snapshot
Create a global temporary table with the proper
indexes
Do a straight select (no distinct) from
TVM_MARKET_FLAT_BOM
(do not include the functions for the
descriptions).
3) At that point put together a procedure to
update the
descriptions in the in groups
by vehicle line in the global temporary
table.
4) Use this table and put the information
(grouped by
assembly location code,
wers_9270_code (Taurus),
plant, commodity_code) into another
table
that is indexed, and use THIS table
for the look up screen.

CREATE INDEX I_MV_TMLL on
MV_TVM_MKT_LOC_LOOKUP(part_using_location_code,
wers_9270_code,
assembly_location_code, commodity_code ,
wers_market_code)

52
Solution (1) Create a global temporary
table and bring
information over to it, then update
groups of records. Then put this

information into a separate table
for the lookup screens create global temporary
table gl_t1 (part_Using_location_code
varchar2(5), part_using_location_name
varchar2(100), wers_9270_code varchar2(3),
wers_9270_name varchar2(100),
assembly_location_code varchar2(5),
assembly_location_name varchar2(100),
commodity_code varchar2(4), commodity_Desc
varchar2(100), wers_market_code varchar2(5),
wers_market_name varchar2(100) ) on commit
preserve rows (2) Index columns that are in
italics.
53
The result
Elapsed 000553.74 SQLgt list 1 insert into
gl_t1 2 (part_using_location_code, 3
wers_9270_code, 4 assembly_location_code, 5
commodity_code, 6 wers_market_code 7 )
8 SELECT PART_uSING_LOCATION_CODE , 9
wers_9270_code, 10 assembly_location_code, 11
commodity_code, 12 wers_market_code 13 from
tvm_market_flat_bom_at_toread
54