improving oracle sql statement

[bob hollis]

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

[Massimo Bianchi · Accepted solution]

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]

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]

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]

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

[Massimo Bianchi]

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]

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]

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]

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]

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