Home » RDBMS Server » Performance Tuning » ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner
ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #636190] Sun, 19 April 2015 09:53 Go to next message
paolfili
Messages: 6
Registered: March 2015
Location: Italy
Junior Member
Hi all.
After the second reading of the Kevin's book I think again this is by far the best book on Oracle SQL Tuning.
The ability to explain so CLEARLY and DEEPLY complex subjects is not so common.
The suggestion of a METHOD of analysis and solution is a huge value.
And the mix of experience and wisdom you can observe is really important in this book.
I think we (as orafaq forum visitor) have here, one of the best(probably THE best) teacher on the Oracle SQL Tuning arena.
So, I'd like to hear some OTHER more in depth explanation from such a master (but any other post is welcome) on some areas of knowledge explored in this
wonderful book.

-------------------------
As first post some METHODOLOGICAL questions.

1)FRP on ROWS(and BLOCKS)
Kevin.
Please correct me If I' m wrong.
The FRP method use count(*) as an APPROXIMATION of the workload (in term of BLOCKS) for each rowsets.
So, in case of very different avg row length for the tables in play is possible that using the FRP on rows , is not a good approximation of using the (more suitable)
FRP on BLOCKS.
Am I correct interpreting the matter?

2)BIND VARIABLES and avg(count(*))
This is a FOCAL POINT of the whole subject and I don't think the correct solution will be using a avg(count(*))
Becouse when DIFFERENT bind values tuples cause very DIFFERENT rowsets cardinality and think invokes (in a natural way) different execution plans I think that averaging
the result set for the FRP query will be not a good idea.
But IMHO this is a really complex topic (and I' ll come back to this in my following posts)


Kindly Waiting for Kevin (or any other interested to this subject) reply

Paolo
Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #636191 is a reply to message #636190] Sun, 19 April 2015 11:16 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
KM's FRP methodology is, I would say, a technique for calculating the appropriate join order. This is only the first part of developing an efficient plan. The number of blocks to access to retrieve those rows would influence a later decision: the access method.

I do not see bind variables as relevant in any way. They are going to be peeked during the hard parse, so no different from using literals.
Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #636202 is a reply to message #636191] Sun, 19 April 2015 13:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Thank you for your kind words. I would like to make several points.

First, I would never dispute your wisdom as to my being a great teacher on these subjects. I thank you for that.

I would also point out that there are many people on ORAFaq with exceptional backgrounds and deep knowledge. For example, John who has just provided some answers is way ahead of me in many areas. Check out his pedigree at SKILL BUILDERS and you will see for example that he is a Certified Oracle Master multiple times over. I have none of these designations. You do not reach John's level of skill by accident. It was a great day for us when he joined our Clan.

As to the question of ROWS as a measure of workload. This is indeed a method of determining DRIVING TABLE and JOIN ORDER. There are a couple of reasons why it is so good. One is that it works directly against the primary goal of performance which is to manipulate the least amount of data possible by reducing workload as early as possible. Two, it does #1 because workload reduction transfers down a chain of table joins in a query, by ensuring that at least for PRECISION QUERIES, whatever percentage of workload was removed before, is also removed from the next table to be joined to and this is in general multiplicative. For example, if we remove 99% of the rows from the first table this means we will likely be looking for only 1% of the rows in the next table we join to. If the second table is also only supplying 1% of its rows to the query then it is likely what we now have only 1% X 1% or 1 out of 10,000 rows coming out of the join step. So in this way, number of rows is a great way to evaluate workload and thus join order. Also, though blocks etc. might technically be better, the math involved is vastly more complicated and as such, % of rows is far easier for people to get their heads around and thus much easier to teach and use. In practical terms, it also is 99% just as accurate as the more complicated blocks/bytes calculations anyway so why bother with the complicated stuff.

As to bind variables, this is a complex topic. It is true that if you know the actual values, you can generate a plan that is different than if you don't know the values. But the purpose of the book was to teach technique and convey knowledge. Dealing with bind variables only creates confusion in that regard so I skipped a discussion of it to avoid that and instead concentrate on the elemental processes I wanted people to learn. I demonstrated how to generate a plan using specific values so if you bind peek then that works. I also showed that if you did not have these values, you needed to use averages. Mostly we hope that Oracle is able to peek so your task becomes figuring out what values it got when it did so and then use the method if necessary. John said this first.

Your questions are good ones, thanks. I hope these answer them sufficiently. Please ask more if needed.

For those reading this post but not familiar with the book, here is the first chapter, the book's scripts, and a tuning reference document for you to use. You can find the book on Amazon if after reading the first chapter you feel it is worth continuing on with the whole book.

Kevin

[Updated on: Sun, 19 April 2015 13:45]

Report message to a moderator

Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #636573 is a reply to message #636202] Tue, 28 April 2015 08:04 Go to previous messageGo to next message
paolfili
Messages: 6
Registered: March 2015
Location: Italy
Junior Member
Episode 1: part#1 - A not so simple begin


$>uname -a
AIX mpedbr21 1 6 00F64A914C00
--
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 IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
--


---The Query
--I' ve choosed ONE Bind Variable incarnation to have a NO-Bind Variable incarnation of the query

[size=2]SELECT O.NUM_ISCR_COVIP,
         PM.SEQ_PROD_COMM,
         PM.DENOM_PROD_COMM,
         DECODE (F.CODICE_COMPAGNIA_PVG,
                 'A', DECODE (F.TIPO_FND, 'UL', SUBSTR (F.TIPO_FND, 1, 1) || SUBSTR (F.DENOM_FND, 9, 1), F.TIPO_FND),
                 F.TIPO_FND)
            TIPO_FND,
         SUM (
              DECODE (COD_TIPO_FLUSSO,  '1', -1,  '0', 1)
            * (  NVL (D.IMP_ADDIZIONALI, 0)
               + NVL (D.IMP_DIRITTI_NETTO, 0)
               + NVL (D.IMP_IMPORTO_NETTO, 0)
               + NVL (D.IMP_IMPOSTA, 0)
               + NVL (D.IMP_IMPOSTA_DIRITTI, 0)
               + NVL (D.IMP_INTERES_RITDO_PAG, 0)))
            AS IMP_TOT
            --
			FROM VPO.MOV_CONTABILE_S MC,
         VPO.DTL_MOV_CONTABILE D,
         VPO.PRODOTTO_CTR_S PC,
         VPR.PRODOTTO_COMMERCIALE_V PM,
         VPR.OGGETTO_DOC_STAMPA_V O,
         VPO.TRANCHE_S TR,
         VPR.FONDO_V F
         --
         WHERE 1=1
         --Join Predicates
         AND MC.SEQ_MOV_CONTABILE = D.SEQ_MOV_CONTABILE
         AND MC.SEQ_RAPPORTO = PC.SEQ_RAPPORTO
         AND F.CODICE_COMPAGNIA_PVG = PM.CODICE_COMPAGNIA_PVG
         AND PC.SEQ_PROD_COMM = PM.SEQ_PROD_COMM
         AND PC.SEQ_PROD_COMM = O.SEQ_OGG
         AND TR.SEQ_FONDO = F.SEQ_FONDO
         --Constant Test Predicates
         AND MC.TIPO_MOV_CONTABILE = '001'
         AND MC.DATA_EFF_COPERTURA >= :B4
         AND MC.COD_STATO_MOVIMENTO IN ('I', 'E')
         AND MC.ID_DATA_INIZIO_VAL <= :B2
         AND :B2 < MC.ID_DATA_FINE_VAL
         AND MC.ID_TIMESTAMP_INIZIO_VAL <= :B1
         AND :B1 < MC.ID_TIMESTAMP_FINE_VAL
         AND D.SEQ_TRANCHE IS NOT NULL
         AND D.TIPO_DTL_MOV_CONT = '001'
         AND D.TIPO_DTL_MOV_CONT_SEC = '001'
         AND PC.ID_DATA_INIZIO_VAL <= :B2
         AND :B2 < PC.ID_DATA_FINE_VAL
         AND PC.ID_TIMESTAMP_INIZIO_VAL <= :B1
         AND :B1 < PC.ID_TIMESTAMP_FINE_VAL
         AND PC.COD_STATO_PROD_CTR = '1'
         AND PM.TIPO_PROD_LEGALE IN ('02', '04')
         AND PM.CODICE_COMPAGNIA_PVG = :B3
         AND O.TIPO_OGG = '02'
         AND O.NUM_ISCR_COVIP IS NOT NULL
         AND TR.ID_DATA_INIZIO_VAL <= :B2
         AND :B2 < TR.ID_DATA_FINE_VAL
         AND TR.ID_TIMESTAMP_INIZIO_VAL <= :B1
         AND :B1 < TR.ID_TIMESTAMP_FINE_VAL
         --Aggregations
         GROUP BY O.NUM_ISCR_COVIP,
         PM.SEQ_PROD_COMM,
         PM.DENOM_PROD_COMM,
         F.TIPO_FND,
         F.DENOM_FND,
         F.CODICE_COMPAGNIA_PVG
	
[/size]

----------------------
Explained Plan

SQL> @../showplan11gshort77.sql

Plan hash value: 4174829704

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         | 18314 |  5490K|       |   118K  (1)| 00:23:44 |       |       |
|   1 |  HASH GROUP BY                           |                         | 18314 |  5490K|  5872K|   118K  (1)| 00:23:44 |       |       |
|*  2 |   HASH JOIN                              |                         | 18314 |  5490K|       |   117K  (1)| 00:23:34 |       |       |
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |    90 |  3600 |       |    16   (0)| 00:00:01 |       |       |
|   4 |    NESTED LOOPS                          |                         |       |       |       |            |          |       |       |
|   5 |     NESTED LOOPS                         |                         |  4546 |  1185K|       |   117K  (1)| 00:23:34 |       |       |
|   6 |      NESTED LOOPS                        |                         |  4919 |  1052K|       | 83304   (1)| 00:16:40 |       |       |
|   7 |       NESTED LOOPS                       |                         |  4492 |   785K|       | 65666   (1)| 00:13:08 |       |       |
|*  8 |        HASH JOIN                         |                         |  1985 |   222K|       | 18048   (1)| 00:03:37 |       |       |
|*  9 |         TABLE ACCESS FULL                | PRODOTTO_COMMERCIALE_S  |    41 |  1558 |       |    26   (0)| 00:00:01 |       |       |
|  10 |         NESTED LOOPS                     |                         |       |       |       |            |          |       |       |
|  11 |          NESTED LOOPS                    |                         | 15682 |  1179K|       | 18021   (1)| 00:03:37 |       |       |
|* 12 |           TABLE ACCESS FULL              | OGGETTO_DOC_STAMPA_S    |     3 |    75 |       |     3   (0)| 00:00:01 |       |       |
|* 13 |           INDEX RANGE SCAN               | VPO080_VPRC80_FK_IS     | 17301 |       |       |    80   (0)| 00:00:01 |       |       |
|* 14 |          TABLE ACCESS BY INDEX ROWID     | PRODOTTO_CTR_S          |  5121 |   260K|       |  6006   (1)| 00:01:13 |       |       |
|  15 |        PARTITION RANGE ITERATOR          |                         |     2 |   128 |       |    24   (0)| 00:00:01 |   KEY |   KEY |
|* 16 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |     2 |   128 |       |    24   (0)| 00:00:01 |   KEY |   KEY |
|* 17 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |    32 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|  18 |       PARTITION RANGE ITERATOR           |                         |     1 |    40 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
|* 19 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |     1 |    40 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
|* 20 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |     4 |       |       |     2   (0)| 00:00:01 |   KEY |   KEY |
|* 21 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |     7 |       |       |     3   (0)| 00:00:01 |       |       |
|* 22 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |     1 |    48 |       |     7   (0)| 00:00:01 | ROWID | ROWID |
--------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$AE39D3DB
   3 - SEL$AE39D3DB / FONDO_S@SEL$4
   9 - SEL$AE39D3DB / VPRC80S@SEL$2
  12 - SEL$AE39D3DB / VPRE65S@SEL$3
  13 - SEL$AE39D3DB / PC@SEL$1
  14 - SEL$AE39D3DB / PC@SEL$1
  16 - SEL$AE39D3DB / MC@SEL$1
  17 - SEL$AE39D3DB / MC@SEL$1
  19 - SEL$AE39D3DB / D@SEL$1
  20 - SEL$AE39D3DB / D@SEL$1
  21 - SEL$AE39D3DB / TR@SEL$1
  22 - SEL$AE39D3DB / TR@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$AE39D3DB")
      SWAP_JOIN_INPUTS(@"SEL$AE39D3DB" "FONDO_S"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2")
      USE_HASH(@"SEL$AE39D3DB" "FONDO_S"@"SEL$4")
      NLJ_BATCHING(@"SEL$AE39D3DB" "TR"@"SEL$1")
      USE_NL(@"SEL$AE39D3DB" "TR"@"SEL$1")
      USE_NL(@"SEL$AE39D3DB" "D"@"SEL$1")
      USE_NL(@"SEL$AE39D3DB" "MC"@"SEL$1")
      USE_HASH(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2")
      NLJ_BATCHING(@"SEL$AE39D3DB" "PC"@"SEL$1")
      USE_NL(@"SEL$AE39D3DB" "PC"@"SEL$1")
      LEADING(@"SEL$AE39D3DB" "VPRE65S"@"SEL$3" "PC"@"SEL$1" "VPRC80S"@"SEL$2" "MC"@"SEL$1" "D"@"SEL$1" "TR"@"SEL$1" "FONDO_S"@"SEL$4")
      FULL(@"SEL$AE39D3DB" "FONDO_S"@"SEL$4")
      INDEX(@"SEL$AE39D3DB" "TR"@"SEL$1" ("TRANCHE_S"."SEQ_TRANCHE" "TRANCHE_S"."ID_SEQ_INS"))
      INDEX_RS_ASC(@"SEL$AE39D3DB" "D"@"SEL$1" ("DTL_MOV_CONTABILE"."SEQ_MOV_CONTABILE"))
      INDEX_RS_ASC(@"SEL$AE39D3DB" "MC"@"SEL$1" ("MOV_CONTABILE_S"."SEQ_RAPPORTO" "MOV_CONTABILE_S"."ID_TIMESTAMP_FINE_VAL"
              "MOV_CONTABILE_S"."ID_DATA_FINE_VAL" "MOV_CONTABILE_S"."ID_DATA_INIZIO_VAL" "MOV_CONTABILE_S"."ID_TIMESTAMP_INIZIO_VAL"))
      FULL(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2")
      INDEX(@"SEL$AE39D3DB" "PC"@"SEL$1" ("PRODOTTO_CTR_S"."SEQ_PROD_COMM"))
      FULL(@"SEL$AE39D3DB" "VPRE65S"@"SEL$3")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      MERGE(@"SEL$4")
      MERGE(@"SEL$3")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$AE39D3DB")
      ALL_ROWS
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TR"."SEQ_FONDO"="SEQ_FONDO" AND "CODICE_COMPAGNIA_PVG"="CODICE_COMPAGNIA_PVG")
   3 - filter("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "CODICE_COMPAGNIA_PVG"='I')
   8 - access("PC"."SEQ_PROD_COMM"="SEQ_PROD_COMM")
   9 - filter("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND ("TIPO_PROD_LEGALE"='02' OR "TIPO_PROD_LEGALE"='04') AND
              "CODICE_COMPAGNIA_PVG"='I')
  12 - filter("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "TIPO_OGG"='02' AND "NUM_ISCR_COVIP" IS NOT NULL)
  13 - access("PC"."SEQ_PROD_COMM"="SEQ_OGG")
  14 - filter("PC"."ID_TIMESTAMP_FINE_VAL">2014123199999999 AND "PC"."ID_DATA_FINE_VAL">TO_DATE(' 2014-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "PC"."ID_TIMESTAMP_INIZIO_VAL"<=2014123199999999 AND "PC"."COD_STATO_PROD_CTR"='1' AND
              "PC"."ID_DATA_INIZIO_VAL"<=TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  16 - filter("MC"."DATA_EFF_COPERTURA">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "MC"."TIPO_MOV_CONTABILE"='001' AND ("MC"."COD_STATO_MOVIMENTO"='E' OR "MC"."COD_STATO_MOVIMENTO"='I'))
  17 - access("MC"."SEQ_RAPPORTO"="PC"."SEQ_RAPPORTO" AND "MC"."ID_TIMESTAMP_FINE_VAL">2014123199999999 AND
              "MC"."ID_DATA_FINE_VAL">TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "MC"."ID_DATA_INIZIO_VAL"<=TO_DATE('
              2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "MC"."ID_TIMESTAMP_INIZIO_VAL"<=2014123199999999)
       filter("MC"."ID_DATA_FINE_VAL">TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "MC"."ID_DATA_INIZIO_VAL"<=TO_DATE(' 2014-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "MC"."ID_TIMESTAMP_INIZIO_VAL"<=2014123199999999)
  19 - filter("D"."SEQ_TRANCHE" IS NOT NULL AND "D"."TIPO_DTL_MOV_CONT_SEC"='001' AND "D"."TIPO_DTL_MOV_CONT"='001')
  20 - access("MC"."SEQ_MOV_CONTABILE"="D"."SEQ_MOV_CONTABILE")
  21 - access("D"."SEQ_TRANCHE"="TR"."SEQ_TRANCHE")
  22 - filter("TR"."ID_TIMESTAMP_FINE_VAL">2014123199999999 AND "TR"."ID_DATA_FINE_VAL">TO_DATE(' 2014-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "TR"."ID_TIMESTAMP_INIZIO_VAL"<=2014123199999999 AND "TR"."ID_DATA_INIZIO_VAL"<=TO_DATE(' 2014-12-31
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - (#keys=6) "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "DENOM_PROD_COMM"[VARCHAR2,30],
       "TIPO_FND"[CHARACTER,2], "DENOM_FND"[VARCHAR2,30], "CODICE_COMPAGNIA_PVG"[CHARACTER,1],
       SUM(DECODE("COD_TIPO_FLUSSO",'1',(-1),'0',1)*(NVL("D"."IMP_ADDIZIONALI",0)+NVL("D"."IMP_DIRITTI_NETTO",0)+NVL("D"."IMP_IMPORTO_NETTO
       ",0)+NVL("D"."IMP_IMPOSTA",0)+NVL("D"."IMP_IMPOSTA_DIRITTI",0)+NVL("D"."IMP_INTERES_RITDO_PAG",0)))[22]
   2 - (#keys=2) "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2],
       "SEQ_PROD_COMM"[NUMBER,22], "DENOM_PROD_COMM"[VARCHAR2,30], "NUM_ISCR_COVIP"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22],
       "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22],
       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
   3 - "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "SEQ_FONDO"[NUMBER,22]
   4 - (#keys=0) "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30],
       "NUM_ISCR_COVIP"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22],
       "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22],

       "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1], "TR"."SEQ_FONDO"[NUMBER,22]
   5 - (#keys=0) "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30],
       "NUM_ISCR_COVIP"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22],
       "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22],
       "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1], "TR".ROWID[ROWID,10]
   6 - (#keys=0) "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30],
       "NUM_ISCR_COVIP"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22],
       "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22],
       "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
   7 - (#keys=0) "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30],
       "NUM_ISCR_COVIP"[VARCHAR2,30], "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
   8 - (#keys=1) "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30],
       "NUM_ISCR_COVIP"[VARCHAR2,30], "PC"."SEQ_RAPPORTO"[NUMBER,22]

   9 - "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22]
  10 - (#keys=0) "NUM_ISCR_COVIP"[VARCHAR2,30], "PC"."SEQ_PROD_COMM"[NUMBER,22], "PC"."SEQ_RAPPORTO"[NUMBER,22]
  11 - (#keys=0) "NUM_ISCR_COVIP"[VARCHAR2,30], "PC".ROWID[ROWID,10], "PC"."SEQ_PROD_COMM"[NUMBER,22]
  12 - "SEQ_OGG"[NUMBER,22], "NUM_ISCR_COVIP"[VARCHAR2,30]
  13 - "PC".ROWID[ROWID,10], "PC"."SEQ_PROD_COMM"[NUMBER,22]
  14 - "PC"."SEQ_RAPPORTO"[NUMBER,22]
  15 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
  16 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
  17 - "MC".ROWID[ROWID,10]
  18 - "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22],
       "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22],
       "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
  19 - "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22],

       "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22],
       "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
  20 - "D".ROWID[ROWID,10]
  21 - "TR".ROWID[ROWID,10]
  22 - "TR"."SEQ_FONDO"[NUMBER,22]

161 rows selected.

Elapsed: 00:00:00.22

--------------------------------------------------------------------------------------
--Historical Performance Context (thanks to sqlhc by Carlos Sierra)

#	Snaphot	          InstId PlanHV  Vers Execs	Fetch    ParseCalls	    BufferGets	DiskReads	RowsProc	ElapsedTime(secs)	CPUTime(secs)	IOTime(secs)	OptimizerMode	OptEnvHV
	
	
1	2015-03-24/09:00:16	1	4174829704	2	2	11		   1				1349497	377065		9	  		721.181					20.71		703.002				ALL_ROWS	2037524259
2	2015-03-25/09:00:33	1	4174829704	2	2	11  	   1				1360231	442873		9			758.438					23.61		737.991				ALL_ROWS	2037524259
3	2015-03-26/09:00:51	1	4174829704	1	2	11		   1				1354501	396364		9			851.146					29.21		825.108				ALL_ROWS	2037524259
4	2015-03-27/09:00:05	1	4174829704	1	2	11		   1				1350349	378434		9			693.076					21.13		674.378				ALL_ROWS	2037524259
5	2015-03-28/09:00:08	1	4174829704	1	2	11		   1				1350830	377121		9			640.106					24.96		619.481				ALL_ROWS	2037524259
6	2015-03-29/09:00:16	1	4174829704	1	2	11		   1				1365198	487493		9			911.604					32.53		874.985				ALL_ROWS	2037524259
7	2015-03-30/09:00:31	1	4174829704	1	2	11		   1				1349327	377089		9			665.389					22.5		646.198				ALL_ROWS	2037524259
8	2015-03-31/09:00:47	1	4174829704	1	2	11		   1				1351904	393276		9			680.065					21.98		660.86				ALL_ROWS	2037524259
9	2015-04-05/09:00:22	1	4174829704	1	2	2		   1				261956	100465		0			78.674					7.64		72.246				ALL_ROWS	2037524259
10	2015-04-06/09:00:40	1	4174829704	2	2	2		   1				262769	100366		0			52.45					5.34		47.992				ALL_ROWS	2037524259
11	2015-04-12/09:00:14	1	4174829704	1	2	2		   1				263241	100423		0			78.345					7.64		71.843				ALL_ROWS	2037524259

---------------------------------------------------------------------------------------
--Execution Time is more or less around 400secs for executions with 9 rows extracted, and 60secs for O rows extracted (probably in funcution of bind variable tuples).

--------------------------------------
--REAL Execution Plan - TAKE#1

SQL> @Query_5yag3uqq0fyz8.sql

5004                              1000004118 419EY  ZIONE              GS 1475093.02
5004                              1000004818 419EY  12                 GS 41150529.8
5004                              1000004118 419EY  ZIONE              GS  329982.23
5004                              1000002898 419EY  RD PIP STRATEGICO  UL  207101.77
5004                              1000004118 419EY  ZIONE              UL       1050
5004                              1000002898 419EY  RD PIP STRATEGICO  UL  190884.29
5004                              1000004818 419EY  12                 UL  100703.68
5004                              1000004118 419EY  ZIONE              UL      10345
5004                              1000002798 419EY  RD                 GS  420750.45
5004                              1000004818 419EY  12                 GS 7904207.21
5004                              1000002898 419EY  RD PIP STRATEGICO  GS 52686508.1
5004                              1000004818 419EY  12                 UL   82764.63
5004                              1000002898 419EY  RD PIP STRATEGICO  GS 4655567.43

13 rows selected.


Elapsed: 00:29:11.76

----
To depurate the effect of the OS_CACHE(main)/Buffer_Cache I re-run the query

SQL> @Query_5yag3uqq0fyz8.sql

5004                              1000004118 419EY CONVENZIONE              GS 1475093.02
5004                              1000004818 419EY PIP 2012                 GS 41150529.8
5004                              1000004118 419EY CONVENZIONE              GS  329982.23
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  207101.77
5004                              1000004118 419EY CONVENZIONE              UL       1050
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  190884.29
5004                              1000004818 419EY PIP 2012                 UL  100703.68
5004                              1000004118 419EY CONVENZIONE              UL      10345
5004                              1000002798 419EY STANDARD                 GS  420750.45
5004                              1000004818 419EY PIP 2012                 GS 7904207.21
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 52686508.1
5004                              1000004818 419EY PIP 2012                 UL   82764.63
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 4655567.43

13 rows selected.

Elapsed: 00:04:57.18
SQL> @../xplan

SQL_ID  9p3nydp6uwy3q, child number 1
-------------------------------------
SELECT O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) * (NVL(D.IMP_ADDIZIONALI,0) +
NVL(D.IMP_DIRITTI_NETTO ,0) + NVL(D.IMP_IMPORTO_NETTO,0) +
NVL(D.IMP_IMPOSTA,0) + NVL( D.IMP_IMPOSTA_DIRITTI,0) +
NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT FROM VPO.MOV_CONTABILE_S
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O,
VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND
MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND

MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <MC.ID_DATA_FINE_VAL AND
MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < MC.ID_TIMESTAMP_FINE_VAL
AND MC.SEQ_MOV_CONTABILE=D.SEQ_MOV_CONTABILE AND D.SEQ_TRANCHE IS NOT
NULL AND D.TIPO_DTL_MOV_CONT = '001' AND D.TIPO_DTL_MOV_CONT_SEC =
'001' AND MC.SEQ_RAPPORTO

Plan hash value: 4174829704

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows |E-Bytes|E-Temp | A-Rows |   A-Time   | Buffers | OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |      1 |        |       |       |     13 |00:04:40.81 |    3111K|      |       |          |
|   1 |  HASH GROUP BY                           |                         |      1 |  18314 |  5490K|  5872K|     13 |00:04:40.81 |    3111K|  761K|   761K| 2499K (0)|
|*  2 |   HASH JOIN                              |                         |      1 |  18314 |  5490K|       |    178K|00:04:40.07 |    3111K|  909K|   909K| 1241K (0)|
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |  3600 |       |     88 |00:00:00.01 |      67 |      |       |          |
|   4 |    NESTED LOOPS                          |                         |      1 |        |       |       |    178K|00:04:39.67 |    3110K|      |       |          |
|   5 |     NESTED LOOPS                         |                         |      1 |   4546 |  1185K|       |   1372K|00:03:17.99 |    2187K|      |       |          |
|   6 |      NESTED LOOPS                        |                         |      1 |   4919 |  1052K|       |    179K|00:02:50.53 |    1644K|      |       |          |
|   7 |       NESTED LOOPS                       |                         |      1 |   4492 |   785K|       |    167K|00:02:00.86 |    1137K|      |       |          |
|*  8 |        HASH JOIN                         |                         |      1 |   1985 |   222K|       |  62430 |00:00:42.55 |     123K|  955K|   955K| 1268K (0)|
|*  9 |         TABLE ACCESS FULL                | PRODOTTO_COMMERCIALE_S  |      1 |     41 |  1558 |       |     18 |00:00:00.01 |     113 |      |       |          |
|  10 |         NESTED LOOPS                     |                         |      1 |        |       |       |  62430 |00:00:42.40 |     123K|      |       |          |
|  11 |          NESTED LOOPS                    |                         |      1 |  15682 |  1179K|       |    797K|00:00:01.99 |    4279 |      |       |          |
|* 12 |           TABLE ACCESS FULL              | OGGETTO_DOC_STAMPA_S    |      1 |      3 |    75 |       |      4 |00:00:00.01 |       6 |      |       |          |
|* 13 |           INDEX RANGE SCAN               | VPO080_VPRC80_FK_IS     |      4 |  17301 |       |       |    797K|00:00:01.79 |    4273 |      |       |          |
|* 14 |          TABLE ACCESS BY INDEX ROWID     | PRODOTTO_CTR_S          |    797K|   5121 |   260K|       |  62430 |00:00:39.98 |     119K|      |       |          |
|  15 |        PARTITION RANGE ITERATOR          |                         |  62430 |      2 |   128 |       |    167K|00:01:18.19 |    1013K|      |       |          |
|* 16 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      2 |   128 |       |    167K|00:01:17.94 |    1013K|      |       |          |
|* 17 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  62430 |     32 |       |       |    818K|00:00:25.94 |     207K|      |       |          |
|  18 |       PARTITION RANGE ITERATOR           |                         |    167K|      1 |    40 |       |    179K|00:00:49.47 |     507K|      |       |          |
|* 19 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |    167K|      1 |    40 |       |    179K|00:00:48.90 |     507K|      |       |          |
|* 20 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |    167K|      4 |       |       |    366K|00:00:15.72 |     337K|      |       |          |
|* 21 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |    179K|      7 |       |       |   1372K|00:00:26.91 |     542K|      |       |          |
|* 22 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |   1372K|      1 |    48 |       |    178K|00:01:20.79 |     923K|      |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$AE39D3DB
   3 - SEL$AE39D3DB / FONDO_S@SEL$4
   9 - SEL$AE39D3DB / VPRC80S@SEL$2
  12 - SEL$AE39D3DB / VPRE65S@SEL$3
  13 - SEL$AE39D3DB / PC@SEL$1
  14 - SEL$AE39D3DB / PC@SEL$1
  16 - SEL$AE39D3DB / MC@SEL$1
  17 - SEL$AE39D3DB / MC@SEL$1
  19 - SEL$AE39D3DB / D@SEL$1
  20 - SEL$AE39D3DB / D@SEL$1
  21 - SEL$AE39D3DB / TR@SEL$1

  22 - SEL$AE39D3DB / TR@SEL$1

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=178): '2014/01/01 00:00:00'
   2 - (VARCHAR2(30), CSID=178): '2014/12/31 00:00:00'
   3 - (VARCHAR2(30), CSID=178, Primary=2)
   4 - (NUMBER): 2014123199999999
   5 - (NUMBER, Primary=4)
   6 - (VARCHAR2(30), CSID=178, Primary=2)
   7 - (VARCHAR2(30), CSID=178, Primary=2)
   8 - (NUMBER, Primary=4)

   9 - (NUMBER, Primary=4)
  10 - (CHAR(30), CSID=178): 'I                               '
  11 - (VARCHAR2(30), CSID=178, Primary=2)
  12 - (VARCHAR2(30), CSID=178, Primary=2)
  13 - (NUMBER, Primary=4)
  14 - (NUMBER, Primary=4)

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TR"."SEQ_FONDO"="SEQ_FONDO" AND "CODICE_COMPAGNIA_PVG"="CODICE_COMPAGNIA_PVG")
   3 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "CODICE_COMPAGNIA_PVG"=:B3))
   8 - access("PC"."SEQ_PROD_COMM"="SEQ_PROD_COMM")

   9 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND INTERNAL_FUNCTION("TIPO_PROD_LEGALE") AND "CODICE_COMPAGNIA_PVG"=:B3))
  12 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "TIPO_OGG"='02' AND "NUM_ISCR_COVIP" IS NOT NULL))
  13 - access("PC"."SEQ_PROD_COMM"="SEQ_OGG")
  14 - filter(("PC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "PC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "PC"."COD_STATO_PROD_CTR"='1' AND "PC"."ID_DATA_FINE_VAL">:B2 AND
              "PC"."ID_DATA_INIZIO_VAL"<=:B2))
  16 - filter(("MC"."DATA_EFF_COPERTURA">=:B4 AND "MC"."TIPO_MOV_CONTABILE"='001' AND INTERNAL_FUNCTION("MC"."COD_STATO_MOVIMENTO")))
  17 - access("MC"."SEQ_RAPPORTO"="PC"."SEQ_RAPPORTO" AND "MC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "MC"."ID_DATA_FINE_VAL">:B2 AND "MC"."ID_DATA_INIZIO_VAL"<=:B2 AND
              "MC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1)
       filter(("MC"."ID_DATA_FINE_VAL">:B2 AND "MC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "MC"."ID_DATA_INIZIO_VAL"<=:B2))
  19 - filter(("D"."SEQ_TRANCHE" IS NOT NULL AND "D"."TIPO_DTL_MOV_CONT_SEC"='001' AND "D"."TIPO_DTL_MOV_CONT"='001'))
  20 - access("MC"."SEQ_MOV_CONTABILE"="D"."SEQ_MOV_CONTABILE")
  21 - access("D"."SEQ_TRANCHE"="TR"."SEQ_TRANCHE")
  22 - filter(("TR"."ID_TIMESTAMP_FINE_VAL">:B1 AND "TR"."ID_DATA_FINE_VAL">:B2 AND "TR"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "TR"."ID_DATA_INIZIO_VAL"<=:B2))


Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "DENOM_PROD_COMM"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "DENOM_FND"[VARCHAR2,30],
       "CODICE_COMPAGNIA_PVG"[CHARACTER,1], SUM(DECODE("COD_TIPO_FLUSSO",'1',(-1),'0',1)*(NVL("D"."IMP_ADDIZIONALI",0)+NVL("D"."IMP_DIRITTI_NETTO",0)+NVL("D"."IMP_IMPORTO_NETTO",0)+NVL("D"."
       IMP_IMPOSTA",0)+NVL("D"."IMP_IMPOSTA_DIRITTI",0)+NVL("D"."IMP_INTERES_RITDO_PAG",0)))[22]
   2 - (#keys=2) "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "SEQ_PROD_COMM"[NUMBER,22], "DENOM_PROD_COMM"[VARCHAR2,30],
       "NUM_ISCR_COVIP"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22],
       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
   3 - "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "SEQ_FONDO"[NUMBER,22]
   4 - "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "NUM_ISCR_COVIP"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22],
       "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22],

       "COD_TIPO_FLUSSO"[CHARACTER,1], "TR"."SEQ_FONDO"[NUMBER,22]
   5 - "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "NUM_ISCR_COVIP"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22],
       "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22],
       "COD_TIPO_FLUSSO"[CHARACTER,1], "TR".ROWID[ROWID,10]
   6 - "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "NUM_ISCR_COVIP"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22],
       "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22],
       "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
   7 - "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "NUM_ISCR_COVIP"[VARCHAR2,30], "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
   8 - (#keys=1) "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "NUM_ISCR_COVIP"[VARCHAR2,30], "PC"."SEQ_RAPPORTO"[NUMBER,22]
   9 - "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22]
  10 - "NUM_ISCR_COVIP"[VARCHAR2,30], "PC"."SEQ_PROD_COMM"[NUMBER,22], "PC"."SEQ_RAPPORTO"[NUMBER,22]
  11 - "NUM_ISCR_COVIP"[VARCHAR2,30], "PC".ROWID[ROWID,10], "PC"."SEQ_PROD_COMM"[NUMBER,22]
  12 - "SEQ_OGG"[NUMBER,22], "NUM_ISCR_COVIP"[VARCHAR2,30]

  13 - "PC".ROWID[ROWID,10], "PC"."SEQ_PROD_COMM"[NUMBER,22]
  14 - "PC"."SEQ_RAPPORTO"[NUMBER,22]
  15 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
  16 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
  17 - "MC".ROWID[ROWID,10]
  18 - "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22],
       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
  19 - "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22],
       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
  20 - "D".ROWID[ROWID,10]
  21 - "TR".ROWID[ROWID,10]
  22 - "TR"."SEQ_FONDO"[NUMBER,22]


141 rows selected.

Elapsed: 00:00:00.18

----------------------------------------------------------------------------------
--The plan here ha more realistic Elapsed Time of some 400 seconds
--The plan here gives the initial hints:
a)13 rows selected -> PRECISION QUERY ?
Should we want to see only NESTED LOOP with INDEXes on INNER table of the joins (where it's reasonable)
b)The DRIVING TABLE(Definition#2 the more used) is OGGETTO_DOC_STAMPA_S (only 4 rows!....
can this be a good reason for CBO to choose this table as drinving table?)
but the DRIVING TABLE(Definition#3 - the outer table of the join after the preparation work) is the (big) TRANCHE_S (after a really NON-selective Predicate Query).
c)Cardinality Estimates are wrong (orders of magnitude) for many step

----------------------------------------------------------------------------------

OK.After First Analysis (the query is fast enough to allow a comparison between Estimated and Actual rows) it's time to observe the FRP SPREADSHEET produced based on Explain Plan

SQL> @FRP_21042015.sql

ID    TAB TABLE_NAME             TABLE_A   NUM_ROWS   ROWCOUNT CARDINALITY FILTERED_CARDINALITY ACTUAL_FRP   PLAN_FRP
----- --- ---------------------- ------- ---------- ---------- ----------- -------------------- ---------- ----------
    3 VPR FONDO_S                FONDO_S       2166       2169          90                   88        4.1        4.2
    9 VPR PRODOTTO_COMMERCIALE_S VPRC80S       2684       2705          41                   18         .7        1.5
   12 VPR OGGETTO_DOC_STAMPA_S   VPRE65S          4          4           3                    4        100         75
   13 VPO PRODOTTO_CTR_S         PC         7491511    7491511       17301
   14 VPO PRODOTTO_CTR_S         PC         7491511    7491511        5121               331009        4.4         .1
   16 VPO MOV_CONTABILE_S        MC       154931757  155615151           2              9383891          6          0
   17 VPO MOV_CONTABILE_S        MC       154931757  155615151          32             52656736       33.8          0
   19 VPO DTL_MOV_CONTABILE      D        207252296  217335478           1             82185765       37.8          0
   20 VPO DTL_MOV_CONTABILE      D        207252296  217335478           4
   21 VPO TRANCHE_S              TR       297837084  297837084           7
   22 VPO TRANCHE_S              TR       297837084  297837084           1             40296608       13.5          0

11 rows selected.


--------
So What?
1)
0.7 as the Best Filtered Rows Percentuage (<2%) for PRODOTTO_COMMERCIALE_S -> Preferred Driving Table?....
anyway a small small Preferred table) --> PRECISION QUERY (according also to the 13 rows returned from query)

2)
4.1% for the small FONDO_S and 4.4 for the bigger PRODOTTO_CTR_S offer these as the next candidates for a "good" DRIVING TABLE

------------------------------------------------------------------------------------------------------------------------------------- --
------------------------------------------------------------------------------------------------------------------------------------- --
End of Part#1 ----
In the next part I' ll present my exploration towards optimization using hints to change driving_table/driving_order




P.S.Any possibility to re-size what is formattated in code-mode [ big plans overflow the row's length Sad )



Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #636588 is a reply to message #636573] Tue, 28 April 2015 10:05 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
You could have a look at [spoiler] tag for such lengthy codes.
Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #636712 is a reply to message #636573] Thu, 30 April 2015 10:20 Go to previous messageGo to next message
paolfili
Messages: 6
Registered: March 2015
Location: Italy
Junior Member
Episode 1: part#2 - A not so simple begin


----
"It' now time to make a change" to ONLY (for now) the DRIVING TABLE using (explorative) hints

Take#1)
Based on PC.SEQ_PROD_COMM = O.SEQ_OGG we impose /*+ LEADING("VPRC80S"@"SEL$2" "PC"@"SEL$1") */ respectively 0.7% and 4.4% of our FRP spreadsheet


SQL> @Query_5yag3uqq0fyz8_Lead1.sql

NUM_ISCR_COVIP                 SEQ_PROD_COMM DENOM_PROD_COMM                TI    IMP_TOT
------------------------------ ------------- ------------------------------ -- ----------
5004                              1000004118 419EY CONVENZIONE              GS 1475093.02
5004                              1000004118 419EY CONVENZIONE              GS  329982.23
5004                              1000004818 419EY PIP 2012                 GS 41150529.8
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  207101.77
5004                              1000004118 419EY CONVENZIONE              UL       1050
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  190884.29
5004                              1000002798 419EY STANDARD                 GS  420750.45
5004                              1000004118 419EY CONVENZIONE              UL      10345
5004                              1000004818 419EY PIP 2012                 UL  100703.68
5004                              1000004818 419EY PIP 2012                 GS 7904207.21
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 52686508.1

NUM_ISCR_COVIP                 SEQ_PROD_COMM DENOM_PROD_COMM                TI    IMP_TOT
------------------------------ ------------- ------------------------------ -- ----------
5004                              1000004818 419EY PIP 2012                 UL   82764.63
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 4655567.43

13 rows selected.

Elapsed: 00:03:19.72


SQL> @../xplan

SQL_ID  3y1y7d1f0kwzb, child number 0
-------------------------------------
SELECT /*+                 LEADING("VPRC80S"@"SEL$2" "PC"@"SEL$1") */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) * (NVL(D.IMP_ADDIZIONALI,0) +
NVL(D.IMP_DIRITTI_NETTO ,0) + NVL(D.IMP_IMPORTO_NETTO,0) +
NVL(D.IMP_IMPOSTA,0) + NVL( D.IMP_IMPOSTA_DIRITTI,0) +
NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT FROM VPO.MOV_CONTABILE_S
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O,
VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND

MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND
MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <MC.ID_DATA_FINE_VAL AND
MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < MC.ID_TIMESTAMP_FINE_VAL
AND MC.SEQ_MOV_CONTABILE=D.SEQ_MOV_CONTABILE AND D.SEQ_TRANCHE IS NOT
NULL AND D.TIPO_DTL_MOV_CONT

Plan hash value: 2381553728

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows |E-Bytes|E-Temp | A-Rows | Buffers |  OMem |  1Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |      1 |        |       |       |     13 |    3130K|       |       |
|   1 |  HASH GROUP BY                           |                         |      1 |  10353 |  3103K|  5360K|     13 |    3130K|   761K|   761K|
|*  2 |   HASH JOIN                              |                         |      1 |  16721 |  5013K|       |    178K|    3130K|   909K|   909K|
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |  3600 |       |     88 |      67 |       |       |
|   4 |    NESTED LOOPS                          |                         |      1 |        |       |       |    178K|    3130K|       |       |
|   5 |     NESTED LOOPS                         |                         |      1 |   4151 |  1082K|       |   1372K|    2206K|       |       |
|   6 |      NESTED LOOPS                        |                         |      1 |   4492 |   960K|       |    179K|    1664K|       |       |
|   7 |       NESTED LOOPS                       |                         |      1 |   4101 |   716K|       |    167K|    1156K|       |       |
|*  8 |        HASH JOIN                         |                         |      1 |   1812 |   203K|       |  62430 |     143K|  1134K|  1134K|
|*  9 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      3 |    75 |       |      4 |       6 |       |       |
|* 10 |         HASH JOIN                        |                         |      1 |    256K|    21M|       |  73515 |     143K|   955K|   955K|
|* 11 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     41 |  1558 |       |     18 |     113 |       |       |
|* 12 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |   2217K|   109M|       |    331K|     142K|       |       |
|  13 |        PARTITION RANGE ITERATOR          |                         |  62430 |      2 |   128 |       |    167K|    1013K|       |       |
|* 14 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      2 |   128 |       |    167K|    1013K|       |       |
|* 15 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  62430 |     32 |       |       |    818K|     207K|       |       |
|  16 |       PARTITION RANGE ITERATOR           |                         |    167K|      1 |    40 |       |    179K|     507K|       |       |
|* 17 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |    167K|      1 |    40 |       |    179K|     507K|       |       |
|* 18 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |    167K|      4 |       |       |    366K|     337K|       |       |
|* 19 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |    179K|      7 |       |       |   1372K|     542K|       |       |
|* 20 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |   1372K|      1 |    48 |       |    178K|     923K|       |       |
-------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$AE39D3DB
   3 - SEL$AE39D3DB / FONDO_S@SEL$4
   9 - SEL$AE39D3DB / VPRE65S@SEL$3
  11 - SEL$AE39D3DB / VPRC80S@SEL$2
  12 - SEL$AE39D3DB / PC@SEL$1
  14 - SEL$AE39D3DB / MC@SEL$1
  15 - SEL$AE39D3DB / MC@SEL$1
  17 - SEL$AE39D3DB / D@SEL$1
  18 - SEL$AE39D3DB / D@SEL$1
  19 - SEL$AE39D3DB / TR@SEL$1
  20 - SEL$AE39D3DB / TR@SEL$1

Peeked Binds (identified by position):
--------------------------------------


   1 - (VARCHAR2(30), CSID=178): '2014/01/01 00:00:00'
   2 - (VARCHAR2(30), CSID=178): '2014/12/31 00:00:00'
   3 - (VARCHAR2(30), CSID=178, Primary=2)
   4 - (NUMBER): 2014123199999999
   5 - (NUMBER, Primary=4)
   6 - (VARCHAR2(30), CSID=178, Primary=2)
   7 - (VARCHAR2(30), CSID=178, Primary=2)
   8 - (NUMBER, Primary=4)
   9 - (NUMBER, Primary=4)
  10 - (CHAR(30), CSID=178): 'I                               '
  11 - (VARCHAR2(30), CSID=178, Primary=2)
  12 - (VARCHAR2(30), CSID=178, Primary=2)
  13 - (NUMBER, Primary=4)
  14 - (NUMBER, Primary=4)

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TR"."SEQ_FONDO"="SEQ_FONDO" AND "CODICE_COMPAGNIA_PVG"="CODICE_COMPAGNIA_PVG")
   3 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "CODICE_COMPAGNIA_PVG"=:B3))
   8 - access("PC"."SEQ_PROD_COMM"="SEQ_OGG")
   9 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "TIPO_OGG"='02' AND "NUM_ISCR_COVIP" IS NOT NULL))
  10 - access("PC"."SEQ_PROD_COMM"="SEQ_PROD_COMM")
  11 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND INTERNAL_FUNCTION("TIPO_PROD_LEGALE") AND "CODICE_COMPAGNIA_PVG"=:B3))
  12 - filter(("PC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "PC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "PC"."COD_STATO_PROD_CTR"='1' AND "PC"."ID_DATA_FINE_VAL">:B2 AND
              "PC"."ID_DATA_INIZIO_VAL"<=:B2))
  14 - filter(("MC"."DATA_EFF_COPERTURA">=:B4 AND "MC"."TIPO_MOV_CONTABILE"='001' AND INTERNAL_FUNCTION("MC"."COD_STATO_MOVIMENTO")))
  15 - access("MC"."SEQ_RAPPORTO"="PC"."SEQ_RAPPORTO" AND "MC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "MC"."ID_DATA_FINE_VAL">:B2 AND "MC"."ID_DATA_INIZIO_VAL"<=:B2 AND
              "MC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1)
       filter(("MC"."ID_DATA_FINE_VAL">:B2 AND "MC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "MC"."ID_DATA_INIZIO_VAL"<=:B2))
  17 - filter(("D"."SEQ_TRANCHE" IS NOT NULL AND "D"."TIPO_DTL_MOV_CONT_SEC"='001' AND "D"."TIPO_DTL_MOV_CONT"='001'))
  18 - access("MC"."SEQ_MOV_CONTABILE"="D"."SEQ_MOV_CONTABILE")
  19 - access("D"."SEQ_TRANCHE"="TR"."SEQ_TRANCHE")
  20 - filter(("TR"."ID_TIMESTAMP_FINE_VAL">:B1 AND "TR"."ID_DATA_FINE_VAL">:B2 AND "TR"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "TR"."ID_DATA_INIZIO_VAL"<=:B2))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "DENOM_PROD_COMM"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "DENOM_FND"[VARCHAR2,30],
       "CODICE_COMPAGNIA_PVG"[CHARACTER,1], SUM(DECODE("COD_TIPO_FLUSSO",'1',(-1),'0',1)*(NVL("D"."IMP_ADDIZIONALI",0)+NVL("D"."IMP_DIRITTI_NETTO",0)+NVL("D"."IMP_IMPORTO_NETTO",0)+NVL("D"."
       IMP_IMPOSTA",0)+NVL("D"."IMP_IMPOSTA_DIRITTI",0)+NVL("D"."IMP_INTERES_RITDO_PAG",0)))[22]
   2 - (#keys=2) "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22],
       "DENOM_PROD_COMM"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22],
       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
   3 - "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "SEQ_FONDO"[NUMBER,22]
   4 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22],
       "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22],
       "COD_TIPO_FLUSSO"[CHARACTER,1], "TR"."SEQ_FONDO"[NUMBER,22]
   5 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22],
       "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22],
       "COD_TIPO_FLUSSO"[CHARACTER,1], "TR".ROWID[ROWID,10]
   6 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "D"."IMP_IMPORTO_NETTO"[NUMBER,22],
       "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22],
       "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
   7 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
   8 - (#keys=1) "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "PC"."SEQ_RAPPORTO"[NUMBER,22]
   9 - "SEQ_OGG"[NUMBER,22], "NUM_ISCR_COVIP"[VARCHAR2,30]
  10 - (#keys=1) "SEQ_PROD_COMM"[NUMBER,22], "PC"."SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "PC"."SEQ_RAPPORTO"[NUMBER,22]
  11 - "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22]
  12 - "PC"."SEQ_PROD_COMM"[NUMBER,22], "PC"."SEQ_RAPPORTO"[NUMBER,22]
  13 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
  14 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]
  15 - "MC".ROWID[ROWID,10]
  16 - "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22],
       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
  17 - "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22],
       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
  18 - "D".ROWID[ROWID,10]
  19 - "TR".ROWID[ROWID,10]
  20 - "TR"."SEQ_FONDO"[NUMBER,22]


136 rows selected.

---
Not bad ... some improvements ... 3:19.72
Take #2)
Let's try the other starting combination /*+ LEADING("VPRC80S"@"SEL$2" "FONDO_S"@"SEL$4") */

Mmmhhh ... 12:08.92 .... Too much time elapsed ... Let's stop the execution

---------

SQL> @Query_5yag3uqq0fyz8_Lead2.sql
FROM VPO.MOV_CONTABILE_S MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC, VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O, VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <MC.ID_DATA_FINE_VAL AND MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < MC.ID_TIMESTAMP_FINE_VAL AND MC.SEQ_MOV_CONTABILE=D.SEQ_MOV_CONTABILE AND D.SEQ_TRANCHE IS NOT NULL AND D.TIPO_DTL_MOV_CONT = '001' AND D.TIPO_DTL_MOV_CONT_SEC = '001' AND MC.SEQ_RAPPORTO=PC.SEQ_RAPPORTO AND PC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <PC.ID_DATA_FINE_VAL AND PC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < PC.ID_TIMESTAMP_FINE_VAL AND PC.COD_STATO_PROD_CTR='1' AND PC.SEQ_PROD_COMM=PM.SEQ_PROD_COMM AND PM.TIPO_PROD_LEGALE IN ('02','04') AND PM.CODICE_COMPAGNIA_PVG=:B3 AND PC.SEQ_PROD_COMM=O.SEQ_OGG AND O.TIPO_OGG='02' AND O.NUM_ISCR_COVIP IS NOT NULL AND D.SEQ_TRANCHE=TR.SEQ_TRANCHE AND TR.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <TR.ID_DATA_FINE_VAL AND TR.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < TR.ID_TIMESTAMP_FINE_VAL AND TR.SEQ_FONDO=F.SEQ_FONDO AND F.CODICE_COMPAGNIA_PVG=PM.CODICE_COMPAGNIA_PVG GROUP BY O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,F.TIPO_FND,F.DENOM_FND,F.CODICE_COMPAGNIA_PVG
         *
ERROR at line 6:
ORA-01013: user requested cancel of current operation


Elapsed: 00:12:08.92

SQL> @../xplan

SQL_ID  3rnh3tgqwmaxm, child number 0
-------------------------------------
SELECT /*+                 LEADING("VPRC80S"@"SEL$2" "FONDO_S"@"SEL$4")
*/ O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) * (NVL(D.IMP_ADDIZIONALI,0) +
NVL(D.IMP_DIRITTI_NETTO ,0) + NVL(D.IMP_IMPORTO_NETTO,0) +
NVL(D.IMP_IMPOSTA,0) + NVL( D.IMP_IMPOSTA_DIRITTI,0) +
NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT FROM VPO.MOV_CONTABILE_S
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O,
VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND

MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND
MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <MC.ID_DATA_FINE_VAL AND
MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < MC.ID_TIMESTAMP_FINE_VAL
AND MC.SEQ_MOV_CONTABILE=D.SEQ_MOV_CONTABILE AND D.SEQ_TRANCHE IS NOT
NULL AND D.TIPO_DTL_MOV_

Plan hash value: 3690204842

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Starts | E-Rows |E-Bytes|E-Temp | A-Rows | Buffers | Writes |  OMem |1Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |      1 |        |       |       |      0 |       0 |      0 |       |     |
|   1 |  HASH GROUP BY                       |                        |      1 |  10353 |  3103K|  5360K|      0 |       0 |      0 |  6804K|1878K|
|   2 |   NESTED LOOPS                       |                        |      1 |        |       |       |    157K|      83M|    211K|       |     |
|   3 |    NESTED LOOPS                      |                        |      1 |  16721 |  5013K|       |    106M|      11M|    211K|       |     |
|*  4 |     HASH JOIN                        |                        |      1 |    402K|    99M|    80M|     13M|    2603K|    211K|  1503M|  32M|
|   5 |      PART JOIN FILTER CREATE         | :BF0000                |      1 |    367K|    76M|       |     14M|    1981K|  60195 |       |     |
|*  6 |       HASH JOIN                      |                        |      1 |    367K|    76M|    25M|     14M|    1981K|  60195 |   564M|  16M|
|   7 |        PART JOIN FILTER CREATE       | :BF0001                |      1 |    162K|    23M|       |   5493K|     143K|      0 |       |     |
|*  8 |         HASH JOIN                    |                        |      1 |    162K|    23M|       |   5493K|     143K|      0 |  1134K|1134K|
|*  9 |          TABLE ACCESS FULL           | OGGETTO_DOC_STAMPA_S   |      1 |      3 |    75 |       |      4 |       6 |      0 |       |     |
|* 10 |          HASH JOIN                   |                        |      1 |     22M|  2843M|       |   6469K|     143K|      0 |   825K| 825K|
|* 11 |           HASH JOIN                  |                        |      1 |   3714 |   282K|       |   1584 |     180 |      0 |   955K| 955K|
|* 12 |            TABLE ACCESS FULL         | PRODOTTO_COMMERCIALE_S |      1 |     41 |  1558 |       |     18 |     113 |      0 |       |     |
|* 13 |            TABLE ACCESS FULL         | FONDO_S                |      1 |     90 |  3600 |       |     88 |      67 |      0 |       |     |
|* 14 |           TABLE ACCESS FULL          | PRODOTTO_CTR_S         |      1 |   2217K|   109M|       |    331K|     142K|      0 |       |     |
|  15 |        PARTITION RANGE JOIN-FILTER   |                        |      1 |   4280K|   261M|       |   2517K|    1838K|      0 |       |     |
|* 16 |         TABLE ACCESS FULL            | MOV_CONTABILE_S        |      5 |   4280K|   261M|       |   2517K|    1838K|      0 |       |     |
|  17 |      PARTITION RANGE JOIN-FILTER     |                        |      1 |     12M|   466M|       |     10M|     622K|      0 |       |     |
|* 18 |       TABLE ACCESS FULL              | DTL_MOV_CONTABILE      |      1 |     12M|   466M|       |     10M|     622K|      0 |       |     |
|* 19 |     INDEX RANGE SCAN                 | TRANCHE_S_P01          |     13M|      7 |       |       |    106M|    8866K|      0 |       |     |
|* 20 |    TABLE ACCESS BY GLOBAL INDEX ROWID| TRANCHE_S              |    106M|      1 |    48 |       |    157K|      71M|      0 |       |     |
---------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$AE39D3DB
   9 - SEL$AE39D3DB / VPRE65S@SEL$3
  12 - SEL$AE39D3DB / VPRC80S@SEL$2
  13 - SEL$AE39D3DB / FONDO_S@SEL$4

  14 - SEL$AE39D3DB / PC@SEL$1
  16 - SEL$AE39D3DB / MC@SEL$1
  18 - SEL$AE39D3DB / D@SEL$1
  19 - SEL$AE39D3DB / TR@SEL$1
  20 - SEL$AE39D3DB / TR@SEL$1

Peeked Binds (identified by position):
--------------------------------------

   1 - (VARCHAR2(30), CSID=178): '2014/01/01 00:00:00'
   2 - (VARCHAR2(30), CSID=178): '2014/12/31 00:00:00'
   3 - (VARCHAR2(30), CSID=178, Primary=2)
   4 - (NUMBER): 2014123199999999

   5 - (NUMBER, Primary=4)
   6 - (VARCHAR2(30), CSID=178, Primary=2)
   7 - (VARCHAR2(30), CSID=178, Primary=2)
   8 - (NUMBER, Primary=4)
   9 - (NUMBER, Primary=4)
  10 - (CHAR(30), CSID=178): 'I                               '
  11 - (VARCHAR2(30), CSID=178, Primary=2)
  12 - (VARCHAR2(30), CSID=178, Primary=2)
  13 - (NUMBER, Primary=4)
  14 - (NUMBER, Primary=4)

Predicate Information (identified by operation id):
---------------------------------------------------


   4 - access("MC"."SEQ_MOV_CONTABILE"="D"."SEQ_MOV_CONTABILE")
   6 - access("MC"."SEQ_RAPPORTO"="PC"."SEQ_RAPPORTO")
   8 - access("PC"."SEQ_PROD_COMM"="SEQ_OGG")
   9 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "TIPO_OGG"='02' AND "NUM_ISCR_COVIP" IS NOT NULL))
  10 - access("PC"."SEQ_PROD_COMM"="SEQ_PROD_COMM")
  11 - access("CODICE_COMPAGNIA_PVG"="CODICE_COMPAGNIA_PVG")
  12 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND INTERNAL_FUNCTION("TIPO_PROD_LEGALE") AND "CODICE_COMPAGNIA_PVG"=:B3))
  13 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "CODICE_COMPAGNIA_PVG"=:B3))
  14 - filter(("PC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "PC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "PC"."COD_STATO_PROD_CTR"='1' AND "PC"."ID_DATA_FINE_VAL">:B2 AND "PC"."ID_DATA_INIZIO_VAL"<=:B2))
  16 - filter(("MC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "MC"."DATA_EFF_COPERTURA">=:B4 AND "MC"."ID_DATA_FINE_VAL">:B2 AND "MC"."TIPO_MOV_CONTABILE"='001' AND "MC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND
              INTERNAL_FUNCTION("MC"."COD_STATO_MOVIMENTO") AND "MC"."ID_DATA_INIZIO_VAL"<=:B2))
  18 - filter(("D"."SEQ_TRANCHE" IS NOT NULL AND "D"."TIPO_DTL_MOV_CONT_SEC"='001' AND "D"."TIPO_DTL_MOV_CONT"='001'))

  19 - access("D"."SEQ_TRANCHE"="TR"."SEQ_TRANCHE")
  20 - filter(("TR"."ID_TIMESTAMP_FINE_VAL">:B1 AND "TR"."ID_DATA_FINE_VAL">:B2 AND "TR"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "TR"."ID_DATA_INIZIO_VAL"<=:B2 AND "TR"."SEQ_FONDO"="SEQ_FONDO"))

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "DENOM_PROD_COMM"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "DENOM_FND"[VARCHAR2,30], "CODICE_COMPAGNIA_PVG"[CHARACTER,1],
       SUM(DECODE("COD_TIPO_FLUSSO",'1',(-1),'0',1)*(NVL("D"."IMP_ADDIZIONALI",0)+NVL("D"."IMP_DIRITTI_NETTO",0)+NVL("D"."IMP_IMPORTO_NETTO",0)+NVL("D"."IMP_IMPOSTA",0)+NVL("D"."IMP_IMPOSTA_DIRITTI",0
)+NV

       L("D"."IMP_INTERES_RITDO_PAG",0)))[22]
   2 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2],
       "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22],

       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22]
   3 - "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_FONDO"[NUMBER,22], "DENOM_FND"[VARCHAR2,30],
       "TIPO_FND"[CHARACTER,2], "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1], "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,2
2],

       "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "TR".ROWID[ROWID,10]
   4 - (#keys=1) "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_FONDO"[NUMBER,22], "DENOM_FND"[VARCHAR2,30],
       "TIPO_FND"[CHARACTER,2], "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1],
       "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22]
   5 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22], "MC"."SEQ_MOV_CONTABILE"[NUMBER,22], "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1],
       "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_FONDO"[NUMBER,22], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2]
   6 - (#keys=1) "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_FONDO"[NUMBER,22], "DENOM_FND"[VARCHAR2,30],
       "TIPO_FND"[CHARACTER,2], "MC"."SEQ_MOV_CONTABILE"[NUMBER,22]

   7 - "PC"."SEQ_RAPPORTO"[NUMBER,22], "PC"."SEQ_RAPPORTO"[NUMBER,22], "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1],
       "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_FONDO"[NUMBER,22], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2]
   8 - (#keys=1) "NUM_ISCR_COVIP"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_FONDO"[NUMBER,22], "DENOM_FND"[VARCHAR2,30],
       "TIPO_FND"[CHARACTER,2], "PC"."SEQ_RAPPORTO"[NUMBER,22]
   9 - "SEQ_OGG"[NUMBER,22], "NUM_ISCR_COVIP"[VARCHAR2,30]
  10 - (#keys=1) "SEQ_PROD_COMM"[NUMBER,22], "PC"."SEQ_PROD_COMM"[NUMBER,22], "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_FONDO"[NUMBER,22], "DENOM_FND"[VARCHAR2,30],
       "TIPO_FND"[CHARACTER,2], "PC"."SEQ_RAPPORTO"[NUMBER,22]
  11 - (#keys=1) "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "SEQ_FONDO"[NUMBER,22]
  12 - "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_PROD_COMM"[VARCHAR2,30], "SEQ_PROD_COMM"[NUMBER,22]
  13 - "CODICE_COMPAGNIA_PVG"[CHARACTER,1], "DENOM_FND"[VARCHAR2,30], "TIPO_FND"[CHARACTER,2], "SEQ_FONDO"[NUMBER,22]
  14 - "PC"."SEQ_PROD_COMM"[NUMBER,22], "PC"."SEQ_RAPPORTO"[NUMBER,22]
  15 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22], "MC"."SEQ_RAPPORTO"[NUMBER,22]
  16 - "MC"."SEQ_MOV_CONTABILE"[NUMBER,22], "MC"."SEQ_RAPPORTO"[NUMBER,22]

  17 - "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."SEQ_MOV_CONTABILE"[NUMBER,22],
       "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
  18 - "D"."IMP_IMPORTO_NETTO"[NUMBER,22], "D"."IMP_DIRITTI_NETTO"[NUMBER,22], "D"."IMP_IMPOSTA"[NUMBER,22], "D"."SEQ_TRANCHE"[NUMBER,22], "D"."SEQ_MOV_CONTABILE"[NUMBER,22],
       "D"."IMP_IMPOSTA_DIRITTI"[NUMBER,22], "D"."IMP_ADDIZIONALI"[NUMBER,22], "D"."IMP_INTERES_RITDO_PAG"[NUMBER,22], "COD_TIPO_FLUSSO"[CHARACTER,1]
  19 - "TR".ROWID[ROWID,10]


134 rows selected.

Elapsed: 00:00:00.17

------------
Take#3)
This DRIVING TABLE options triggers a worse plan executions.
The viable option remains /*+ LEADING("VPRC80S"@"SEL$2" "PC"@"SEL$1") */

It's the moment to correct the Estimated Cardinalities errors
At the beginning using OPT_ESTIMATE hint correct the access to PRODOTTO_CTR_S of PHV 2381553728

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows |E-Bytes|E-Temp | A-Rows |
-----------------------------------------------------------------------------------------------------------------------
-----SNAP-----
|* 11 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     41 |  1558 |       |     18 |
|* 12 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |   2217K|   109M|       |    331K|
-----SNAP---- 



Let's correct the Estimated Cardinality on PC

/*+
leading(PM PC)
CARDINALITY (PC 331000)
*/

--


SQL> @Query_5yag3uqq0fyz8_CardKM1.sql
5004                              1000004118 419EY CONVENZIONE              GS 1475093.02
5004                              1000004118 419EY CONVENZIONE              GS  329982.23
5004                              1000004818 419EY PIP 2012                 GS 41150529.8
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  207101.77
5004                              1000004118 419EY CONVENZIONE              UL       1050
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  190884.29
5004                              1000002798 419EY STANDARD                 GS  420750.45
5004                              1000004118 419EY CONVENZIONE              UL      10345
5004                              1000004818 419EY PIP 2012                 UL  100703.68
5004                              1000004818 419EY PIP 2012                 GS 7904207.21
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 52686508.1
5004                              1000004818 419EY PIP 2012                 UL   82764.63
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 4655567.43

13 rows selected.

Elapsed: 00:04:03.23
SQL> @../xplan
SQL_ID  85ra83r7ucfsy, child number 0
-------------------------------------
SELECT /*+                 leading(PM PC)                 CARDINALITY
(PC 331000) */ O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) * (NVL(D.IMP_ADDIZIONALI,0) +
NVL(D.IMP_DIRITTI_NETTO ,0) + NVL(D.IMP_IMPORTO_NETTO,0) +
NVL(D.IMP_IMPOSTA,0) + NVL( D.IMP_IMPOSTA_DIRITTI,0) +
NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT FROM VPO.MOV_CONTABILE_S
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O,
VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND
MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND
MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <MC.ID_DATA_FINE_VAL AND
MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < MC.ID_TIMESTAMP_FINE_VAL
AND MC.SEQ_MOV_CONTABILE=D.SEQ_MOV_CONTABILE AND D.SEQ_TRANCHE IS NOT
NULL AND D.TIP

Plan hash value: 2381553728

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows |E-Bytes|E-Temp | A-Rows | Buffers |  OMem |  1Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
---SNAP---
|* 10 |         HASH JOIN                        |                         |      1 |    256K|    21M|       |  73515 |     143K|   955K|   955K|
|* 11 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     41 |  1558 |       |     18 |     113 |       |       |
|* 12 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|   109M|       |    331K|     142K|       |       |
---SNAP----
-----------------------------------------------------------------------------------------------------------------------------------------------


---
Take #4)
Same PHV : 2381553728
It's time to correct HASH JOIN Cardinality Estimate
/*+
leading(PM PC)
CARDINALITY (PC 331000)
OPT_ESTIMATE(@"SEL$AE39D3DB", JOIN, ("VPRC80S"@"SEL$2","PC"@"SEL$1"), SCALE_ROWS=2)
*/




SQL> @Query_5yag3uqq0fyz8_CardKM2.sql
5004                              1000004118 419EY CONVENZIONE              GS 1475093.02
5004                              1000004118 419EY CONVENZIONE              GS  329982.23
5004                              1000004818 419EY PIP 2012                 GS 41150529.8
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  207101.77
5004                              1000004118 419EY CONVENZIONE              UL       1050
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  190884.29
5004                              1000002798 419EY STANDARD                 GS  420750.45
5004                              1000004118 419EY CONVENZIONE              UL      10345
5004                              1000004818 419EY PIP 2012                 UL  100703.68
5004                              1000004818 419EY PIP 2012                 GS 7904207.21
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 52686508.1
5004                              1000004818 419EY PIP 2012                 UL   82764.63
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 4655567.43

13 rows selected.

Elapsed: 00:03:20.00
SQL> @../xplan
SQL_ID  cw4j3fsnsa9xp, child number 0
-------------------------------------
SELECT /*+                 leading(PM PC)                 CARDINALITY
(PC 331000) OPT_ESTIMATE(@"SEL$AE39D3DB", JOIN, ("VPRC80S"@"SEL$2",
"PC"@"SEL$1"), SCALE_ROWS=2) */ O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DEN
OM_PROD_COMM, DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',S
UBSTR(F.TIPO_FND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND)
TIPO_FND, SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) *
(NVL(D.IMP_ADDIZIONALI,0) + NVL(D.IMP_DIRITTI_NETTO ,0) +
NVL(D.IMP_IMPORTO_NETTO,0) + NVL(D.IMP_IMPOSTA,0) + NVL(
D.IMP_IMPOSTA_DIRITTI,0) + NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT
FROM VPO.MOV_CONTABILE_S MC, VPO.DTL_MOV_CONTABILE D,
VPO.PRODOTTO_CTR_S PC, VPR.PRODOTTO_COMMERCIALE_V PM,
VPR.OGGETTO_DOC_STAMPA_V O, VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE
MC.TIPO_MOV_CONTABILE='001' AND MC.DATA_EFF_COPERTURA>=:B4 AND
MC.COD_STATO_MOVIMENTO IN ('I','E') AND MC.ID_DATA_INIZIO_VAL <= :B2
AND :B2 <MC.ID_DATA_FINE_VAL AND MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND
:B1 < MC.ID_TIMESTAMP_FINE_VAL

Plan hash value: 2381553728

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows |E-Bytes|E-Temp | A-Rows | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
---SNAP ----
|   7 |       NESTED LOOPS                       |                         |      1 |   1225 |   214K|       |    167K|    1156K|       |       |          |
|*  8 |        HASH JOIN                         |                         |      1 |    541 | 62215 |       |  62430 |     143K|  1134K|  1134K|  654K (0)|
|*  9 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      3 |    75 |       |      4 |       6 |       |       |          |
|* 10 |         HASH JOIN                        |                         |      1 |  76489 |  6722K|       |  73515 |     143K|   955K|   955K| 1292K (0)|
|* 11 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     41 |  1558 |       |     18 |     113 |       |       |          |
|* 12 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|    16M|       |    331K|     142K|       |       |          |
|  13 |        PARTITION RANGE ITERATOR          |                         |  62430 |      2 |   128 |       |    167K|    1013K|       |       |          |
|* 14 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      2 |   128 |       |    167K|    1013K|       |       |          |
--SNAP ------
------------------------------------------------------------------------------------------------------------------------------------------------------------


----
Take#5)
It' s time to correct estimate of the HASH JOIN al Id=8
62430(A-Rows)/541(Starts*E-rows)= 115



SQL> @Query_5yag3uqq0fyz8_CardKM3.sql
5004                              1000004118 419EY CONVENZIONE              GS 1475093.02
5004                              1000004118 419EY CONVENZIONE              GS  329982.23
5004                              1000004818 419EY PIP 2012                 GS 41150529.8
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  207101.77
5004                              1000004118 419EY CONVENZIONE              UL       1050
5004                              1000002798 419EY STANDARD                 GS  420750.45
5004                              1000002898 419EY STANDARD PIP STRATEGICO  UL  190884.29
5004                              1000004818 419EY PIP 2012                 UL  100703.68
5004                              1000004118 419EY CONVENZIONE              UL      10345
5004                              1000004818 419EY PIP 2012                 GS 7904207.21
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 52686508.1
5004                              1000004818 419EY PIP 2012                 UL   82764.63
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 4655567.43

13 rows selected.

Elapsed: 00:02:01.25

------------------
()
So ... A new Plan Hash Value and some performance obtained with the correction on the HASH_JOIN cardinality of the Id= 9 of the new plan[173K vs 173k].
I' ve here introduced here a new interesting "tool of the trade".
The xxplan script taken from a blog post of Randolf Geist of 23.12.2011 that is a precursor of the wonderful tool that is ASH_PLAN.
Ok. It's produces little (really a lot) messy plan, but the formatting here has some
limit and I find really useful to add some other element to my analysis that xxplan can add.
For now what is indicated as [A-Time Self] (time elapsed for A SINGLE STEP] and E-Rows+Starts which is the number to compare with the E-Rows to correct teh cardinalities estimates.
Here come in play also an interesting new actor. The BF of the plan (aka BLOOM FILTER).
An Oracle 11g features to speed up Hash Join.

------------------

SQL> @../xxplan 5mx0wavtzxdmd 0
SQL_ID  5mx0wavtzxdmd, child number 0
-------------------------------------
SELECT /*+                 leading(PM PC)                 CARDINALITY
(PC 331000) OPT_ESTIMATE(@"SEL$AE39D3DB", JOIN, ("VPRC80S"@"SEL$2",
"PC"@"SEL$1"), SCALE_ROWS=2) OPT_ESTIMATE(@"SEL$AE39D3DB", JOIN,
("VPRC80S"@"SEL$2", "PC"@"SEL$1" ,"VPRE65S"@"SEL$3"), SCALE_ROWS=115) )
*/ O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)|SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) * (NVL(D.IMP_ADDIZIONALI,0) +
NVL(D.IMP_DIRITTI_NETTO ,0) + NVL(D.IMP_IMPORTO_NETTO,0) +
NVL(D.IMP_IMPOSTA,0) + NVL( D.IMP_IMPOSTA_DIRITTI,0) +
NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT FROM VPO.MOV_CONTABILE_S
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O,
VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND
MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND
MC.ID_DATA_INIZIO_VAL


Plan hash value: 3607434300

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                   | Starts | E-Rows |E-Temp | Pstart| Pstop | A-Rows | Buffers |  OMem |  1Mem | Used-Mem | A-Time Self |E-Rows*Sta|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                        |      1 |        |       |       |       |     13 |    4808K|       |       |          | 00:00:00.00 |          |
|   1 |  HASH GROUP BY                          |                        |      1 |   4992 |  1608K|       |       |     13 |    4808K|   761K|   761K| 1235K (0)| 00:00:00.53 |     4992 |
|*  2 |   HASH JOIN                             |                        |      1 |   4992 |       |       |       |    178K|    4808K|   909K|   909K| 1256K (0)| 00:00:00.27 |     4992 |
|*  3 |    TABLE ACCESS FULL                    | FONDO_S                |      1 |     90 |       |       |       |     88 |      67 |       |       |          | 00:00:00.01 |       90 |
|   4 |    NESTED LOOPS                         |                        |      1 |        |       |       |       |    178K|    4808K|       |       |          | 00:00:00.76 |          |
|   5 |     NESTED LOOPS                        |                        |      1 |   1239 |       |       |       |   1372K|    3884K|       |       |          | 00:00:00.48 |     1239 |
|   6 |      NESTED LOOPS                       |                        |      1 |   1341 |       |       |       |    179K|    3400K|       |       |          | 00:00:00.16 |     1341 |
|*  7 |       HASH JOIN                         |                        |      1 |   1225 |       |       |       |    167K|    2967K|  1134K|  1134K|  661K (0)| 00:00:00.18 |     1225 |
|*  8 |        TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S   |      1 |      3 |       |       |       |      4 |       6 |       |       |          | 00:00:00.00 |        3 |
|*  9 |        HASH JOIN                        |                        |      1 |    173K|  7624K|       |       |    173K|    2967K|  6547K|  2254K| 9551K (0)| 00:00:01.72 |      173K|
|  10 |         PART JOIN FILTER CREATE         | :BF0000                |      1 |  76489 |       |       |       |  73515 |     143K|       |       |          | 00:00:00.04 |    76489 |
|* 11 |          HASH JOIN                      |                        |      1 |  76489 |       |       |       |  73515 |     143K|   955K|   955K| 1266K (0)| 00:00:00.17 |    76489 |
|* 12 |           TABLE ACCESS FULL             | PRODOTTO_COMMERCIALE_S |      1 |     41 |       |       |       |     18 |     113 |       |       |          | 00:00:00.01 |       41 |
|* 13 |           TABLE ACCESS FULL             | PRODOTTO_CTR_S         |      1 |    331K|       |       |       |    331K|     142K|       |       |          | 00:00:04.54 |      331K|
|  14 |         PARTITION RANGE JOIN-FILTER     |                        |      1 |   4280K|       |:BF0000|:BF0000|   2860K|    2824K|       |       |          | 00:00:00.69 |     4280K|
|* 15 |          TABLE ACCESS FULL              | MOV_CONTABILE_S        |      9 |   4280K|       |:BF0000|:BF0000|   2860K|    2824K|       |       |          | 00:00:45.55 |       39M|
|  16 |       PARTITION RANGE ITERATOR          |                        |    167K|      1 |       |   KEY |   KEY |    179K|     432K|       |       |          | 00:00:00.43 |      168K|
|* 17 |        TABLE ACCESS BY LOCAL INDEX ROWID| DTL_MOV_CONTABILE      |    167K|      1 |       |   KEY |   KEY |    179K|     432K|       |       |          | 00:00:12.84 |      168K|
|* 18 |         INDEX RANGE SCAN                | VPO075_VPO070_FK_I     |    167K|      4 |       |   KEY |   KEY |    366K|     284K|       |       |          | 00:00:05.23 |      672K|
|* 19 |      INDEX RANGE SCAN                   | TRANCHE_S_P01          |    179K|      7 |       |       |       |   1372K|     484K|       |       |          | 00:00:07.20 |     1256K|
|* 20 |     TABLE ACCESS BY GLOBAL INDEX ROWID  | TRANCHE_S              |   1372K|      1 |       | ROWID | ROWID |    178K|     923K|       |       |          | 00:00:40.44 |     1373K|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Can someone suggest some other enhancement ?
Thanks

Paolo

[Updated on: Thu, 30 April 2015 10:33]

Report message to a moderator

Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #636724 is a reply to message #636190] Thu, 30 April 2015 16:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
try these hints to see if the cardinality estimates are the cause of your problem. Don't use any other hints but these.

cardinality(PC 331009)
cardinality(MC 9383891)
cardinality(D 82185765)
cardinality(TR 40296608)

with these updated cardinalities, the table VPRC80S should become the driving table and you should get a precision style query plan (assuming the necessary indexes are in place).

If this pans out then you should look into statistics collection to correct the problem.

Kevin
Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #637053 is a reply to message #636190] Fri, 08 May 2015 11:01 Go to previous messageGo to next message
paolfili
Messages: 6
Registered: March 2015
Location: Italy
Junior Member
Thanks Kevin for your reply.


But ....Seems NOT to work...


SQL> @../xplan
SQL_ID  910vpaathd6d1, child number 0
-------------------------------------
SELECT /*+ cardinality(PC 331009) cardinality(MC 9383891) cardinality(D
82185765) cardinality(TR 40296608) */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
---------------------SNAP-----------------------------------------
MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <MC.ID_DATA_FINE_VAL AND
MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < MC.ID_TIMESTAMP_FINE_VAL
AND MC.SEQ_MOV_CONTABILE=D.SEQ_MOV_CONTABILE AND D.SEQ_TRAN

Plan hash value: 4174829704

----------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |      1 |        |
|   1 |  HASH GROUP BY                           |                         |      1 |   5589 |
|*  2 |   HASH JOIN                              |                         |      1 |   5589 |
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |
|   4 |    NESTED LOOPS                          |                         |      1 |        |
|   5 |     NESTED LOOPS                         |                         |      1 |   1387 |
|   6 |      NESTED LOOPS                        |                         |      1 |   2371 |
|   7 |       NESTED LOOPS                       |                         |      1 |   1282 |
|*  8 |        HASH JOIN                         |                         |      1 |    296 |
|*  9 |         TABLE ACCESS FULL                | PRODOTTO_COMMERCIALE_S  |      1 |     41 |
|  10 |         NESTED LOOPS                     |                         |      1 |        |
|  11 |          NESTED LOOPS                    |                         |      1 |   2341 |
|* 12 |           TABLE ACCESS FULL              | OGGETTO_DOC_STAMPA_S    |      1 |      3 |
|* 13 |           INDEX RANGE SCAN               | VPO080_VPRC80_FK_IS     |      1 |  17301 |
|* 14 |          TABLE ACCESS BY INDEX ROWID     | PRODOTTO_CTR_S          |  60787 |    764 |
|  15 |        PARTITION RANGE ITERATOR          |                         |  10610 |      4 |
|* 16 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  10610 |      4 |
|* 17 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  10610 |     32 |
|  18 |       PARTITION RANGE ITERATOR           |                         |  25710 |      2 |
|* 19 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |  25710 |      2 |
|* 20 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |  25710 |      4 |
|* 21 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |  27748 |      7 |
|* 22 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |    218K|      1 |
----------------------------------------------------------------------------------------------


Neither adding all cardinalities values works ....
SQL> @../xplan
SQL_ID  dngq4cvtfupfa, child number 0
-------------------------------------
SELECT /*+ cardinality(F 88) cardinality(PM 18) cardinality(O 4)
cardinality(PC 331009) cardinality(MC 9383891) cardinality(D 82185765)
cardinality(TR 40296608) */ O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_P
ROD_COMM, DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBST
------------------SNAP---------------------
:B1 < MC.ID_TIMESTAMP_FINE_VAL AND M

Plan hash value: 4174829704

----------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |      1 |        |
|   1 |  HASH GROUP BY                           |                         |      1 |   3113 |
|*  2 |   HASH JOIN                              |                         |      1 |   3113 |
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     88 |
|   4 |    NESTED LOOPS                          |                         |      1 |        |
|   5 |     NESTED LOOPS                         |                         |      1 |    786 |
|   6 |      NESTED LOOPS                        |                         |      1 |   1344 |
|   7 |       NESTED LOOPS                       |                         |      1 |    727 |
|*  8 |        HASH JOIN                         |                         |      1 |    168 |
|*  9 |         TABLE ACCESS FULL                | PRODOTTO_COMMERCIALE_S  |      1 |     18 |
|  10 |         NESTED LOOPS                     |                         |      1 |        |
|  11 |          NESTED LOOPS                    |                         |      1 |   3058 |
|* 12 |           TABLE ACCESS FULL              | OGGETTO_DOC_STAMPA_S    |      1 |      4 |
|* 13 |           INDEX RANGE SCAN               | VPO080_VPRC80_FK_IS     |      1 |  17301 |
|* 14 |          TABLE ACCESS BY INDEX ROWID     | PRODOTTO_CTR_S          |  36295 |    764 |
|  15 |        PARTITION RANGE ITERATOR          |                         |   5371 |      4 |
|* 16 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |   5371 |      4 |
|* 17 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |   5371 |     32 |
|  18 |       PARTITION RANGE ITERATOR           |                         |  11418 |      2 |
|* 19 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |  11418 |      2 |
|* 20 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |  11418 |      4 |
|* 21 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |  12264 |      7 |
|* 22 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |  95243 |      1 |
----------------------------------------------------------------------------------------------



Slow by slow ... Here is what works ....

If we try to fix ONLY the cardinality for a table (i.e. PC) does not work
....


SQL> @../xplan
SQL_ID  gj4920aj4cxdu, child number 0
-------------------------------------
SELECT /*+ cardinality(PC 11) --cardinality(MC 9383891) --cardinality(D
82185765) */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
--------------SNAP--------------------------------------
AND MC.SEQ_MOV_CONTABILE=D.SEQ_MOV_CONTABILE AND D.SEQ_TR

Plan hash value: 3920407859

-------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Starts | E-Rows |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |      1 |        |
|   1 |  HASH GROUP BY                           |                            |      1 |      4 |
|*  2 |   HASH JOIN                              |                            |      1 |      4 |
|   3 |    NESTED LOOPS                          |                            |      1 |        |
|   4 |     NESTED LOOPS                         |                            |      1 |      1 |
|   5 |      NESTED LOOPS                        |                            |      1 |      1 |
|   6 |       NESTED LOOPS                       |                            |      1 |      1 |
|   7 |        NESTED LOOPS                      |                            |      1 |      1 |
|   8 |         NESTED LOOPS                     |                            |      1 |      3 |
|*  9 |          TABLE ACCESS FULL               | OGGETTO_DOC_STAMPA_S       |      1 |      3 |
|* 10 |          TABLE ACCESS BY INDEX ROWID     | PRODOTTO_CTR_S             |      1 |      1 |
|* 11 |           INDEX RANGE SCAN               | VPO080_VPRC80_FK_IS        |      1 |  17301 |
|* 12 |         TABLE ACCESS BY INDEX ROWID      | PRODOTTO_COMMERCIALE_S     |   5350 |      1 |
|* 13 |          INDEX RANGE SCAN                | PRODOTTO_COMMERCIALE_S_P01 |   5350 |      4 |
|  14 |        PARTITION RANGE ITERATOR          |                            |   5350 |      2 |
|* 15 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S            |   5350 |      2 |
|* 16 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001    |   5350 |     32 |
|  17 |       PARTITION RANGE ITERATOR           |                            |  11348 |      1 |
|* 18 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE          |  11348 |      1 |
|* 19 |         INDEX RANGE SCAN                 | DTL_MOV_CONTABILE_IDX_001  |  11348 |      1 |
|* 20 |      INDEX RANGE SCAN                    | TRANCHE_S_P01              |  12190 |      7 |
|* 21 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S                  |  94630 |      1 |
|* 22 |    TABLE ACCESS FULL                     | FONDO_S                    |      0 |     90 |
-------------------------------------------------------------------------------------------------





What really works is fixing the Leading Pairs....



SQL> @../xplan
SQL_ID  4s7wa1mvqxv24, child number 0
-------------------------------------
SELECT /*+                 LEADING("VPRC80S"@"SEL$2" "PC"@"SEL$1")
           cardinality(PC 331009) */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) * (NVL(D.IMP_ADDIZIONALI,0) +
NVL(D.IMP_DIRITTI_NETTO ,0) + NVL(D.IMP_IMPORTO_NETTO,0) +
NVL(D.IMP_IMPOSTA,0) + NVL( D.IMP_IMPOSTA_DIRITTI,0) +
NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT FROM VPO.MOV_CONTABILE_S
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O,
VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND
MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND
MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <MC.ID_DATA_FINE_VAL AND
MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < MC.ID_TIMESTAMP_FINE_VAL
AND MC.SEQ_MOV_CONTABILE=D.SEQ_MOV_CONTABILE AND D.SEQ_TRANC

Plan hash value: 2381553728

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |      1 |        |     13 |
|   1 |  HASH GROUP BY                           |                         |      1 |   2496 |     13 |
|*  2 |   HASH JOIN                              |                         |      1 |   2496 |    178K|
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |     88 |
|   4 |    NESTED LOOPS                          |                         |      1 |        |    178K|
|   5 |     NESTED LOOPS                         |                         |      1 |    620 |   1372K|
|   6 |      NESTED LOOPS                        |                         |      1 |    671 |    179K|
|   7 |       NESTED LOOPS                       |                         |      1 |    612 |    167K|
|*  8 |        HASH JOIN                         |                         |      1 |    271 |  62430 |
|*  9 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      3 |      4 |
|* 10 |         HASH JOIN                        |                         |      1 |  38245 |  73515 |
|* 11 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     41 |     18 |
|* 12 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|    331K|
|  13 |        PARTITION RANGE ITERATOR          |                         |  62430 |      2 |    167K|
|* 14 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      2 |    167K|
|* 15 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  62430 |     32 |    818K|
|  16 |       PARTITION RANGE ITERATOR           |                         |    167K|      1 |    179K|
|* 17 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |    167K|      1 |    179K|
|* 18 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |    167K|      4 |    366K|
|* 19 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |    179K|      7 |   1372K|
|* 20 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |   1372K|      1 |    178K|
-------------------------------------------------------------------------------------------------------



Let's add some Cardinality Hints

-----

SQL> @../xplan
SQL_ID  c34mngh8yx6ad, child number 0
-------------------------------------
SELECT /*+                 LEADING("VPRC80S"@"SEL$2" "PC"@"SEL$1")
           cardinality(PC 331009)                 cardinality(MC
9383891)                 cardinality(D 82185765)
cardinality(TR 40296608) */ O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_P
ROD_COMM, DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBST
R(F.TIPO_FND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND)
TIPO_FND, SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) *
(NVL(D.IMP_ADDIZIONALI,0) + NVL(D.IMP_DIRITTI_NETTO ,0) +
NVL(D.IMP_IMPORTO_NETTO,0) + NVL(D.IMP_IMPOSTA,0) + NVL(
D.IMP_IMPOSTA_DIRITTI,0) + NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT
FROM VPO.MOV_CONTABILE_S MC, VPO.DTL_MOV_CONTABILE D,
VPO.PRODOTTO_CTR_S PC, VPR.PRODOTTO_COMMERCIALE_V PM,
VPR.OGGETTO_DOC_STAMPA_V O, VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE
MC.TIPO_MOV_CONTABILE='001' AND MC.DATA_EFF_COPERTURA>=:B4 AND
MC.COD_STATO_MOVIMENTO IN ('I','E') AND MC.ID_DATA_INIZIO_VAL <= :B2
AND :B2 <MC.ID_DATA_FINE_VAL AND MC.ID_TI

Plan hash value: 2381553728

----------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |      1 |        |
|   1 |  HASH GROUP BY                           |                         |      1 |   5103 |
|*  2 |   HASH JOIN                              |                         |      1 |   5103 |
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |
|   4 |    NESTED LOOPS                          |                         |      1 |        |
|   5 |     NESTED LOOPS                         |                         |      1 |   1267 |
|   6 |      NESTED LOOPS                        |                         |      1 |   2165 |
|   7 |       NESTED LOOPS                       |                         |      1 |   1171 |
|*  8 |        HASH JOIN                         |                         |      1 |    271 |
|*  9 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      3 |
|* 10 |         HASH JOIN                        |                         |      1 |  38245 |
|* 11 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     41 |
|* 12 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|
|  13 |        PARTITION RANGE ITERATOR          |                         |  10405 |      4 |
|* 14 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  10405 |      4 |
|* 15 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  10405 |     32 |
|  16 |       PARTITION RANGE ITERATOR           |                         |  21731 |      2 |
|* 17 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |  21731 |      2 |
|* 18 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |  21731 |      4 |
|* 19 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |  23525 |      7 |
|* 20 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |    181K|      1 |
------------------------------------------

---

Nothing to do .... Sad
Let' s try to make one step forward

/*+
LEADING("VPRC80S"@"SEL$2" "PC"@"SEL$1")
cardinality(PC 331009)
cardinality(PM 18)
*/

--------

SQL> @../xplan
SQL_ID  60arck6rtzquq, child number 0
-------------------------------------
SELECT /*+                 LEADING("VPRC80S"@"SEL$2" "PC"@"SEL$1")
           cardinality(PC 331009)                 cardinality(PM  18)
*/ O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) * (NVL(D.IMP_ADDIZIONALI,0) +
NVL(D.IMP_DIRITTI_NETTO ,0) + NVL(D.IMP_IMPORTO_NETTO,0) +
NVL(D.IMP_IMPOSTA,0) + NVL( D.IMP_IMPOSTA_DIRITTI,0) +
NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT FROM VPO.MOV_CONTABILE_S
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O,
VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND
MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND
MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <MC.ID_DATA_FINE_VAL AND
MC.ID_TIMESTAMP_INIZIO_VAL <= :B1 AND :B1 < MC.ID_TIMESTAMP_FINE_VAL
AND MC.SEQ_MOV_CONTABILE

Plan hash value: 2381553728

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |      1 |        |     13 |
|   1 |  HASH GROUP BY                           |                         |      1 |   1083 |     13 |
|*  2 |   HASH JOIN                              |                         |      1 |   1083 |    178K|
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |     88 |
|   4 |    NESTED LOOPS                          |                         |      1 |        |    178K|
|   5 |     NESTED LOOPS                         |                         |      1 |    269 |   1372K|
|   6 |      NESTED LOOPS                        |                         |      1 |    291 |    179K|
|   7 |       NESTED LOOPS                       |                         |      1 |    266 |    167K|
|*  8 |        HASH JOIN                         |                         |      1 |    117 |  62430 |
|*  9 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      3 |      4 |
|* 10 |         HASH JOIN                        |                         |      1 |  16593 |  73515 |
|* 11 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     18 |     18 |
|* 12 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|    331K|
|  13 |        PARTITION RANGE ITERATOR          |                         |  62430 |      2 |    167K|
|* 14 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      2 |    167K|
|* 15 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  62430 |     32 |    818K|
|  16 |       PARTITION RANGE ITERATOR           |                         |    167K|      1 |    179K|
|* 17 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |    167K|      1 |    179K|
|* 18 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |    167K|      4 |    366K|
|* 19 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |    179K|      7 |   1372K|
|* 20 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |   1372K|      1 |    178K|
-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------



Step by Step It's time to add O table

/*+
LEADING(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2" "PC"@"SEL$1" "VPRE65S"@"SEL$3")
cardinality(PC 331009)
cardinality(PM 18)
cardinality (O 4)
*/


------------------------------------------------------------
SQL> @../xplan
SQL_ID  95gsduhp58zz2, child number 0
-------------------------------------
SELECT /*+                 LEADING(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2"
"PC"@"SEL$1" "VPRE65S"@"SEL$3")                 cardinality(PC 331009)
               cardinality(PM  18)                 cardinality (O 4) */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)||SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
--------------SNAP--------------
MC.ID_TIMESTAMP_INIZIO_VA

Plan hash value: 2381553728

-------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                    | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                         |      1 |        |     13 |
|   1 |  HASH GROUP BY                           |                         |      1 |   1415 |     13 |
|*  2 |   HASH JOIN                              |                         |      1 |   1415 |    178K|
|*  3 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |     88 |
|   4 |    NESTED LOOPS                          |                         |      1 |        |    178K|
|   5 |     NESTED LOOPS                         |                         |      1 |    351 |   1372K|
|   6 |      NESTED LOOPS                        |                         |      1 |    380 |    179K|
|   7 |       NESTED LOOPS                       |                         |      1 |    347 |    167K|
|*  8 |        HASH JOIN                         |                         |      1 |    153 |  62430 |
|*  9 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      4 |      4 |
|* 10 |         HASH JOIN                        |                         |      1 |  16593 |  73515 |
|* 11 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     18 |     18 |
|* 12 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|    331K|
|  13 |        PARTITION RANGE ITERATOR          |                         |  62430 |      2 |    167K|
|* 14 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      2 |    167K|
|* 15 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  62430 |     32 |    818K|
|  16 |       PARTITION RANGE ITERATOR           |                         |    167K|      1 |    179K|
|* 17 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |    167K|      1 |    179K|
|* 18 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |    167K|      4 |    366K|
|* 19 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |    179K|      7 |   1372K|
|* 20 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |   1372K|      1 |    178K|
-------------------------------------------------------------------------------------------------------

-----------------------------------------------------------

We try


/*+
LEADING(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2" "PC"@"SEL$1" "VPRE65S"@"SEL$3" "MC"@"SEL$1")
cardinality(PC 331009)
cardinality(PM 18)
cardinality (O 4)
cardinality (MC 3)
*/



----------------------------------------------------------
SQL> @../xxplan
SQL_ID  241rc63abts9k, child number 0
-------------------------------------
SELECT /*+                 LEADING(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2"
"PC"@"SEL$1" "VPRE65S"@"SEL$3" "MC"@"SEL$1")
cardinality(PC 331009)                 cardinality(PM  18)
   cardinality (O 4)                 cardinality (MC 3) */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)|SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
SUM(DECODE(COD_TIPO_FLUSSO,'1',-1,'0',1) * (NVL(D.IMP_ADDIZIONALI,0) +
NVL(D.IMP_DIRITTI_NETTO ,0) + NVL(D.IMP_IMPORTO_NETTO,0) +
NVL(D.IMP_IMPOSTA,0) + NVL( D.IMP_IMPOSTA_DIRITTI,0) +
NVL(D.IMP_INTERES_RITDO_PAG,0) )) AS IMP_TOT FROM VPO.MOV_CONTABILE_S
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAMPA_V O,
VPO.TRANCHE_S TR, VPR.FONDO_V F WHERE MC.TIPO_MOV_CONTABILE='001' AND
MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOVIMENTO IN ('I','E') AND
MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2 <M




Plan hash value: 2381553728

-------------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                                | Name                    | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------------------------------------------
|   0 |     |  21 | SELECT STATEMENT                         |                         |      1 |        |     13 |
|   1 |   0 |  20 |  HASH GROUP BY                           |                         |      1 |    625 |     13 |
|*  2 |   1 |  19 |   HASH JOIN                              |                         |      1 |    625 |    178K|
|*  3 |   2 |   1 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |     88 |
|   4 |   2 |  18 |    NESTED LOOPS                          |                         |      1 |        |    178K|
|   5 |   4 |  16 |     NESTED LOOPS                         |                         |      1 |    155 |   1372K|
|   6 |   5 |  14 |      NESTED LOOPS                        |                         |      1 |    168 |    179K|
|   7 |   6 |  10 |       NESTED LOOPS                       |                         |      1 |    153 |    167K|
|*  8 |   7 |   6 |        HASH JOIN                         |                         |      1 |    153 |  62430 |
|*  9 |   8 |   2 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      4 |      4 |
|* 10 |   8 |   5 |         HASH JOIN                        |                         |      1 |  16593 |  73515 |
|* 11 |  10 |   3 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     18 |     18 |
|* 12 |  10 |   4 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|    331K|
|  13 |   7 |   9 |        PARTITION RANGE ITERATOR          |                         |  62430 |      1 |    167K|
|* 14 |  13 |   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      1 |    167K|
|* 15 |  14 |   7 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  62430 |     32 |    818K|
|  16 |   6 |  13 |       PARTITION RANGE ITERATOR           |                         |    167K|      1 |    179K|
|* 17 |  16 |  12 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |    167K|      1 |    179K|
|* 18 |  17 |  11 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |    167K|      4 |    366K|
|* 19 |   5 |  15 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |    179K|      7 |   1372K|
|* 20 |   4 |  17 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |   1372K|      1 |    178K|
-------------------------------------------------------------------------------------------------------------------

--------------

For the first time cardinality is not imposed as requested from hint on MC
Anyway E-Rows*Starts is close enough to A-Rows for MC.
BELOW the complete plan
The other ratios between E-Rows*Starts/A-Rows is close enough to 1 so ....



SQL_ID  c2xhvyr9h9pj7, child number 0
-------------------------------------
SELECT /*+                 LEADING(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2"
"PC"@"SEL$1" "VPRE65S"@"SEL$3" "MC"@"SEL$1")
cardinality(PC 331009)                 cardinality(PM  18)
   cardinality (O 4)             --    cardinality (MC 2) */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
-----------SNAP---------------------------
MC.ID_DATA_INIZIO_VAL <= :B2 AND :B2

Plan hash value: 2381553728

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                                | Name                    | Starts | E-Rows |E-Bytes| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 |     |  21 | SELECT STATEMENT                         |                         |      1 |        |       |     13 |00:04:17.67 |    3130K|       |       | 
|   1 |   0 |  20 |  HASH GROUP BY                           |                         |      1 |   1415 |   424K|     13 |00:04:17.67 |    3130K|   761K|   761K| 
|*  2 |   1 |  19 |   HASH JOIN                              |                         |      1 |   1415 |   424K|    178K|00:04:16.80 |    3130K|   909K|   909K| 
|*  3 |   2 |   1 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |  3600 |     88 |00:00:00.01 |      67 |       |       | 
|   4 |   2 |  18 |    NESTED LOOPS                          |                         |      1 |        |       |    178K|00:04:16.33 |    3130K|       |       | 
|   5 |   4 |  16 |     NESTED LOOPS                         |                         |      1 |    351 | 93717 |   1372K|00:02:55.86 |    2206K|       |       | 
|   6 |   5 |  14 |      NESTED LOOPS                        |                         |      1 |    380 | 83220 |    179K|00:02:21.80 |    1664K|       |       | 
|   7 |   6 |  10 |       NESTED LOOPS                       |                         |      1 |    347 | 62113 |    167K|00:01:25.61 |    1156K|       |       | 
|*  8 |   7 |   6 |        HASH JOIN                         |                         |      1 |    153 | 17595 |  62430 |00:00:04.52 |     143K|  1134K|  1134K| 
|*  9 |   8 |   2 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      4 |   100 |      4 |00:00:00.01 |       6 |       |       | 
|* 10 |   8 |   5 |         HASH JOIN                        |                         |      1 |  16593 |  1458K|  73515 |00:00:04.40 |     143K|   955K|   955K| 
|* 11 |  10 |   3 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     18 |   684 |     18 |00:00:00.01 |     113 |       |       | 
|* 12 |  10 |   4 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|    16M|    331K|00:00:04.12 |     142K|       |       | 
|  13 |   7 |   9 |        PARTITION RANGE ITERATOR          |                         |  62430 |      2 |   128 |    167K|00:01:20.97 |    1013K|       |       | 
|* 14 |  13 |   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      2 |   128 |    167K|00:01:20.70 |    1013K|       |       | 
|* 15 |  14 |   7 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  62430 |     32 |       |    818K|00:00:26.06 |     207K|       |       | 
|  16 |   6 |  13 |       PARTITION RANGE ITERATOR           |                         |    167K|      1 |    40 |    179K|00:00:55.96 |     507K|       |       | 
|* 17 |  16 |  12 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |    167K|      1 |    40 |    179K|00:00:55.29 |     507K|       |       | 
|* 18 |  17 |  11 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |    167K|      4 |       |    366K|00:00:20.67 |     337K|       |       | 
|* 19 |   5 |  15 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |    179K|      7 |       |   1372K|00:00:33.52 |     542K|       |       | 
|* 20 |   4 |  17 |     TABLE ACCESS BY GLOBAL INDEX ROWID   | TRANCHE_S               |   1372K|      1 |    48 |    178K|00:01:19.54 |     923K|       |       | 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$AE39D3DB
   3 - SEL$AE39D3DB / FONDO_S@SEL$4
   9 - SEL$AE39D3DB / VPRE65S@SEL$3
  11 - SEL$AE39D3DB / VPRC80S@SEL$2
  12 - SEL$AE39D3DB / PC@SEL$1
  14 - SEL$AE39D3DB / MC@SEL$1
  15 - SEL$AE39D3DB / MC@SEL$1
  17 - SEL$AE39D3DB / D@SEL$1
  18 - SEL$AE39D3DB / D@SEL$1
  19 - SEL$AE39D3DB / TR@SEL$1
  20 - SEL$AE39D3DB / TR@SEL$1

Outline Data
-------------

  /*+
      ############SNAP################################
	  USE_NL(@"SEL$AE39D3DB" "TR"@"SEL$1")
      NLJ_BATCHING(@"SEL$AE39D3DB" "TR"@"SEL$1")
      ##########SNAP##################################
	  USE_HASH_AGGREGATION(@"SEL$AE39D3DB")
      END_OUTLINE_DATA
  */

  20 - "TR"."SEQ_FONDO"[NUMBER,22]





THE OPTIMIZER HAS ESTIMATED FAIRLY WELL CARDINALITIES --- > The plan SHOULD BE NEAR OPTIMAL ....
But we can make some exploring with the NO_NLJ_BATCHING hint


---------------

SQL> @Query_5yag3uqq0fyz8_Lead10.sql
5004                              1000004118 419EY CONVENZIONE              GS 1475093.02
-----------------------------SNAP-----------------------------------------------------------
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 4655567.43

13 rows selected.



Elapsed: 00:02:49.77
SQL> @../xxplan
SQL_ID  7pmvk30rbkt5g, child number 1
-------------------------------------
SELECT /*+                 LEADING(@"SEL$AE39D3DB" "VPRC80S"@"SEL$2"
"PC"@"SEL$1" "VPRE65S"@"SEL$3" "MC"@"SEL$1")
cardinality(PC 331009)                 cardinality(PM  18)
   cardinality (O 4)                 cardinality (MC .2)
 NO_NLJ_BATCHING(@"SEL$AE39D3DB" "TR"@"SEL$1") */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
-----SNAP-------------------------------------------
MC.DATA_EFF_COPERTURA>=:B4 AND MC.COD_STATO_MOV

Plan hash value: 3503732798

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                                | Name                    | Starts | E-Rows | A-Rows | Buffers |  1Mem | A-Time Self |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 |     |  20 | SELECT STATEMENT                         |                         |      1 |        |     13 |    3130K|       | 00:00:00.00 |
|   1 |   0 |  19 |  HASH GROUP BY                           |                         |      1 |    312 |     13 |    3130K|   761K| 00:00:00.42 |
|*  2 |   1 |  18 |   HASH JOIN                              |                         |      1 |    312 |    178K|    3130K|   909K| 00:00:00.31 |
|*  3 |   2 |   1 |    TABLE ACCESS FULL                     | FONDO_S                 |      1 |     90 |     88 |      67 |       | 00:00:00.01 |
|*  4 |   2 |  17 |    TABLE ACCESS BY GLOBAL INDEX ROWID    | TRANCHE_S               |      1 |      1 |    178K|    3130K|       | 00:00:23.31 |
|   5 |   4 |  16 |     NESTED LOOPS                         |                         |      1 |     78 |   1552K|    2206K|       | 00:00:00.53 |
|   6 |   5 |  14 |      NESTED LOOPS                        |                         |      1 |     84 |    179K|    1664K|       | 00:00:00.19 |
|   7 |   6 |  10 |       NESTED LOOPS                       |                         |      1 |     77 |    167K|    1157K|       | 00:00:00.10 |
|*  8 |   7 |   6 |        HASH JOIN                         |                         |      1 |    153 |  62430 |     143K|   947K| 00:00:00.14 |
|*  9 |   8 |   2 |         TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S    |      1 |      4 |      4 |       6 |       | 00:00:00.00 |
|* 10 |   8 |   5 |         HASH JOIN                        |                         |      1 |  16593 |  73515 |     143K|   872K| 00:00:00.26 |
|* 11 |  10 |   3 |          TABLE ACCESS FULL               | PRODOTTO_COMMERCIALE_S  |      1 |     18 |     18 |     113 |       | 00:00:00.01 |
|* 12 |  10 |   4 |          TABLE ACCESS FULL               | PRODOTTO_CTR_S          |      1 |    331K|    331K|     142K|       | 00:00:03.43 |
|  13 |   7 |   9 |        PARTITION RANGE ITERATOR          |                         |  62430 |      1 |    167K|    1013K|       | 00:00:00.20 |
|* 14 |  13 |   8 |         TABLE ACCESS BY LOCAL INDEX ROWID| MOV_CONTABILE_S         |  62430 |      1 |    167K|    1013K|       | 00:01:17.25 |
|* 15 |  14 |   7 |          INDEX RANGE SCAN                | MOV_CONTABILE_S_IDX_001 |  62430 |     32 |    818K|     207K|       | 00:00:23.40 |
|  16 |   6 |  13 |       PARTITION RANGE ITERATOR           |                         |    167K|      1 |    179K|     507K|       | 00:00:00.43 |
|* 17 |  16 |  12 |        TABLE ACCESS BY LOCAL INDEX ROWID | DTL_MOV_CONTABILE       |    167K|      1 |    179K|     507K|       | 00:00:21.59 |
|* 18 |  17 |  11 |         INDEX RANGE SCAN                 | VPO075_VPO070_FK_I      |    167K|      4 |    366K|     337K|       | 00:00:07.65 |
|* 19 |   5 |  15 |      INDEX RANGE SCAN                    | TRANCHE_S_P01           |    179K|      7 |   1372K|     542K|       | 00:00:10.33 |
---------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$AE39D3DB
   3 - SEL$AE39D3DB / FONDO_S@SEL$4
   4 - SEL$AE39D3DB / TR@SEL$1
   9 - SEL$AE39D3DB / VPRE65S@SEL$3
  11 - SEL$AE39D3DB / VPRC80S@SEL$2
  12 - SEL$AE39D3DB / PC@SEL$1
  14 - SEL$AE39D3DB / MC@SEL$1
  15 - SEL$AE39D3DB / MC@SEL$1
  17 - SEL$AE39D3DB / D@SEL$1
  18 - SEL$AE39D3DB / D@SEL$1
  19 - SEL$AE39D3DB / TR@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      ###############SNAP###########################
      USE_NL(@"SEL$AE39D3DB" "TR"@"SEL$1")
      NLJ_PREFETCH(@"SEL$AE39D3DB" "TR"@"SEL$1")
      USE_HASH(@"SEL$AE39D3DB" "FONDO_S"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$AE39D3DB" "VPRE65S"@"SEL$3")
      SWAP_JOIN_INPUTS(@"SEL$AE39D3DB" "FONDO_S"@"SEL$4")
      USE_HASH_AGGREGATION(@"SEL$AE39D3DB")
      END_OUTLINE_DATA
  */

  14 - (NUMBER, Primary=4)

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TR"."SEQ_FONDO"="SEQ_FONDO" AND "CODICE_COMPAGNIA_PVG"="CODICE_COMPAGNIA_PVG")
   3 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "CODICE_COMPAGNIA_PVG"=:B3))
   4 - filter(("TR"."ID_TIMESTAMP_FINE_VAL">:B1 AND "TR"."ID_DATA_FINE_VAL">:B2 AND "TR"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "TR"."ID_DATA_INIZIO_VAL"<=:B2))
   8 - access("PC"."SEQ_PROD_COMM"="SEQ_OGG")
   9 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "TIPO_OGG"='02' AND "NUM_ISCR_COVIP" IS NOT NULL))
  10 - access("PC"."SEQ_PROD_COMM"="SEQ_PROD_COMM")
  11 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND INTERNAL_FUNCTION("TIPO_PROD_LEGALE") AND "CODICE_COMPAGNIA_PVG"=:B3))
  12 - filter(("PC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "PC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "PC"."COD_STATO_PROD_CTR"='1' AND "PC"."ID_DATA_FINE_VAL">:B2 AND "PC"."ID_DATA_INIZIO_VAL"<=:B2))
  14 - filter(("MC"."DATA_EFF_COPERTURA">=:B4 AND "MC"."TIPO_MOV_CONTABILE"='001' AND INTERNAL_FUNCTION("MC"."COD_STATO_MOVIMENTO")))
  15 - access("MC"."SEQ_RAPPORTO"="PC"."SEQ_RAPPORTO" AND "MC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "MC"."ID_DATA_FINE_VAL">:B2 AND "MC"."ID_DATA_INIZIO_VAL"<=:B2 AND
              "MC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1)
       filter(("MC"."ID_DATA_FINE_VAL">:B2 AND "MC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "MC"."ID_DATA_INIZIO_VAL"<=:B2))
  17 - filter(("D"."SEQ_TRANCHE" IS NOT NULL AND "D"."TIPO_DTL_MOV_CONT_SEC"='001' AND "D"."TIPO_DTL_MOV_CONT"='001'))
  18 - access("MC"."SEQ_MOV_CONTABILE"="D"."SEQ_MOV_CONTABILE")
  19 - access("D"."SEQ_TRANCHE"="TR"."SEQ_TRANCHE")


-------------------------------------------------------------------------------------------------------

Not any relevant different results ... mmhhh ...

Let's try with OPT_ESTIMATE that fudge cardinality of MC et ...
voilĂ  appears .... Bloom Filter .....




SQL> @Query_5yag3uqq0fyz8_HH7_BloomFilters.sql
5004                              1000004118 419EY CONVENZIONE              GS 1475093.02
5004                              1000004118 419EY CONVENZIONE              GS  329982.23
###############################SNAP#######################################################
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 52686508.1
5004                              1000004818 419EY PIP 2012                 UL   82764.63
5004                              1000002898 419EY STANDARD PIP STRATEGICO  GS 4655567.43

13 rows selected.

Elapsed: 00:03:31.29
SQL> @../xxplan
SQL_ID  2g92kzmfg7ghs, child number 0
-------------------------------------
SELECT /*+ LEADING(PM PC) USE_HASH(PM) SWAP_JOIN_INPUTS(PM)
OPT_ESTIMATE(@"SEL$AE39D3DB", TABLE, "VPRC80S"@"SEL$2", ROWS=18)
OPT_ESTIMATE(@"SEL$AE39D3DB", TABLE, "PC"@"SEL$1", ROWS=331000)
OPT_ESTIMATE(@"SEL$AE39D3DB", JOIN, ("VPRC80S"@"SEL$2", "PC"@"SEL$1"),
SCALE_ROWS=4.2) OPT_ESTIMATE(@"SEL$AE39D3DB", TABLE, "VPRE65S"@"SEL$3",
ROWS=4) OPT_ESTIMATE(@"SEL$AE39D3DB", JOIN, ("VPRE65S"@"SEL$3",
"VPRC80S"@"SEL$2", "PC"@"SEL$1"), SCALE_ROWS=200) */
O.NUM_ISCR_COVIP,PM.SEQ_PROD_COMM,PM.DENOM_PROD_COMM,
DECODE(F.CODICE_COMPAGNIA_PVG,'A',DECODE(F.TIPO_FND,'UL',SUBSTR(F.TIPO_F
ND,1,1)|SUBSTR(F.DENOM_FND,9,1),F.TIPO_FND),F.TIPO_FND) TIPO_FND,
########################SNAP##############################################
MC, VPO.DTL_MOV_CONTABILE D, VPO.PRODOTTO_CTR_S PC,
VPR.PRODOTTO_COMMERCIALE_V PM, VPR.OGGETTO_DOC_STAM

Plan hash value: 3607434300

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Pid | Ord | Operation                               | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  1Mem | A-Time Self |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 |     |  21 | SELECT STATEMENT                        |                        |      1 |        |     13 |00:03:31.26 |    4807K|   3200K|       | 00:00:00.00 |
|   1 |   0 |  20 |  HASH GROUP BY                          |                        |      1 |   5941 |     13 |00:03:31.26 |    4807K|   3200K|   761K| 00:00:00.50 |
|*  2 |   1 |  19 |   HASH JOIN                             |                        |      1 |   5941 |    178K|00:03:30.76 |    4807K|   3200K|   909K| 00:00:00.25 |
|*  3 |   2 |   1 |    TABLE ACCESS FULL                    | FONDO_S                |      1 |     90 |     88 |00:00:00.01 |      67 |     66 |       | 00:00:00.01 |
|   4 |   2 |  18 |    NESTED LOOPS                         |                        |      1 |        |    178K|00:03:30.50 |    4807K|   3200K|       | 00:00:00.75 |
|   5 |   4 |  16 |     NESTED LOOPS                        |                        |      1 |   1475 |   1372K|00:02:34.22 |    3883K|   3032K|       | 00:00:00.49 |
|   6 |   5 |  14 |      NESTED LOOPS                       |                        |      1 |   1596 |    179K|00:02:27.77 |    3399K|   3019K|       | 00:00:00.15 |
|*  7 |   6 |  10 |       HASH JOIN                         |                        |      1 |   1457 |    167K|00:02:08.81 |    2967K|   2967K|  1134K| 00:00:00.18 |
|*  8 |   7 |   2 |        TABLE ACCESS FULL                | OGGETTO_DOC_STAMPA_S   |      1 |      4 |      4 |00:00:00.01 |       6 |      6 |       | 00:00:00.00 |
|*  9 |   7 |   9 |        HASH JOIN                        |                        |      1 |    157K|    173K|00:02:08.64 |    2967K|   2967K|  2254K| 00:00:01.72 |
|  10 |   9 |   6 |         PART JOIN FILTER CREATE         | :BF0000                |      1 |  69688 |  73515 |00:00:04.75 |     143K|    143K|       | 00:00:00.04 |
|* 11 |  10 |   5 |          HASH JOIN                      |                        |      1 |  69688 |  73515 |00:00:04.71 |     143K|    143K|   955K| 00:00:00.17 |
|* 12 |  11 |   3 |           TABLE ACCESS FULL             | PRODOTTO_COMMERCIALE_S |      1 |     18 |     18 |00:00:00.01 |     113 |    111 |       | 00:00:00.01 |
|* 13 |  11 |   4 |           TABLE ACCESS FULL             | PRODOTTO_CTR_S         |      1 |    331K|    331K|00:00:04.53 |     142K|    142K|       | 00:00:04.53 |
|  14 |   9 |   8 |         PARTITION RANGE JOIN-FILTER     |                        |      1 |   4280K|   2860K|00:02:02.17 |    2824K|   2824K|       | 00:00:00.69 |
|* 15 |  14 |   7 |          TABLE ACCESS FULL              | MOV_CONTABILE_S        |      9 |   4280K|   2860K|00:02:01.48 |    2824K|   2824K|       | 00:02:01.48 |
|  16 |   6 |  13 |       PARTITION RANGE ITERATOR          |                        |    167K|      1 |    179K|00:00:18.80 |     432K|  52004 |       | 00:00:00.40 |
|* 17 |  16 |  12 |        TABLE ACCESS BY LOCAL INDEX ROWID| DTL_MOV_CONTABILE      |    167K|      1 |    179K|00:00:18.40 |     432K|  52004 |       | 00:00:13.55 |
|* 18 |  17 |  11 |         INDEX RANGE SCAN                | VPO075_VPO070_FK_I     |    167K|      4 |    366K|00:00:04.85 |     284K|  11264 |       | 00:00:04.85 |
|* 19 |   5 |  15 |      INDEX RANGE SCAN                   | TRANCHE_S_P01          |    179K|      7 |   1372K|00:00:05.96 |     483K|  13552 |       | 00:00:05.96 |
|* 20 |   4 |  17 |     TABLE ACCESS BY GLOBAL INDEX ROWID  | TRANCHE_S              |   1372K|      1 |    178K|00:00:55.53 |     923K|    167K|       | 00:00:55.53 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$AE39D3DB
   3 - SEL$AE39D3DB / FONDO_S@SEL$4
   8 - SEL$AE39D3DB / VPRE65S@SEL$3
  12 - SEL$AE39D3DB / VPRC80S@SEL$2
  13 - SEL$AE39D3DB / PC@SEL$1
  15 - SEL$AE39D3DB / MC@SEL$1
  17 - SEL$AE39D3DB / D@SEL$1
  18 - SEL$AE39D3DB / D@SEL$1
  19 - SEL$AE39D3DB / TR@SEL$1
  20 - SEL$AE39D3DB / TR@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ##############SNAP###########################
      USE_NL(@"SEL$AE39D3DB" "D"@"SEL$1")
      USE_NL(@"SEL$AE39D3DB" "TR"@"SEL$1")
      NLJ_BATCHING(@"SEL$AE39D3DB" "TR"@"SEL$1")
      USE_HASH(@"SEL$AE39D3DB" "FONDO_S"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$AE39D3DB" "VPRE65S"@"SEL$3")
      SWAP_JOIN_INPUTS(@"SEL$AE39D3DB" "FONDO_S"@"SEL$4")
      USE_HASH_AGGREGATION(@"SEL$AE39D3DB")
      END_OUTLINE_DATA
  */



Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TR"."SEQ_FONDO"="SEQ_FONDO" AND "CODICE_COMPAGNIA_PVG"="CODICE_COMPAGNIA_PVG")
   3 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "CODICE_COMPAGNIA_PVG"=:B3))
   7 - access("PC"."SEQ_PROD_COMM"="SEQ_OGG")
   8 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND "TIPO_OGG"='02' AND "NUM_ISCR_COVIP" IS NOT NULL))
   9 - access("MC"."SEQ_RAPPORTO"="PC"."SEQ_RAPPORTO")
  11 - access("PC"."SEQ_PROD_COMM"="SEQ_PROD_COMM")
  12 - filter(("ID_TIMESTAMP_FINE_VAL"=9999123199235959 AND INTERNAL_FUNCTION("TIPO_PROD_LEGALE") AND "CODICE_COMPAGNIA_PVG"=:B3))
  13 - filter(("PC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "PC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "PC"."COD_STATO_PROD_CTR"='1' AND "PC"."ID_DATA_FINE_VAL">:B2 AND "PC"."ID_DATA_INIZIO_VAL"<=:B2))
  15 - filter(("MC"."ID_TIMESTAMP_FINE_VAL">:B1 AND "MC"."DATA_EFF_COPERTURA">=:B4 AND "MC"."ID_DATA_FINE_VAL">:B2 AND "MC"."TIPO_MOV_CONTABILE"='001' AND "MC"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND
              INTERNAL_FUNCTION("MC"."COD_STATO_MOVIMENTO") AND "MC"."ID_DATA_INIZIO_VAL"<=:B2))
  17 - filter(("D"."SEQ_TRANCHE" IS NOT NULL AND "D"."TIPO_DTL_MOV_CONT_SEC"='001' AND "D"."TIPO_DTL_MOV_CONT"='001'))
  18 - access("MC"."SEQ_MOV_CONTABILE"="D"."SEQ_MOV_CONTABILE")
  19 - access("D"."SEQ_TRANCHE"="TR"."SEQ_TRANCHE")
  20 - filter(("TR"."ID_TIMESTAMP_FINE_VAL">:B1 AND "TR"."ID_DATA_FINE_VAL">:B2 AND "TR"."ID_TIMESTAMP_INIZIO_VAL"<=:B1 AND "TR"."ID_DATA_INIZIO_VAL"<=:B2))


---------------------------------------------------------------------------------------------------------



Anyway not any evident result in term of better Response Time ....

My next exploration will be on the field: ACCESS PATH
[obviously using Kevin's Book method ... ]


-- [A_Time Self] in the last execution plan is the time for EACH single plan step (Tnx to Randolf Geist )


OK.
For now I stop here.
As always any suggestion is welcome ...



[Updated on: Fri, 08 May 2015 11:02]

Report message to a moderator

Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #637056 is a reply to message #637053] Sat, 09 May 2015 01:52 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Let's try with OPT_ESTIMATE that fudge cardinality of MC et ...
voilĂ  appears .... Bloom Filter .....

Are you sure that you have a Bloom filter? The docs say that you get a Bloom filtered join
only if you have a parallel query, and I see it reported in the plan differently. Like this:
 
orclz> select /*+ px_join_filter(emp) parallel */ * from emp natural join dept where dname='SALES';

Execution Plan
----------------------------------------------------------
Plan hash value: 2247075918

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ D
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     5 |   585 |     4   (0)| 00:00:01 |        |      |
|   1 |  PX COORDINATOR        |          |       |       |            |          |        |      |
|   2 |   PX SEND QC (RANDOM)  | :TQ10000 |     5 |   585 |     4   (0)| 00:00:01 |  Q1,00 | P->S | QC (
|*  3 |    HASH JOIN           |          |     5 |   585 |     4   (0)| 00:00:01 |  Q1,00 | PCWP |
|   4 |     JOIN FILTER CREATE | :BF0000  |     1 |    30 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |
|*  5 |      TABLE ACCESS FULL | DEPT     |     1 |    30 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |
|   6 |     JOIN FILTER USE    | :BF0000  |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |
|   7 |      PX BLOCK ITERATOR |          |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |
|*  8 |       TABLE ACCESS FULL| EMP      |    14 |  1218 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
   5 - filter("DEPT"."DNAME"='SALES')
   8 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"EMP"."DEPTNO"))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 2

orclz>

Re: ORACLE SQL PERFORMANCE TUNING AND OPTIMIZATION - Kevin Meade's book corner [message #637195 is a reply to message #637056] Tue, 12 May 2015 10:29 Go to previous message
paolfili
Messages: 6
Registered: March 2015
Location: Italy
Junior Member
Thanks John for your reply.
Probably you are right, but I think there are various different examples of bloom filter usage from the CBO.
The absence of Table Queue (TQ) and the presence of PART JOIN FILTER CREATE in the plan I reported ,
let me think to something similar to Join Bloom Partition Pruning in a serial plan .
(the case n.2 reported in https://juliandontcheff.wordpress.com/2012/08/28/bloom-filters-for-dbas/
and another example in http://www.hellodba.com/reader.php?ID=204).
Anyway John you are right when you observe that the usual usage of Bloom Filtering is in PX.


Previous Topic: How to check index is re-built
Next Topic: Find cpu MHz in Oracle view
Goto Forum:
  


Current Time: Thu Mar 28 18:14:01 CDT 2024