1 / 55

QUERY TUNING – As the Oracle Developer- Don’t be a target for slow performance

QUERY TUNING – As the Oracle Developer- Don’t be a target for slow performance. 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

hailey
Download Presentation

QUERY TUNING – As the Oracle Developer- Don’t be a target for slow performance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. QUERY TUNING – As the Oracle Developer- Don’t 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.

  3. 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 (>, <) • 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 * • FROM s_emp • WHERE • Title is null;

  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 >, < • Select * from s_emp • where hire_date >= sysdate • and hire_date < sysdate + 1;

  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 >= ‘ ‘; • 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 beused. 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. @ 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 @explain from SQL Set up a query to go after the plan_table: select lpad(' ',2*level) || 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 ID=1; Put this in an sql file called @explain2.sql Then do @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 • TABLE ACCESS FULL VEHICLE --> NOTICE A FULL TABLE SCAN • LOOK for FULL TABLE SCAN and attempt to eliminate it.

  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

  14. (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_nameIndex 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'; • EXPLAIN PLAN SET statement_id = 'TEST' FOR • select count(*) from vehicle • where • assembly_location_code = 'AP24A' • and production_date = '06-apr-01'; --> Notice that an index will still not be used • @explain.sql • @explain2.sql

  15. (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 • WHERE statement_id = 'TEST'; • EXPLAIN PLAN SET statement_id = 'TEST' FOR • select /*+ index(vehicle) */ count(*) from vehicle • where • assembly_location_code = 'AP24A' • and production_date = '06-apr-01'; --> Notice an index will be used now

  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'; • EXPLAIN PLAN SET statement_id = 'TEST' FOR • select /*+ index(vehicle FKI_VEHICLE_1) */ count(*) from vehicle • where • assembly_location_code = 'AP24A' • and production_date = '06-apr-01'; --> Notice the FKI_VEHICLE_1 hint will be used now.

  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: • -------------------> This does not use an index • select count(*) from vehicle • where • assembly_location_code = 'AP24A' • and production_date = '06-apr-01'; • SQL> @tt • COUNT(*) • ---------- • 787 • Elapsed: 00:00:10.00 ------> Notice it is 10 seconds.

  18. (3) Tuning of SQL queries (cont) (3) Enable indexes to be used – ELIMINATE “TABLE ACCESS FULL “ (continued) --------------------> This is where the query does use an index SQL> 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 SQL> set timing on SQL> @tt COUNT(*) ---------- 787 Elapsed: 00:00:00.88 ------> 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 > 0 and currency_code > ' '; 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: SQL> list 1 select column_name, index_name from all_ind_columns 2 where index_name = 'I_VEHICLE_5' 3* and table_name = 'VEHICLE' SQL> / 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 SQL> set timing on SQL> 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 <= 1) 9 and vehicle_id > 0 10* and currency_code > ' ' SQL> / COUNT(*) ---------- 1803 Elapsed: 00:00:00.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_qtrly_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_nameIndex name MFAL_FEATURE_CODE CGEM_CFC_IND_X MFAL_VL_CODE 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) */ -> 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_qtrly_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_LOCATION_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_STRUCTURE_CODE,IB.BOM_ID VIN, IB.EFFECTIVE_DATE PRODUCTION_DATE,IB.CPSC_CODE,IB.TOP_CPSC_CODE,PART_BASE, PART_PREFIX,PART_SUFFIX,PART_CONTROL_NUMBER,ULP.PART_DESCRIPTION, ULP.PART_TYPE,PI.COMMODITY_CODE,L.LOCATION_NAME SUPPLIER_NAME, ULP.PART_UNIT_OF_MEASURE,IB.USAGE_QUANTITY,IB.WEIGHT, IB.NOTES,IB.ALTERNATE_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_NUMBER, 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_MANAGER_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_EFFECTIVE_DATE, ICS.TRANSFER_PRICE ICS_PRICE,ICS.CURRENCY_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_ID=PI.PART_ID AND IB.PART_ID=ULP.PART_ID AND IB.PART_USING_LOCATION_CODE=ULP.PART_USING_LOCATION_CODE AND IB.SHIP_FROM_LOCATION_CODE=L.LOCATION_CODE AND IB.PART_ID=PO.PART_ID(+) AND IB.PART_USING_LOCATION_CODE=PO.PART_USING_LOCATION_CODE(+) AND IB.SHIP_FROM_LOCATION_CODE=PO.SHIP_FROM_LOCATION_CODE(+) AND IB.PO_EFFECTIVE_DATE=PO.PO_EFFECTIVE_DATE(+) AND IB.PO_CHARGE_FROM_DATE=PO.CHARGE_FROM_DATE(+) AND IB.PART_ID=ICS.PART_ID(+) AND IB.PART_USING_LOCATION_CODE=ICS.PART_USING_LOCATION_CODE(+) AND IB.SHIP_FROM_LOCATION_CODE=ICS.SHIP_FROM_LOCATION_CODE(+) AND IB.TRANSFER_PRICE_EFFECTIVE_DATE=ICS.TRANSFER_PRICE_EFFECTIVE_DATE(+) AND IB.BOM_ID=vV.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_ID=PI.PART_ID AND ulp.part_id=pi.part_id  add this part to the where clause and IB.PART_ID=ULP.PART_ID AND IB.PART_USING_LOCATION_CODE=ULP.PART_USING_LOCATION_CODE AND IB.SHIP_FROM_LOCATION_CODE=L.LOCATION_CODE AND IB.PART_ID=PO.PART_ID(+) AND IB.PART_USING_LOCATION_CODE=PO.PART_USING_LOCATION_CODE(+) AND IB.SHIP_FROM_LOCATION_CODE=PO.SHIP_FROM_LOCATION_CODE(+) AND IB.PO_EFFECTIVE_DATE=PO.PO_EFFECTIVE_DATE(+) AND IB.PO_CHARGE_FROM_DATE=PO.CHARGE_FROM_DATE(+) AND IB.PART_ID=ICS.PART_ID(+) AND IB.PART_USING_LOCATION_CODE=ICS.PART_USING_LOCATION_CODE(+) AND IB.SHIP_FROM_LOCATION_CODE=ICS.SHIP_FROM_LOCATION_CODE(+) AND IB.TRANSFER_PRICE_EFFECTIVE_DATE=ICS.TRANSFER_PRICE_EFFECTIVE_DATE(+) AND IB.BOM_ID=vV.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 employee’s 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 employee’s 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).

  37. (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_emp%rowtype; • And then do the fetch from this cursor: • … • open cnt_emp(v_emp_id); • fetch cnt_emp into cnt_emp_rec; • … • close cnt_emp;

  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_EFFECTIVE_DATE) (SELECT A.PART_ID, A.PART_USING_LOCATION_CODE, A.SHIP_FROM_LOCATION_CODE,A.PART_SOURCE_EFFECTIVE_DATE FROM PART_SOURCE A WHERE A.PART_USING_LOCATION_CODE=V_PLANT AND A.PART_ID =V_PART_ID AND A.PERCENT_OF_BUSINESS>0 AND A.PART_SOURCE_EFFECTIVE_DATE= (SELECT MAX(PART_SOURCE_EFFECTIVE_DATE) FROM PART_SOURCE B WHERE B.PART_ID=A.PART_ID AND B.PART_USING_LOCATION_CODE= A.PART_USING_LOCATION_CODE AND B.SHIP_FROM_LOCATION_CODE=A.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_ID=V_PART_ID AND PART_USING_LOCATION_CODE=V_PLANT); 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_LOCATION_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_EFFECTIVE_DATE FROM PART_SOURCE A WHERE A.PART_USING_LOCATION_CODE=V_PLANT AND A.PART_ID =V_PART_ID AND A.PERCENT_OF_BUSINESS>0 AND A.PART_SOURCE_EFFECTIVE_DATE= (SELECT MAX(PART_SOURCE_EFFECTIVE_DATE) FROM PART_SOURCE B WHERE B.PART_ID=A.PART_ID AND B.PART_USING_LOCATION_CODE= A.PART_USING_LOCATION_CODE AND B.SHIP_FROM_LOCATION_CODE=A.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_ID=V_PART_ID AND PART_USING_LOCATION_CODE=V_PLANT);

  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_exchange_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_start_date DATE; v_vehicle VARCHAR2(3); v_bulk_material NUMBER; v_st_line_bom NUMBER; v_all_parts_bom NUMBER; v_vin NUMBER; v_vin1 NUMBER:=0; v_vin2 NUMBER:=0; v_count NUMBER; v_bulk_eff_date DATE; v_vin_all_perc_diff NUMBER; v_vin_st_perc_diff NUMBER; TYPE t_plant_table IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER; v_plant_table t_plant_table; v_veh_line_count NUMBER:=0;

  41. CURSOR c_iterate_veh_lines IS SELECT DISTINCT WERS_9270_CODE FROM VEHICLE V, MKT_MDL MM WHERE ASSEMBLY_LOCATION_CODE=v_plant AND PRODUCTION_DATE >= v_start_date AND PRODUCTION_DATE <= v_end_date AND mm.mkt_mdl_sakey >= 0 and MM.MKT_MDL_SAKEY=V.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_code=v_plant and vehicle_line_code=v_vehicle and effective_date= (select max(effective_date) from bulk_material_cost t2 where t1.assembly_location_code = t2.assembly_location_code 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 <= 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/rate_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_code=v_plant and vmc.vehicle_id=v.vehicle_id and MM.mkt_mdl_sakey=V.mkt_mdl_sakey and mm.wers_9270_code=v_vehicle and from_cur=vmc.currency_code and to_cur='USD' and rt_type='CRRNT' and er.effdt=v_exchange_date;  Notice we are replacing implicit with explicit

  42. sum1rec sum1%rowtype; max_bulk_rec max_bulk%rowtype; 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):='AP11A'; v_plant_table(11):='AP12A'; v_plant_table(12):='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_table(19):='AP22A'; v_plant_table(20):='AP23A'; v_plant_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_table(26):='0129A'; v_plant_table(27):='0134A'; v_plant_table(28):='0145A'; v_plant_table(29):='0264F'; 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_plant:=v_plant_table(v_plant_index); v_veh_line_count:=0; for v_veh_rec in c_iterate_veh_lines loop v_veh_line_count:=v_veh_line_count+1; v_curr_start_date:=v_start_date; v_vehicle:=v_veh_rec.wers_9270_code;

  43. for v_plant_index in 1..31 loop v_plant:=v_plant_table(v_plant_index); v_veh_line_count:=0; for v_veh_rec in c_iterate_veh_lines loop v_veh_line_count:=v_veh_line_count+1; v_curr_start_date:=v_start_date; v_vehicle:=v_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('-'); dbms_output.put_line('-'); loop exit when v_curr_start_date > 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_date = v_curr_start_date and

  44. V.assembly_location_code=v_plant and vv.vehicle_id=v.vehicle_id and MM.mkt_mdl_sakey(+)=V.mkt_mdl_sakey and MM.wers_9270_code is null and vmc.vehicle_id=v.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.vin=vv.vin and  Noticemaking all joins possible vs.record_id=(SELECT MAX(RECORD_ID) FROM VEHICLE_SUSPENSE vss where vin=vv.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_code=v_vehicle and from_cur=vmc.currency_code and to_cur='USD' and rt_type='CRRNT' and er.effdt=v_exchange_date; v_vin:=nvl(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_code=v_plant and MM.mkt_mdl_sakey(+)=V.mkt_mdl_sakey and (MM.wers_9270_code is null or (mm.wers_9270_code=v_vehicle)) and vmc.vehicle_id=v.vehicle_id AND

  45. vv.vehicle_id=v.vehicle_id and vs.vin(+)=vv.vin and (vs.vehicle_line_code is null or (vs.vehicle_line_code=v_vehicle and vs.record_id=(SELECT MAX(RECORD_ID) FROM VEHICLE_SUSPENSE where vin=vv.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.effdt=v_exchange_date); v_vin:=v_vin-(v_count*v_bulk_material); 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_code=v_vehicle and er.from_cur=dfb.currency_code and er.to_cur='USD' and rt_type='CRRNT' and er.effdt=v_exchange_date; select sum(ext_purc_material_cost*accumulated_supplier_quantity) into v_all_parts_bom from daily_assembly_end_item_bom where production_date = v_curr_start_date and assembly_location_code=v_plant and vehicle_line_code=v_vehicle; v_st_line_bom:=nvl(v_st_line_bom,0); v_all_parts_bom:=nvl(v_all_parts_bom,0); v_vin:=nvl(v_vin,0); IF v_vin=0 THEN v_vin_all_perc_diff:=0;

  46. ELSE v_vin_all_perc_diff:=abs((v_vin-v_all_parts_bom)/(v_vin)); END IF; IF v_vin=0 THEN v_vin_st_perc_diff:=0; ELSE v_vin_st_perc_diff:=abs((v_vin-v_st_line_bom)/(v_vin)); END IF; IF v_count > 0 THEN dbms_output.put_line(v_plant||','||v_vehicle||','||v_bulk_material||','|| v_curr_start_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_date:=v_curr_start_date+1; ELSE dbms_output.put_line(v_plant||','||v_vehicle||','||v_bulk_material||','|| v_curr_start_date|| ',' ||v_st_line_bom|| ',' ||v_all_parts_bom||','||v_vin||','||v_vin_all_perc_diff|| ','||v_vin_st_perc_diff||','||v_count||','||v_count); v_curr_start_date:=v_curr_start_date+1; END IF; end loop; end loop; IF v_veh_line_count=0 THEN dbms_output.put_line('No 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_DATE 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(part_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_commodity_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.

More Related