RE: Exadata Smart Scan/ORDER BY/Chained Rows
Date: Fri, 13 May 2016 10:11:48 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282D3AA5_at_EXMBX01.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Patrick Jolliffe [jolliffe_at_gmail.com] 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri May 13 2016 - 12:11:48 CEST