Understanding Execute Explain Plan Results in Oracle SQL Developer

Question

I am trying to optimize the request, but I don’t quite understand the part of the information returned from the Explain plan . Can someone tell me the value of the OPTIONS and COST columns? In the OPTIONS column, I see only the word FULL. In the COST column, I can conclude that lower cost means faster request. But what exactly is the cost and the acceptable threshold?

Answer 1

EXPLAIN PLAN output is the debug output of the Oracle query optimizer. COST is the end result of a cost optimizer (CBO) whose goal is to choose which of the many possible plans should be used to trigger the request. CBO calculates the relative cost for each plan, then selects the plan with the lowest cost.

(Note: in some cases, CBO does not have enough time to evaluate all possible plans, in these cases, it simply selects the plan with the lowest cost found so far)

In general, one of the biggest contributions to a slow query is the number of lines read to service the request (more precisely, the blocks), so the cost will be partially based on the number of lines that the optimizer estimates will need to be read.

For example, let's say you have the following query:

SELECT emp_id FROM employees WHERE months_of_service = 6; 

(The months_of_service column has a NOT NULL constraint on it and a regular index on it.)

There are two main plans that the optimizer can choose here:

• Plan 1: Read all the lines from the "employees" table, for each check whether the predicate is true ( months_of_service=6 ).
• Plan 2. Read the index where months_of_service=6 (this results in a set of ROWIDs), then access the table based on the returned ROWIDs.

Suppose that the employees table contains 1,000,000 (1 million) rows. Suppose further that the values ​​for months_of_service vary from 1 to 12 and for some reason are fairly evenly distributed.

The cost of Plan 1 , which includes a FULL SCAN, will cost you to read all the rows in the employee table, which is approximately equal to 1,000,000; but since Oracle can often read blocks using multi-block reads, the actual cost will be lower (depending on how your database is configured) - for example, suppose the number of samples with multiple blocks is 10 - the estimated cost of a full scan is 1,000,000 / 10 ; Total cost = 100,000.

The cost of Plan 2 , which includes an INDEX RANGE SCAN scan and a table search by ROWID, will cost an index scan, as well as the cost of accessing the table using a ROWID. I will not go into how a range index scan will cost, but suppose that the cost of scanning an index index is 1 per row; we expect to find a match in 1 out of 12 cases, so the cost of scanning the index is 1,000,000 / 12 = 83,333; plus the cost of accessing the table (suppose that 1 block is read for access, we cannot use multi-block reads here) = 83.333; Total cost = 166 666.

As you can see, the cost of plan 1 (full scan) is less than the cost of plan 2 (index check + rowid access) - this means that CBO will choose FULL scan.

If the assumptions made here by the optimizer are true, then actually Plan 1 will be preferable and much more effective than Plan 2 - which refutes the myth that FULL scans are "always bad."

The results would be completely different if the optimizer’s goal was FIRST_ROWS (n) instead of ALL_ROWS - in this case, the optimizer would prefer plan 2 because it will often return the first few rows faster, at the cost of less efficiency for the entire query.

Answer 2

CBO builds a decision tree, evaluating the costs of each possible execution path available for each request. Costs are set by the CPU_cost or I / O_cost parameter set in the instance. And the CBO estimates the costs as much as possible with the existing statistics of the tables and indexes that will be used in the query. You should not customize your request based on cost alone. Cost makes it clear WHY the optimizer does what it does. At no cost, you could understand why the optimizer chose the plan that he made. Lower cost does not mean faster request. There are times when this is true, and there will be times when it is wrong. The cost is based on your statistics table, and if they are wrong, the cost will be incorrect.

When setting up a query, you should look at the power and number of lines of each step. Do they make sense? Is the optimizer considered correct? Whether rows are returned correctly. If the information is not present correctly, then most likely the optimizer does not have the proper information necessary to make the right decision. This may be due to outdated or missing statistics in the table and index, as well as cpu-stats. It’s best to update statistics when setting up your query in order to maximize the capabilities of the optimizer. Knowing your circuit also helps a lot when setting up. Knowing when the optimizer chose a really bad solution and pointed it the right way with a little hint can save time.

Answer 3

Here is a link to using EXPLAIN PLAN with Oracle: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm ), with specific information about the columns found here: http: // download .oracle.com / docs / cd / B19306_01 / server.102 / b14211 / ex_plan.htm # i18300

Your mention of "FULL" tells me that the request is doing a full-screen scan to find your data. In some cases, this is normal, otherwise it is an indicator of a poor index / query record.

As a rule, with explanations, you want your query to use keys, so Oracle can find the data you need by accessing the fewest rows. Ultimately, you will someday be able to access the architecture of your tables. If the costs remain too high, you might have to think about how to tune your circuit diagram for better performance.

Answer 4

In recent versions of Oracle, COST is the time that the optimizer expects from a query, expressed in units of time required to read one block.

So, if reading one block takes 2 ms, and the cost is expressed as "250", a request may be required to complete 500 ms.

The optimizer calculates the cost based on the estimated number of single-block and multi-block readings, as well as on the processor consumption in the plan. the latter can be very useful to minimize costs by performing certain operations in front of others in order to try to avoid high CPU costs.

This begs the question of how the optimizer knows how long the operations take. recent versions of Oracle allow you to create collections of "system statistics", which, of course, should not be confused with the statistics of tables or indexes. System statistics are measurements of hardware performance, most importantly:

• How long it takes to read one block.
• How long does multitasking reading take
• How large is multi-block reading (often different from the maximum possible due to the fact that the size of the table is less than the maximum value and other reasons).
• Processor performance

These numbers can vary greatly depending on the operating environment of the system, and various statistics can be stored for daily OLTP operations and nightly periodic reporting operations, and for end-of-month reporting if you want.

Given these statistical data sets, this query execution plan can be estimated for cost in different operating environments, which may facilitate the use of a full table scan several times or an index scan in others.

The cost is not perfect, but the optimizer becomes better at self-monitoring with each release and can respond to the actual cost compared to the estimated cost in order to make better decisions in the future. it also makes prediction difficult.

Please note that the cost is not necessarily wall time, since parallel query operations consume the total amount of time for multiple threads.

In older versions of Oracle, the cost of CPU operations was ignored, and the relative cost of single and multi-block reads was effectively corrected in accordance with the init parameters.

Answer 5

FULL probably refers to a full table scan, which means that indexes are not used. This usually indicates that something is wrong, unless the query should use all the rows in the table.

Cost is a number that signals the sum of the various loads, the processor, memory, disk, IO, and high numbers are usually bad. Numbers are added when moving to the root of the plan, and each branch should be checked to identify bottlenecks.

You can also query v $ sql and v $ session to get statistics about SQL statements, and this will have detailed metrics for all kinds of resources, timings and runtimes.