Solved

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

Posted on 2006-11-30
4
265 Views
Last Modified: 2008-01-09
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
Comment
Question by:nick_sa
4 Comments
 

Accepted Solution

by:
leroopsin earned 70 total points
ID: 18053837
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
 
LVL 1

Assisted Solution

by:rgss
rgss earned 70 total points
ID: 18057845
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now