Chinar,
You're right, of course. I misread the plan.
So the direct path writes are either the join data being written to the
final table - and maybe there's a lot more that the optimizer predicted and
the "problem" is simply the time it takes to do all the I/O, or maybe the
optimizer got its estimate of the build table so wrong that it spilled to
disc.
For the OP - if you're licensed for the diagnostic and tuning packs you can
see exactly where you are with the query and where the work is happening by
calling the SQL Monitor. (See, for example,
https://jonathanlewis.wordpress.com/2018/04/06/sql-monitor/ for an example
of getting a text output. Since you're query is running parallel the
monitoring should take place automatically.
Regards
Jonathan Lewis
On Thu, May 7, 2020 at 7:28 AM Chinar Aliyev <chinaraliyev@xxxxxxxxx> wrote:
Hi,
@Jonathan, I think each slave got 1MB(1131K) data not 10GB. Because
"build table" size is 1131K (estimated) and 10GB is the result of the
join.
@Ram, if most time of the SQL execution is consumed by "DIRECT READ/WRITE"
from/to temp it means there could be wrong cardinality estimation(miss)
for efgh table. Becuse hash table (based on efgh) could have been stored
in PGA completely. Could you display(or gather stats) real number of blocks
and rows of that table?.
Also, it looks the value of "optimizer_dynamic_sampling" params is 11
(auto) - from note section of the plan. In general, if parallel execution
is used the database defines the level of sampling exactly (like 4,6), the
auto sampling mechanism is different. What is the actual value of
"optimizer_dynamic_sampling" in your system?
Best Regards
On Thu, May 7, 2020 at 2:03 AM Ram Raman <veeeraman@xxxxxxxxx> wrote:
Thank you all.
* Thanks Lothar for the explanation. Now i know what P->S signifies; next
time I see a query with P->S I know there is no parallel operations
happening
'above' it in the plan
* Thanks a lot Jonathan. I saw only 4 slaves in operation in pxsession. I
now have to read up on PQ_REPLICATE. And no, our system definitely cannot
read
1.25Gb/sec. We are choking on IO. Many of the direct path reads are to
temp also.
* I got this query from dbms_xplan on a running query. Surprised it did
not give the actual execution plan; had the statistics_level all set in the
session.
We are licensed for SQL_MONITOR. will try that in the next run.
* Thanks for the tip Mark F: It is good to know that I have another
option of splitting a table, efgh in this case, into four or more depending
on the parallelism we
want to use. Will keep that in mind as well for the next tuning
opportunity, esp if we get a table with large number rows and is either not
partitioned or not
partitioned by the join column - is that reasoning correct? Sounds like a
great tip.
I should have added the information about the tables as well, but here
they are:
TABLE_NAME NUM_ROWS LAST_ANAL BLOCKS
------------------------ -------------------- --------- ----------
efgh 193,031 30-APR-20 464
abcd 0 06-MAY-20 0
SCE 1,547,210,780 14-MAY-19 37444971
All the 193,000 rows in efgh (has only 2 number columns) are unique with
respect to esk, but does not have an index on it. Would an index help given
that it has to operate on all the data in efgh? I doubt it since it has to
grab all the rows.
* Bloom filters: More reading I guess. Is it explained in the oracle
manual?
Ram.
On Wed, May 6, 2020 at 12:30 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
Until 19c a query that uses a Bloom filter will "lose" the Bloom filter
when it's changed to "insert as select".
However CTAS does work, and there is a patch, that allows insert /*+
append */ as select to use a Bloom filter.
See blog and comments:
https://jonathanlewis.wordpress.com/2016/07/08/dml-and-bloom/
Regards
Jonathan Lewis
On Wed, May 6, 2020 at 4:04 PM Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
Hi,
I wonder if a Bloom Filter could be used on sce. Would that be faster
than probing? I would think so ...
There seems to be many rows not surviving the join.
Regards
Lothar
--
--
*Chinar Aliyev*
Visit My :Blog <http://chinaraliyev.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <https://www.linkedin.com/in/chinaraliyev/>*
My Twitter <https://twitter.com/MohamedHouri> - ChinarAliyev
<https://twitter.com/ChinarAliyev>
