"10053" for Exadata smart table scan?
From: Dave Herring <gdherri_at_gmail.com>
Date: Mon, 18 Sep 2017 16:26:44 -0500
Message-ID: <CAFN=diC4ma4YpWaWCfKdPqRaT+-XvvhmBmk7rowV-gxqw9sH4g_at_mail.gmail.com>
We have an X-6 environment where we're not getting smart table scans on a particular cursor and nothing is standing out as to why, so I'm wondering if anyone knows of a way to trace/debug why the choice is being made.
Date: Mon, 18 Sep 2017 16:26:44 -0500
Message-ID: <CAFN=diC4ma4YpWaWCfKdPqRaT+-XvvhmBmk7rowV-gxqw9sH4g_at_mail.gmail.com>
We have an X-6 environment where we're not getting smart table scans on a particular cursor and nothing is standing out as to why, so I'm wondering if anyone knows of a way to trace/debug why the choice is being made.
A few details: X-6, Oracle 12.1.0.2, Linux 6.8. Database was restored from a backup of an X-4, Oracle 11.2.0.4 database on Linux 5.11.
Between X-6 and X-4 for this cursor the plan_hash_value is the same and the xplans details (including predicts section) are exactly the same, including references to "storage" in both sections.
Session tracing shows time taken up on "cell multiblock physical read" on the X-6 whereas on X-4 I see "cell smart table scan" and as you can imagine, the X-4 cursor runs much faster than on X-6.
Is there any way to trace where and why decisions are made when initially Oracle seems to think it'll use smart table scans and then give up?
Dave
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 18 2017 - 23:26:44 CEST