Solved

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

Posted on 2006-11-30
4
266 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
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.
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…

863 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

18 Experts available now in Live!

Get 1:1 Help Now