Query will take 400 Hours! How to optimize?

[PFC]

The following query is going to take over 400 hours to run. And that's after adding an index which cut off 100 hours from the estimated completion time.

I ran this on a micro test DB (20 MB) and it worked fine. I'm now testing it on small dataset of only 1.2 Million records.

DATASTORE_ID and STOCKID Number(12)
CUR_AMT Number(5)
T_DATASTORE_A=1.2 Million Records
T_STOCK_HISTORY=600 Thousand Records
Index is created on T_STOCK_HISTORY

UPDATE
T_STOCK_HISTORY
SET
CUR_AMT =
-2+
( SELECT T_DATASTORE_A.AMT
FROM T_DATASTORE_A
WHERE T_DATASTORE_A.DATASTORE_ID = T_STOCK_HISTORY.STOCKID AND
(T_DATASTORE_A.AMT > 2)
)
WHERE T_STOCK_HISTORY.STOCKID IN
( SELECT T_DATASTORE_A.DATASTORE_ID
FROM T_DATASTORE_A
WHERE T_DATASTORE_A.DATASTORE_ID = T_STOCK_HISTORY.STOCKID AND
(T_DATASTORE_A.AMT > 2));


I'm new to Oracle and I don't know how to optimize queries. I know that explain plans exist, but I don't know how to use them. Any tips on what to change? And more importantly how can I learn how to optimize queries?

[fordikon]

hi,

create the index on columns where-ever you r using where clause in query.

try this.

[pss]

Create index
on T_DATASTORE_A (DATASTORE_ID,AMT);

Create index
on T_STOCK_HISTORY (STOCKID);

Sergey.

[chrisrlong]

Several questions:

- What tools are you using? If only SQL*Plus (ugh ), then do set autotrace on before you run your query and it will give you the actual time, the plan and some stats. Of course, to get the plans without running the statement is a little different. Using another tool would help. Here is a link to the docs for explain plan:

http://technet.oracle.com/docs/produ.../doc_index.htm

- Are you expecting only one row to come back from:
( SELECT T_DATASTORE_A.AMT
FROM T_DATASTORE_A
WHERE T_DATASTORE_A.DATASTORE_ID = T_STOCK_HISTORY.STOCKID AND
(T_DATASTORE_A.AMT > 2)
...for each record in T_STOCK_HISTORY ?
This seems unlikely given that T_STOCK_HISTORY is smaller. Your query, however, requires that only one row is returned. If not, you will get an error. You can only set a column equal to a single value, so when setting it equal to a sub-select, that sub-select *must* return only one value.

- "Index is created on T_STOCK_HISTORY". Ok, but what is the index?

The suggestions by pss are fine, but including the amt in the index may be overkill. Basically, you want to create a set of indexes that are as small as possible, but give you as much coverage as possible. While including amt in the index will help *this* query only because it can then be a 'covered' index, it may not help other queries and therefore be extra space in the larger picture.

How to optimize SQL? Buy my book once I'm done with it (Yes - shameless plug ) Until then, Oracle SQL High Performance Tuning, 2nd Edition is supposed to be the definitive SQL optimization book for Oracle.

Hope this helps,

- Chris

[chrisrlong]

Oh, I also wanted to ask how you came up with this 400 hour theory? Basically, nothing can tell you that. You must be reading something wrong.

- Chris

[jmodic]

Originally posted by chrisrlong
- What tools are you using? If only SQL*Plus (ugh ), then do set autotrace on before you run your query and it will give you the actual time, the plan and some stats. Of course, to get the plans without running the statement is a little different. Using another tool would help.

Chris,

This might be little off-topic, but anyway: whay do you think getting explain plan without runing the query is not possible in SQL*Plus? All you have to do is to isue:

SET AUTOTRACE TRACEONLY EXPLAIN

before running the query. This way the query will not be executed, but explain plan will be displayed.

[chrisrlong]

... because I tried it and it didn't work. Maybe I did something wrong :( Of course, I'm also using SQL*Plus 8.0.5.0.0 on an Oracle 8.1.6.2.0, so maybe that's the issue

- Chris

[PFC]

Thanks for the tips everyone!

I had created indexes on the PK already. I will try the PK, AMT index to see if it's faster. I take it a "covered" index is an index where the PK and a frequently selected field are both included, correct?

The query is supposed to return a single number for each data record. I need to verify the difference in the tables. Thinking about it, T_DATASTORE_A should be a subset of T_STOCK_HISTORY.

Running oemapp instance on Oracle 8.1.5 gives an estimate of how long a very long running query will take. I think Oracle 8.1.6 does this also. The value will change depending on server load, naturally. But if it's the only thing running you get a good estimate.

Chrislong, do you need a reviewer of your draft copies? If it covers performance tuning, I swear I'll read it cover to cover and try every example. I'm serious, just email me at: ora_newbie@yahoo.com if you are interested. The DW I'm working with would be a great place to put the performance tips to the test!

[jmodic]

Originally posted by chrisrlong
How to optimize SQL? Buy my book once I'm done with it (Yes - shameless plug )

I will immediatelly take advantage of this shameless plug of yours . Chris, if you need additional technical reviewer for your book, I'd be more than happy to participate. If you are interested, mail me at jmodic@src.si

[chrisrlong]

Well, thank you both for the support. I will definitely be in touch

FYI: I will be covering OLTP design and programming issues - no DBA stuff or DW stuff - sorry

It'll probably be a little while yet too - this is turning out to be a very big pain . I haven't even written a page in a couple weeks - I've been completely re-writing and enhancing my auto-CRUD-generating macro-code scripts for ERwin - also a bigger pain than expected.

I'll keep you posted

- Chris