Thanks for all of your inputs, will follow the advice's.
On Sat, Jul 8, 2017 at 6:02 PM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
Balwanth,
1) Why are you asking me if it's appropriate - your initial question was
about why your weren't seeing parallel execution when you'd hinted it for
that table. Since the hint you chose directs the optimizer to calculate the
cost of a parallel tablescan I assumed you had decided that a parallel
tablescan would be appropriate. So what made you decide that you should use
that hint in the first place. I will offer you one thought - you have to
know more about your data. Your table is partitioned so maybe there's
something about the query that means the "tablescan" might limit itself to
scanning only one (or a small number) of partitions.
2) The optimizer uses a lot more information about the table than you've
supplied to decide a path - what about the column stats with the number of
distict values, low and highs, whether or not there are any histograms. As
for the index stats - telling me that the index was last analyzed at some
point in time tells me nothing about the index, and doesn't even guarantee
that the stats are any good - was it a 100% sample or was it a sample of
just 10 rows (rhetorical question) both would show the same last_analyzed
date, one might have much better stats than the other. What about the stats
on the columns defining that index ? Is it a multi-column index, or single
column, is there anything unusual about the columns (is one descending, for
example).
3) If you want to understand the type of thinking that allows you to pick
a good execution plan (when you think the optimizer is picking a bad one),
here's an old article that you could use as a starting point:
https://www.simple-talk.com/sql/performance/designing-
efficient-sql-a-visual-approach
Once you've decided on a good plan you can worry about hinting it if it's
really necessary; but the work you do getting to the plan might allow you
to see where the statistics (in your case those related to the DEVICE
table, possibly) are causing a problem.
Regards
Jonathan Lewis
________________________________________
From: Balwanth B <balwanthdba@xxxxxxxxx>
Sent: 08 July 2017 14:37
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: Parallel not being used by Oracle
Hi Jonathan,
Your use of the full() hint is incorrect - you should use the alias, not
the table name (which, in fact, is what you did in the parallel hint in
your original post).
I will make sure to change the hint properly and see how it runs. Can you
please let me know if doing that in production is appropriate since the
table size is 120 GB with 620 million records, FTS on that big table?
I also pointed out that you seemed to have a statistics problem relating
to the DEVICE table or the sys-named index on it. What did you do do
address that comment ?
STATS looks good.
SQL> SELECT NUM_ROWS, AVG_ROW_LEN, BLOCKS, LAST_ANALYZED FROM
USER_TABLES where table_name='DEVICE';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZED
---------- ----------- ---------- -------------------
32240810 336 1599099 2017 07 08
00:13:14
SQL> SELECT LAST_ANALYZED FROM USER_INDEXES where
index_name='SYS_C0016783';
LAST_ANALYZED
-------------------
2017 07 08 00:13:46
From Oracle's perspective the join between DEVICE and
TMP_HDM_CLEANUP_INSTANCE is going to produce one row - so your first
problem is to find out why that's the case and work out how to make sure
that Oracle has a better idea of what the data looks like.
I can only think of using use_hash hint, please let me know if there are
any other ways for striking Oracle's mind.
Thanks,
Balwanth
On Sat, Jul 8, 2017 at 7:23 AM, Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>> wrote:
Your use of the full() hint is incorrect - you should use the alias, not
the table name (which, in fact, is what you did in the parallel hint in
your original post).
I also pointed out that you seemed to have a statistics problem relating
to the DEVICE table or the sys-named index on it. What did you do do
address that comment ?
From Oracle's perspective the join between DEVICE and
TMP_HDM_CLEANUP_INSTANCE is going to produce one row - so your first
problem is to find out why that's the case and work out how to make sure
that Oracle has a better idea of what the data looks like. First step -
check the statistics on the objects and see if they make sense.
