Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Miroslav.

Asked: October 21, 2017 - 6:53 am UTC

Last updated: December 13, 2017 - 2:05 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

SET lines 777
SET pages 10000
SET trimspool ON

--Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLE t(ID INTEGER PRIMARY KEY,flag VARCHAR2(4000));

INSERT /*+append*/ INTO t
SELECT LEVEL,LPAD('a',4000,' ') FROM dual CONNECT BY LEVEL <= 500000;
COMMIT;

CALL dbms_stats.gather_table_stats(USER,'T');

SELECT /*+gather_plan_statistics*/ * 
  FROM t 
 WHERE id = (SELECT MIN(id) 
               FROM t 
              WHERE flag LIKE 'a%');

SELECT PLAN_TABLE_OUTPUT FROM table (DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALL RUNSTATS_LAST'));

SQL_ID  5jbcgzgkhfkay, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics*/ *   FROM t  WHERE id = (SELECT
MIN(id)                FROM t               WHERE flag LIKE 'a%')

Plan hash value: 2119356367

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |       |     3 (100)|          |      0 |00:00:09.95 |     500K|    500K|
|   1 |  TABLE ACCESS BY INDEX ROWID| T             |      1 |      1 |  4006 |     3   (0)| 00:00:01 |      0 |00:00:09.95 |     500K|    500K|
|*  2 |   INDEX UNIQUE SCAN         | SYS_C00142636 |      1 |      1 |       |     2   (0)| 00:00:01 |      0 |00:00:09.95 |     500K|    500K|
|   3 |    SORT AGGREGATE           |               |      1 |      1 |  4006 |            |          |      1 |00:00:09.95 |     500K|    500K|
|*  4 |     TABLE ACCESS FULL       | T             |      1 |    500K|  1910M|   109K  (1)| 00:21:59 |      0 |00:00:09.95 |     500K|    500K|
------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T@SEL$1
   2 - SEL$1 / T@SEL$1
   3 - SEL$2
   4 - SEL$2 / T@SEL$2

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

   2 - access("ID"=)
   4 - filter("FLAG" LIKE 'a%')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "ID"[NUMBER,22], "T"."FLAG"[VARCHAR2,4000]
   2 - "T".ROWID[ROWID,10], "ID"[NUMBER,22]
   3 - (#keys=0) MIN("ID")[22]
   4 - "ID"[NUMBER,22]

Is there any reasonable explanation why such an expensive plan has a total cost of 3 while it contains a full scan with cost 109K with the estimated time of 00:21:59?

and Connor said...

I've logged a bug on that.

Costs are meant to be cumulative. For example, a simple rephrase of the query

SQL> CREATE TABLE t(ID INTEGER ,flag VARCHAR2(4000)) tablespace largets;
 
Table created.
 
SQL>
SQL> INSERT /*+append*/ INTO t
  2  SELECT LEVEL,LPAD('a',1000,' ') FROM dual CONNECT BY LEVEL <= 100;
 
100 rows created.
 
SQL> COMMIT;
 
Commit complete.
 
SQL>
SQL> exec dbms_stats.gather_table_stats(USER,'T');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.set_table_stats('','T',numrows=>1000000000,numblks=>1000000000/8);
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> alter table t add primary key ( id );
 
Table altered.

SQL> SELECT  *
  2  FROM t , (SELECT MIN(id) c
  3            FROM t
  4            WHERE flag LIKE 'a%') x
  5  WHERE t.id = x.c;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 420077589
 
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |  1017 |    33M  (1)| 00:22:05 |
|   1 |  NESTED LOOPS                |              |     1 |  1017 |    33M  (1)| 00:22:05 |
|   2 |   VIEW                       |              |     1 |    13 |    33M  (1)| 00:22:05 |
|   3 |    SORT AGGREGATE            |              |     1 |  1004 |            |          |
|*  4 |     TABLE ACCESS FULL        | T            |  1000M|   935G|    33M  (1)| 00:22:05 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T            |     1 |  1004 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C0041530 |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------



keeps the cost as we would expect

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Bug Id

Miroslav Kotula, October 24, 2017 - 10:38 am UTC

Thanks a lot for that workaround. I have actually tried to rewrite it like this
SELECT * 
  FROM (SELECT *
          FROM t 
         WHERE flag LIKE 'a%'
         ORDER BY ID) 
 WHERE ROWNUM <=1

to avoid going twice to the table.
Could You, please, share the bug id since I could not find it on Oracle Support page?

thanks

Miro
Connor McDonald
October 25, 2017 - 3:46 am UTC

27013166


Cannot not still find the bug on the support page

Miroslav Kotula, December 06, 2017 - 10:36 am UTC

Hello Connor,

I still haven't found the bug on the support page. Is it not public or You have some secret link?

thanks in advance

Miro
Connor McDonald
December 13, 2017 - 2:05 am UTC

It is probably not public.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.