No question at this time
DBA Top 10
1 A. Kavsek 12400
2 M. Cadot 7300
3 B. Vroman 6400
4 P. Wisse 5600
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48458
Total active users1487
Act. users last 24h2
Act. users last hour1
Registered user hits last week190
Registered user hits last month829
Go up

explain plan getting full table scan.
Next thread: To find the list of procedures and their respective tables used inside the Procedure in
Prev thread: multiple tablespace remap in single

Message Score Author Date
Hi, query------ MERGE INTO ABPPMGR.MD_CUBE_P...... javed akhtar May 21, 2019, 09:14
PLAN_TABLE_OUTPUT -------------------------------...... javed akhtar May 21, 2019, 09:15
Hi Javed, Pleas use <b><</b>pre<b>></b> and <b>...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse May 21, 2019, 10:35
Hi Javed, <i>my concern is why this full table ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse May 22, 2019, 09:40

Follow up by mail Click here


Subject: explain plan getting full table scan.
Author: javed akhtar, India
Date: May 21, 2019, 09:14, 201 days ago
Os info: linux
Oracle info: 12
Message: Hi,

query------

MERGE INTO ABPPMGR.MD_CUBE_PEGGING MCP USING (SELECT SMR.ITEM, SMR.SITE, SMR.SELLER, SMR.SOURCE_NAME, SMR.SOURCE_SITE,
T.BUCKET FROM ABPPMGR.SOURCE_MEMBER_RELATIONSHIP SMR, ABPPMGR.PLAN_MASTER P, ABPPMGR.TIME_MASTER T, ABPPMGR.MD_CUBE_AT_LEAF_LEVEL MD,
SCPOMGR.DBPARAM DP WHERE P.STATUS = 'Current' AND T.BUCKET >= P.PLAN_BKT AND T.BUCKET < (P.PLAN_BKT + DP.U_FCSTNBROLLINGMONTHS) AND
SMR.ITEM = MD.ITEM AND SMR.SITE = MD.SITE AND SMR.SELLER = MD.SELLER AND T.BUCKET = MD.BUCKET AND SMR.SYS_ENT_STATE = 'ACTIVE')
D ON ( MCP.SELLER = D.SELLER AND MCP.SITE = D.SITE AND MCP.ITEM = D.ITEM AND MCP.SOURCE_NAME = D.SOURCE_NAME AND
MCP.SOURCE_SITE = D.SOURCE_SITE AND MCP.BUCKET = D.BUCKET) WHEN NOT MATCHED THEN INSERT (MCP.SELLER, MCP.SITE, MCP.ITEM,
MCP.SOURCE_NAME, MCP.SOURCE_SITE, MCP.BUCKET) VALUES (D.SELLER, D.SITE, D.ITEM, D.SOURCE_NAME, D.SOURCE_SITE, D.BUCKET)

exlain plan

MERGE INTO ABPPMGR.MD_CUBE_PEGGING MCP USING (SELECT SMR.ITEM, SMR.SITE, SMR.SELLER, SMR.SOURCE_NAME, SMR.SOURCE_SITE,
T.BUCKET FROM ABPPMGR.SOURCE_MEMBER_RELATIONSHIP SMR, ABPPMGR.PLAN_MASTER P, ABPPMGR.TIME_MASTER T, ABPPMGR.MD_CUBE_AT_LEAF_LEVEL MD,
SCPOMGR.DBPARAM DP WHERE P.STATUS = 'Current' AND T.BUCKET >= P.PLAN_BKT AND T.BUCKET < (P.PLAN_BKT + DP.U_FCSTNBROLLINGMONTHS) AND
SMR.ITEM = MD.ITEM AND SMR.SITE = MD.SITE AND SMR.SELLER = MD.SELLER AND T.BUCKET = MD.BUCKET AND SMR.SYS_ENT_STATE = 'ACTIVE')
D ON ( MCP.SELLER = D.SELLER AND MCP.SITE = D.SITE AND MCP.ITEM = D.ITEM AND MCP.SOURCE_NAME = D.SOURCE_NAME AND
MCP.SOURCE_SITE = D.SOURCE_SITE AND MCP.BUCKET = D.BUCKET) WHEN NOT MATCHED THEN INSERT (MCP.SELLER, MCP.SITE, MCP.ITEM,
MCP.SOURCE_NAME, MCP.SOURCE_SITE, MCP.BUCKET) VALUES (D.SELLER, D.SITE, D.ITEM, D.SOURCE_NAME, D.SOURCE_SITE, D.BUCKET)

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

