can you manually set statistics so that size is > threshold?
On Tue, Sep 19, 2017 at 10:09 AM Dave Herring <gdherri@xxxxxxxxx> wrote:
Ryan, unless I'm misunderstanding your response my main question was
answered at first by Alex, with the suggestion of using "ALTER SESSION SET
EVENTS 'trace[nsmtio]';". Once I did this I (and using Frits' blog) I
could see possibly all the details I needed.
The tough part is what to do now. My employer cancelled their ULA w/
Oracle so now all support is through a 3rd party, which means I'll have to
explain all this for 1 party and have them explain it to Oracle, then back
through the phone game.
Dave
On Tue, Sep 19, 2017 at 9:43 AM, Ryan January <rjanuary@xxxxxxxxx> wrote:
On Sep 18, 2017, at 4:26 PM, Dave Herring <gdherri@xxxxxxxxx> wrote:a particular cursor and nothing is standing out as to why, so I'm wondering
We have an X-6 environment where we're not getting smart table scans on
if anyone knows of a way to trace/debug why the choice is being made.
from a backup of an X-4, Oracle 11.2.0.4 database on Linux 5.11.
A few details: X-6, Oracle 12.1.0.2, Linux 6.8. Database was restored
the xplans details (including predicts section) are exactly the same,
Between X-6 and X-4 for this cursor the plan_hash_value is the same and
including references to "storage" in both sections.
on the X-6 whereas on X-4 I see "cell smart table scan" and as you can
Session tracing shows time taken up on "cell multiblock physical read"
imagine, the X-4 cursor runs much faster than on X-6.
initially Oracle seems to think it'll use smart table scans and then give
Is there any way to trace where and why decisions are made when
up?
Dave
Getting directly back to the question of if there's a way to trace why
the decision is made. IIRC The decision to do a smart scan is at the
storage layer, and below the optimizer's reach. The only thing you can do
is ensure you have an environment which would _allow_ for smart scans to
occur. The first/easiest step I go to is checking for valid storage
indexes on the columns you're interested in.
If anyone is aware of a trace that we could do on the storage cells, that
would be good to know.
