cancel
Showing results for 
Search instead for 
Did you mean: 

improving oracle sql statement

SOLVED
Go to solution
bob hollis
Frequent Advisor

improving oracle sql statement

I have to create a tab-seperated file to import into Cognos. My sql statement looks something like this, but the performance is terrible - anyone have any suggestions?

select a||''||b||''||c from table where ...;

Since everything is concatinated, I don't believe it takes advantage of indexes or anything. My actual sql is more complex, and had table joins, but that is the general idea.
Thanks
23 REPLIES
Massimo Bianchi
Honored Contributor
Solution

Re: improving oracle sql statement

Hi,
the problem is not on the part you pasted, but on the other :)


The pasted section only states how output is formatted to the user, but do not affect the way it is retrieved.

For a cross-check issue an explain plan for the statement with and without the pipes, and check any difference.


Question is if:
- did you created proper index for this query?
- did you analyzed the table and the indexes?
- did you use any hint, like parallel, for faster retrieving ?

Massimo
Steven Gillard_2
Honored Contributor

Re: improving oracle sql statement

Best way to start troubleshooting this is to analyze the execution plan for the statement with EXPLAIN PLAN. See the following link for details:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/ch13_exp.htm#822

That will tell you if indexes are being used or not.

Regards,
Steve
bob hollis
Frequent Advisor

Re: improving oracle sql statement

ok - here is the explain plan - thanks for the advice on this. You were right, the "||" didn't matter. There IS a function where I change a JD EDWARDS julian date to YYYYMM that is rather complex for the F4111 table.
Can somebody tell me what all this means?

OPERATIONS OPTIONS OBJECT_NAME
------------------------- --------------- -----------------------
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL F46011
TABLE ACCESS BY INDEX ROWID F4111
INDEX RANGE SCAN F4111_6
TABLE ACCESS BY INDEX ROWID F4102
INDEX RANGE SCAN F4102_10
TABLE ACCESS BY INDEX ROWID F4801T
INDEX UNIQUE SCAN F4801T_PK
bob hollis
Frequent Advisor

Re: improving oracle sql statement

Also - we use CBO with a weekly analysis of all the tables - database is set to "choose"
Massimo Bianchi
Honored Contributor

Re: improving oracle sql statement

first problem: TABLE ACCESS FULL F46011

issue an analyze against this table, or create some appropriate index on that table

how is the analyze computed? compute or estimate statistcs ?

maybe a weekly run is not sufficient, think of half-week run..


The function to calculate the julian date... why don't you create another table, which maintain such conversions always ? We could change from a calculation to a lookup against another table... much faster.

Can you change the query and issue the query as parallel, if you use parallel query server ? Maybe you can think of adding them, just for this purpose.

Massimo

Steven Gillard_2
Honored Contributor

Re: improving oracle sql statement

What version of Oracle are you running?

The plan is telling you that the indexes for tables F4111, F4102 and F4801T are being used (which is good), but that a full table scan is being done on F46011. If this is a large table that could explain why the performance is bad.

For extra details, turn on SQL tracing with "ALTER SESSION SET SQL_TRACE=TRUE" before running the query. Then find your trace file in the UDUMP directory and run tkprof over it (with the explain option to include the explain plan output). That will show you where all the time is being spent.

There's not much else I can tell you without understanding your database or the query you're trying to run. Have a good read of the "designing and tuning for performance" guide (link in my last post - click on contents), there are loads of tips and tricks for optimising performance.

Regards,
Steve
bob hollis
Frequent Advisor

Re: improving oracle sql statement

we are currently running the 8.1.6.2 version of oracle.
i checked and all tables and indexes involved in the query were analyzed yesterday
the sizes of the individual tables
f46011 39,508 rows
f4801t 242,646 rows
f4102 163,619 rows
f4111 3,650,629 rows

the actual query
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' ;
bob hollis
Frequent Advisor

Re: improving oracle sql statement

currently the query is taking 8-10 hours to run - our trace files are limited to 5 meg.
Any advice on setting up a sql trace?
Brian Crabtree
Honored Contributor

Re: improving oracle sql statement

Bob,

1. Verify that you have a index on F46011 on IQMCU, IQITM, IQUWUM, and IQUOM. Preferably, one index on all 4 columns would give the best performance.

2. You can setup a sqltrace from the system by modifying the "sql_trace" parameter in the init.ora, or in the session by issuing "alter session set sql_trace = true".

Also, in the future, you might not want to issue 8 points to people until the end. It marks the thread as solved, which leads people to skip it. Point values of 1-7 will not do this. Up to you, just my opinion.

Thanks,

Brian
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
Volker Borowski
Honored Contributor

Re: improving oracle sql statement

... hit Enter too fast (so no points on this one:-)

Same approach for

F4102.IBLITM, F4102.IBMCU, F4102.IBSRP6

The first two fields for the join and the third one to save the table access and serve the index scan.

Which fields did you put into your index F4102_V01 ?
If the first two from above are already in, add the third field as well !

BTW: Here is a good description
http://www.adp-gmbh.ch/ora/explainplan.html
Esp. the last example in the index section describes this "index acces only" feature.

Volker
bob hollis
Frequent Advisor

Re: improving oracle sql statement

The new indexes I???ve added really helped. Execution time has dropped from over 8 hours to 25 minutes or less. TKPROF???s total row fetches went from 2 billion to 5 million.

297376 NESTED LOOPS
325541 NESTED LOOPS
325541 NESTED LOOPS
31661 TABLE ACCESS BY INDEX ROWID F46011
31661 INDEX RANGE SCAN (object id 10664)
357200 TABLE ACCESS BY INDEX ROWID F4111
1312371 INDEX RANGE SCAN (object id 10281)
651080 TABLE ACCESS BY INDEX ROWID F4102
1185110 INDEX RANGE SCAN (object id 10663)
297376 TABLE ACCESS BY INDEX ROWID F4801T
622916 INDEX UNIQUE SCAN (object id 8009)

Here are the answers to questions asked ??? I also have attached more information.
If anyone has any more insights, they would be welcome!
I haven???t tried to use a concatenated index for F4801T or the other tricks ??? just straight indexes (the new indexes have a _V01 or _V02 at the end)

DB Version 8.1.6.2
No parallel query
Records returned 297,376

counts
F4111 3,470,584

.F4111 where F4111.ILDCT = 'IC' 440,643

F4111 where F4111.ILDCT = 'IS' 36,841

F4102 161,019

F4801T 229,301

F46011 38,684

F46011 31,660
where F46011.IQUWUM = 'KG' AND F46011.IQUOM = 'EA';


attached file includes
1) counts
2) the queries about indexes & columns
3) the query about number of distinct values
4) the tkprof of the query with the new indexes.

Thanks a lot everyone!


Brian Crabtree
Honored Contributor

Re: improving oracle sql statement

Bob,

You definately want to consider putting a bitmap index on ILDCT, and might want to consider putting a bitmap index on ILMCU. (I am only looking at this one query).

You should also look at your queries, and drop some of your indexes. Remember that an index will be used only when the first entry of the index is called in the index. You have a large number of indexes on the tables, which might be better if you tuned the indexes for the queries, and removed some of them (better performance on inserts).

Thanks,

Brian
Volker Borowski
Honored Contributor

Re: improving oracle sql statement

Hi Bob,

I re-inserted some comments into your attachment, just to show, how I got to my results.
Hope this is interesting.

Brian is right: The ILDCT index will lead to the best result, although I am not convinced the optimizer will go for it. You might need a hint to convince him.

Hey 25 minutes is not bad from 8 hours.
I'll be you'll be able to reach < 10 minutes !

Volker