OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Full table scan (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Full table scan [message #653792]

Tue, 19 July 2016 02:15

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
                              )

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.

Report message to a moderator

Re: Full table scan [message #653794 is a reply to message #653792]

Tue, 19 July 2016 02:50

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Full table scan [message #653800 is a reply to message #653794]

Tue, 19 July 2016 04:30

samiran_cts
Messages: 52
Registered: January 2012

Member

Thank you Michel but how to avoid this and solution approach to avoid this.

Report message to a moderator

Re: Full table scan [message #655531 is a reply to message #653800]

Thu, 01 September 2016 04:36

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".

Report message to a moderator

Could you tell me ... [message #659013 is a reply to message #653792]

Wed, 04 January 2017 03:19

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

Barbara Boehmer
Messages: 9104
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

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: Could you tell me ... [message #659057 is a reply to message #659013]

Thu, 05 January 2017 04:21

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Full table scan [message #663921 is a reply to message #653794]

Mon, 26 June 2017 04:44

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.

Report message to a moderator

Previous Topic:	While creating of M-View, lock the SYS.OBJ$ table
Next Topic:	create partitioning a table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 30 16:42:16 CST 2025