Re: Strange Behaviour (with Test Case)
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-lReceived on Wed Aug 05 2015 - 15:36:07 CEST