Re: Exadata Smart Scan/ORDER BY/Chained Rows

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Fri, 13 May 2016 18:31:42 +0800
Message-ID: <CABx0cSVia-9X3Zuo0X8OJvwQWbv5OapQkm8zK+aGb2CO1wwn=g_at_mail.gmail.com>

You are also absolutely right that the table has over 255 columns. I had never considered that as the reason for the table fetch continued row. Below for your reference complete stats (for a slighty simplified testcases) - sorry trying to keep simple by being selective on stats. Note I have checked we don't have compression on the table Regards Patirck

SQL> SELECT /*+MONITOR GATHER_PLAN_STATISTICS*/ SZEDBT 2 FROM
F4101Z1

3 WHERE (SZUPMJ = 116134 AND SZTDAY BETWEEN 152503 AND 153003 );

no rows
selected

SQL> exec
mystats_pkg.ms_stop(p_threshold=>1);

MyStats report : 13-MAY-2016
18:25:42

Summary Timings

Type Statistic
Name
Value

------  ----------------------------------------------------------------
----------------

TIMER snapshot interval
(seconds)
8.64
TIMER CPU time used
(seconds)
8.56

2. Statistics
Report

Type Statistic
Name
Value

------  ----------------------------------------------------------------
----------------

TIME repeated bind elapsed
time
1
STAT table scans (long
tables)
1
STAT workarea executions -
optimal
1
STAT calls to get snapshot scn:
kcmgss
2
STAT Requests to/from
client
4
STAT SQL*Net roundtrips to/from
client
4
STAT non-idle wait
count
4
STAT recursive
calls
4
STAT execute
count
5
STAT opened cursors
cumulative
5
STAT parse count
(total)
5
STAT session cursor cache
hits
5
STAT user
calls
6
STAT workarea memory
allocated
6
STAT recursive cpu
usage
9
STAT global enqueue
releases
12
STAT global enqueue gets
sync
14
STAT calls to
kcmgcs
52
TIME parse time
elapsed
70
STAT CPU used by this
session
865
STAT CPU used when call
started
865
TIME elapsed
time
873
STAT bytes sent via SQL*Net to
client
950
TIME PL/SQL execution elapsed
time
1,286
STAT bytes received via SQL*Net from
client
1,629
STAT table scan blocks
gotten
1,254,770
STAT table fetch continued
row
1,265,216
STAT buffer is not pinned
count
3,732,674
STAT no work - consistent read
gets
4,987,444
STAT consistent
gets
4,987,492
STAT consistent gets from
cache
4,987,492
STAT consistent gets
pin
4,987,492
STAT consistent gets pin
(fastpath)
4,987,492
STAT session logical
reads
4,987,492
STAT table scan disk non-IMC rows
gotten
7,563,531
STAT table scan rows
gotten
7,563,531
TIME DB
CPU
8,645,685
TIME sql execute elapsed
time
8,683,184
TIME DB
time
8,683,979
STAT logical read bytes from cache
40,857,534,464

SQL> exec
mystats_pkg.ms_start(p_include_latches=>false);

PL/SQL procedure successfully
completed.

SQL> SELECT /*+MONITOR GATHER_PLAN_STATISTICS*/ null

2 FROM
F4101Z1

3 WHERE (SZUPMJ = 116134 AND SZTDAY BETWEEN 152503 AND 153003 );

no rows
selected

SQL> exec
mystats_pkg.ms_stop(p_threshold=>1);

MyStats report : 13-MAY-2016
18:29:53

Summary Timings

Type Statistic
Name
Value

------  ----------------------------------------------------------------
----------------

TIMER snapshot interval
(seconds)
0.87
TIMER CPU time used
(seconds)
0.31

2. Statistics
Report

Type Statistic
Name
Value

------  ----------------------------------------------------------------
----------------

