Operating System - HP-UX
1748179 Members
4109 Online
108758 Solutions
New Discussion юеВ

Re: improving oracle sql statement

 
SOLVED
Go to solution
Graham Cameron_1
Honored Contributor

Re: improving oracle sql statement

A couple of ideas.
First, the trivial:
From SQL*PLus, SET COLSEP "" (where is the tab character) will delimit columns by tabs rather than spaces, so there is no need for syntax like
select a||''||b||...

Second, your query is picking rows based on F4111.ILDCT and F46011.IQUWUM. Are these cols indexed? If the number of distinct values in each is low you should consider a bitmap index.

Other than that, once you've exhausted the explain plan and trace options, I should contact oracle support (assuming you have a contract).

-- Graham

Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Steven Gillard_2
Honored Contributor

Re: improving oracle sql statement

As long as you turn on tracing for your session only and turn it off after the query the resulting trace file should be quite small, even if it takes ages to run. DONT turn it on in init.ora, that will enable it in all sessions!

Tkprof will give you much more information about where the bulk of the work is being done, then you can focus your tuning efforts there. As with any performance problem, change one thing at a time and measure with further tracing. If you can, set up a test system with a proportionally smaller version of the DB.

A couple of other points / things to try:

* Run the query at a quieter time - if there is loads of DML activity on the queried data performance will be severely hit because Oracle will have to keep pulling blocks out of rollback (and you run the risk of snapshot too old problems).

* Omit the complicated date calculations you're doing, just for comparison sake.

* Try doing some of the queries as temporary views (ie nested select statements in the from clause), especially the query on F4111 as its your largest table. That has helped me in the past.

* Measure other DB performance indicators such as buffer cache hit ratio while the query is being run - you may need to increase your SGA size.

And try posting to the comp.databases.oracle.server - there are many more SQL experts there!

Regards,
Steve
Yogeeraj_1
Honored Contributor

Re: improving oracle sql statement

hi,

you should call SQL Trace and TKPROF to the rescue.

For more details see: http://otn.oracle.com/doc/oracle8i_816/server.816/a76992/ch14_str.htm

If possible post your output so that we can have a look.

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
bob hollis
Frequent Advisor

Re: improving oracle sql statement

here are the results of tkprof
This one only took 4 hours because I ran it on a test machine that isn't used much - about the same number of records though.
I did find out the following from looking at the where clause - the *LITM compares char(25)
and the 2 *MCU compares are char(12) - those might be expensive.
Any other insights? You guys are great!

SELECT F4111.ILLITM, F4111.ILMCU, F4111.ILDCT,
to_char(to_date(substr(to_char(F4111.ILTRDJ),1,3)+1900||substr(to_char(F4111.ILTRDJ),4,3),'YYYYDDD'),'YYYYMMDD') ,
F4111.ILTRQT, F4111.ILPAID, F4102.IBSRP6, F4801T.WALINE, F46011.IQUOM, F46011.IQGWEI, F46011.IQUWUM, F4111.ILRCD
FROM JDEDATA.F4111 , JDEDATA.F4102 , JDEDATA.F4801T , JDEDATA.F46011
WHERE
F4111.ILLITM = F4102.IBLITM AND
F4111.ILMCU = F4102.IBMCU AND
F4111.ILDOCO = F4801T.WADOCO AND
F4111.ILMCU = F46011.IQMCU AND
F4111.ILITM = F46011.IQITM AND (F4111.ILDCT = 'IC' OR F4111.ILDCT = 'IS') AND
F46011.IQUWUM = 'KG' AND F46011.IQUOM = 'EA'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 19827 10914.94 14058.22 253810 865676081 4 297376
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19829 10914.94 14058.22 253810 865676081 4 297376

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
297376 NESTED LOOPS
325541 NESTED LOOPS
325541 NESTED LOOPS
31661 TABLE ACCESS FULL F46011
357200 TABLE ACCESS BY INDEX ROWID F4111
1312360 INDEX RANGE SCAN (object id 10281)
651080 TABLE ACCESS BY INDEX ROWID F4102
1901694724 INDEX RANGE SCAN (object id 7132)
297376 TABLE ACCESS BY INDEX ROWID F4801T
622916 INDEX UNIQUE SCAN (object id 8009)

