• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Delay query from mlsec to 3 min within a year Java to Oracle

We have a bunch a query statements in Java code wich goes to Oracle (SELECT... FROM... etc. all the good stuff)
One year ago it takes only few millisecods now up to 3 mins nothing was not change in the code. So it somethig in DB. So what can cause this kind of problem? Thank you all.
0
nick_sa
Asked:
nick_sa
2 Solutions
 
leroopsinCommented:
Hi you,

Because you have a lot of data for one year, so that it takes long for SELECT. How to slove this problem.

1. You should put HINT to your SQL
EX: SELECT /*+ INDEX(pe IDX_PERSON_ID)*/ CONTEXT_ID
     FROM Person pe
Note: IDX_PERSON_ID is a index of the table Person
Please refer to http://www.psoug.org/reference/hints.html for detail.

I think if you apply this to your SQL, I sure that you will reduce a lot of time.

2. You can use The Automatic Workload Repository (AWR) which is a new infrastructure component that is at the heart of the collection and processing of the vast amount of performance data in Oracle Database 10g. It lets you know which SQL needs to improve in you system and it gives you the best solution.

Best regards,

Sin Le
0
 
rgssCommented:
If your database has got bigger over time and now your queries are running slowly, the queries probably need to be tuned to run more efficiently now (which would not have been an issue with small tables).

You should look at the 'explain plan' of each query and ensure there are no table scans which will run slowly. SQL software should have a function for viewing the explain plan, PLSQL Developer is one I have used for Oracle queries.

If you dont understand the explain plan results, or what to do about it to make the query more specific, I suggest you post the SQL statement and the explain plan here to get some help.

Hope this helps.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now