Home » RDBMS Server » Performance Tuning » SQL Query too slow (11g, Windows)
SQL Query too slow [message #613605] Thu, 08 May 2014 08:17 Go to next message
aldy
Messages: 6
Registered: May 2014
Location: India
Junior Member
Hi,

Database Version - 11.2.0.2.0

I've 2 tables - A and B each with 120 million records and I'm trying to pull additional records alone from Table A.
There are 4 columns getting joined on either tables and all those columns are been indexed and statistics have been gathered recently.

I've tried NOT EXISTS, OUTER JOIN but to avail, very slow response time.

Would be great if you could share some ideas on how to improve the performance.
Re: SQL Query too slow [message #613606 is a reply to message #613605] Thu, 08 May 2014 08:22 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Welcome!

aldy wrote on Thu, 08 May 2014 18:47
I've tried NOT EXISTS, OUTER JOIN but to avail, very slow response time.


We don't have enough information to suggest.

Please post the required details as mentioned here How to tune SQL or Identify Performance Problem and Bottleneck

[Updated on: Thu, 08 May 2014 08:24]

Report message to a moderator

Re: SQL Query too slow [message #613607 is a reply to message #613605] Thu, 08 May 2014 08:28 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Sounds like you want rows in table A not in table B.
  • Make sure that all 4 columns in table A are defined as NOT NULL
  • Make sure that all 4 columns in table B are defined as NOT NULL
  • Create an index on the 4 join columns of table B
  • Write your SQL as a NOT IN sub-query

Ross Leishman
Re: SQL Query too slow [message #613608 is a reply to message #613607] Thu, 08 May 2014 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I've tried NOT EXISTS, OUTER JOIN but to avail, very slow response time.
did all give the desired result set?
How do we know which result set in the one you desire?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: SQL Query too slow [message #613609 is a reply to message #613607] Thu, 08 May 2014 08:46 Go to previous messageGo to next message
aldy
Messages: 6
Registered: May 2014
Location: India
Junior Member
I tried using NOT IN as well and after 40 to 50 mins, i killed the session as it was taking too long.
Re: SQL Query too slow [message #613612 is a reply to message #613609] Thu, 08 May 2014 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: SQL Query too slow [message #613614 is a reply to message #613609] Thu, 08 May 2014 08:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
aldy wrote on Thu, 08 May 2014 19:16
I tried using NOT IN as well and after 40 to 50 mins, i killed the session as it was taking too long.


Ross did not suggest only that, but 3 other points as well. When all those points are put together and applied then you might see some difference. But to comment about anything, we would require some additional information. Please follow the links provided to you and post the required details.
Re: SQL Query too slow [message #613618 is a reply to message #613614] Thu, 08 May 2014 09:07 Go to previous messageGo to next message
aldy
Messages: 6
Registered: May 2014
Location: India
Junior Member
SQL Used
SELECT FND.C1,
  FND.C2,
  FND.C3,
  FND.C4
FROM A FND,
  B stg
WHERE FND.ROW_FLAG = 'Y'
AND FND.C1         = STG.C1(+)
AND FND.C2         = STG.C2(+)
AND FND.C3         = STG.C3(+)
AND FND.C4         = STG.C4(+)
AND STG.C1        IS NULL;

Explain plan for the same
Plan hash value: 1870775268
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                            |  1441K|    49M|       |   975K  (3)| 03:15:12 |       |       |
|*  1 |  FILTER                  |                            |       |       |       |            |          |       |       |
|*  2 |   HASH JOIN RIGHT OUTER  |                            |  1441K|    49M|  3985M|   975K  (3)| 03:15:12 |       |       |
|   3 |    TABLE ACCESS FULL     | B                          |   144M|  2336M|       | 52592  (10)| 00:10:32 |       |       |
|   4 |    PARTITION RANGE ALL   |                            |   144M|  2611M|       |   506K  (3)| 01:41:13 |     1 |1048575|
|   5 |     PARTITION LIST SINGLE|                            |   144M|  2611M|       |   506K  (3)| 01:41:13 |   KEY |   KEY |
|   6 |      TABLE ACCESS FULL   | A                          |   144M|  2611M|       |   506K  (3)| 01:41:13 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("STG"."C1" IS NULL)
   2 - access("FND"."C1"="STG"."C1"(+) AND 
              "FND"."C2"="STG"."C2"(+) AND 
              "FND"."C3"="STG"."C3"(+) AND "FND"."C4"="STG"."C4"(+))


[RL: code tags]

[Updated on: Thu, 08 May 2014 16:37] by Moderator

Report message to a moderator

Re: SQL Query too slow [message #613620 is a reply to message #613618] Thu, 08 May 2014 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT C1, C2, C3, C4 FROM A
MINUS
SELECT C1, C2, C3 C4 FROM B;
Re: SQL Query too slow [message #613624 is a reply to message #613620] Thu, 08 May 2014 09:34 Go to previous messageGo to next message
aldy
Messages: 6
Registered: May 2014
Location: India
Junior Member
Blackswan,

the cost has shooted like anything after using the MINUS operator and do you think using minus operator on 140 million records table is a good practice??

Plan hash value: 68970491

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 144M| 4947M| | 2251K (41)| 07:30:15 | | |
| 1 | MINUS | | | | | | | | |
| 2 | SORT UNIQUE | | 144M| 2611M| 4981M| 1379K (4)| 04:35:55 | | |
| 3 | PARTITION RANGE ALL | | 144M| 2611M| | 506K (3)| 01:41:13 | 1 |1048575|
| 4 | PARTITION LIST SINGLE| | 144M| 2611M| | 506K (3)| 01:41:13 | KEY | KEY |
| 5 | TABLE ACCESS FULL | A | 144M| 2611M| | 506K (3)| 01:41:13 | KEY | KEY |
| 6 | SORT UNIQUE | | 144M| 2336M| 4415M| 871K (5)| 02:54:20 | | |
| 7 | TABLE ACCESS FULL | B | 144M| 2336M| | 52592 (10)| 00:10:32 | | |
-------------------------------------------------------------------------------------------------------------------------------
Re: SQL Query too slow [message #613628 is a reply to message #613624] Thu, 08 May 2014 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
which columns have NOT NULL constraint?

>the cost has shooted like anything after using the MINUS operator and do you think using minus operator on 140 million records table is a good practice??
you are free to use any SQL that gives the desired result set.
First make work, then make it faster.

a result set that is returned quickly but contains wrong data is sub-optimal.
Re: SQL Query too slow [message #613629 is a reply to message #613624] Thu, 08 May 2014 10:38 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP,

1. Did you observe the FTS on both tables? Do you have an idea about why?
2. Do you really need an outer join? Because, some/most of the times it has been observed that an outer join is used unnecessarily adding much more overhead than required.
3. You are yet to explain about the indexes.
4. What about the integrity constraints?
5. If point 4 is answered, you need to tell us whether the foreign keys are supported by an index. Since it plays an important role in performance, strictly speaking about dunsel join removals.
And much more...

P.S. - Not sure completely if dunsel join removal would actually apply here.

[Updated on: Thu, 08 May 2014 10:40]

Report message to a moderator

Re: SQL Query too slow [message #613644 is a reply to message #613629] Thu, 08 May 2014 16:51 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Could be that you're running out of memory. According to your first plan, Oracle estimates it will need about 4GB of the PGA for the hash join, otherwise it will spill to disk and take a lot longer.

Take a look at this article on Serial Partition-Wise Joins. They can be used to make memory bound joins more scalable.
The idea would be partiton (or sub-partition) BOTH tables on the 4 join keys. I would try it with 128 partitions. If you can't change the partitioning of the table - and you are ONLY selecting columns from the join key (not selecting any other columns) then you could try creating globally partitioned indexes on both tables - GLOBALLY PARTITIONED BY HASH (A,B,C,D) PARTITIONS 128

The idea here is to lower the memory footprint

Ross Leishman
Re: SQL Query too slow [message #613673 is a reply to message #613644] Fri, 09 May 2014 02:02 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If your stats really are up to date, I am thinking "A" is a really "fat" table? Oo there are LOBs in there or something?

It's estimating 10 minutes to scan "B" but 10 times that value to do the same to "A".

"B" is more like the expected performance - Is there's something funky with "A"?

[Updated on: Fri, 09 May 2014 02:03]

Report message to a moderator

Re: SQL Query too slow [message #613681 is a reply to message #613673] Fri, 09 May 2014 02:30 Go to previous messageGo to next message
aldy
Messages: 6
Registered: May 2014
Location: India
Junior Member
A table has indexes created on each column, whereas table B has a composite primary key on those 4 columns.
Re: SQL Query too slow [message #613683 is a reply to message #613681] Fri, 09 May 2014 02:45 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                            |  1441K|    49M|       |   975K  (3)| 03:15:12 |       |       |
|*  1 |  FILTER                  |                            |       |       |       |            |          |       |       |
|*  2 |   HASH JOIN RIGHT OUTER  |                            |  1441K|    49M|  3985M|   975K  (3)| 03:15:12 |       |       |
|   3 |    TABLE ACCESS FULL     | B                          |   144M|  2336M|       | 52592  (10)| 00:10:32 |       |       |
|   4 |    PARTITION RANGE ALL   |                            |   144M|  2611M|       |   506K  (3)| 01:41:13 |     1 |1048575|
|   5 |     PARTITION LIST SINGLE|                            |   144M|  2611M|       |   506K  (3)| 01:41:13 |   KEY |   KEY |
|   6 |      TABLE ACCESS FULL   | A                          |   144M|  2611M|       |   506K  (3)| 01:41:13 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------


Ummm... this says your Table A has 1,048,575 partitions. Is that right? Wouldn't each partition have about 100 rows on average?

It doesn't really sound optimal. Just for kicks, try it on a table with no fewer than 1M rows per partition and see how that goes.

Another thought, have you ever deleted lots of data from Table A? Those empty but previously used blocks could still be costly in a full table scan.

Ross Leishman
Re: SQL Query too slow [message #613684 is a reply to message #613683] Fri, 09 May 2014 02:53 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
@Ross.

More likely interval partitioned. It looks like that in plans.

[Updated on: Fri, 09 May 2014 02:53]

Report message to a moderator

Re: SQL Query too slow [message #613704 is a reply to message #613684] Fri, 09 May 2014 06:29 Go to previous messageGo to next message
aldy
Messages: 6
Registered: May 2014
Location: India
Junior Member
@Ross,Roach

Yes this is indeed RANGE partition and to the question raised by Ross, Yes we do truncate and load the data often from the file received from source systems.
Re: SQL Query too slow [message #613742 is a reply to message #613605] Fri, 09 May 2014 16:42 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There is way too much missing information here. But we will try anyway.

I suspect first that the number of partitions, if it really is 1 million plays a significant role here. That seems like way way way too many partitions to me but what do I know.

Second, unless your partitioning is based on some subset of your join columns, I won't help you much. Oracle will simple have to repartition all the data anyway.

It would help if you provided the following:

1. the basis of the partitioning on both tables (both tables are partitioned the same way right?)
2. primary key definitions on the two tables
3. indications of if the join columns are defined as null or not null
4. the predicates portion of the query plan as just the plan_output is almost useless
5. existence of any foreign key constraints between the two tables and what these are
6. maybe the real query? as I doubt this is it

The use of outer-join as a away to simulate an anti-join does not work well in cases where there are lots of matches because these matches must be done first and then tossed out, hence the FILTER operation at line 1. Best I think to go back to the NOT EXISTS. That way the join attempts will stop for each key once a match is found. Unless of course you are joining across what is the primary key of both tables.

Third, please explain the partitioning pattern you are using.

Last, what kind of changes are you willing to make?

1. would you be willing to change the partitioning scheme of these two tables?
2. would you be willing to increase pga_aggregate_target?
3. would you be willing to use parallel query (IF IT IS APPROPRIATE, MIGHT NOT BE FOR YOUR SYSTEM)?

if the only purpose is to make this query go fast then the following seems to me to be the right approach.

hash partition (f1,f2,f3,f4) partitions 32 (do this on both tables (check the docs for the proper syntax))

select /*+ parallel (4) */ ...
from a
where not exists (select null from b where b.c1 = a.c1 ...)
/

What we would be looking for from this would be:

1. for the hash partitioning to make the joins small, about 4 million rows each
2. for the parallel query to allow you to join multiple partitions concurrently. I have shown 4 here but you can increase or decrease based on your systems available resources
3. for the anti-join to stop once a match is found and thus minimize join attempts (assuming we get an anti-join in the query plan)

I am guessing the corresponding plan would look like this, but with parallel and partition info too

|   0 | SELECT STATEMENT      |  
|   1 |  HASH JOIN RIGHT ANTI |  
|   2 |   TABLE ACCESS FULL   | B
|   3 |   TABLE ACCESS FULL   | A


As with most queries there are two basic costs:

1. the cost of initially getting data from the tables (precision query vs. warehouse query)
2. the cost of everything else in query execution (read set operations like joins/sorts/etc.)

You goal is to minimize the biggest pieces, possibly both types of costs.

For #1 (initial data acquisition) think row filtering (you have none), and column projection (think covering indexes or EXADATA, neither of which you have indicated you have).
For #2 (other stuff) for this query means make the join efficient which means memory management and partitioning to achieve optimal or one-pass join thus minimizing temp space usage.

Good luck, Kevin

[Updated on: Fri, 09 May 2014 17:06]

Report message to a moderator

Re: SQL Query too slow [message #613754 is a reply to message #613742] Sat, 10 May 2014 10:18 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Well described by Kevin. Very informative.

The only thing I contemplate is that, whether supporting indexes for the foriegn keys would help with respect to dunsel join removal. I would really want to know if it implies in OP's case if OP provides this information.

[Updated on: Sat, 10 May 2014 10:24]

Report message to a moderator

Re: SQL Query too slow [message #613755 is a reply to message #613605] Sat, 10 May 2014 10:21 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Nope. There will be no join elimination (oracle's officle term for what I call Dunsel Join Removal). The existential question cannot be answered only through knowledge of constraints. We actually have to visit the data for this one.

Kevin
Previous Topic: High CPU Consuming Query
Next Topic: Please help me execution plan is looking bad
Goto Forum:
  


Current Time: Thu Mar 28 09:43:35 CDT 2024