Hello, I have a query that norma 1

[Romanichine]

Hello,

I have a query that normally takes 1 second to run but when I add an "ORDER BY" clause, it raises to 15 MINUTES!

Here is the query:

SELECT ss.srv_id, ss.service_no, Orig_pop.pop_name,
sstc.srv_type_code, ssc.Component_no, sssc.Status_Descr ,ss.Cust_Name,
ssc.Component_Descr, ssp.Name
FROM spar_services ss,spar_pop orig_pop, spar_service_type_codes sstc, spar_service_components ssc,
spar_service_status_codes sssc, spar_service_providers ssp,
spar_contracts sc, spar_services ss_parent,
spar_component_types sctc, spar_service_provider_accounts sspa, spar_pop dest_pop
WHERE ss.srv_id = ssc.srv_id
AND ssc.srv_status_id = sssc.srv_status_id (+)
AND ss.sp_id = ssp.sp_id (+)
AND ss.sp_acct_id = sspa.sp_acct_id (+)
AND ss.origin_pop_id = orig_pop.pop_id (+)
AND ss.dest_pop_id = dest_pop.pop_id (+)
AND ss.srv_type_id = sstc.srv_type_id (+)
AND ss.contract_id = sc.contract_id (+)
AND ssc.component_type_id = sctc.comp_type_id (+)
AND ss.srv_id = ss_parent.srv_id (+)
AND ss.srv_type_id <> 1
AND ss.Cust_Name LIKE('%TEST%')
ORDER BY ss.service_no, ssc.Component_no


Can anyone help?

Thanks
--
Roman

 

[Turkbear]

Hi, have you run an explain plan on this with and without the clause?

 

[Romanichine]

I ran autotrace with the order by on and it gives something like this (see below). When I run it without the order by clause I get costs of 48 and below...


0 SELECT STATEMENT Optimizer=CHOOSE (Cost=214722 Card=795805 B
ytes=237945695)

1 0 SORT (ORDER BY) (Cost=214722 Card=795805 Bytes=237945695)
2 1 NESTED LOOPS (OUTER) (Cost=32 Card=795805 Bytes=23794569
5)

3 2 NESTED LOOPS (OUTER) (Cost=32 Card=130674 Bytes=386795
04)

4 3 NESTED LOOPS (OUTER) (Cost=32 Card=421 Bytes=119143)
5 4 HASH JOIN (OUTER) (Cost=32 Card=421 Bytes=118301)
6 5 NESTED LOOPS (Cost=19 Card=421 Bytes=114091)
7 6 NESTED LOOPS (OUTER) (Cost=5 Card=7 Bytes=1687
)

8 7 NESTED LOOPS (OUTER) (Cost=5 Card=3 Bytes=68
4)

9 8 NESTED LOOPS (OUTER) (Cost=5 Card=2 Bytes=
430)

10 9 NESTED LOOPS (OUTER) (Cost=4 Card=1 Byte
s=185)

11 10 NESTED LOOPS (OUTER) (Cost=3 Card=1 By
tes=171)

12 11 TABLE ACCESS (BY INDEX ROWID) OF 'SP
AR_SERVICES' (Cost=2 Card=1 Bytes=158)

13 12 INDEX (RANGE SCAN) OF 'SS_NNI_ID'
(NON-UNIQUE) (Cost=1 Card=1)

14 11 TABLE ACCESS (BY INDEX ROWID) OF 'SP
AR_SERVICE_TYPE_CODES' (Cost=1 Card=19 Bytes=247)

15 14 INDEX (UNIQUE SCAN) OF 'SRVT_PK' (
UNIQUE)

16 10 TABLE ACCESS (BY INDEX ROWID) OF 'SPAR
_SERVICE_PROVIDERS' (Cost=1 Card=21 Bytes=294)

17 16 INDEX (UNIQUE SCAN) OF 'SP_PK' (UNIQ
UE)

18 9 TABLE ACCESS (BY INDEX ROWID) OF 'SPAR_P
OP' (Cost=1 Card=246 Bytes=7380)

19 18 INDEX (UNIQUE SCAN) OF 'SYS_C002408' (
UNIQUE)

20 8 INDEX (UNIQUE SCAN) OF 'CONTRACT_PK' (UNIQ
UE)

21 7 INDEX (UNIQUE SCAN) OF 'SYS_C002408' (UNIQUE
)

22 6 TABLE ACCESS (BY INDEX ROWID) OF 'SPAR_SERVICE
_COMPONENTS' (Cost=2 Card=6017 Bytes=180510)

23 22 INDEX (RANGE SCAN) OF 'SSC_SRV_ID' (NON-UNIQ
UE) (Cost=1 Card=6017)

24 5 TABLE ACCESS (FULL) OF 'SPAR_SERVICE_STATUS_CODE
S' (Cost=1 Card=4 Bytes=40)

25 4 INDEX (UNIQUE SCAN) OF 'CMP_TYP_PK' (UNIQUE)
26 3 INDEX (UNIQUE SCAN) OF 'SYS_C002455' (UNIQUE)
27 2 INDEX (UNIQUE SCAN) OF 'SPA_PK' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
17 consistent gets
0 physical reads
0 redo size
898 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed

 

