Thanks. You are absolutely correct about the ORDER BY clause affecting
column projection, I had missed that.
Column Projection Information (identified by operation
id):
-----------------------------------------------------------
1 - (#keys=1) "SZEDBT"[NCHAR,30],
NULL[0]
2 -
"SZEDBT"[NCHAR,30]
On 13 May 2016 at 18:11, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
What's the level of table compression ?
How many columns in the table definition, and how many of them are likely
to have been populated
There are no stats reported about compression units - is this you being
selective about the stats you're showing use.
The first set of stats suggest that you've got rows with more than 255
columns
The second set of stats suggest that you've got columnar compression in
place - which is why I ask about the reported stats
Key detail - your order by clause is on a column that's not in the select
list, and the position of that column in the table definition could affect
both the cost and the run-time mechanism that has to be used.
Regards
Jonathan Lewis
Send
<https://webmail.demon.co.uk/owa/?ae=PreFormAction&a=ReplyAll&t=IPM.Note&id=RgAAAAD3x7gzDZuUQbmvte7pqTsiBwDOcCF3Myc%2fSaihYu4HT2TZAAAACimhAADOcCF3Myc%2fSaihYu4HT2TZAAAoLKgZAAAJ&pspid=_1463133384485_94279059#>
http://jonathanlewis.wordpress.com
@jloracle
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Patrick Jolliffe [jolliffe@xxxxxxxxx]
*Sent:* 13 May 2016 10:41
*To:* oracle-l
*Subject:* Exadata Smart Scan/ORDER BY/Chained Rows
Trying to understand some behaviour we are seeing on our Exadata instance
(12.1.0.2).
Below list some SQL statments, a selection of stats for each execution,
and some analysis of chained rows.
First thing I am struggling with is that ORDER BY clause seems to stop
smart scan from taking place.
My understanding of mechanism is that the TABLE ACCESS STORAGE FULL step
should be unaware of the parent operation.
Also can see that storage and filter details are same in both cases.
Note I have done some analysis and can see that 99% of table is in buffer
cache so could understand that as a reason that smart scan is not taking
place, but again I don't understand why the ORDER BY would make any
difference.
Second, (not sure whether or not related) I don't understand why each
block accessed seems to translate to a table fetch continued row.
Per analysis we only have 22,000 chained rows.
Happy to provide any further details or statistics as required, but didn't
want to overload the email.
Note is Friday evening in my time zone, so apologies if I am not able to
get required information for a couple of days.
Regard, Patrick
SQL> SELECT /*+MONITOR GATHER_PLAN_STATISTICS*/
NULL
2 FROM
F4101Z1
3 WHERE SZTYTN = 'JDEITEM' AND SZDRIN = '2' AND SZTNAC =
'UA'
4 AND (SZUPMJ > 116134 OR (SZUPMJ = 116134 AND SZTDAY >= 152503
))
5 AND (SZUPMJ < 116134 OR (SZUPMJ = 116134 AND SZTDAY < 153003
))
6 ORDER BY SZEDBT;
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
| A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0
|00:00:09.06 | 4987K|
| 1 | SORT ORDER BY | | 1 | 1 | 0
|00:00:09.06 | 4987K|
|* 2 | TABLE ACCESS STORAGE FULL| F4101Z1 | 1 | 1 | 0
|00:00:09.06 | 4987K|
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation
id):
---------------------------------------------------
2 - storage((("SZUPMJ">116134 OR ("SZUPMJ"=116134 AND
"SZTDAY">=152503))
AND
"SZTNAC"=U'UA' AND "SZTYTN"=U'JDEITEM' AND "SZDRIN"=U'2' AND
("SZUPMJ"<116134 OR
("SZUPMJ"=116134 AND
"SZTDAY"<153003))))
filter((("SZUPMJ">116134 OR ("SZUPMJ"=116134 AND "SZTDAY">=152503))
AND
"SZTNAC"=U'UA' AND "SZTYTN"=U'JDEITEM' AND "SZDRIN"=U'2' AND
("SZUPMJ"<116134 OR
("SZUPMJ"=116134 AND
"SZTDAY"<153003))))
STAT table scan rows
gotten
7,563,531
STAT table scan blocks
gotten
1,254,770
STAT table fetch continued
row
1,265,216
STAT logical read bytes from
cache 40,857,534,464
********************************************************************************************************************************************
********************************************************************************************************************************************
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0
|00:00:01.10 | 1258K| 1254K| | | |
|* 1 | TABLE ACCESS STORAGE FULL| F4101Z1 | 1 | 1 | 0
|00:00:01.10 | 1258K| 1254K| 1025K| 1025K| 3085K (0)|
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation
id):
---------------------------------------------------
1 - storage((("SZUPMJ">116134 OR ("SZUPMJ"=116134 AND
"SZTDAY">=152503)) AND "SZTNAC"=U'UA' AND "SZTYTN"=U'JDEITEM'
AND
"SZDRIN"=U'2' AND ("SZUPMJ"<116134 OR ("SZUPMJ"=116134 AND
"SZTDAY"<153003))))
filter((("SZUPMJ">116134 OR ("SZUPMJ"=116134 AND "SZTDAY">=152503))
AND "SZTNAC"=U'UA' AND "SZTYTN"=U'JDEITEM' AND
"SZDRIN"=U'2' AND ("SZUPMJ"<116134 OR ("SZUPMJ"=116134 AND
"SZTDAY"<153003))))
STAT table scans (direct
read)
1
STAT table scans (long
tables)
1
STAT table scan blocks
gotten
736
STAT table fetch continued
row
3,357
STAT chained rows rejected by
cell
3,358
STAT chained rows skipped by
cell
3,358
STAT table scan rows
gotten
4,253
STAT chained rows processed by
cell
3,729,316
STAT physical read
bytes
10,279,075,840
********************************************************************************************************************************************
********************************************************************************************************************************************
ANALYZE TABLE CRPDTA.F4101Z1 LIST CHAINED ROWS INTO CHAINED_ROWS;
select count(*) from chained_rows;
22 695
select count(*) from CRPDTA.F4101Z1 ;
3 778 849