column using indexes

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
C5057069287_643 SITE
C5057069287_643 ITEM
C5057069287_643 SOURCE_NAME
C5057069287_643 SOURCE_SITE
C5057069287_643 BUCKET
I5057069287_644 SELLER
I5057069287_644 SITE
I5057069287_644 ITEM
I5057069287_644 SOURCE_NAME
I5057069287_644 SOURCE_SITE
I5057069287_645 MEM_SEGMENT_ID
I5057069287_646 BUCKET
I5057069287_647 SYS_NC_TYPE
I5057069287_648 ITEM
I5057069287_648 SITE
I5057069287_648 SELLER
I5057069287_648 SOURCE_NAME
I5057069287_648 SOURCE_SITE
C5057069287_643 SELLER



my concern is why this full table scan is happening.

TABLE ACCESS FULL | MD_CUBE_PEGGING | 56M| 8257M| | 261K (1)| 00:00:21 |
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: explain plan getting full table scan.
Author: javed akhtar, India
Date: May 21, 2019, 09:15, 201 days ago
Message: PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------
Plan hash value: 981902909

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1448K| 1071M| | 705K (1)| 00:00:56 |
| 1 | MERGE | MD_CUBE_PEGGING | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN OUTER | | 1448K| 341M| 90M| 705K (1)| 00:00:56 |
|* 4 | HASH JOIN | | 893K| 80M| 73M| 60113 (1)| 00:00:05 |
| 5 | NESTED LOOPS | | 1433K| 57M| | 44303 (1)| 00:00:04 |
| 6 | MERGE JOIN CARTESIAN| | 1 | 12 | | 6 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | PLAN_MASTER | 1 | 9 | | 3 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 3 | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | DBPARAM | 1 | 3 | | 3 (0)| 00:00:01 |
|* 10 | INDEX FAST FULL SCAN| C5057069287_622 | 1433K| 40M| | 44297 (1)| 00:00:04 |
|* 11 | TABLE ACCESS FULL | SOURCE_MEMBER_RELATIONSHIP | 2683K| 133M| | 5654 (1)| 00:00:01 |
| 12 | TABLE ACCESS FULL | MD_CUBE_PEGGING | 56M| 8257M| | 261K (1)| 00:00:21 |
----------------------------------------------------------------------------------------------------------------
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: explain plan getting full table scan.
Author: Philip Wisse, Netherlands
Date: May 21, 2019, 10:35, 201 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Javed,

Pleas use <pre> and </pre> tags for formatting like:

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1448K | 1071M | | 705K (1) | 00:00:56 |
| 1 | MERGE | MD_CUBE_PEGGING | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN OUTER | | 1448K | 341M | 90M | 705K (1) | 00:00:56 |
|* 4 | HASH JOIN | | 893K | 80M | 73M | 60113 (1) | 00:00:05 |
| 5 | NESTED LOOPS | | 1433K | 57M | | 44303 (1) | 00:00:04 |
| 6 | MERGE JOIN CARTESIAN | | 1 | 12 | | 6 (0) | 00:00:01 |
|* 7 | TABLE ACCESS FULL | PLAN_MASTER | 1 | 9 | | 3 (0) | 00:00:01 |
| 8 | BUFFER SORT | | 1 | 3 | | 3 (0) | 00:00:01 |
| 9 | TABLE ACCESS FULL | DBPARAM | 1 | 3 | | 3 (0) | 00:00:01 |
|* 10 | INDEX FAST FULL SCAN | C5057069287_622 | 1433K | 40M | | 44297 (1) | 00:00:04 |
|* 11 | TABLE ACCESS FULL | SOURCE_MEMBER_RELATIONSHIP | 2683K | 133M | | 5654 (1) | 00:00:01 |
| 12 | TABLE ACCESS FULL | MD_CUBE_PEGGING | 56M | 8257M | | 261K (1) | 00:00:21 |
------------------------------------------------------------------------------------------------------------
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: explain plan getting full table scan.
Author: Philip Wisse, Netherlands
Date: May 22, 2019, 09:40, 200 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Javed,

my concern is why this full table scan is happening.
TABLE ACCESS FULL | MD_CUBE_PEGGING | 56M| 8257M| | 261K (1)| 00:00:21

Obviously the index C5057069287_643 contains all the columns involved.
I guess the reason why it's not used some of the columns are nullable.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here