Re: Comparing apples to apples on Exadata

Yep - looks like I didn't pay enough attention when skimming through the original email - I thought that the top 2 SQLMon reports were the good ones and bottom 2 the bad ones. Should have read the explanation!

Indeed the smart scan had kicked in in both cases listed in the 1st section, so not much to add to Jonathan's comments ... perhaps some ideas about what may cause this (minding that there's no info about the DB/table layout differences, if there's any difference in query text/columns accessed in different DBs etc):

1. there's transactional activity on the queried table in the slow database, but not in the fast database. in the slow database the smart scan has to fall back to regular block shipping mode for many blocks due to consistent reads (and/or delayed block cleanouts) that also need to access undo blocks. this can cause orders of magnitude performance difference for scanning.
2. the rows are very wide and in the slow database they are chained, but not in the fast database (if the fast DB has bigger block size than smaller - this assumes no HCC is used)
3. the query in the fast DB accesses columns lower than column number 255, in slow DB higher than col #255 (historically there have been a few bugs with smart scans & queries that access data from columns >255)

--
Tanel Poder
https://blog.tanelpoder.com


On Fri, Dec 15, 2017 at 5:29 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk


> wrote:




>

> Tanel,


>

> The top set both show Cell Offload, so I don't think it's (purely) a smart

> scan thing.

>

> I'd like to see the session stats for the executions, since that will

> probably explain what the I/O and CPU are spent on, but from the top set

> one thing I note is that the cell physical reads are single block, and the

> slow system does 62,900 compared to 6,000 on the fast system. To me this

> suggests either a chained rows or a read-consistency/cleanout/commit time

> effect on the slow system.

>

> With repeated executions and offload disabled the slow query does 4M

> buffer gets (which is remarkably similar to the number of rows is that a

> coincidence, or a comment on the number of migrated rows?) and only 324MB

> read in 9,000 read requests against 3GB in 6,000 requests - that suggests a

> lot of table blocks cached on the tablescan - perhaps because so many of

> them have been pre-read and got into the hot cache area thanks to

> "continued row fetches" in the initial runs.

>

>

> Regards

> Jonathan Lewis

>

> ________________________________________

> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on

> behalf of Tanel Poder <tanel_at_tanelpoder.com>

> Sent: 14 December 2017 23:06:37

> To: wbeldma_at_uwo.ca

> Cc: ORACLE-L

> Subject: Re: Comparing apples to apples on Exadata

>

> In the fast case, the direct path read (thus also smart scan) has kicked

> in. In the slow case it hasn't and regular buffered IO via buffer cache is

> used.

>

> One indicator of that is the fast query having a Cell offload column in

> its stats, the slow query doesn't (therefore there was no smart scan).

>

> The other indicator is that you have "cell...physical read" wait events

> showing up in the slow scenario. Smart scan can be so fast asynchronously

> feeding data for your query, so that in the fast case you see only CPU

> usage and no IO wait events at all.

>

>

>


--
http://www.freelists.org/webpage/oracle-l