Home » RDBMS Server » Performance Tuning » Why rownum is not used in the query
Why rownum is not used in the query [message #654415] Tue, 02 August 2016 08:18 Go to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
Hello Sir,

I have one query which was doing good for the last 1 year but suddenly the query is degrading and in the AWR report the rownum is not being used. The query is like this:

select PXCOVERINSKEY,"PXCOVEREDCOUNT","PXOBJCLASS","PXUPDATEDATETIME","PYRESOLVEDTIMESTAMP","PYSTATUSWORK","PZINSKEY",
"EXTENDEDARCHIVE","UNARCHDATETIME" FROM TEST_WORK 
WHERE PYRESOLVEDTIMESTAMP < TRUNC(SYSDATE) - 90
And nvl(unarchdatetime, sysdate - 7) < sysdate -1
AND PXOBJCLASS = 'JPMC-CIB-GSM-Work-Suspect-Txn%'
AND "PYSTATUSWORK" like 'Resolved%'
AND ( EXTENDEDARCHIVE is null or EXTENDEDARCHIVE = 'NO')
AND rownum < 90

But in the AWR report the query is becoming and ignoring the rownum.
SELECT /*+ OPAQUE_TRANSFORM */ "PXCOVERINSKEY", "PXCOVEREDCOUNT", "PXOBJCLASS", "PXUPDATEDATETIME", "PYRESOLVEDTIMESTAMP", "PYSTATUSWORK", "PZINSKEY", "EXTENDEDARCHIVE", "UNARCHDATETIME" FROM  "TEST_WORK" WHERE "PXOBJCLASS" LIKE :1 AND "PYSTATUSWORK" LIKE :2 AND ("EXTENDEDARCHIVE" IS NULL OR "EXTENDEDARCHIVE"=:3) AND "PYRESOLVEDTIMESTAMP"<TRUNC(:4)-:"SYS_B_0" AND NVL("UNARCHDATETIME", :5-:"SYS_B_1")<:6-:"SYS_B_2"

So what could be the reason for ignoring the rownum in the query ? There are indexes present in the where condition still performance is degrading. If rownum is replaced with analytical function row_number() can its performance will improve.

Regards,
Samiran

Report message to a moderator

Re: Why rownum is not used in the query [message #654416 is a reply to message #654415] Tue, 02 August 2016 08:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
 
SELECT pxcoverinskey, 
       "pxcoveredcount", 
       "pxobjclass", 
       "pxupdatedatetime", 
       "pyresolvedtimestamp", 
       "pystatuswork", 
       "pzinskey", 
       "extendedarchive", 
       "unarchdatetime" 
FROM   test_work 
WHERE  pyresolvedtimestamp < Trunc(SYSDATE) - 90 
       AND Nvl(unarchdatetime, SYSDATE - 7) < SYSDATE - 1 
       AND pxobjclass = 'JPMC-CIB-GSM-Work-Suspect-Txn%' 
       AND "pystatuswork" LIKE 'Resolved%' 
       AND ( extendedarchive IS NULL 
              OR extendedarchive = 'NO' ) 
       AND ROWNUM < 90

http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz


post EXPLAIN PLAN

post CREATE TABLE & CREATE INDEX for all involved

Report message to a moderator

Re: Why rownum is not used in the query [message #654417 is a reply to message #654415] Tue, 02 August 2016 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Because both are not about the same query.

Report message to a moderator

Re: Why rownum is not used in the query [message #654451 is a reply to message #654417] Wed, 03 August 2016 03:38 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Rownum isn't the only difference, the order of the where clause is different, plus this:
AND PXOBJCLASS = 'JPMC-CIB-GSM-Work-Suspect-Txn%'


WHERE "PXOBJCLASS" LIKE :1

As Michel says - the query AWR is reporting isn't the one you posted at the top.

Report message to a moderator

Re: Why rownum is not used in the query [message #654454 is a reply to message #654451] Wed, 03 August 2016 03:57 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
Right Michel, is it the oracle optimizes the query in its own way and formatting the query in different way? I would like to day the below query is part of procedure, where the query is formed dynamically and row limit is always passed. If i run this standalone query and see the explain plan then there is no problem, but when it is run from procedure the problem arises.

Report message to a moderator

Re: Why rownum is not used in the query [message #654455 is a reply to message #654454] Wed, 03 August 2016 04:02 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
It might look that the problematic query and the query reported in AWR are different but no where from the application the query that is reported in the AWR was called. It is part of the procedure where the query is built dynamically.In all environment not causing problem, only in prod this mysterious things appeared.

Report message to a moderator

Re: Why rownum is not used in the query [message #654456 is a reply to message #654454] Wed, 03 August 2016 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

You don't understand.
What you report from AWR is NOT the query you are looking for, it is another one, from somewhere else but it is not the one you are concerned. In AWR, you are not looking at the correct query, whatever you think, it is not this one unless there is a very big bug in the optimizer, it will not convert a "=" to a "LIKE".

Report message to a moderator

Re: Why rownum is not used in the query [message #654457 is a reply to message #654455] Wed, 03 August 2016 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
no where from the application the query that is reported in the AWR was called.
And from outside the application?

Report message to a moderator

Re: Why rownum is not used in the query [message #654459 is a reply to message #654457] Wed, 03 August 2016 04:11 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
I cross checked and I made it wrong 
AND PXOBJCLASS = 'JPMC-CIB-GSM-Work-Suspect-Txn%'
. In the code it is actually
AND PXOBJCLASS like 'JPMC-CIB-GSM-Work-Suspect-Txn%'
. So it is correct as per the AWR report, but the rownum is missing which is making concern for me.

Report message to a moderator

Re: Why rownum is not used in the query [message #654461 is a reply to message #654459] Wed, 03 August 2016 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

How can we now believe anything you have posted?

Report message to a moderator

Re: Why rownum is not used in the query [message #654463 is a reply to message #654461] Wed, 03 August 2016 04:45 Go to previous messageGo to next message
samiran_cts
Messages: 52
Registered: January 2012
Member
The query is something like this 
SqlQuery:= SqlQuery || ' select pxcoverinskey,nvl(pxcoveredcount,0) pxcoveredcount ,pzInskey,pyresolvedtimestamp, pxupdatedatetime,sysdate, nvl(EXTENDEDARCHIVE,' || '''No'''|| ')  extendedarchive, pxobjclass from '||SCHEMA_NAME ||'.'||workTableName' where PYRESOLVEDTIMESTAMP < trunc(SYSDATE) - '||cutoffDays ;
    
      SqlQuery:= SqlQuery || ' and nvl(unarchdatetime, sysdate - 7) < sysdate -1 ';
    end if;

    if( workClass is not null ) then
      SqlQuery := SqlQuery || ' and PXOBJCLASS like '|| ':1';
    end if;
    if (jobName='ARCHIVE') then
     
      SqlQuery:= SqlQuery || ' and pystatuswork like ' || ':2'; 
      if extendedarchive= 'Yes' then
        SqlQuery:= SqlQuery || ' and EXTENDEDARCHIVE = :3';
      end if;
      if (extendedarchive= 'No' and workclass = 'JPMC-CIB-GSM-Work-Suspect-Client') then
        SqlQuery:= SqlQuery || ' and ( EXTENDEDARCHIVE is null or EXTENDEDARCHIVE = :3)';
      end if;
      if rowlimit > 0 then
        SqlQuery:= SqlQuery || ' and rownum < '|| rowlimit;     
      end if;
I hope I have explained the problem statement.

Report message to a moderator

Re: Why rownum is not used in the query [message #654464 is a reply to message #654463] Wed, 03 August 2016 04:51 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then that part of the code was probably called while "rowlimit" was not 0 or negative.....

Report message to a moderator

Re: Why rownum is not used in the query [message #654472 is a reply to message #654464] Wed, 03 August 2016 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Should be:
while "rowlimit" was 0 or negative.....

Report message to a moderator

Re: Why rownum is not used in the query [message #654473 is a reply to message #654472] Wed, 03 August 2016 08:14 Go to previous message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seriously - why would assume oracle left off the rownum clause when you've got code that that is designed to leave it off when constructing the SQL?

Report message to a moderator

Previous Topic: Approach for Oracle Profiles to tune queries
Next Topic: Data related to DB Metrics for a period
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Feb 02 17:59:04 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.