Home » RDBMS Server » Performance Tuning » sql slow
sql slow [message #278892] Tue, 06 November 2007 03:31 Go to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
I have this two statements. the first one runs fast and returns no rows, but the second one hangs.

----
1. select b.centre, b.gl_code, A.NL_ACCOUNT_CODE, sum(a.amount) "Actual Movement", NVL(C.AMOUNT, 0) "Year End Value",
NVL(C.AMOUNT, 0) - sum(a.amount) "Difference"
from nlacvals a, nlpaccn b, nlacvals c
where a.company = 'UK'
and b.gl_code = '93231'
AND B.COMPANY = A.COMPANY
AND B.NL_ACCOUNT_CODE = A.NL_ACCOUNT_CODE
AND A.PERIOD_NO BETWEEN 200000 AND 200012
AND C.COMPANY (+) = A.COMPANY
AND C.NL_ACCOUNT_CODE (+) = A.NL_ACCOUNT_CODE
AND C.PERIOD_NO (+) = 200100
GROUP BY B.CENTRE, B.GL_CODE, A.NL_ACCOUNT_CODE, C.AMOUNT
HAVING NVL(C.AMOUNT, 0) - sum(a.amount) <> 0
ORDER BY 1,2

---

2.

select b.centre, b.gl_code, A.NL_ACCOUNT_CODE, sum(a.amount) "Actual Movement", NVL(C.AMOUNT, 0) "Year End Value",
NVL(C.AMOUNT, 0) - sum(a.amount) "Difference"
from nlacvals a, nlpaccn b, nlacvals c
where a.company = 'UK'
and b.gl_code = '93231'
AND B.COMPANY = A.COMPANY
AND B.NL_ACCOUNT_CODE = A.NL_ACCOUNT_CODE
AND A.PERIOD_NO BETWEEN 200100 AND 200112
AND C.COMPANY (+) = A.COMPANY
AND C.NL_ACCOUNT_CODE (+) = A.NL_ACCOUNT_CODE
AND C.PERIOD_NO (+) = 200200
GROUP BY B.CENTRE, B.GL_CODE, A.NL_ACCOUNT_CODE, C.AMOUNT
HAVING NVL(C.AMOUNT, 0) - sum(a.amount) <> 0
ORDER BY 1,2
--


Any idea where tuing is required

explain plan.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=108)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=12 Card=1 Bytes=108)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'NLPACCN' (Cost=2 Car
d=1 Bytes=30)

4 3 NESTED LOOPS (Cost=4 Card=1 Bytes=108)
5 4 NESTED LOOPS (OUTER) (Cost=2 Card=1 Bytes=78)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'NLACVALS' (Cos
t=1 Card=1 Bytes=39)

7 6 INDEX (RANGE SCAN) OF 'NLACVALS_PK' (UNIQUE) (
Cost=2 Card=1)

8 5 TABLE ACCESS (BY INDEX ROWID) OF 'NLACVALS' (Cos
t=1 Card=1 Bytes=39)

9 8 INDEX (RANGE SCAN) OF 'NLACVALS_PK' (UNIQUE) (
Cost=1 Card=1)

10 4 INDEX (RANGE SCAN) OF 'XIF1667NLPACCN' (NON-UNIQUE
) (Cost=1 Card=267)

------

help appreciated


Re: sql slow [message #278893 is a reply to message #278892] Tue, 06 November 2007 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Also read How to Identify Performance Problem and Bottleneck .

Regards
Michel

[Updated on: Tue, 06 November 2007 03:34]

Report message to a moderator

Re: sql slow [message #278895 is a reply to message #278892] Tue, 06 November 2007 03:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you say 'hangs', how long have you let it run for before losing patience with it?

When were the statistics last refreshed?

Re: sql slow [message #278897 is a reply to message #278895] Tue, 06 November 2007 03:52 Go to previous messageGo to next message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
nlacaval - never analysed
nlpaccn - 31-10-2007

It takes more than an hour and then kill it.
Re: sql slow [message #278899 is a reply to message #278897] Tue, 06 November 2007 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Run Dbms_Stats on the un-analysed table and try again.

It's a bad idea to have only some of your tables analysed, as it forces the CBO to use it's default statistics for your table, and they're almost certain to be badly wrong.
Re: sql slow [message #278900 is a reply to message #278897] Tue, 06 November 2007 04:07 Go to previous message
mwansalovewell
Messages: 71
Registered: October 2007
Location: uk
Member
analysed the table and indexes

the query now runs as expected less than a second.

thanks.
Previous Topic: Help Please
Next Topic: Performance Bottleneck!!!
Goto Forum:
  


Current Time: Tue Dec 06 00:20:05 CST 2016

Total time taken to generate the page: 0.13308 seconds