[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to Improve the Query Performance

Posted on 2012-08-28
16
Medium Priority
?
444 Views
Last Modified: 2012-09-20
Hi
We have a database Table which consits of Million Million records
When we query  from the database it takes more time to reterive ,
Please can any suggest how to Improve of select query!
Using Oracle database.

Thanks in Advance.
0
Comment
Question by:nrajasekhar7
  • 6
  • 4
  • 2
  • +3
15 Comments
 
LVL 5

Accepted Solution

by:
Omid Omarkhail earned 1500 total points
ID: 38339875
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38339886
if you have old data, check if you still need it ...
delete the old data if you don't need it anymore

then add indexes as needed
you'll need to examine every single query to see what indexes are needed
0
 
LVL 23

Expert Comment

by:David
ID: 38340494
If you cannot affect the size of the data, nor the scope of the query, the third approach is to improve your physical system.   Consider newer (faster) storage, OS, RAM.  Also, ensure your high-load queries are executing when there is least demand on the resources.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38340591
Can you post the query and explain plan?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38342170
Topics: SQL Server 2008, Databases Miscellaneous, Oracle Database
Can someone please drop the SQL Server 2008 topic as it does not apply?
0
 

Author Comment

by:nrajasekhar7
ID: 38344056
Hi

We are using the Backend database and fetching the Data from GUI Application,
When i fetch the the Data from GUI then it is getting corba exception ,
from the GUI ,In that Tables we have so many miilion records every minute the data will be coming in.
So How should i write a generally select query to fetch the records using where updatetime between so and so and  type =2  order by.

select query is depend up on the user wish .wht he want to query.
Inferface usinf the hibernate query,So please can help me to reteruve the hibernate query faster way.

Cheers
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 38344387
just some pointers:
a gui doesn't have data
it is a visual interface to data, the data is visualised by the gui, not the other way around

a gui executes a query on a database, and the database returns a recordset to the gui.
the gui visualizes this dataset

you need to come up with specific queries, the explain plan for the query and your table structure

>>the user wish
if you are developping the gui, then it's up to you to map all the users wishes to specific queries
with those specific queries we can help you
we need all those details

if you can't give those details, then it's impossible to help

a bit like asking a blind man to drive your car for you...
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38345305
Can you explain a little more about your application?

Is this a real time interface where you want to constantly retrieve the most recent data form the table while preserving the rows already there?

I assume there is a date/time stamp in the database.  You just need to know the max date/time retrieved from the last query and grab everything after that date/time.

I also assume there is an index on that column in the database and it is using it and still to slow?

Then you might need to look into Oracle Partitioning which requires an extra license.

We really need to see the SQL and execution plan.
0
 

Author Comment

by:nrajasekhar7
ID: 38348491
Hi slightwv

 we are Using Java interface Gui Appilcation tool as Front end
And we are Using the time stamp column  in select query.
and some other where cluases like
Please find the Sample query:
SELECT  ID, USERID,Message  FROM system_log
WHERE time BETWEEN TO_DATE('02-MAR-2012:00:00:00','DD-MON-YYYY:HH24:MI:SS') AND TO_DATE('16-MAR-2012:23:59:59','DD-MON-YYYY:HH24:MI:SS')
and User_id='SYSTEM' and Message Like '%DPW%'
ORDER BY  TIME;

So, How to reterive the data faster for the Huge Database if we use datetime in where condition ,
Please need an urgent assistance.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38348500
To get the fastest and most accurate responses, you need to respond to our questions in a timely manner.

Please post the explain plan for the query.
0
 

Author Comment

by:nrajasekhar7
ID: 38366526
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  TABLE ACCESS FULL   | TREND_DATA  |       |       |       |
--------------------------------------------------------------------
 
Note: rule based optimization, PLAN_TABLE' is old version


 
-----------------------------------------------------------------------------------
| Id  | Operation                    |  Name              | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |       |       |       |
|   1 |  SORT ORDER BY               |                    |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| TREND         |       |       |       |
|   3 |    AND-EQUAL                 |                    |       |       |       |
|   4 |     INDEX RANGE SCAN         | INDEX1  |       |       |       |
|   5 |     INDEX RANGE SCAN         | _INDEX4  |       |       |       |
|   6 |     INDEX RANGE SCAN         | INDEX2  |       |       |       |
-----------------------------------------------------------------------------------
 
Note: rule based optimization, PLAN_TABLE' is old version

Please suggest
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38367748
There are two plans there.  Are we talking about one query or two?

I would also suggest not using RULE based optimization.

>>PLAN_TABLE' is old version

Depending on your version, there isn't a need to create a local version of the PLAN_TABLE.  In newer versions it is a built-in Global Temporary Table.  If you have a local copy, drop it.
0
 

Author Comment

by:nrajasekhar7
ID: 38370732
Yes, the Above two plans are for two Queries,
Please can you suggest  how should i create a new PLAN_TABLE in newer versions it is a built-in Global Temporary Table.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38370741
Just drop the local one if you created it.  As the user running it (not sys or system), just drop it: drop table plan_table;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38417547
nrajasekhar7,

Can I ask how the link you accepted helped answer the question?  My thought is you just closed this out to avoid the new question lock.

If none of the answers helped solve your issue you should have deleted the question.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

829 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