kenneth naim tune your sql session #429 top sql and pl/sql performance mistakes

Kenneth NaimTune Your SqlSession #429

Top SQL and PL/SQL Performance Mistakes

Speaker Qualifications

Kenneth Naim – Oracle Database / Data Warehouse Consultant

• Architected and coded the ETL for several data warehouses for insurance and points processing companies.

• Wrote proprietary batch (sub-ledger) interfaces for financial applications.

• Tuned dozens of batch & OLTP processes by writing better and more creative sql.

Benefits of SQL Tuning

• 1-5 order of magnitude reducing in resource utilization.

• 1-3 order of magnitude reduction in elapsed time.• Users stop complaining.• Developers keep up to date on the logic of the code

allowing future enhancements to be implemented quicker. Less Legacy code that nobody knows how it works.

• By implementing current best practices, future database upgrades will run into less issues.

Performance Issues

• Selecting from dual– Using decode within select from dual statements.– Execution of PL/SQL functions or calculations

from dual.

• Performing logic inside a loop instead of within cursor.

• Using custom functions instead of joins.– OLTP Environment– Batch Environment

Performance Issues Cont’d

• Using self joins.– Current/Most recent record.– Comparing each record to following record.– Details and Summary

• Coding without using bulk processing as 10g optimizes the code for you on the back end.

• Checking to see if a row exists prior to doing an insert, update or delete.

• Code repetition.• Using Autonomous Transactions for logging.• Missing Indexes

Selecting from dualSelecting from dual

Dual is a table that• Has one row and one column.• Oracle provides so data that is not stored in table can

be retrieved such as – pseudo columns (user, sysdate etc.)– pl/sql function return values– calculations of data passed in with the query

(7*12)• Costs 5 logical I/Os to query (prior to Oracle 10g) • Costs 3 logical I/O’s (10g+ - fast dual) • Runs very fast when run once.• Can consume a tremendous amount of resources

when used improperly.

Decode within select from dual

Example 1.1select decode(greatest(greatest(v_month_1_usage,

v_month_2_usage),

v_month_3_usage),0,

decode(v_customer_type, 'W', v_min_usage_amount ),0)

into v_usage_amountfrom dual;

A simple conditional block similar is implemented using a select from dual query within a loop.

Decode within select from dual

Solution 1.1case when greatest(v_month_1_usage,

v_month_2_usage,

v_month_3_usage) = 0 and

v_customer_type = 'W'

then v_usage_amount := v_min_usage_amount;

else v_usage_amount:= 0;end case;

Use a case or an If/Then/Else statement instead.

Execution of PL/SQL functions or calculations from dual.

Example 1.2

select safe_divide_by_zero(v_sales,v_quantity_sold)

into v_price_per_unitfrom dual;

Safe_divide_by_zero is a pl/sql function that prevents a division_by_zero error. It contains just logic and no other calls to the SQL engine.

Execution of PL/SQL functions or calculations from dual.

Solution 1.2v_price_per_unit:= safe_divide_by_zero(v_sales,v_quantity_sold);

The solution is the same as the previous example. By assigning the function to a variable we avoid the overhead of dual, and reduce the elapsed time from the tens of milliseconds to nanoseconds.

Performing Logic Inside a LoopPerforming Logic Inside a LoopExample 2for i in (select a.*, b.* from items a, stores b where a.store_id=b.store_id)

loop if i.item_type ='Furniture' then v_markup_factor=1.2;

elsif i.item_type ='Housewares' then v_markup_factor=1.35;

else v_markup_factor=1; end if; if i.store_type='Premium' then v_markup_factor := v_markup_factor * 1.2; elsif i.store_type='Discount' then v_markup_factor := v_markup_factor * .88; end if;

v_price := i.wholesale_price * v_markup; update item_prices set retail_price = v_price where item_id = i.item_id and store_id = i.store_id;end loop;

Performing Logic Inside a LoopPerforming Logic Inside a LoopSolution 2

• Convert each of the If/Then blocks into case statements.

• The second case statement for store_type, needed an an else clause which was not in the original pl/sql code to preserve the logic as every case has to be handled in SQL case statements.

• Use a Merge Statement to process all the rows within the sql engine, instead of row by row in pl/sql.