STAT cell num smartio automem buffer allocation attempts
1
STAT cell
scans
1
STAT consistent gets
examination
1
STAT consistent gets examination
(fastpath)
1
STAT parse count
(hard)
1
TIME repeated bind elapsed
time
1
STAT table scans (direct
read)
1
STAT table scans (long
tables)
1
STAT workarea executions -
optimal
1
STAT Requests to/from
client
5
STAT SQL*Net roundtrips to/from
client
5
STAT table fetch by
rowid
6
STAT enqueue
releases
7
STAT enqueue
requests
7
STAT user
calls
7
STAT index scans
kdiixs1
8
STAT calls to get snapshot scn:
kcmgss
9
STAT recursive cpu
usage
9
STAT session cursor cache
hits
9
STAT execute
count
11
STAT opened cursors
cumulative
11
STAT parse count
(total)
11
STAT calls to
kcmgcs
28
STAT workarea memory
allocated
29
STAT recursive
calls
36
STAT CPU used by this
session
40
STAT CPU used when call
started
40
STAT global enqueue
releases
45
STAT global enqueue gets
sync
47
STAT non-idle wait
time
54
STAT user I/O wait
time
54
TIME elapsed
time
95
STAT table scan blocks
gotten
736
STAT bytes sent via SQL*Net to
client
997
TIME PL/SQL execution elapsed
time
1,173
STAT bytes received via SQL*Net from
client
1,638
TIME hard parse elapsed
time
2,282
TIME parse time
elapsed
2,478
STAT table fetch continued
row
3,357
STAT chained rows rejected by
cell
3,358
STAT chained rows skipped by
cell
3,358
STAT buffer is not pinned
count
3,378
STAT no work - consistent read
gets
3,379
STAT consistent gets
pin
3,401
STAT consistent gets pin
(fastpath)
3,401
STAT consistent gets from
cache
3,402
STAT table scan disk non-IMC rows
gotten
4,253
STAT table scan rows
gotten
4,253
STAT cell flash cache read
hits
9,737
STAT physical read requests
optimized
9,809
STAT physical read total multi block
requests
9,817
STAT physical read IO
requests
9,826
STAT physical read total IO
requests
9,826
STAT file io wait
time
11,559
STAT non-idle wait
count
16,146
STAT session uga memory
max
130,976
STAT session pga memory
max
131,072
TIME DB
CPU
385,942
TIME sql execute elapsed
time
827,230
TIME DB
time
830,169
STAT cell blocks helped by minscn
optimization
1,245,738
STAT cell blocks processed by cache
layer
1,245,738
STAT cell blocks processed by data
layer
1,245,738
STAT cell blocks processed by txn
layer
1,245,738
STAT consistent gets
direct
1,254,770
STAT physical
reads
1,254,770
STAT physical reads
direct
1,254,770
STAT consistent
gets
1,258,172
STAT session logical
reads
1,258,172
STAT cell physical IO interconnect
bytes
2,388,400
STAT cell physical IO interconnect bytes returned by smart scan
2,388,400
STAT chained rows processed by
cell
3,729,316
STAT logical read bytes from
cache
27,869,184
STAT cell physical IO bytes saved by storage index
73,990,144
STAT cell IO uncompressed bytes
10,205,085,696
STAT cell physical IO bytes eligible for predicate offload 10,279,075,840
STAT physical read bytes
10,279,075,840
STAT physical read total bytes
10,279,075,840
STAT physical read total bytes optimized 10,261,282,816

3.
Options

Statistics types : statistics=Y, latches=N, time model=Y
Reporting filter : threshold=1

4.
About

MyStats v3.0 by Adrian Billington (http://www.oracle-developer.net)
Based on the SNAP_MY_STATS utility by Jonathan Lewis

End of
report

PL/SQL procedure successfully
completed.

On 13 May 2016 at 18:11, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> 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
> _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-l

Received on Fri May 13 2016 - 12:31:42 CEST

This message: [ Message body ]
Next message: Svetoslav Gyurov: "Re: Upgrade ASM and database to 12c - No GRID or RAC"
Previous message: Jonathan Lewis: "RE: Exadata Smart Scan/ORDER BY/Chained Rows"
In reply to: Jonathan Lewis: "RE: Exadata Smart Scan/ORDER BY/Chained Rows"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message