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@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(@"SEL$1")
FULL(@"SEL$1"
"TEST_FACT"@"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@xxxxxxxxx> 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@xxxxxxxxx> wrote:
Could you show the execution plan or raw trace?
