0

I've made a test:

SQL> set autotrace on;  << autotrace on
SQL> select * from danilo.testepart where nome = 'danilo3';

        ID NOME
---------- ----------------------------------------------------------------------------------------------------
         3 danilo3


Execution Plan
----------------------------------------------------------
Plan hash value: 3406920286 <<plan hash value

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |               |     1 |    65 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TESTEPART     |     1 |    65 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                 | TESTEPART_IDX |     1 |       |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NOME"='danilo3')
...

Looking for sql_text using the plan_hash_value in v$sqlarea:

SQL> select sql_text from v$sqlarea where plan_hash_value = '3406920286';

SQL_TEXT
---------------------------------------------------------------------------------------------------------
select * from danilo.testepart where nome = 'danilo3'
EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * from danilo.testepart where nome = 'danilo3'

My question is, the sql_text show one line with explain plan set.. so the output of autotrace on sqlplus is estimate and not the real plan used in the sql?

1 Answer 1

1

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/tuning-SQL-Plus.html

8.1.2 Execution Plan

The Execution Plan shows the SQL optimizer's query execution path. Execution Plan output is generated using EXPLAIN PLAN and DBMS_XPLAN.

Yes, it is possible to have a different actual plan. Example:

create table t1 (c1 number);
insert into t1 select 1 from dual connect by level <= 100000;
insert into t1 values (2);
create index i1 on t1(c1);
variable B1 number
exec :B1 := 2;
set autotrace on
select * from t1 where c1 = :B1;

        C1
----------
         2

Execution Plan
----------------------------------------------------------
Plan hash value: 955340196

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   933 | 12129 |    59   (2)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| I1   |   933 | 12129 |    59   (2)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"=TO_NUMBER(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        446  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

But the actual plan was:

SQL> set autotrace off
SQL> select sql_id, sql_text from v$sql where sql_text = 'select * from t1 where c1 = :B1';

SQL_ID        SQL_TEXT
------------- ----------------------------------------
9677k5kzddy78 select * from t1 where c1 = :B1

SQL> select * from table(dbms_xplan.display_cursor('9677k5kzddy78'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID  9677k5kzddy78, child number 0
-------------------------------------
select * from t1 where c1 = :B1

Plan hash value: 1816312439

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| I1   |     1 |    13 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:B1)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


22 rows selected.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.