I found the parallel hint is not working if i dont give alias name for the table which is remote object. the same is working fine if i give alias name for the table and mention the same in parallel hint.
if the run the query with the parallel hint in local database , it is working fine regardless of alias name.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
remote database
===========
without alias name
1 select /*+parallel(4)*/ count(*)
2* from pax_mco_fln_hist_dtls@CVUQFOP_CVU_QRY_DBLINK where flnmth='01-feb-2014'
SQL> /
COUNT(*)
----------
3852260
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=61999 Card=
1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 PARTITION RANGE (SINGLE) (Cost=61999 Card=3595181 Bytes=
28761448)
3 2 TABLE ACCESS (FULL) OF 'PAX_MCO_FLN_HIST_DTLS' (TABLE) NCVU.WOR
(Cost=61999 Card=3595181 Bytes=28761448) LD
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
353 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
with alias name
1 select /*+parallel(t,4)*/ count(*)
2* from pax_mco_fln_hist_dtls@CVUQFOP_CVU_QRY_DBLINK t where flnmth='01-feb-
SQL>
SQL> /
COUNT(*)
----------
3852260
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE (Cost=17182 Card=
1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 PX COORDINATOR
3 2 PX SEND* (QC (RANDOM)) OF ':TQ10000' :Q1000
4 3 SORT* (AGGREGATE) :Q1000
5 4 PX BLOCK* (ITERATOR) (Cost=17182 Card=3595181 Byte :Q1000
s=28761448)
6 5 TABLE ACCESS* (FULL) OF 'PAX_MCO_FLN_HIST_DTLS' :Q1000
(TABLE) (Cost=17182 Card=3595181 Bytes=28761448)
3 PARALLEL_TO_SERIAL
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_CHILD
6 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
353 bytes sent via SQL*Net to client
431 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
local database
===========
1 select /*+parallel(4)*/ count(*)
2* from pax_mco_fln_hist_dtls aa where flnmth='01-feb-2014'
SQL> /
COUNT(*)
----------
3852260
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17182 Card=1 Bytes=8
)
1 0 SORT (AGGREGATE)
2 1 PX COORDINATOR
3 2 PX SEND* (QC (RANDOM)) OF ':TQ10000' :Q1000
4 3 SORT* (AGGREGATE) :Q1000
5 4 PX BLOCK* (ITERATOR) (Cost=17182 Card=3595181 Byte :Q1000
s=28761448)
6 5 TABLE ACCESS* (FULL) OF 'PAX_MCO_FLN_HIST_DTLS' :Q1000
(TABLE) (Cost=17182 Card=3595181 Bytes=28761448)
3 PARALLEL_TO_SERIAL
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_CHILD
6 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
21 recursive calls
5824 db block gets
1112608 consistent gets
388765 physical reads
0 redo size
353 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1 select /*+parallel(4)*/ count(*)
2* from pax_mco_fln_hist_dtls where flnmth='01-feb-2014'
SQL> /
COUNT(*)
----------
3852260
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=17182 Card=1 Bytes=8
)
1 0 SORT (AGGREGATE)
2 1 PX COORDINATOR
3 2 PX SEND* (QC (RANDOM)) OF ':TQ10000' :Q1000
4 3 SORT* (AGGREGATE) :Q1000
5 4 PX BLOCK* (ITERATOR) (Cost=17182 Card=3595181 Byte :Q1000
s=28761448)
6 5 TABLE ACCESS* (FULL) OF 'PAX_MCO_FLN_HIST_DTLS' :Q1000
(TABLE) (Cost=17182 Card=3595181 Bytes=28761448)
3 PARALLEL_TO_SERIAL
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_CHILD
6 PARALLEL_COMBINED_WITH_PARENT
Statistics
----------------------------------------------------------
13 recursive calls
5824 db block gets
1112608 consistent gets
388765 physical reads
0 redo size
353 bytes sent via SQL*Net to client
435 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed