Home » SQL & PL/SQL » SQL & PL/SQL » Query tune
Query tune [message #214083] Sun, 14 January 2007 21:34 Go to next message
feizz
Messages: 16
Registered: December 2006
Location: Singapore
Junior Member

Hi all,
please help me to tune this query as it take a very long time to run. I've tried to use NOT LIKE on != , but it is still take a long time, unless i remove the != condition.



===================
		 select a.poplgr, min (a.poopno) poopno 
		 from pfods.mpdope a 
		 where a.pocono=1 
		 and   a.pofaci='MF1' 
		 and   a.postrt='STD' 
                 and   a.poopds != 'P250 SMD-AUTOMAT'
                 and   a.POPRNO in (select b.mbitno 
		                    from   pfods.mitbal b 
		 	                where  b.mbcono = 1 
							and    b.mbwhlo='MF1' 
							and    b.mbitno in (select c.roprno 
			                                    from   pfods.mmoplp c
                                           	    where  c.rofidt <= to_date('08-JAN-07','DD-MON-YY') 
												and    c.rofaci='MF1' 
												and    c.rocono=1))
								
		group by a.poplgr


[moderator: added CODE tags]
[moderator: reformated SQL to drop max line length]

[Updated on: Mon, 15 January 2007 02:27] by Moderator

Report message to a moderator

Re: Query tune [message #214086 is a reply to message #214083] Sun, 14 January 2007 22:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
We need more information
- the current EXPLAIN PLAN
- #rows in pfods.mpdope 
- #rows in pfods.mpdope with pocono=1 
                         and pofaci='MF1' 
                         and postrt='STD' 
                         and poopds != 'P250 SMD-AUTOMAT' 
- #rows in pfods.mitbal 
- #rows in pfods.mitbal with mbcono = 1 and mbwhlo='MF1' 
- #rows in pfods.mmoplp
- #rows in pfods.mmoplp with rofidt <= to_date('08-JAN-07','DD-MON-YY')
                         and rofaci='MF1' 
                         and rocono=1
- #rows returned by the query BEFORE aggregation (ie. remove the GROUP BY/MIN)

Ross Leishman

[Updated on: Sun, 14 January 2007 22:31]

Report message to a moderator

icon9.gif  Re: Query tune [message #214097 is a reply to message #214086] Sun, 14 January 2007 23:28 Go to previous messageGo to next message
feizz
Messages: 16
Registered: December 2006
Location: Singapore
Junior Member

Hi Ross,
this is the output, please let me know if you need further info.

=======
select count(1) from pfods.mpdope -- 549528 rows
select count(1) from pfods.mpdope where pocono=1
and pofaci='MF1'
and postrt='STD'
and poopds != 'P250 SMD-AUTOMAT' -- 337151 rows

select count(1) from pfods.mitbal -- 69732 rows
select count(1) from pfods.mitbal where mbcono = 1 and mbwhlo='MF1' -- 39371 rows
select count(1) from pfods.mmoplp -- 17922 rows

select count(1) from pfods.mmoplp where rofidt <= to_date('08-JAN-07','DD-MON-YY')
and rofaci='MF1'
and rocono=1 -- 12 rows
Re: Query tune [message #214102 is a reply to message #214083] Sun, 14 January 2007 23:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
where is EXPLAIN_PLAN?

[Updated on: Sun, 14 January 2007 23:44] by Moderator

Report message to a moderator

Re: Query tune [message #214107 is a reply to message #214102] Mon, 15 January 2007 00:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's right, the Explain Plan, AND the number of rows returned before aggregation. ie.
select count(*)
from pfods.mpdope a 
where a.pocono=1 
and   a.pofaci='MF1' 
and   a.postrt='STD' 
and   a.poopds != 'P250 SMD-AUTOMAT'
and   a.POPRNO in (
    select b.mbitno 
    from   pfods.mitbal b 
    where  b.mbcono = 1 
    and    b.mbwhlo='MF1' 
    and    b.mbitno in (
        select c.roprno 
        from   pfods.mmoplp c
        where  c.rofidt <= to_date('08-JAN-07','DD-MON-YY') 
        and    c.rofaci='MF1' 
        and    c.rocono=1
    )
)


Also, it would be helpful to know:
    select count(*), count(distinct b.mbitno)
    from   pfods.mitbal b 
    where  b.mbcono = 1 
    and    b.mbwhlo='MF1' 
    and    b.mbitno in (
        select c.roprno 
        from   pfods.mmoplp c
        where  c.rofidt <= to_date('08-JAN-07','DD-MON-YY') 
        and    c.rofaci='MF1' 
        and    c.rocono=1
    )



Ross Leishman
Re: Query tune [message #214345 is a reply to message #214107] Mon, 15 January 2007 23:50 Go to previous messageGo to next message
feizz
Messages: 16
Registered: December 2006
Location: Singapore
Junior Member

Hi Ross,
i'm sorry for the late reply.
However, here is the explain plan. As for the first query, i couldn't get the answer as it still taking time (too long). Anyway, i've got the explain plan for 1st and 2nd query.
I uploaded the explain plan.


Regards Faizal

Re: Query tune [message #214401 is a reply to message #214345] Tue, 16 January 2007 04:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
OK, this tells us that the query is driving off mpdope, using the index on one or both of pofaci='MF1' and a.postrt='STD'. Then, for each row returned, it is probing the other two tables.

Problem is, there are 337151 matching rows in mpdope, resulting in 337151 individual probes into the the other tables. ie. It is running the two sub-queries 337151 times!

Unfortunately, you still haven't given me enough information to solve this, but I can narrow it down to one of two likely possibilities: either there will be LOTS of rows in mpdope that match the sub-query, or there will be FEW.

First of all, you should gather statistics on all three tables using the following:
call DBMS_STATS.GATHER_TABLE_STATS('pfods', 'mpdope');
call DBMS_STATS.GATHER_TABLE_STATS('pfods', 'mitbal');
call DBMS_STATS.GATHER_TABLE_STATS('pfods', 'mmoplp');

The optimizer is clearly getting this very wrong - it thinks there are far fewer rows than there actually are. I suspect the statistics are way out of date. Try that; if it doesn't help, try each of the following:

Solution for LOTS of matching rows
select /*+ ORDERED USE_HASH(a) FULL(a)*/ 
      a.poplgr, min (a.poopno) poopno 
from pfods.mpdope a 
where a.pocono=1 
and   a.pofaci='MF1' 
and   a.postrt='STD' 
and   a.poopds != 'P250 SMD-AUTOMAT'
and   a.POPRNO in (
    select /*+ ORDERED USE_HASH(b) FULL(b) */
           b.mbitno 
    from   pfods.mitbal b 
    where  b.mbcono = 1 
    and    b.mbwhlo='MF1' 
    and    b.mbitno in (
        select /*+ FULL(c) */ 
               c.roprno 
        from   pfods.mmoplp c
        where  c.rofidt <= to_date('08-JAN-07','DD-MON-YY') 
        and    c.rofaci='MF1' 
        and    c.rocono=1
    )
)
group by a.poplgr

The Explain Plan for this should not show any index scans or Nested Loops joins. Post it here if you are in doubt.

Solution for FEW matching rows
select /*+ ORDERED USE_NL(a)*/ 
      a.poplgr, min (a.poopno) poopno 
from pfods.mpdope a 
where a.pocono=1 
and   a.pofaci='MF1' 
and   a.postrt='STD' 
and   a.poopds != 'P250 SMD-AUTOMAT'
and   a.POPRNO in (
    select /*+ ORDERED USE_NL(b)*/
           b.mbitno 
    from   pfods.mitbal b 
    where  b.mbcono = 1 
    and    b.mbwhlo='MF1' 
    and    b.mbitno in (
        select /*+ FULL(c) */ 
               c.roprno 
        from   pfods.mmoplp c
        where  c.rofidt <= to_date('08-JAN-07','DD-MON-YY') 
        and    c.rofaci='MF1' 
        and    c.rocono=1
    )
)
group by a.poplgr

This assumes that indexes exist on a.POPRNO and b.mbitno, and that less than 10% of each of these tables will match the sub-query. The Explain Plan should show 2 Nested Loops joins, and the tables in order of mmoplp, mitbal, mpdope from innermost to outermost in the Explain Plan.

Ross Leishman
Previous Topic: function errored out as saying exp not an identifier
Next Topic: simple query. PLEASE GIVE ANSWER
Goto Forum:
  


Current Time: Thu Dec 08 23:57:33 CST 2016

Total time taken to generate the page: 0.10444 seconds