Explain Plan with Oracle

Question

I want to take a look at the execution-Plans in Oracle. I tried this:

select /*+ gather_plan_statistics */ /*test4*/ * from tag
where key='name';

SELECT t.*
FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number, 'TYPICAL PROJECTION')) t WHERE sql_text LIKE '%test4%';

The result was:

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    22 (100)|          |
|*  1 |  TABLE ACCESS FULL| TAG  |    14 |   714 |    22   (0)| 00:00:01 |

My problem is that the Rows are not correct, there are 3019 Rows with key='name'. This is an estimated Value.

My question is, how can I get the real value ?

Thanks, Andre

Please always past the complete execution plan; not just snippets. Since the optimizer is estimating only 14 rows, it's likely your stats are not up to date. So run dbms_stats.gather_table_stats( user, 'TAG' ); — BobC, Mar 03 '17 at 23:56

score 2 · Answer 1 · answered Jun 06 '14 at 12:23

You should use this command:

select * from table(dbms_xplan.display_cursor(format => 'ALLSTATS LAST'));

And look for differences between the columns E-Rows and A-Rows in the output.

See also How do I know if the cardinality estimates in a plan are accurate?

score 1 · Answer 2 · edited May 23 '17 at 12:25

1

In SQL*Plus you can use the AUTOTRACE feature.

See here for an example.

edited May 23 '17 at 12:25

Community

1
1

answered Jun 06 '14 at 08:08

Thilo

257,207
101
511
656

I set autotrace on and tried the option with autotrace traceonly to, but it doesen't change a thing. – Andre Jun 06 '14 at 08:36

score 0 · Answer 3 · answered Mar 03 '17 at 23:54

0

Do this:

alter session set statistics_level=ALL;

Then run:

select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST' ) );

answered Mar 03 '17 at 23:54

BobC

4,208
1
12
15

Explain Plan with Oracle

3 Answers3