| sudheer pai |
Posted: Jul 14, 2007 06:14:05 AM |
Total Post: 33
Joined: Feb, 2007
|
Hi,
if i(scott) give the select * from employees for the first time, the
related blocks come to the DBBC and the next time they are used from them.
but if my query becomes select * from employees where department_id=50;
then will i use the same data that was for the first time fetched in my
Buffer.
if so, what about if some hr user(having priviliges) is typing select *
from hr.employees; then will he again use the same data from the Buffer.
or is the execution plan after parsing is totally different even for the
slightest of the text change in my SQL query.
|
|
|
 Ahmed |
| Posted: Jul 16, 2007 07:47:41 AM |  |
|
Total Post: 13
Joined: Mar, 2006
|
MAY THE EXECUTION PLAN BE DIFFERENT AS MAY ONE OF THE SELECT STATEMENTS USE AN INDEX SCAN AND THE OTHER MAY USE A FULL TABLE SCAN
|
|
|
|
|
| KS |
| Posted: Jul 16, 2007 08:44:40 AM | |
|
Total Post: 60
Joined: Nov, 2006
|
Yes, Even for a single change in the SQL query ,it will go for Parsing and prepare a execution plan.
- Sri
|
|
|
|
|
| sudheer pai |
| Posted: Jul 16, 2007 09:18:01 AM | |
|
Total Post: 33
Joined: Feb, 2007
|
sri that is clear that the plan wud change..
but tell me if the buffer wud be read???
will the 2nd statement use the data in the buffer of will the data be pull again to the buffer???
|
|
|
|
|
| KS |
| Posted: Jul 16, 2007 11:24:26 AM | |
|
Total Post: 60
Joined: Nov, 2006
|
Yes,it will be using the DBBC.
It will look for the Latest SCN and it will fetch it from the DBCC (if present already)
|
|
|
|
|
| dipali |
| Posted: Jul 20, 2007 07:47:55 AM | |
|
Total Post: 31
Joined: Dec, 2006
|
i think,
when scott fires
select * from emp;
at that time, the emp table of scott schema gets loaded in buffer cache..
while when hr fires
select * from hr.emp;
(as asked in question)
at that time, the emp table of hr schema gets loaded..
Both are completely different objects (though having the same name..)
So there is no question.. Both will be loaded differently and occupy different space in database buffer cache...
|
|
|
|
|
| positive |
| Posted: Jun 29, 2008 08:49:43 PM | |
|
Total Post: 361
Joined: Jun, 2008
|
sql execution allocates a portion of memory in PGA for user specific operations.
For details refer:
http://neworacledba.blogspot.com/
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
