Home » RDBMS Server » Performance Tuning » How to tune a select query which gives a result of 120 million rows?
How to tune a select query which gives a result of 120 million rows? [message #186997] Thu, 10 August 2006 08:32 Go to next message
serkandemir
Messages: 5
Registered: July 2006
Junior Member
Hi guys,
I am making a select query over jdbc which gives a resultset of 120 million rows. My mission is to select the rows and load into cache of my application. This mission takes 3-4 hours on Oracle 9 DB. Our DBA reserved a cache size of 3GB for DB.
I am adding a prefetch size of 10000 rows for my select query.
Table is also indexed and partitioned.
My query is basically;
select * from NAMES_LIST;

Could you give a list of some both DB side and application side tuning actions for me?

thanks lot,

Serkan
Re: How to tune a select query which gives a result of 120 million rows? [message #187001 is a reply to message #186997] Thu, 10 August 2006 08:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>My mission is to select the rows and load into cache of my application.
I suspect that your "application" does not run on the same system as Oracle RDBMS.
If so, I hope you have a Big Pipe between RDBMS & AS to move all those rows between the two systems.
I hope that you have implemented a smart multi-user application so that each user does not try to cache its own copy of this information.
I suspect that the System Architect did not seriously consider scalability, but on the surface the requirements are flawed, IMO.
Re: How to tune a select query which gives a result of 120 million rows? [message #187002 is a reply to message #187001] Thu, 10 August 2006 08:47 Go to previous messageGo to next message
serkandemir
Messages: 5
Registered: July 2006
Junior Member
Application and DBs are on seperate machines and application is a standalone one, only our application can execute query on this table.
Re: How to tune a select query which gives a result of 120 million rows? [message #187004 is a reply to message #187001] Thu, 10 August 2006 08:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I agree with Anacedent.
Why on earth would you need to pass 120 million rows to the user interface.
This sort of processing begs to be done on the server in pl/sql.
Re: How to tune a select query which gives a result of 120 million rows? [message #187006 is a reply to message #187004] Thu, 10 August 2006 08:56 Go to previous messageGo to next message
serkandemir
Messages: 5
Registered: July 2006
Junior Member
We are not passing 120 million rows to any user interface. We are transferring a table from our DB to our application. Our application is a server and caching names by the way.
Re: How to tune a select query which gives a result of 120 million rows? [message #187009 is a reply to message #186997] Thu, 10 August 2006 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Our application is a server and caching names by the way.
What happens when data in the underlying table changes?
How does the "cached" information stay current with the table?
Re: How to tune a select query which gives a result of 120 million rows? [message #187120 is a reply to message #187009] Thu, 10 August 2006 23:57 Go to previous messageGo to next message
serkandemir
Messages: 5
Registered: July 2006
Junior Member
Whenever new data is needed to add or existings are changed, we have triggered to add this data both the main table and another table for only used for changes. A thread in our application reads this changes table in some periods and updates its cache.
Re: How to tune a select query which gives a result of 120 million rows? [message #187645 is a reply to message #186997] Mon, 14 August 2006 15:49 Go to previous message
Peter D.
Messages: 19
Registered: June 2006
Location: Warsaw, Poland
Junior Member
Hi,
Try to set CBO to FIRST_ROW(n). It should use execution plan to return as fast as it is possible first n rows. I don't know if user need to see 120 milion rows at a time.

Peter D.
Previous Topic: Compund index vs simple index
Next Topic: Enable Row Movement in Partitioning and Overhead
Goto Forum:
  


Current Time: Sun Dec 04 10:55:03 CST 2016

Total time taken to generate the page: 0.08009 seconds