Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Select query with 1 Million rows
oradba1403
post May 2 2017, 07:57 PM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 2-May 17
Member No.: 51,951



Hi Experts,

I was asked this question in an interview, can you please help improve my knowledge?

Q: Suppose there is a query which returns a Million rows and it is being executed every 10 minutes. How will Oracle do this? Will it fetch them from disk every time when SGA is not large enough?

Go to the top of the page
 
+Quote Post
burleson
post May 3 2017, 01:33 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,409
Joined: 26-January 04
Member No.: 13



Hi Oradba and welcome to the forum!

>> Will it fetch them from disk every time when SGA is not large enough?

Only the first time. If it is from a full-table scan the rows are stored in the PGA:

Please read:

http://www.dba-oracle.com/art_orafaq_oracl..._table_scan.htm

A million rows table can be easily cached:

Please read:

http://www.dba-oracle.com/art_so_oracle_di...uffer_cache.htm






--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
oradba1403
post May 4 2017, 06:35 PM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 2-May 17
Member No.: 51,951



QUOTE (burleson @ May 3 2017, 01:33 PM) *
Hi Oradba and welcome to the forum!

>> Will it fetch them from disk every time when SGA is not large enough?

Only the first time. If it is from a full-table scan the rows are stored in the PGA:

Please read:

http://www.dba-oracle.com/art_orafaq_oracl..._table_scan.htm

A million rows table can be easily cached:

Please read:

http://www.dba-oracle.com/art_so_oracle_di...uffer_cache.htm



Thanks a lot for the explanation.
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd May 2017 - 06:19 PM