How to generate a useful sql execution plan oracle optimizer blog. A sql execution plan reveals a great deal about how the oracle database plans to execute or has executed a sql statement. You can also issue the explain plan statement as part of the sql trace facility this statement also determines the cost of executing the statement. How to generate a useful sql execution plan oracle. The explain plan statement displays execution plans chosen by the oracle optimizer for select, update, insert, and delete statements. Oracle 11g automatically monitors sql statements if they are run in parallel, or consume 5 or. With the query optimizer, execution plans can and do change as the underlying optimizer inputs change. This plan can differ from the actual execution plan a sql statement because of differences in the execution environment and explain plan environment. This table can be located in the current schema or a shared schema and is created using in sqlplus as follows.
The row source tree is the core of the execution plan. An explain plan predicts how oracle will process your query. This article describes the installation of oracle database 11g release 2 11. When using oracle, if you prepend the explain plan for command to a given sql query, the database will store the. A statements execution plan is the sequence of operations oracle performs to run the statement. Indication of whether or not dynamic sampling was used to produce the plan. Name if any of the outline or the sql profile used to build the execution plan. How to get the sql execution plan on oracle vlad mihalcea. This statement inserts a row describing each step of the execution plan into a specified table. Home oracle database online documentation library, 11g release 2 11. An execution plan describes the steps it actually took.
Do you need to understand sql execution plans to be an effective oracle database expert. How to create an execution plan oracle all things sql blog. No but most of us like to learn new things, and its fun to take a look inside the machine sometimes. How can i find explain plan already used when a query was running. Explain plan output shows how oracle database would run the sql statement when the statement was explained. Release number of the oracle database that produced the explain plan. This chapter describes how to manage sql execution plans using sql plan management. Insights into statistics, query optimization and the oracle optimizer.
Home database oracle database online documentation 11g release 1 11. Use the explain plan statement to determine the execution plan oracle database follows to execute a specified sql statement. The article is based on a server installation with a minimum of 2g swap, with selinux set to permissive and the firewall disabled. Sql statements execution plan can change unexpectedly, for a variety of reasons including. Oracle database 11g is the first database on the market capable of solving this challenge. Text html xml active active html available in 11gr2 requires a download of javascript.
754 422 529 320 270 1003 950 1238 1634 1235 591 1618 1254 1064 1028 153 153 852 57 954 1408 344 1091 1572 902 921 422 1458 675 171 1452 669 116 337 1409 750 214 245 1346 1387 197 877 984