topic Re: improving oracle sql statement in Operating System - HP-UX

improving oracle sql statement

bob hollis — Mon, 08 Sep 2003 11:09:50 GMT

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

Re: improving oracle sql statement

Massimo Bianchi — Mon, 08 Sep 2003 11:31:17 GMT

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

Re: improving oracle sql statement

Steven Gillard_2 — Mon, 08 Sep 2003 11:50:35 GMT

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

Re: improving oracle sql statement

bob hollis — Mon, 08 Sep 2003 14:12:04 GMT

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

Re: improving oracle sql statement

bob hollis — Mon, 08 Sep 2003 14:15:10 GMT

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

Re: improving oracle sql statement

Massimo Bianchi — Mon, 08 Sep 2003 14:37:32 GMT

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

Re: improving oracle sql statement

Steven Gillard_2 — Mon, 08 Sep 2003 14:37:38 GMT

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

Re: improving oracle sql statement

bob hollis — Mon, 08 Sep 2003 15:03:41 GMT

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' ;

Re: improving oracle sql statement

bob hollis — Mon, 08 Sep 2003 17:03:31 GMT

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?

Re: improving oracle sql statement

Brian Crabtree — Mon, 08 Sep 2003 19:24:58 GMT

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

Re: improving oracle sql statement

Graham Cameron_1 — Tue, 09 Sep 2003 06:31:13 GMT

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

Re: improving oracle sql statement

Steven Gillard_2 — Tue, 09 Sep 2003 07:54:05 GMT

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

Re: improving oracle sql statement

Yogeeraj_1 — Tue, 09 Sep 2003 09:09:25 GMT

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

Re: improving oracle sql statement

bob hollis — Tue, 09 Sep 2003 10:39:03 GMT

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)

********************************************************************************

Re: improving oracle sql statement

Volker Borowski — Tue, 09 Sep 2003 11:55:13 GMT

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

Re: improving oracle sql statement

Steven Gillard_2 — Tue, 09 Sep 2003 15:07:03 GMT

> 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

Re: improving oracle sql statement

Volker Borowski — Tue, 09 Sep 2003 16:50:00 GMT

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

Re: improving oracle sql statement

bob hollis — Wed, 10 Sep 2003 16:07:44 GMT

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

Re: improving oracle sql statement

Volker Borowski — Wed, 10 Sep 2003 16:26:24 GMT

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

Re: improving oracle sql statement

Massimo Bianchi — Wed, 10 Sep 2003 16:37:28 GMT

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