[Turkbear]

Does the query without the order by complete in that 1 second or just start returning records?

Without an Order By the query can start returning records immediately - with the order by clause, however, the complete dataset returned by the query is held in memory until the sort is complete and then the data is returned to your screen..

 

[Romanichine]

The query is returning 4 rows (that's right only four and it takes 15 minutes!). So the &quot;start returning&quot; idea doesn't stand here.

--
Roman

 

[Turkbear]

That is odd, since the Autotrace output shows 12 sorts in memory which seems excessive for 4 rows...

 

[karluk]

If you haven't tried it already, you should try taking advantage of the fact that the unordered query runs fast. Perhaps you can fool Oracle into using the access path of the unordered query by doing something like

select * from
(place unordered query here)
order by ...

With any luck, the nested query will run fast and you will incur only the added overhead of ordering a four row result set at the end.

 

[DaPi]

I have something simliar:

select table_owner, table_name, u.index_name
from outln.djp$used_indexes u, sys.all_indexes s
where s.index_name=u.index_name
order by table_owner, table_name, u.index_name -- goes slow!
/

outln.djp$used_indexes is single cloumn varchar2, indexed and only 180 rows. 200 rows are returned.

Without the ORDER BY, it was &quot;instantaneous&quot;; with, it took about 3 minutes.

I don't have permissions on SYS objets to run explain plan as a normal user, so I ran it with autotrace as SYS. In BOTH cases results came back immediately - in the case of the ORDER BY the explain plan took several minutes to generate!

The stats differed by 1 memory sort, the explain plans were similar in the NL's (the order of two tables was inverted) the most drmatic difference was the &quot;Cost&quot; of the sort:

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5071958750 Card=1686
4521619 Bytes=5683343785603)

1 0 SORT (ORDER BY) (Cost=5071958750 Card=16864521619 Bytes=56
83343785603)

2 1 FILTER
3 2 NESTED LOOPS (OUTER) (Cost=17 Card=16864521619 Bytes=5
683343785603)

4 3 NESTED LOOPS (Cost=17 Card=414158193 Bytes=134187254
532)


Now I have done all that, the sorted query runs fast as a normal user!

I don't understand - but does this give anyone a clue to what is going on?

 

[DaPi]

Karluk's suggestion:
select * from
(place unordered query here)
order by ...
was slow too.

 

[sem]

Is OUTLN an ordinary user or system schema for using OUTLINEs?

 

[DaPi]

OUTLN was created (I think) when the db was created - yes, it is where the outlines go. I can't see anything special about it - it has CONNECT, DBA & RESOURCE roles.

So I copied the table to a non-system, DBA schema. For the first run BOTH the execution AND the explain plan (run as SYS) take several minutes! and there are now 36 memory sorts. Subsequent runs (as SYS or normal user) return the data fast but take ages for the explain plan (as SYS) and memory sorts is back down to 1.

My conclusion is that, for some obscure reason, the parsing/optimisation phase is very slow. So the total time for the query is long, repeating the IDENTICAL query runs fast ('cos it's in the pool).

Romanichine - if you hit the / after a slow execution , is it faster the second time?

 

[DaPi]

Further thoughts trying to find common ground:

The SYS tables have not been ANALYZED. Is that possible/advisable?

Romanichine - have you run
ANALYZE TABLE <xx> COMPUTE STATISTICS;
on the tables in your query?

 

[jad]

have you come across a tool called 'toad'

http://www.quest.com/toad/

the expert edition has a tuning tool which is brilliant. you can download a trial version there as well.

there is a freeware version of toad

http://www.toadsoft.com

... but unfortunately it doesn't seem to do tuning ...

 

[Romanichine]

DaPi,

Yes the query is fast the second time. I guess it's because it's cached by Oracle.

--
Roman

 

[Romanichine]

DaPi,

I tried
ANALYZE TABLE <xx> COMPUTE STATISTICS;

I got the following message &quot;Table analyzed.&quot; but I don't know how to see the results. Can you tell me?

--
Roman

 

[DaPi]

Hi Roman,

YOU don't need to see the results, the optimiser uses them! (The stats are stored in SYS tables; you can see them in views like user_tables, user_indexes - the analyze fills in columns like num_rows and distinct_keys). I run this for all my tables every week-end.

The question now is - if you analyze all the tables in the query does it run faster even after a db restart?

 

[Romanichine]

DaPi,

I ran the analyze table, restarted Oracle and WOW it's fast any way now!!

I guess my problem isn't the same than yours but thank you so much for pointing this solution to me!

--
Roman

 

[DaPi]

For the record, I ran ANALYZE on the SYS tables (does not seem to have caused any havoc - but do it at your own risk & peril) and my query & explain both run fast now. So I was on the right track thinking we had a common prob.

Can anyone out there explain what was going on to cause the parse/optimise to be so slow (if indeed that was what was happening)?