OraFAQ Forum: Performance Tuning » How to tune a select query which gives a result of 120 million rows?

Home » RDBMS Server » Performance Tuning » How to tune a select query which gives a result of 120 million rows?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to tune a select query which gives a result of 120 million rows? [message #186997]

Thu, 10 August 2006 08:32

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
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?

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	Compund index vs simple index
Next Topic:	Enable Row Movement in Partitioning and Overhead

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 05:56:30 CST 2024