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>
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.
Regards
Jonathan Lewis
________________________________________
From: Balwanth B <balwanthdba@xxxxxxxxx>
Sent: 07 July 2017 19:16
To: Jonathan Lewis; Powell, Mark
Cc: ORACLE-L
Subject: Re: Parallel not being used by Oracle
HI Jonathan,
After making the change I am able to see below plan
SQL_ID 6a1dchjszsarm, child number 0
-------------------------------------
SELECT /*+ parallel(4) full(parametervalue) */
DISTINCT(PV.PARAMETER_VALUE_NAME), D.DEVICETYPE_ID FROM DEVICE D,
PARAMETERVALUE PV, TMP_HDM_CLEANUP_INSTANCE TMP WHERE
D.CACHED_DATA_RECORD_ID = PV.DATA_RECORD_ID AND D.DEVICETYPE_ID =
TMP.DEVICETYPE_ID AND PV.PARAMETER_VALUE_NAME LIKE
TMP.PARAMETER_VALUE_NAME ESCAPE :B1
Plan hash value: 3730697107
------------------------------------------------------------
------------------------------------------------------------
------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ
Distrib |
------------------------------------------------------------
------------------------------------------------------------
------------------------------------
| 0 | SELECT STATEMENT | |
| | 3444 (100)| | | | | |
|
| 1 | PX COORDINATOR | |
| | | | | | | |
|
| 2 | PX SEND QC (RANDOM) | :TQ10001 |
1 | 100 | 3444 (1)| 00:00:42 | | | Q1,01 | P->S | QC
(RAND) |
| 3 | HASH UNIQUE | |
1 | 100 | 3444 (1)| 00:00:42 | | | Q1,01 | PCWP |
|
| 4 | PX RECEIVE | |
1 | 100 | 3443 (1)| 00:00:42 | | | Q1,01 | PCWP |
|
| 5 | PX SEND HASH | :TQ10000 |
1 | 100 | 3443 (1)| 00:00:42 | | | Q1,00 | P->P | HASH
|
| 6 | NESTED LOOPS | |
1 | 100 | 3443 (1)| 00:00:42 | | | Q1,00 | PCWP |
|
| 7 | NESTED LOOPS | |
1 | 57 | 3442 (1)| 00:00:42 | | | Q1,00 | PCWP |
|
| 8 | PX BLOCK ITERATOR | |
| | | | | | Q1,00 | PCWC |
|
|* 9 | TABLE ACCESS FULL | TMP_HDM_CLEANUP_INSTANCE |
4125 | 185K| 3 (0)| 00:00:01 | | | Q1,00 | PCWP |
|
| 10 | TABLE ACCESS BY INDEX ROWID| DEVICE |
1 | 11 | 1 (0)| 00:00:01 | | | Q1,00 | PCWP |
|
|* 11 | INDEX RANGE SCAN | SYS_C0016783 |
1822K| | 1 (0)| 00:00:01 | | | Q1,00 | PCWP |
|
| 12 | PARTITION HASH ITERATOR | |
1 | 43 | 1 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP |
|
|* 13 | INDEX RANGE SCAN | UQ_PARAM_NEW |
1 | 43 | 1 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP |
|
------------------------------------------------------------
------------------------------------------------------------
------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
9 - SEL$1 / TMP@SEL$1
10 - SEL$1 / D@SEL$1
11 - SEL$1 / D@SEL$1
13 - SEL$1 / PV@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access(:Z>=:Z AND :Z<=:Z)
11 - access("D"."DEVICETYPE_ID"="TMP"."DEVICETYPE_ID")
13 - access("D"."CACHED_DATA_RECORD_ID"="PV"."DATA_RECORD_ID" AND
"PV"."PARAMETER_VALUE_NAME" LIKE "TMP"."PARAMETER_VALUE_NAME" ESCAPE :B1)
filter("PV"."PARAMETER_VALUE_NAME" LIKE
"TMP"."PARAMETER_VALUE_NAME" ESCAPE :B1)
--
//www.freelists.org/webpage/oracle-l