Performing Logic Inside a LoopPerforming Logic Inside a Loopmerge into prices x using (select a.*, b.*,

case when i.item_type ='Furniture' then 1.2

when i.item_type ='Housewares' then 1.35 else 1 end *

case when i.store_type='Premium' then 1.2

when i.store_type='Discount' then .88

else 1 end * wholesale_price retail_price

from items a inner join stores b using (store_id)) y

on (item_id = i.item_id and store_id = i.store_id)

when matched then update

set x.retail_price=y.retail_price where retail_price is null;

Custom Functions Instead of JoinsCustom Functions Instead of Joins

Custom Functions used instead of joins• Are executed at least once per row of data

processed.• Can be executed multiple times per row

processed when the sql statement uses a comparison operations like max.

• Provide encapsulation, although code changes are more frequent than data structure changes and the amount of logic being encapsulated is small.


Example 3.1 - OLTPcreate or replace function f_get_policy_id (policy_number_in in number)

return numberis v_policy_id number;begin select policy_id into v_policy_id from policies where policy_number=policy_number_in; return v_policy_id;end;


Example 3.1 – OLTP Cont’d

select * from policy_periods

where policy_id = f_get_policy_id (:1);

select *

from premium

where f_get_policy_id (policy_number) = :1;


Solution 3.1 – OLTP

select *

from policies a,

policy_periods b

where a.policy_id=b.policy_idand a.policy_id;


Example 3.2 – Batch Processing

select f_get_policy_id (policy_number) policy_id,

sum(written_premium) written_premium

from premium

where transaction_date between :1 and :2

group by get_policy_id (policy_number)order by 2 desc;


Solution 3.2 – Batch Processing

select policy_id,

sum(written_premium) written_premium

from premium a, policies b

where a.policy_number=b.policy_number

and transaction_date between :1 and :2

order by 2 desc


Summary 3• Using joins instead of custom functions

eliminates context switches and reduces overhead and processing time involved in running a query.

• Use custom functions only when a significant amount of logic is embedded in it and has a reasonable possibility of changing. Ideally these functions should only contain logic and not DML (select, insert update, delete, merge) statements.

Self Joins

• Joins of a table against itself in various forms.• Are typically used to get the detail and

summary data from a table.• Can be used to or find a logical progression

(i.e. current and previous record)• Was the best method prior to Oracle version

8.1.6 (2000) to retrieve these type of data within the database.

Self Joins

Example 4.1 – Current/ Most Recent Record

select claim_num, est_seq_num, a.est_amt_claim_exp,

a.est_amt_employers_liability,

a.est_amt_legal_expense, a.est_amt_medical

from wcis_dba.estimate a

where est_seq_num = (select max(b.est_seq_num)

from wcis_dba.estimate b

where a.claim_num=b.claim_num)

order by claim_num;

Self Joins

Solution 4.1 - Current/ Most Recent Record

select claim_num, est_seq_num, a.est_amt_claim_exp, a.est_amt_employers_liability, a.est_amt_legal_expense, a.est_amt_medicalfrom (select claim_num, est_seq_num, a.est_amt_claim_exp,

a.est_amt_employers_liability, a.est_amt_legal_expense, a.est_amt_medical,

row_number () over (partition by claim_num

order by est_seq_num desc) rn

from wcis_dba.estimate a)where rn=1 order by claim_num

Self JoinsUsing the analytical function row_number• Reduced the workload by 1 full scan and 1 hash join .• Allowing the optimizer to disregard all rows beyond the first

one, increasing performance as seen by step 2 where the filter from outer query is pushed down into the inner query.

Self Joins

Solution 4.1 - Current/ Most Recent Record

select claim_num, est_seq_num, a.est_amt_claim_exp, a.est_amt_employers_liability, a.est_amt_legal_expense, a.est_amt_medicalfrom (select claim_num, est_seq_num, a.est_amt_claim_exp,

a.est_amt_employers_liability, a.est_amt_legal_expense, a.est_amt_medical,

row_number () over (partition by claim_num

order by est_seq_num desc) rn

from wcis_dba.estimate a)where rn=1 order by claim_num

Self Joins

Example 4.2 – Comparing each Record to a following record.

This example illustrates a commonly asked question

“What is the frequency of sales by our repeat customers?

select a.customer_id,

round(avg(b.sale_date - a.sale_date)) avg_days_btwn_sales

from sales a, sales b

where a.customer_id=b.customer_id

and a.customer_sale_num+1 = b.customer_sale_numgroup by a.customer_id;

