1 / 13

SQL Optimizer: Overview, Execution, and Tuning

Learn how the SQL optimizer works, how SQL is executed, and how to identify and tune statements. Explore the Explain Plan feature and modify the optimizer for better performance.

marywthompson
Download Presentation

SQL Optimizer: Overview, Execution, and Tuning

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. Agenda • Overview of the optimizer • How SQL is executed • Identifying statements that need tuning • Explain Plan • Modifying the plan

  2. Optimizer • Attempts to find best path • Mode is set in init.ora file (can also be set for a session) • Rule Based • Init.ora optimizer_mode = rule • Based on set of rules executed in order • Cost Based • Init.ora optimizer_mode = choose and table(s) analyzed (uses rule otherwise) • Based on statistics logged in tables • Tables analyzed by DBA - this generates the statistics • NOTE that these must be updated regularly to be meaningful

  3. Processing SQL • Optimizer looks at the following: • details of the syntax • any conditions to satisfy (where) • the tables and indexes (if any) • any “hints” • any stats via the ANALYZE function • WHERE clause - least restrictive to most restrictive

  4. SQL Execution Stages • Parse • SQL syntax is checked • Placed into cursor cache in the SGA • Execution plan is built • Describe/Bind • program variables are associated with the cursor • Open/Execute • SQL is run until the first row returns • Fetch

  5. Tuning SQL – identifying statements • Set timing on • V$sqlarea DEFINE threshold = 100 SELECT parsing_user_id, command_type, disk_reads, buffer_gets, executions, sql_text FROM v$sqlarea WHERE disk_reads > &threshold ORDER BY disk_reads DESC; • explain plan • set autotrace on set autotrace traceonly explain statistics

  6. Explain Plan Overview • Explain Plan provides explanation of path taken to retrieve data (SQL execution path) • Set up Explain plan environment • Create the PLAN_TABLE with @ORACLE_HOME/rdbms/admin/utlxplan.sql • run explain plan by preceeding SQL with explain plan set statement_id=user||’_1’ -- identify your SQL statements for <query>

  7. Explain Plan Overview (con’d) • Read the plan by selecting from PLAN_TABLE SELECT LPAD(‘ ’,2*Level)||Operation||’ ’ || Options || ‘ ’ || decode(object_owner, null, ‘ ’, object_owner||‘.’||object_name) || ‘ ’ ||initcap(optimizer)||decode(cost, null, ‘ ’, ‘Cost=‘||cost)|| decode(cardinality, null, ‘ ’, ‘Rows Expected=‘ ||cardinality|| ‘Result Set Size=‘||bytes|| ‘ Bytes’) Q_Plan FROM PLAN_TABLE CONNECT BY prior ID = Parent_ID AND Statement_ID=user||’_1’ START WITH ID=0 and Statement_ID=user||‘_1’;

  8. Explain Plan Overview (con’d) • Operations and options • index • range scan, unique scan • table access • full, by rowid • join operations • nested loops, merge join • Delete from PLAN_TABLE when done • tuning by changing the plan • add/modify indexes • optimizer hints • rewrite SQL

  9. Sample Output SQL> Explain plan set statement_id='demo' for 2 select ename, loc, sal, hiredate 3 from emp, dept 4 where emp.deptno=dept.deptno; Explained. SQL> SELECT LPAD(' ',2*(level-1)) || operation operation, 2 Options, object_name, position 3 FROM plan_table 4 CONNECT BY PRIOR id=parent_id 5 and statement_id='demo' 6 START WITH id=1 and statement_id='demo'; OPERATION OPTIONS OBJECT_NAME POSITION ------------------------------ ------------------------- --------------- --------- MERGE JOIN 1 SORT JOIN 1 TABLE ACCESS FULL DEPT 1 SORT JOIN 2 TABLE ACCESS FULL EMP 1

  10. Changing the PLAN • Change order of tables in from clause • suppress indexes where applicable • concatenate a null to a char column or add zero to number column will suppress the index • use FULL hint • use complete key for joins • don’t use function calls on columns in the where clause unless you have a function based index (possible with 8i) • improve the index scheme to match access pattern

  11. SQL tuning tips • try EXISTS instead of IN • try NOT EXISTS instead of NOT IN • use joins in place of EXISTS • use most selective index • use rowid if possible? -careful • remember the cache! • Full table scans aren’t always bad… • timing information

  12. Hints • /*+ and */ after a SELECT designate a hint to the optimizer • if you don’t include the +, Oracle will not give you an error - just becomes a comment • many available hints • index select /*+ INDEX(COMPANY) */Name, City, Statefrom COMPANYwhere City = 'Roanoke'and State = 'VA'; • full, all_rows, first_rows, index_desc, use_nl, many others...

  13. Lab: Explain Plan • Setup PLAN_TABLE • run explain plan • query PLAN_TABLE • delete from PLAN_TABLE (or change statement_id) • modify query and re-run

More Related