Re: Strange Behaviour (with Test Case)

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Wed, 5 Aug 2015 21:36:07 +0800
Message-ID: <CABx0cSX9sBvytBxMzps-xtUS8bFCw0V+49r-YLxuT1NP6JVKJw_at_mail.gmail.com>



21:34:53 SQL> select * from
table(dbms_xplan.display_cursor(format=>'allstats last advanced'));

PLAN_TABLE_OUTPUT



SQL_ID cpyzpwfcpn902, child number
0


SELECT /*+ gather_plan_statistics */ SUM( CASE WHEN
(CHRTYPE = 'R03' OR CHRTYPE = 'LDU') THEN RATE /10
END ) FROM
TEST_FACT Plan hash value:
1326015606



| Id  | Operation                  | Name      | Starts | E-Rows |E-Bytes|
Cost (%CPU)| E-Time |
A-Rows | A-Time | Buffers
|

|   0 | SELECT STATEMENT           |           |      1 |        |
|     3 (100)|          |
     1 |00:00:00.01 |       7
|
|   1 |  SORT AGGREGATE            |           |      1 |      1 |    16
|            |          |
     1 |00:00:00.01 |       7
|
|   2 |   TABLE ACCESS STORAGE FULL| TEST_FACT |      1 |      3 |    48
|     3   (0)| 00:00:01 |
     3 |00:00:00.01 |       7

|

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


   1 -
SEL$1    2 - SEL$1 / TEST_FACT_at_SEL$1

Outline
Data


/*+

BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.2') DB_VERSION('12.1.0.2') ALL_ROWS OUTLINE_LEAF(_at_"SEL$1")

      FULL(_at_"SEL$1"
"TEST_FACT"_at_"SEL$1")

END_OUTLINE_DATA */

Column Projection Information (identified by operation id):


   1 - (#keys=0) SUM(CASE "CHRTYPE" WHEN 'R03' THEN "RATE"/10 WHEN 'LDU' THEN "RATE"/10 END )[22]
   2 - (rowset=200) "CHRTYPE"[VARCHAR2,3], "RATE"[NUMBER,22] Note


  • dynamic statistics used: dynamic sampling (level=2)

On 5 August 2015 at 20:29, Patrick Jolliffe <jolliffe_at_gmail.com> wrote:

> Raw trace means what - will try to generate tommorrow?  Here is explain
> plan for now, sorry about the formatting:
>
> 20:27:25 SQL> select * from
> table(dbms_xplan.display_cursor);
>
>
> PLAN_TABLE_OUTPUT
>
>
> ---------------------------------------------------------------------------------------------------
> ---------------------------------------------------------------------------------
>
> SQL_ID  67bm5czzp2hs0, child number
> 0
> -------------------------------------
>
> SELECT SUM(       CASE          WHEN (CHRTYPE = 'R03' OR CHRTYPE
> =
> 'LDU')          THEN RATE     /10       END )    FROM
> TEST_FACT
>
>
> Plan hash value:
> 1326015606
>
>
>
> ----------------------------------------------------------------------------------------
>
> | Id  | Operation                  | Name      | Rows  | Bytes | Cost
> (%CPU)| Time     |
> ----------------------------------------------------------------------------------------
>
> |   0 | SELECT STATEMENT           |           |       |       |     3
> (100)|          |
> |   1 |  SORT AGGREGATE            |           |     1 |    16
> |            |          |
> |   2 |   TABLE ACCESS STORAGE FULL| TEST_FACT |     3 |    48 |     3
> (0)| 00:00:01 |
> ----------------------------------------------------------------------------------------
>
>
>
> Note
>
> -----
>
>    - dynamic statistics used: dynamic sampling
> (level=2)
>
>
>
>
> 19 rows
> selected.
>
>
>
> Elapsed: 00:00:00.20
>
> On 5 August 2015 at 19:37, Sayan Sergeevich Malakshinov <
> malakshinovss_at_psbank.ru> wrote:
>
>> Could you show the execution plan or raw trace?
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 05 2015 - 15:36:07 CEST

Original text of this message