Self Joins

Solution 4.2 – Comparing each Record to a following record.

select customer_id, round(avg(days_between_sales)) avg_days_btwn_sales

from (select a.customer_id, lead(sales_date,1,null) over (partition by customer_id

order by customer_sale_num) –

sales_date days_between_sales from sales a)where days_between_sales is not nullgroup by customer_id;

Self Joins

Example 4.3 – Details and Summary

select decode(customer_id,null,'Grand Total', customer_id)

customer_id, amount Total from (select customer_id, sum(amount) amount,--customer_total

1 sort_order from sales group by customer_id union all select null, sum(amount) amount,--grand_total

2 sort_order from sales)

order by sort_order,1;

Self Joins

Solution 4.3 – Details and Summary

select decode(customer_id,null,'Grand Total',

customer_id) customer_id,

sum(amount) customer_total

from sales

where customer_id<100group by rollup(customer_id);

Self Joins

Summary 4• Using analytical functions one can avoid the

overhead associated with joining a table to itself, resulting in significantly less system resource utilization, faster query execution and a more scalable application.

Not Using Bulk ProcessingNot Using Bulk Processing

Example 5 – Coding without using Bulk Processing sing Bulk Processing because Oracle 10g optimizes It for you already.because Oracle 10g optimizes It for you already.

• This argument is popular because Oracle 10g fetches 100 rows instead of 1 within a loop decreasing the overhead associated with fetching.• Other DML statements within the loop are not optimized and are still processed 1 at a time.


Example 5In this example we will compare the performance of a traditional for loop inserting 1.6 million records vs. a bulk collect loop with a forall statement by looking at a tkprof of a 10046 of each process.

The tkprof breaks the code into 3 components

• PL/SQL Block

• Cursor

• Insert Statement.

Not Using Bulk ProcessingNot Using Bulk ProcessingPL/SQL Blockbegin for i in (select policy_id from policies) loop insert into policy_test values(i.policy_id);

end loop;end;

Call Count CPU Elapsed Rows

Parse 1 0.00 0.00 0

Execute 1 80.53 83.77 1

Fetch 0 0.00 0.00 0

Total 2 80.53 83.77 1


CursorSELECT POLICY_ID FROM POLICIES


Parse 1 0.00 0.00 0

Execute 1 0.01 0.00 0

Fetch 16,481 3.50 3.57 1,648,008

Total 16,483 3.51 3.57 1,648,008

Not Using Bulk ProcessingNot Using Bulk ProcessingInsert StatementINSERT INTO POLICY_TEST VALUES (:B1 )


Parse 1 0.00 0.00 0

Execute

1,648,008 345.62 352.41

1,648,008

Fetch 0 0.00 0.00 0

Total

1,648,009 345.6 352.41

1,648,008

Not Using Bulk ProcessingNot Using Bulk ProcessingSolution 5 PL/SQL Blockdeclare type array_policy_id is table of policy_test.policy_id%type;

ar_policy_id array_policy_id; cursor cur_policies is select policy_id from policies;

begin open cur_policies; loop fetch cur_policies bulk collect into ar_policy_id limit 1000;

forall i in 1..ar_policy_id.count insert into policy_test values(ar_policy_id(i));

exit when cur_policies%notfound; end loop; end;


PL/SQL Block Continued


Parse 1 0.06 0.05 0

Execute 1 0.26 0.35 1

Fetch 0 0.00 0.00 0

Total 2 0.32 0.41 1


CursorSELECT POLICY_ID FROM POLICIESCall Count CPU

Elapsed Rows

Parse 1 0.00 0.00 0

Execute 1 0.00 0.00 0

Fetch

1,649 4.65 4.73

1,648,008

Total

1,651 4.65 4.73

1,648,008



Parse 1 0.00 0.00 0

Execute

1,649 3.17 2.99

1,648,008

Fetch 0 0.00 0.00 0

Total

1,650 3.17 2.99

1,648,008

Insert StatementINSERT INTO POLICY_TEST VALUES (:B1 )


Summary 5By using bulk collect overall elapsed time decreased by 98% from 7+ minutes to 8 seconds.

Section For Loop Bulk Processing Pct Change

PL/SQL Block 83.8 0.4 99.50%

Cursor 3.6 4.7 -30.60%

Insert Statement 352.4 3.0 99.10%

Total 439.8 8.1 98.20%

