Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-11-30
4
Medium Priority
?
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Accepted Solution

by:
leroopsin earned 280 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 280 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

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Progress

715 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