Home » SQL & PL/SQL » SQL & PL/SQL » SQL
SQL [message #268831] Wed, 19 September 2007 21:25 Go to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
Hello Experts,

This is a very weird query, I've put it on the server to run so as to monitor it easily, it's been more than 30 hours and still running and not using too much temp space no waits no locks etc. but CPU time is steadly increasing ..... looks like spinning.

select distinct T373917.INTEGRATION_ID as c1,
T373917.NAME as c2,
T374137.NAME as c3,
T374137.ATTRIB_02 as c4
from W_ORG_D T373917, WC_ORG_EXT_XM_D T374137, W_ORG_FXM T375143
where ( T373917.ROW_WID = T375143.ACCOUNT_WID (+) and T373917.INTEGRATION_ID = T374137.ACCOUNT_ID (+) ) and ( T373917.ACCNT_FLG = 'Y' )
order by c1, c2, c3, c4

Any help would be much appreciated.

Thanks!
Re: SQL [message #268833 is a reply to message #268831] Wed, 19 September 2007 21:31 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Is it spinning clockwise or counter clockwise?

>from W_ORG_D T373917, WC_ORG_EXT_XM_D T374137, W_ORG_FXM T375143

Why is W_ORG_FXM T375143 included in the FROM clause when it contributes NO data to the SELECT clause?
If it were removed & subordinated into the WHERE clause your query might complete sooner.
Re: SQL [message #268839 is a reply to message #268833] Wed, 19 September 2007 22:03 Go to previous messageGo to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
I don't get it...how do you remove W_ORG_FXM from the FROM clause but yet use one of its attributes in the WHERE clause?
Re: SQL [message #268842 is a reply to message #268831] Wed, 19 September 2007 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> yet use one of its attributes in the WHERE clause?

Upon further review:
select distinct T373917.INTEGRATION_ID as c1,
       T373917.NAME as c2,
       T374137.NAME as c3,
       T374137.ATTRIB_02 as c4
from W_ORG_D T373917, WC_ORG_EXT_XM_D T374137
where T373917.INTEGRATION_ID = T374137.ACCOUNT_ID (+) 
 and  T373917.ACCNT_FLG = 'Y' 
order by c1, c2, c3, c4
Re: SQL [message #268843 is a reply to message #268831] Wed, 19 September 2007 22:11 Go to previous messageGo to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
Sorry, I need that WHERE condition.. or maybe not since its a left outer?

Quote:
T373917.ROW_WID = T375143.ACCOUNT_WID (+)

[Updated on: Wed, 19 September 2007 22:21]

Report message to a moderator

Re: SQL [message #268844 is a reply to message #268831] Wed, 19 September 2007 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Sorry, I need that WHERE condition,
If you say so.

You're On Your Own (YOYO)!
Re: SQL [message #268854 is a reply to message #268844] Wed, 19 September 2007 22:43 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post an Explain Plan.

Ross Leishman
Re: SQL [message #268881 is a reply to message #268831] Thu, 20 September 2007 00:03 Go to previous messageGo to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
Explain plan from one of our smaller environments.

http://i5.photobucket.com/albums/y185/mechos/ExplainPlan.jpg

[Updated on: Thu, 20 September 2007 00:22]

Report message to a moderator

Re: SQL [message #268894 is a reply to message #268831] Thu, 20 September 2007 00:59 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
You should use index on table(s) As your table is full scan

[Updated on: Thu, 20 September 2007 01:00]

Report message to a moderator

Re: SQL [message #268900 is a reply to message #268894] Thu, 20 September 2007 01:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
muzahidul islam wrote on Thu, 20 September 2007 07:59
You should use index on table(s) As your table is full scan

You did notice the hash joins?
Re: SQL [message #268943 is a reply to message #268900] Thu, 20 September 2007 03:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This plan is saying that the tables are quite small. I suspect you have not gathered statistics. Use DBMS_STATS.GATHER_TABLE_STATS to gather statistics and try again.

Ross Leishman
Re: SQL [message #268984 is a reply to message #268943] Thu, 20 September 2007 06:08 Go to previous messageGo to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
rleishman wrote on Thu, 20 September 2007 03:12
This plan is saying that the tables are quite small. I suspect you have not gathered statistics. Use DBMS_STATS.GATHER_TABLE_STATS to gather statistics and try again.

Ross Leishman


I ran this query on a smaller environment so I could get the execution plan. On our bigger environment this query doesn't even complete. Let me at least give the record counts so it gives you an idea.

SELECT COUNT(*) FROM W_ORG_D
2328146

SELECT COUNT(*) FROM WC_ORG_EXT_XM_D
3949852

SELECT COUNT(*) FROM W_ORG_FXM
10512185
Re: SQL [message #268986 is a reply to message #268984] Thu, 20 September 2007 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Time to gather new statistics!

Regards
Michel
Re: SQL [message #268994 is a reply to message #268984] Thu, 20 September 2007 06:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the execution plan for the large query?
Re: SQL [message #269001 is a reply to message #268831] Thu, 20 September 2007 07:08 Go to previous messageGo to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
Sadly it never completes, that's the issue.. would indexing the columns involved in the WHERE clause help with performance?

[Updated on: Thu, 20 September 2007 07:19]

Report message to a moderator

Re: SQL [message #269004 is a reply to message #269001] Thu, 20 September 2007 07:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't need to have run the query to generate an explain plan for it.
SQL> explain plan for select sysdate from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Working out whether indexes would help is one of the things we need the Plan for.

[Updated on: Thu, 20 September 2007 07:30]

Report message to a moderator

Re: SQL [message #269016 is a reply to message #268831] Thu, 20 September 2007 07:54 Go to previous messageGo to next message
mechos
Messages: 11
Registered: September 2007
Junior Member
PLAN_TABLE_OUTPUT
 
---------------------------------------------------------------------------------
| Id  | Operation            |  Name            | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                  |    35M|  2896M|       |   724K|
|   1 |  SORT UNIQUE         |                  |    35M|  2896M|  6309M|   371K|
|*  2 |   HASH JOIN OUTER    |                  |    35M|  2896M|   346M| 18720 |
|*  3 |    HASH JOIN OUTER   |                  |  3949K|   301M|    58M|  9166 |
|*  4 |     TABLE ACCESS FULL| W_ORG_D          |  1164K|    45M|       |  4851 |
|   5 |     TABLE ACCESS FULL| WC_ORG_EXT_XM_D  |  3949K|   146M|       |  1091 |
|   6 |    TABLE ACCESS FULL | W_ORG_FXM        |    10M|    50M|       |  2913 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("T373917"."ROW_WID"="T375143"."ACCOUNT_WID"(+))
   3 - access("T373917"."INTEGRATION_ID"="T374137"."ACCOUNT_ID"(+))
   4 - filter("T373917"."ACCNT_FLG"='Y')
 
Note: cpu costing is off
Re: SQL [message #269017 is a reply to message #269016] Thu, 20 September 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure you want to return 35 million rows?

Regards
Michel
Re: SQL [message #269020 is a reply to message #269016] Thu, 20 September 2007 08:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well that explains it - you're query is trying to do a distinct on about 35,000,000 rows. That's not going to finish any time soon.

Are the statistics used by the query accurate?
Are there roughly 1.1 million rows in W_ORG_D with ACCNT_FLAG = 'Y'

Are you sure you're not missing a join condition on those tables?

If I were you, I'd try running Anacedent's version of your query on your test DB and seeing if the result set is the same as your current version of the query (I will be very suprised if it's different).
That should return a lot fewer rows.
Re: SQL [message #269031 is a reply to message #269020] Thu, 20 September 2007 08:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Am I overseeing the obvious?

I can see no carthesian product in the query, yet the resultset is bigger than each of the tables.
Where's the craig?
Re: SQL [message #269044 is a reply to message #268831] Thu, 20 September 2007 09:35 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> WHERE T373917.ROW_WID = T375143.ACCOUNT_WID (+)
seems to be match every row in T375143 to every row in T373917; even though NO data in T375143 is returned to the SELECT columns!
Other than causing problems I still see no use for this part of the WHERE clause.
But if OP insists it is needed , he is free to keep in the query.
It does not bother my system's performance; just his.
Re: SQL [message #269162 is a reply to message #269044] Thu, 20 September 2007 22:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are joining on non-unique keys. The result is that a join of tables no larger than 4M rows is returning 35M rows (if Oracle's guess is even remotely correct - it could be a lot worse).

You almost certainly do not understand your data model. Show the query to someone in your office who does understand the data model - or any EXPERIENCED data modeller who has access to your system - and get them to correct it so that you are joining on unique keys.

Now, the principal virtue of the programmer being laziness, I expect you not to bother asking around - that could take MINUTES of your time; you'd much rather the query magically start to run faster and return the correct results. So bearing that in mind, here's how you write mind-bogglingly stupid (yet somewhat more efficient) code.

select distinct 
       T373917.INTEGRATION_ID as c1,
       T373917.NAME as c2,
       T374137.NAME as c3,
       T374137.ATTRIB_02 as c4
from   
(      SELECT DISTINCT 
              ROW_WID
       ,      INTEGRATION_ID
       ,      NAME
       FROM   W_ORG_D
       WHERE  ACCNT_FLG = 'Y' 
) T373917
, (
       SELECT DISTINCT
              ACCOUNT_ID 
       ,      NAME
       ,      ATTRIB_02
       FROM   WC_ORG_EXT_XM_D
) T374137
, (
       SELECT DISTINCT
              ACCOUNT_WID 
       FROM   W_ORG_FXM
) T375143
where T373917.ROW_WID = T375143.ACCOUNT_WID (+) 
and   T373917.INTEGRATION_ID = T374137.ACCOUNT_ID (+) 
order by c1, c2, c3, c4


The trick here is to remove the duplicates so that you get a more nearly unique join

If you actually implement this, please don't quote me, I'd rather not be associated with it.

Ross Leishman
Previous Topic: i need to email invoices to customers
Next Topic: Optional Field in form
Goto Forum:
  


Current Time: Sat Dec 03 12:22:21 CST 2016

Total time taken to generate the page: 0.07433 seconds