Hi
Below is the query and the database is 12.1
var SYS_B_00 varchar2(32);
var SYS_B_01 varchar2(32);
var SYS_B_02 varchar2(32);
var SYS_B_03 varchar2(32);
var SYS_B_04 varchar2(32);
var SYS_B_05 varchar2(32);
var SYS_B_06 number;
var SYS_B_07 varchar2(32);
var SYS_B_08 varchar2(32);
var SYS_B_09 varchar2(32);
var SYS_B_10 number;
var SYS_B_11 number;
var SYS_B_12 varchar2(32);
exec :SYS_B_00:='01/';
exec :SYS_B_01:='/';
exec :SYS_B_02:='dd/MM/yyyy';
exec :SYS_B_03:='10/04/2018';
exec :SYS_B_04:='MM/dd/yyyy';
exec :SYS_B_05:='q';
exec :SYS_B_06:=12;
exec :SYS_B_07:='10/04/2018';
exec :SYS_B_08:='MM/dd/yyyy';
exec :SYS_B_09:='q';
exec :SYS_B_10:=1;
exec :SYS_B_11:=1;
exec :SYS_B_12:='PRIMARY';
WITH household_ids
AS ( SELECT DISTINCT household_id
FROM ( SELECT household_id,
To_date( :"SYS_B_00"
|| month
|| :"SYS_B_01"
|| year, :"SYS_B_02" ) AS Quarter_Date
FROM actprof.IMPL_HOUSEHOLD_GDC )
WHERE quarter_date BETWEEN Add_months( Trunc( To_date( :"SYS_B_03", :"SYS_B_04" ), :"SYS_B_05" ), -:"SYS_B_06" )
AND ( Trunc( To_date( :"SYS_B_07", :"SYS_B_08" ), :"SYS_B_09" ) - :"SYS_B_10" ) )
SELECT DISTINCT hh.master_id,
hh.household_id,
hh.account_number,
r.ssntin
FROM ( SELECT rltn.master_id,
rltn.household_id,
rltn.account_number
FROM actprof.IMPL_ADV_HOUSEHOLD_ACCT_RELTN rltn
inner join ( SELECT DISTINCT master_id,
household_id
FROM ( SELECT reltn.master_id,
reltn.household_id,
reltn.account_number,
Rank( )
over (
PARTITION BY reltn.master_id, reltn.household_id
ORDER BY reltn.account_number) RANK
FROM actprof.IMPL_ADV_HOUSEHOLD_ACCT_RELTN reltn
inner join household_ids hi
ON reltn.household_id = hi.household_id )
WHERE rank > :"SYS_B_11" ) r
ON rltn.master_id = r.master_id AND
rltn.household_id = r.household_id ) hh
inner join actprof.IMPL_LPL_BETA_CUST_RLTN r
ON hh.master_id = r.master_id AND
hh.account_number = r.accountno AND
r.relationshiptype = :"SYS_B_12"
ORDER BY hh.master_id,hh.household_id,hh.account_number
/
Global Stats
==============================================================================================
| Elapsed | Cpu | IO | Cluster | Other | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes |
==============================================================================================
| 320 | 76 | 140 | 39 | 66 | 8M | 257K | 2GB | 1528 | 306MB |
==============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3210215320)
=================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
=================================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 180 | +142 | 1 | 0 | | | | | | | | | |
| -> 1 | SORT UNIQUE | | 1093 | 52574 | 180 | +142 | 1 | 0 | | | 534 | 107MB | 2M | 113M | 0.94 | Cpu (3) | |
| -> 2 | NESTED LOOPS | | 1093 | 52573 | 180 | +142 | 1 | 3M | | | | | | | 0.31 | Cpu (1) | |
| -> 3 | NESTED LOOPS | | 1118 | 52573 | 180 | +142 | 1 | 3M | | | | | | | 0.31 | Cpu (1) | |
| -> 4 | HASH JOIN RIGHT SEMI | | 1118 | 52238 | 189 | +133 | 1 | 3M | | | | | 153M | | 1.57 | Cpu (5) | |
| 5 | VIEW | | 157K | 31145 | 9 | +134 | 1 | 2M | | | | | | | | | |
| 6 | WINDOW SORT | | 157K | 31145 | 57 | +86 | 1 | 4M | 3777 | 199MB | 994 | 199MB | | | 3.14 | Cpu (5) | 100% |
| | | | | | | | | | | | | | | | | direct path read temp (5) | |
| 7 | HASH JOIN | | 157K | 29653 | 50 | +85 | 1 | 4M | | | | | | | 1.26 | Cpu (4) | |
| 8 | VIEW | | 81771 | 23273 | 1 | +86 | 1 | 1M | | | | | | | | | |
| 9 | HASH UNIQUE | | 81771 | 23273 | 75 | +12 | 1 | 1M | | | | | | | 1.89 | Cpu (6) | |
| 10 | FILTER | | | | 78 | +9 | 1 | 11M | | | | | | | 0.31 | Cpu (1) | |
| 11 | INDEX FULL SCAN | PK_HOUSEHOLD_GDC | 83917 | 22799 | 86 | +1 | 1 | 11M | 9 | 73728 | | | | | 24.21 | Cpu (77) | |
| 12 | INDEX FULL SCAN | PK_ADV_HOUSEHOLD_ACCT | 8M | 6332 | 49 | +86 | 1 | 8M | | | | | | | 12.58 | gc cr block 2-way (37) | |
| | | | | | | | | | | | | | | | | gc current block 2-way (3) | |
| -> 13 | INDEX FULL SCAN | PK_ADV_HOUSEHOLD_ACCT | 8M | 6332 | 180 | +142 | 1 | 7M | | | | | | | 0.63 | Cpu (2) | |
| -> 14 | INDEX RANGE SCAN | IDX4_LPL_BETA_CUST_RLTN | 1 | 1 | 181 | +141 | 3M | 3M | 75759 | 592MB | | | | | 23.27 | gc current grant 2-way (1) | |
| | | | | | | | | | | | | | | | | Cpu (21) | |
| | | | | | | | | | | | | | | | | db file parallel read (52) | |
| -> 15 | TABLE ACCESS BY INDEX ROWID | IMPL_LPL_BETA_CUST_RLTN | 1 | 1 | 180 | +142 | 3M | 3M | 177K | 1GB | | | | | 29.56 | Cpu (12) | |
| | | | | | | | | | | | | | | | | db file parallel read (81) | |
| | | | | | | | | | | | | | | | | db file sequential read (1) | |
On plan line id 11--Index full scan estimating 83k but it is getting 11M rows .
Below is the Plan
Plan hash value: 3210215320
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 52576 (100)| |
| 1 | SORT UNIQUE | | 1093 | 95091 | | 52574 (2)| 00:00:03 |
| 2 | NESTED LOOPS | | 1093 | 95091 | | 52573 (2)| 00:00:03 |
| 3 | NESTED LOOPS | | 1118 | 95091 | | 52573 (2)| 00:00:03 |
|* 4 | HASH JOIN RIGHT SEMI | | 1118 | 60372 | 6288K| 52238 (2)| 00:00:03 |
|* 5 | VIEW | | 156K| 4445K| | 31145 (3)| 00:00:02 |
| 6 | WINDOW SORT | | 156K| 5518K| 7400K| 31145 (3)| 00:00:02 |
|* 7 | HASH JOIN | | 156K| 5518K| | 29653 (3)| 00:00:02 |
| 8 | VIEW | | 81771 | 878K| | 23273 (3)| 00:00:01 |
| 9 | HASH UNIQUE | | 81771 | 1437K| 2320K| 23273 (3)| 00:00:01 |
|* 10 | FILTER | | | | | | |
|* 11 | INDEX FULL SCAN | PK_HOUSEHOLD_GDC | 83917 | 1475K| | 22799 (3)| 00:00:01 |
| 12 | INDEX FULL SCAN | PK_ADV_HOUSEHOLD_ACCT | 8207K| 195M| | 6332 (1)| 00:00:01 |
| 13 | INDEX FULL SCAN | PK_ADV_HOUSEHOLD_ACCT | 8207K| 195M| | 6332 (1)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX4_LPL_BETA_CUST_RLTN | 1 | | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| IMPL_LPL_BETA_CUST_RLTN | 1 | 33 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("RLTN"."MASTER_ID"="MASTER_ID" AND "RLTN"."HOUSEHOLD_ID"="HOUSEHOLD_ID")
5 - filter("RANK">:SYS_B_11)
7 - access("RELTN"."HOUSEHOLD_ID"="HI"."HOUSEHOLD_ID")
10 - filter(TRUNC(TO_DATE(:SYS_B_07,:SYS_B_08),:SYS_B_09)-:SYS_B_10>=ADD_MONTHS(TRUNC(TO_DATE(:SYS_B_03
,:SYS_B_04),:SYS_B_05),(-:SYS_B_06)))
11 - filter((TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)>=ADD_MONTHS(TRU
NC(TO_DATE(:SYS_B_03,:SYS_B_04),:SYS_B_05),(-:SYS_B_06)) AND
TO_DATE(:SYS_B_00||TO_CHAR("MONTH")||:SYS_B_01||TO_CHAR("YEAR"),:SYS_B_02)<=TRUNC(TO_DATE(:SYS_B_07,:SYS_
B_08),:SYS_B_09)-:SYS_B_10))
14 - access("RLTN"."ACCOUNT_NUMBER"="R"."ACCOUNTNO" AND "R"."RELATIONSHIPTYPE"=:SYS_B_12)
15 - filter("RLTN"."MASTER_ID"="R"."MASTER_ID")
Below is the DDL for the index
CREATE UNIQUE INDEX "ACTPROF"."PK_HOUSEHOLD_GDC" ON "ACTPROF"."IMPL_HOUSEHOLD_GDC" ("MASTER_ID", "HOUSEHOLD_ID", "YEAR", "MONTH")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "ACTSTG_DATA"
column stats on the table "IMPL_HOUSEHOLD_GDC"
COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY NUM_NULLS LAST_ANALYZED HISTOGRAM
------------------------------ --------------- ------------ -------- ---------- ------------------- ---------------
BATCH_ID NUMBER 1 1 0 2018-12-02 09:04:55 NONE
QUARTER NUMBER 4 0 0 2018-12-02 09:04:55 FREQUENCY
YEAR NUMBER 5 0 0 2018-12-02 13:19:10 FREQUENCY
MONTH NUMBER 12 0 0 2018-12-02 13:19:10 FREQUENCY
HOUSEHOLD_MAX_AGE NUMBER 120 0 599259 2018-12-02 09:04:55 NONE
NO_OF_TRADES NUMBER 583 0 0 2018-12-02 09:04:55 NONE
MASTER_ID VARCHAR2 18646 0 0 2018-12-02 13:19:10 HEIGHT BALANCED
GDC NUMBER 521984 0 0 2018-12-02 09:04:55 NONE
HOUSEHOLD_ID VARCHAR2 1539936 0 0 2018-12-02 13:19:10 HEIGHT BALANCED