Checking to see if a row exists

Example 6

for i in (select sd.item_id, sd.location_id

from mdp_matrix mdp, t_ep_dc_stock_date dc where mdp.t_ep_dc_stock_date_id = dc.t_ep_dc_stock_date_id) loop select count(1) into v_exists from sales_data where item_id =i.item_id and location_id =i.location_id and sales_date = v_insert_date and rownum < 2; if v_exists < 1 then insert into sales_data(item_id,location_id, sales_date, last_update_date) values (i.item_id, i.location_id,v_insert_date,sysdate,0,0); end if; end loop;

Checking to see if a row existsSolution 6.1 – Bulk Collect/ Forall Statementsopen cur_leading_zeros; loop fetch cur_ld_zeros bulk collect into ar_item_id, ar_location_id limit 1000; begin forall i in 1..ar_item_id.count save exceptions insert into sales_data(item_id, location_id, sales_date) values (ar_item_id(i), ar_location_id(i), sales_date_in); exception when dml_errors then v_errors:=sql%bulk_exceptions.count; end; exit when cur_ld_zeros %notfound; end loop;end;


Solution 6.2 – DML with Error Logging

exec dbms_errlog.CREATE_ERROR_LOG ('SALES_DATA', 'SALES_DATA_ERRORS');

insert into sales_data (item_id,location_id, sales_date)select mdp.item_id, mdp.location_id, sales_date_infrom mdp_matrix mdp, t_ep_dc_stock_date dcwhere mdp.t_ep_dc_stock_date_ep_id = dc.t_ep_dc_stock_date_ep_id log errors into sales_data_errors reject limit unlimited;


Solution 6.3 – Merge Statement merge into sales_data a using (select mdp.item_id, mdp.location_id, sales_date_in from mdp_matrix mdp, t_ep_dc_stock_date dc where mdp.t_ep_dc_stock_date_ep_id = dc.t_ep_dc_stock_date_ep_id)b on (a.item_id=b.item_id and a.location_id=b.location_id and a.sales_date=b.sales_date) when not matched then insert (item_id,location_id, sales_date, last_update_date, actual_quantity, gpi_sales_wkly) values (b.item_id, b.location_id, b.sales_date, sysdate, 0, 0);


Summary 6• In this example we were inserting 20 million rows into a 1.5 billion row table. Running 20 million Select Counts took a long time.• All 3 solutions were 3-4 time faster on small scale (1-2 million row) tests.• The Insert with DML logging was slower than the Merge and Bulk Collect methods as it has to do additional inserts for the logging.• The Bulk Collect method scaled more linearly than the Merge Statement as it didn’t have to access the 1.5 billion row table.


Example 7 – Code Repetition• Code Repetition is a lot more common than one would

suspect and if it can be eliminated can could significant performance gains.

• Many times code is repeated intentionally in correlated select statements as they can return only one value (if present in the select clause).

• Other times Queries are similar and have differing where clauses to filter the data. Moving the filter from the where clause to the select clause will allow the results of two queries to be achieved with one query.


Example 8 – Logging Using Autonomous TransactionsUsing Autonomous Transactions pragma autonomous_transaction; begin if debug_level_in >= debug_log_level_in and

start_or_end_in='start' then insert into process_logs (process_log_id, process_type,

start_date) values (seq_process_log_id.nextval, process_type_in,

sysdate) returning process_log_id into process_log_id_in_out; elsif debug_level_in >= debug_log_level_in and

start_or_end_in='end' then update process_logs set error_message = error_message_in, end_date = sysdate where process_log_id = process_log_id_in_out; end if; commit;end;

Logging Using Autonomous Using Autonomous TransactionsTransactions

• Autonomous Transactions when used properly are Autonomous Transactions when used properly are lightweight.lightweight.

• Since every autonomous transaction has to have a commit Since every autonomous transaction has to have a commit (or rollback), if it is called too frequently these transactions (or rollback), if it is called too frequently these transactions can put a lot of strain on the log buffer as it is being can put a lot of strain on the log buffer as it is being constantly flushed.constantly flushed.

• For every record loaded into an Oracle Apps environment For every record loaded into an Oracle Apps environment this procedure was called 6108 times consuming 37% of the this procedure was called 6108 times consuming 37% of the elapsed time, and would have added 12 additional days of elapsed time, and would have added 12 additional days of processing time to load of 1 million records.processing time to load of 1 million records.

