Slow PL/SQL query

0 pts.
Tags:
Oracle 8i
SQL
I'm running the following query against two tables residing on an Oracle 8 database. The query takes too long (over an hour) to run. Both tables are indexed - however the main table SALES.SO is HUGE. Does anyone have any suggestions as to how I could improve this query so it can execute faster. Here's the main part of it: SELECT s.cust_cd, d.st, d.reg REGION FROM SALES.SO s AND devl.tblreg d WHERE d.st = s.store_cd AND s.wr_dt between v_dt and v_dt+28 AND s.so_wr_dt < to_date('&e_dt')+1 AND d.reg = 'STL' AND (s.layaway = 'N') OR s.layaway is null) AND s.fin_cust_cd is null Thanks for your help.
ASKED: March 8, 2005  10:28 AM
UPDATED: March 9, 2005  2:58 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

So we can assume that your query is doing a full table scan(s)? Have you looked at the execution plan?

So either it is meant to take long, i.e. it returns most of the rows, or the where clause is not making use of indexed columns.

Get an execution plan and you’ll see where it’s falling short.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JLeask
    Perform an explain on the query. If a tablespace scan is being performed on the SO table, it may be an improvement if an index on column wr_dt is added. The number of brackets within the query looks incorrect - I have assumed that this is a copying error.
    0 pointsBadges:
    report
  • PeteJo
    First, some perspective. An hour or so for a complex query against a "huge" table, is not necessarily abnormal, nor does it mean that it is not tuned. I'm not sure what you mean by "huge". I work with tables that have billions of rows and are terabytes in size and it's not uncommon for some jobs to take half a day or more to finish. Simple math: more data = more time + more overhead. Second, I'd need to know more about your indexes. I'm assuming both tables are indexed on the d.st and s.store_cd columns since this is what you're joining on. If so, you might also consider indexes on other columns such as d.reg, s.layaway, and s.fin_cust_cd. Again, not knowing your environment or your business rules, it's difficult to say for sure if this is desirable since more indexes translate to more space and more time to load data. You can also use hints to force the optimizer to use indexes that it may not be using presently. If your database is running on a box with parallel processors, you can use parallel hints also. I suggest doing an explain plan on the query to see what indexes it is using and then digging around in your Oracle documentation to research the many hints available. Some other things to consider. We use partitioning for some of our larger tables. This allows for huge improvements in data loading, queries and deletes when used correctly. Oracle 8 supports this, so if you really need this query to run faster for some reason on a regular basis, you might look at partitions. Also consider upgrading to 9i or 10g. They allow function-based indexes which could also help you. HTH-- - Pete J.
    0 pointsBadges:
    report
  • Mljsher
    One thing that hasn't been touched on in the responses so far, have the tables in the query been analyzed? I've seen a dramatic drop in query times simply by analyzing the table(s) involved. To check, do a 'select last_analyzed from dba_tables where table_name = ''. If that returns a blank, then the table has not been analyzed. There is a PL/SQL procedure called 'gather_schema_stats' and I beleive there is one called 'gather_table_stats' that you may want to investigate.
    0 pointsBadges:
    report
  • FerencMantfeld
    Let's start at the very beginning, its a very good pplace to start (gosh I sound like Julie Andrews in the sound of music, but /i think I will stick to Oracle). How many rows in each of these tables and what are the indexes on each of these tables ? then we can get to things like "is the table and the associated index analyzed" and the explain plan. This should be looked at way before suggestions like "upgrade to ...." (this sounds like Oracle Support rehearsed generic answers). Secondly, your SQL looks wrong. Please copy and paste the exact SQL you are using from either Sql*Plus or Toad or whatever query tool you're using. Then we start to analyze the problem. I am very sure there are dozens of performance tuning specialists that can help. the other thing you might want to share with all here is the actual procedure / package / function, so we can see what you're trying to do in context. Regards: Ferenc Mantfeld CEO: www.seemoredata.com
    0 pointsBadges:
    report
  • Oradba95
    Hi! pls mention the type of optimizer (RULE/COST), indexed column order, is analyze is done on tables and indexes, OS and other related information that you think would give us an idea about the environment you are running the query. Also note that the opening bracket is missing from the script. I would like to give the possible solutions based on your input. Regards
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following