RE: Parallel not being used by Oracle
Date: Sat, 8 Jul 2017 16:30:38 -0400
Message-ID: <059301d2f829$10276310$30762930$_at_rsiz.com>
Some years ago that would have been considered a big table. Possibly it still is if you have modest or antique hardware.
Without implying that FTS is the best way for you to solve your query, no, doing an FTS shouldn’t be a problem unless you do it in a loop or multiple loops.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Balwanth B
Sent: Saturday, July 08, 2017 9:37 AM
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_at_jlcomp.demon.co.uk> 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_at_gmail.com> 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_at_SEL$1 10 - SEL$1 / D_at_SEL$1 11 - SEL$1 / D_at_SEL$1 13 - SEL$1 / PV_at_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)
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sat Jul 08 2017 - 22:30:38 CEST