oracle_plsql_优化3.ppt

15 May 2001 TENDER DC0101 Workshops DAY3: Workshops Workshop Workshop Bind Variables Oracle performs a CPU intensive hard parse for all new statements Statements already present in the shared pool only require a soft parse Statement matching uses “Exact Text Match”, so literals, case and whitespaces are a problem. bind_variable_usage.sql Unnecessary parses waste CPU and memory bind_performance.sql Workshop Workshop Bind Variables – Cursor Sharing Cursor sharing reduces the impact of literals Bind variables are substituted for literals cursor_sharing.sql Cursor sharing is unnecessary if you do the job properly. Cursor sharing introduces other costs May cause performance issues in some code Workshop Workshop Bind Variables – Dynamic Binds Complex dynamic SQL can make using bind variables difficult and ugly dynamic_binds_setup.sql dynamic_binds_test.sql Contexts allow you to reduce code complexity. dynamic_binds_setup_2.sql dynamic_binds_test.sql Query transformation allows complex dynamic SQL to still use bind variable dynamic_binds_setup_3.sql dynamic_binds_test.sql Workshop Workshop (continue) Use ROWID for Updates ROWIDs are unique row identifier The ROWID represents the physical address of the row in the database, but are subject to change, so don’t store them When you have to select rows, then subsequently update them, retrieve the ROWID also and use that to identify the row to update Rowid_test.sql Workshop Workshop (continue) Short-Circuit Evaluations If left side of an OR expression is TRUE, the whole expression is TRUE TRUE OR FALSE = TRUE TRUE OR TRUE = TRUE If the left of an AND expression is FALSE, the whole expression is FALSE FALSE AND FALSE = FALSE FALSE AND TRUE = FALSE In these cases Oracle doesn’t evaluate the second half of the expression Place “least expensive” tests to the left of expressions short_circuit.sql Workshop Workshop (continue) Logic/Branch Ordering Evaluations of ELSIF and CASE statements stop once a match is found. Plac