********************************************************************************
Volker Borowski
Honored Contributor

Re: improving oracle sql statement

Hi,

first of all, it would be of help to know which indexes are defined.

SQL>
select TABLE_NAME, INDEX_NAME, COLUMN_POSITION, substr(column_name,1,30)
from DBA_IND_COLUMNS
where table_name in ( 'F46011','F4801T','F4102','F4111' )
order by TABLE_NAME, INDEX_NAME, COLUMN_POSITION ;

Second make sure that all tables and indexes are analyzed if possible with COMPUTE STATISTICS.

Third let's check, how the data is distributed for the indexed columns:

select a.TABLE_NAME, a.COLUMN_NAME, a.num_distinct
from dba_TAB_COL_STATISTICS a
where a.table_name in ( 'F46011','F4801T','F4102','F4111' )
and a.column_name in
( select distinct b.column_name from DBA_IND_COLUMNS b where b.table_name=a.table_name);

From the explain, one would suggest, that the FULL SCAN on F46011 ist the disturbing one (which is most likely) but it does not have to be this one.
Esp. it would be interesting to know, how many rows you get for
select count(*) from F46011 where
F46011.IQUWUM = 'KG' AND F46011.IQUOM = 'EA' ;

If this is significantly small against the 39508 rows, go for a combined index on those two columns and if it does not reduce the access time specify this index via hint on your query.

Good hunting
Volker
Steven Gillard_2
Honored Contributor

Re: improving oracle sql statement

> 651080 TABLE ACCESS BY INDEX ROWID F4102
> 1901694724 INDEX RANGE SCAN (object id 7132)

Ouch... almost 2 billion rows compared when table F4102 is brought into the join.

For comparison sake how much faster is the query if you remove F4102 (there's only one selected field from this table)?

What fields on this table are indexed?

I'd really start experimenting on your test system. Break down the query into smaller chunks and work out exactly where all the work is being done; create the index others have suggested; try to change the join order (with the /* +ORDERED */ hint) so that F4102 is joined in last; try using star queries...

Regards,
Steve
Volker Borowski
Honored Contributor

Re: improving oracle sql statement

Steven has a good point.
This seems extremly expensive, esp. as F4102 has only 160.000 rows !

Since F4111 is the biggest one, I'd like to know in addition how many rows your get on

select count(*) from f4111
where ILDCT = 'IC';

select count(*) from f4111
where ILDCT = 'IS';

Thanks
Volker
bob hollis
Frequent Advisor

Re: improving oracle sql statement

I added some indexes and got the following plan
which looks a lot better. I also tried someone's suggestion to make a small table to do the julian conversions via lookup - this turned out to be a very bad idea. I ran this test first and it ran for over 12 hours. It never finished because all the developers were yelling at me that the test system was hardly moving. I'll try it again tonight and use the function. I did find a much simpler version, so we will see.
Thanks for all your help ??? I???ll tell you what happens tomorrow.
---
OPERATIONS OPTIONS OBJECT_NAME
------------------------- --------------- -----------------------
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID F46011
INDEX RANGE SCAN F46011_V05
TABLE ACCESS BY INDEX ROWID F4111
INDEX RANGE SCAN F4111_6
TABLE ACCESS BY INDEX ROWID F4102
INDEX RANGE SCAN F4102_V01
TABLE ACCESS BY INDEX ROWID F4801T
INDEX UNIQUE SCAN F4801T_PK
Volker Borowski
Honored Contributor

Re: improving oracle sql statement

Hi,
just curious, as you did not tell how many rows your query returns :-)

If this is a whole lot ( +Mios ), there is another one:

Index on F4801T.WADOCO,F4801T.WALINE.

This is not obvious, because the Uniqe scan suggests that this join is good. But as you only need one field from this table (second largest), this index would turn the index uniqe scan and the following table access into a index fast full scan (without table access at all).
Statisticly this should cut your gets on this table by two. If the average row-length on this table is big it could be even better, because disk reads should go down as well.
...but only worth in overall number of rows for your query is large !

Good hunting once more
Volker
Massimo Bianchi
Honored Contributor

Re: improving oracle sql statement

My fault,
a lookup table seemed to me a good idea, sorry!

In any case,
glad to see that indexes are working.

Did you also refreshed the statistics ?

Massimo