Home » RDBMS Server » Performance Tuning » Full table scan (Oracle 11g)
Full table scan [message #653792] Tue, 19 July 2016 02:15 Go to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
Hello,

I have one archive script where the source and the archive database are in different database. So I used DB link to access data. Now this script was working fine for 1 year suddenly one issue arises. In the script there is a situation where it is required to get the child cases. So there is a query used:


 merge into sm_arch.sm_archive_stg s
using (
       select pxcoverinskey
             ,pzInskey
             ,pxcoveredcount
             ,pyresolvedtimestamp
             ,pxupdatedatetime
             ,pxobjclass
       from prpc_app.sm_work@DBLNK_GSMARCH.GTI.WHEM.test.NET
       where pxcoverinskey in (
                               select pzInskey
                               from sm_arch.sm_archive_stg
                               where pxcoveredcount > 0
                              )
      ) h
ON (s.pzInskey = h.pzInskey)
when not matched then
insert (pxcoverinskey
       ,pzinskey
       ,pxcoveredcount
       ,pyresolvedtimestamp
       ,pxupdatedatetime
       ,createdatetime
       ,pxobjclass)
values(h.pxcoverinskey
      ,h.pzinskey
      ,h.pxcoveredcount
      ,h.pyresolvedtimestamp
      ,h.pxupdatedatetime
      ,sysdate
      ,h.pxobjclass);


But in the AWR report Oracle modifies the query on the remote site as

SELECT   
PXCOVERINSKEY","PXCOVEREDCOUNT","PXOBJCLASS","PXUPDATEDATETIME","PYRESOLVEDTIMESTAMP","PZINSKEY" FROM "PRPC_APP"."SM_WORK" "SM_WORK" WHERE "PXCOVERINSKEY" IS NOT NULL


I have modified the inside sub query by joining
select pxcoverinskey
             ,pzInskey
             ,pxcoveredcount
             ,pyresolvedtimestamp
             ,pxupdatedatetime
             ,pxobjclass
       from prpc_app.sm_work@DBLNK_GSMARCH.GTI.WHEM.test.NET
       where pxcoverinskey in (
                               select pzInskey
                               from sm_arch.sm_archive_stg
                               where pxcoveredcount > 0
                              )

by

SELECT DSH.pxcoverinskey,
  DSH.pzInskey,
  DSH.pxcoveredcount,
  DSH.pyresolvedtimestamp,
  DSH.pxupdatedatetime,
  DSH.pxobjclass
FROM PRPC_APP.SM_WORK@DBLNK_GSMARCH.GTI.WHEM.test.NET DSH ,
  SM_ARCH.SM_ARCHIVE_STG stg
WHERE dsh.pxcoverinskey = stg.pzInskey
AND stg.pxcoveredcount  > 0

But still the same problem arises in the AWR report. How to modify the query so to avoid the problem. I have also used the driving site hint but didn't help.
Re: Full table scan [message #653794 is a reply to message #653792] Tue, 19 July 2016 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why Oracle modifies the query in remote site? Just because it does not know the values of the local site; it just knows that the value can't be NULL (as NULL can't be in a list of values) and so the remote query text. The further restriction will be done at the local site which can compute the values.

Re: Full table scan [message #653800 is a reply to message #653794] Tue, 19 July 2016 04:30 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
Thank you Michel but how to avoid this and solution approach to avoid this.
Re: Full table scan [message #655531 is a reply to message #653800] Thu, 01 September 2016 04:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
this makes sense

WHERE "PXCOVERINSKEY" IS NOT NULL

why bother to bring back rows from the remote site that have nulls in this column? These will only be dropped during the join (aka. IN) so they are not needed. Thus not sending them across the network makes sense.

as always one must ask, "what did you change".
Could you tell me ... [message #659013 is a reply to message #653792] Wed, 04 January 2017 03:19 Go to previous messageGo to next message
wanear
Messages: 8
Registered: December 2016
Junior Member
hi,

could you tell me
1.how many rows does this sql returns

select pxcoverinskey
,pzInskey
,pxcoveredcount
,pyresolvedtimestamp
,pxupdatedatetime
,pxobjclass
from prpc_app.sm_work@DBLNK_GSMARCH.GTI.WHEM.test.NET
where pxcoverinskey in (
select pzInskey
from sm_arch.sm_archive_stg
where pxcoveredcount > 0
)



2. the size of sm_arch.sm_archive_stg and prpc_app.sm_work@DBLNK_GSMARCH.GTI.WHEM.test.NET

3.and this sql plan
3.1 you can explain plan for select pxcoverinskey
,pzInskey
,pxcoveredcount
,pyresolvedtimestamp
,pxupdatedatetime
,pxobjclass
from prpc_app.sm_work@DBLNK_GSMARCH.GTI.WHEM.test.NET
where pxcoverinskey in (
select pzInskey
from sm_arch.sm_archive_stg
where pxcoveredcount > 0
);

3.2 select * from table(dbms_xplan.display);


[SPLIT from an unrelated topic by LF]
[LF MERGED it back, after seeing BB's message]

[Updated on: Thu, 05 January 2017 03:31] by Moderator

Report message to a moderator

Re: Could you tell me ... [message #659052 is a reply to message #659013] Thu, 05 January 2017 03:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The moderator edit indicates LF split it from what apparently appeared to him to be an unrelated thread. I believe the split was inappropriate. It was probably intended to ask the original poster of the other thread for information, in the form of results from the posted queries, in order to offer help with the tuning. I suspect that it was split from one of the following threads and should be put back. Perhaps either wanear or LF can identify which thread it was split from.


http://www.orafaq.com/forum/mv/msg/201509/653792/#msg_653792

http://www.orafaq.com/forum/mv/msg/201636/654415/#msg_654415

[Updated on: Thu, 05 January 2017 03:23]

Report message to a moderator

Re: Could you tell me ... [message #659053 is a reply to message #659052] Thu, 05 January 2017 03:30 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid you're right, Barbara. It was the first topic you posted (653792) (I remember Kevin's message).

I apologize to all of you, especially @wanear. I'll merge those topics back.

Also, in the future, I'll try to remember to leave track to the original topic so that it would be easier to detect what I did.
Re: Could you tell me ... [message #659057 is a reply to message #659013] Thu, 05 January 2017 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Tue, 27 December 2016 08:13

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
Re: Full table scan [message #663921 is a reply to message #653794] Mon, 26 June 2017 04:44 Go to previous message
samiran_cts
Messages: 52
Registered: January 2012
Member
The problem was resolved keeping sm_archive_stg table in prpc_app schema, so the DB link was avoided and full table scan also.
Previous Topic: While creating of M-View, lock the SYS.OBJ$ table
Next Topic: create partitioning a table
Goto Forum:
  


Current Time: Thu Mar 28 13:02:43 CDT 2024