Home » RDBMS Server » Performance Tuning » Tuning Select Not IN statement(2 Merged) (10g R2)
Tuning Select Not IN statement(2 Merged) [message #510151] Thu, 02 June 2011 11:25 Go to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
Hi There,

We have a person running a query and following is the explain plan

explain plan for
select distinct(extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND')) as ThisBrand
from hh.t_ecomm_mem_relations a
where extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND') not in (select b.code_brand from hh.t_pr_brand b)
and a.code_corr_ecat = 'PREA'
and a.status = 'S'
and a.audit_time > sysdate - 1
;



PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1904775187

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    14 | 32018 |    25   (4)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                         |                        |    14 | 32018 |    25   (4)| 00:00:01 |       |       |
|*  2 |   FILTER                             |                        |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR          |                        |    14 | 32018 |    17   (0)| 00:00:01 |   KEY |    13 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_ECOMM_MEM_RELATIONS  |    14 | 32018 |    17   (0)| 00:00:01 |   KEY |    13 |
|*  5 |      INDEX RANGE SCAN                | X_ECOMM_MEM_RELATIONS3 |    15 |       |     3   (0)| 00:00:01 |   KEY |    13 |
|*  6 |    INDEX FULL SCAN                   | I_PR_BRAND             |     1 |     3 |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "HH"."T_PR_BRAND" "B" WHERE
              LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND'))))
   4 - filter("A"."STATUS"='S')
   5 - access("A"."AUDIT_TIME">SYSDATE@!-1 AND "A"."CODE_CORR_ECAT"='PREA')
       filter("A"."CODE_CORR_ECAT"='PREA')
   6 - filter(LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND')))

=========================

Please let me know if something can be done for this I tried not exists and some Antijoin hints in the subquery which is used in filter NOT IN

I tried minus too

* < code tags > added by BlackSwan; please do so yourself in future

[Updated on: Thu, 02 June 2011 12:16] by Moderator

Report message to a moderator

Tuning Select Not IN statement [message #510153 is a reply to message #510151] Thu, 02 June 2011 11:27 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
Please can anyone help on this

[Updated on: Thu, 02 June 2011 11:32]

Report message to a moderator

Re: Tuning Select Not IN statement [message #510158 is a reply to message #510153] Thu, 02 June 2011 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
After 2 minutes you long to get an answer!

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

For all performances question, Please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: Tuning Select Not IN statement [message #510159 is a reply to message #510158] Thu, 02 June 2011 11:45 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
Im not looking for an instant awnser... It happened that I clicked the tab twice while posting the message and it had posted the same thing again and again so I edited the etxt with one liner

version 10.2.0.4 -- sorry about this.

I have also attached the explain plan as a textpad doc for clear picture

Report message to a moderator

Re: Tuning Select Not IN statement [message #510174 is a reply to message #510159] Thu, 02 June 2011 13:30 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
If that explain is to be believed that query should be fast as lightning. Did you take from the server where the query is running slow or a dev server with less data?

Report message to a moderator

Re: Tuning Select Not IN statement [message #510188 is a reply to message #510174] Thu, 02 June 2011 15:35 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
This query is running on UAT database where the data is less but close to prod

Report message to a moderator

Re: Tuning Select Not IN statement [message #510242 is a reply to message #510188] Fri, 03 June 2011 03:48 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
That would imply the problem is actually the xml functions. Does this one perform better:
SELECT DISTINCT this_brand
FROM (SELECT extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND')
      from hh.t_ecomm_mem_relations a
      where a.code_corr_ecat = 'PREA'
      and a.status = 'S'
      and a.audit_time > sysdate - 1)
WHERE this_brand NOT IN (select b.code_brand from hh.t_pr_brand b);

Report message to a moderator

Re: Tuning Select Not IN statement [message #510249 is a reply to message #510242] Fri, 03 June 2011 04:18 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Is the plan the same in prod?

Report message to a moderator

Re: Tuning Select Not IN statement [message #510299 is a reply to message #510249] Fri, 03 June 2011 09:08 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
this is a good thing which dint flash in my mind, let me get you the plan from prod.

Report message to a moderator

Re: Tuning Select Not IN statement [message #510315 is a reply to message #510299] Fri, 03 June 2011 10:00 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
this is the prod plan

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1904775187

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                        |    14 | 32018 |    24   (5)| 00:00:01 |       |       |
|   1 |  HASH UNIQUE                         |                        |    14 | 32018 |    24   (5)| 00:00:01 |       |       |
|*  2 |   FILTER                             |                        |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR          |                        |    14 | 32018 |    16   (0)| 00:00:01 |   KEY |    13 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| T_ECOMM_MEM_RELATIONS  |    14 | 32018 |    16   (0)| 00:00:01 |   KEY |    13 |
|*  5 |      INDEX RANGE SCAN                | X_ECOMM_MEM_RELATIONS3 |    15 |       |     3   (0)| 00:00:01 |   KEY |    13 |
|*  6 |    INDEX FULL SCAN                   | I_PR_BRAND             |     1 |     3 |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "HH"."T_PR_BRAND" "B" WHERE
              LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND'))))
   4 - filter("A"."STATUS"='S')
   5 - access("A"."AUDIT_TIME">SYSDATE@!-1 AND "A"."CODE_CORR_ECAT"='PREA')
       filter("A"."CODE_CORR_ECAT"='PREA')
   6 - filter(LNNVL("B"."CODE_BRAND"<>EXTRACTVALUE("XMLTYPE"."XMLTYPE"(:B1),'/CalliopeData/Attributes/HOTEL_BRAND')))

23 rows selected.


* [ code tags ] fixed by BlackSwan

[Updated on: Fri, 03 June 2011 10:02] by Moderator

Report message to a moderator

Re: Tuning Select Not IN statement [message #510316 is a reply to message #510315] Fri, 03 June 2011 10:01 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
the plan on prod is exactly the same as which I ran earlier in UAT. I tried using tag /code I dont know why it throws the output in above form

Report message to a moderator

Re: Tuning Select Not IN statement [message #510317 is a reply to message #510316] Fri, 03 June 2011 10:03 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
Sorry I forgot to mention one more thing...
The above suggestion of query to execute "this brand" is not a variable --> Its a an alias for ""extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND')""

so the query suggested to run is throwing line 1 as invalid variable.

Report message to a moderator

Re: Tuning Select Not IN statement [message #510318 is a reply to message #510317] Fri, 03 June 2011 10:09 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
I know it's not a variable:
SELECT DISTINCT this_brand
FROM (SELECT extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND') as this_brand --I missed the alias here
      from hh.t_ecomm_mem_relations a
      where a.code_corr_ecat = 'PREA'
      and a.status = 'S'
      and a.audit_time > sysdate - 1)
WHERE this_brand NOT IN (select b.code_brand from hh.t_pr_brand b);

Report message to a moderator

Re: Tuning Select Not IN statement [message #510343 is a reply to message #510249] Fri, 03 June 2011 15:39 Go to previous messageGo to next message
getzeeshan
Messages: 65
Registered: July 2008
Member
I think the above one works super fast


15:38:53 SYS@hhuat SQL> SELECT DISTINCT this_brand
FROM (SELECT extractvalue(xmltype(a.email_variables), '/CalliopeData/Attributes/HOTEL_BRAND') as this_brand --I missed the alias here
15:39:29   2  15:39:29   3        from hh.t_ecomm_mem_relations a
      where a.code_corr_ecat = 'PREA'
15:39:29   4  15:39:29   5        and a.status = 'S'
15:39:29   6        and a.audit_time > sysdate - 1)
15:39:29   7  WHERE this_brand NOT IN (select b.code_brand from hh.t_pr_brand b);

no rows selected

Elapsed: 00:00:00.00



CM: fixed code tags

[Updated on: Fri, 03 June 2011 16:19] by Moderator

Report message to a moderator

Re: Tuning Select Not IN statement [message #510347 is a reply to message #510343] Fri, 03 June 2011 16:20 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
Most queries are fast when there's no matching data, you might want to try it with a data set that'll actually return something.

And code tags are: [code] and [/code] not </code>

Report message to a moderator

Re: Tuning Select Not IN statement [message #510353 is a reply to message #510347] Fri, 03 June 2011 17:59 Go to previous message
getzeeshan
Messages: 65
Registered: July 2008
Member
I totally agree with you working on a real data set would help us get a better plan and work around. Let me check with the developer if hes going to run the same again a similar query with the proper data to retrieve.

and thx for Code tag.

Thx! for all your help on this.

Report message to a moderator

Previous Topic: Solution Required for Sql Performance issue in oracle due to Null value check(2 Merged)
Next Topic: Speed Up the gathering of stats.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Feb 19 14:43:39 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.