• Even with Debug set to off, we encountered this issue as the Even with Debug set to off, we encountered this issue as the logic to check the debug level was inside the autonomous logic to check the debug level was inside the autonomous transaction.transaction.


Solution 8Solution 8• Separate the code into 2 procedures, a public one Separate the code into 2 procedures, a public one

that checks the debug level and calls the second, a that checks the debug level and calls the second, a private procedure, an autonomous transaction, private procedure, an autonomous transaction, when required based on the debug level.when required based on the debug level.

• The private procedure allowed us to only have to The private procedure allowed us to only have to change the package body, avoiding the compilation change the package body, avoiding the compilation of thousands of packages that were dependent of of thousands of packages that were dependent of this onethis one


Public

begin

if debug_level_in >= debug_log_level_in

then p_log_process (start_or_end_in,

error_message_in,

process_type_in,

process_id_in_out)

end if;

end;


Private pragma autonomous_transaction; begin if start_or_end_in='start' then insert into process_logs (process_log_id,

process_id, start_date) values (seq_process_log_id.nextval,

process_type_in, sysdate) returning process_id into process_id_out; elsif start_or_end_in='end' then update process_logs set error_message = error_message_in, end_date = sysdate where process_id=process_id_in_out; end if; commit;end;

Missing Indexes

• Are very common in pre-packaged one size fits most application as no 2 customers use the system in the same way.

• Even a single missing index one can bring a busy system to a crawl as they load millions of useless blocks into the buffer cache which impacts the application missing the index as well every other application in the same database.

Missing Indexes

Example 9.1 - Web Actions• A web page logged the session id and the submit action of a web pages to prevent issues when users doubeclick, like billing their credit card twice.

• This page checked if a record existed in the table and if it one didn’t exist it proceed else it inserted a record and ignored the second click.

• This table didn’t have any indexes, and a purge process was written so the record accumulated.

• Went unnoticed as the submission process was still relatively fast, but the system itself had a lot of trouble during peak loads.

Missing Indexes

• The table had millions of record but was only 100mb in size.

• The query that accessed this table read 35tb’s of data in a 10 hour peak period. The database size was 200gb.

• By adding the index Logical IO was redcued by 99.976%, from 100mb per query to 24k, from 35tb’s to 8gb per peak period.

• Purge process was implemented to save space.

Missing Indexes

Example 9.2• Consolidation of 2 customer records into 1 in a

prepackaged application took 12 minutes per consolidation.

• A 10046 trace did not show the offending sql as it was probably considered recursive as it was nested very deep in pl/sql calls.

• More requests for merges were coming in per day than could be processed creating a backlog of 100k even when processed in parallel

Missing Indexes

• We used the PL/SQL profiler to identify the bad queries (Metalink Note: 243755.1)

• 12 queries (out of hundreds) were identified as “slow”, each one took between 15 seconds to 2 minutes to complete.

• 11 of the 12 queries were missing an index, the 10th worst performing query was just written poorly and the vendor had a patch for it.

• Once the indexes were added and the patch applied the whole process took 8 seconds and the backog was cleared in a little over an hour.

Missing Indexes

Summary• It doesn’t take many missing indexes to slow down a

particular process or a whole system.• The 10046 trace, AWR’s and the PL/SQL profiler are

the tools to find the offending queries.• Explain plans will help you see which tables are

doing a full scans and help you determine if the full scan is appropriate or not and if an index is going to be helpful.

• As always test to make sure your theories pan out.

Items Learned in this Session

• Avoid using dual whenever possible.• Perform logic with your cursor not inside a loop.• Use joins instead of functions.• Use analytical functions instead of self joins• Use Bulk Collect and Forall Statements• Don’t check if a record exists prior to manipulating it.• Don’t repeat Code.• Use Autonomous Transactions carefully.• Make sure your queries use indexes appropriately.

Favorite Quotes

“I have no particular talent. I am merely inquisitive.”

“Imagination is more important than knowledge.”

“It’s not that I am smart, it’s just that I stay with problems longer.”

--Albert Einstein

Questions?

• Questions? Riddles? Comments?

Thank You

• Please complete your evaluation forms– Kenneth Naim– Top SQL and PL/SQL Performance

Mistakes Session #429– Email incase of additional questions

[email protected]

kenneth naim tune your sql session #429 top sql and pl/sql performance mistakes

Documents