Solved

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

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
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…

762 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