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

SQL> SELECT LAST_ANALYZED FROM USER_INDEXES where index_name='SYS_C0016783';

LAST_ANALYZED

*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-l