The deepest will be the driving table to the immediate outer table 3. Understanding oracle explain plan burleson consulting. The server runs oracle database software and handles the functions required for concurrent, shared data access. By any chance have you written any book on oracle sql plsql tuning which. If nested loop is present, then oracle will try to find its corresponding rows from anothersame table depend on. Explain plan lies explain plan should hardly ever be used you have to be careful when using autotrace and related tools never use explainup with tkprof. Which function you choose for caseinsensitive searches is irrelevant but document whichever you choose, so it is clear to all developers on the team. The row source tree is the core of the execution plan. In a clientserver architecture, the client application initiates a request for an operation to be performed on the database server. The deepest most indented table in the plan will be executed first starting point 2.
It is best to use explain plan to determine an access plan, and then later prove that it is the optimal plan through testing. Use explain plan to see how oracle database accesses partitioned objects for specific queries. First find the innermost line, that is where oracle starts its processing. The row source name for the range partition is partition range. This plan can differ from the actual execution plan a sql statement because of differences in the execution environment and explain plan environment. Oracle sql tuning how to read execution plans explain plan. With the query optimizer, execution plans can and do change as the underlying optimizer inputs change. This book describes how the oracle rdbms arrives at an execution plan. Use oracle trace or the sql trace facility and tkprof to examine individual sql statement performance. Explain plan is the sequence in which oracle would be going to join the tables to build up the query. The explain plan statement displays execution plans chosen by the oracle optimizer for select, update, insert, and delete statements.
This book covers useful tools useful for working with sql and explain plans. Pdf optimizing the execution plan in an oracle database. In this free tutorial you will learn how to generate and read interpret an execution. A statements execution plan is the sequence of operations oracle performs to run the statement. With oracle 11g, sql plan management evolves more fully. Get the complete oracle sql tuning information the landmark book advanced oracle sql. To determine the join order in an execution plan look at the indentation of the tables in the operation column. The query will display the execution plan as shown in the book. With the costbased optimizer, execution plans can and do change as the underlying costs change.
Explain plan output shows how oracle database would run the sql statement when the statement was explained. When you need to troubleshoot inefficient sql, one of the most important diagnostics tools at your disposal is the output of the explain plan command. Important to understand before we discuss execution plans is how oracle. Join order the join order is the order in which the tables are joined together in a multitable sql statement. If you execute the plan without running table statistics. Oracle database architecture introduction to oracle database 111. Partitions accessed after pruning are shown in the partition start and partition stop columns. University of washington oracle instructor for 10 years.
650 857 211 165 287 917 1032 456 744 948 1410 635 16 402 50 479 732 1054 1138 885 1368 963 1520 874 1412 262 729 1262 656 366 771 1092 742 414 1084 517 1448 254 158 100