This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Feb 27, 2013 11:06 AM by Justin_Mungal Go to original post RSS
  • 15. Re: To keep query in buffer cache
    jgarry Guru
    Currently Being Moderated
    These days aren't full table scans likely to go to the pga instead?
  • 16. Re: To keep query in buffer cache
    Rob_J Journeyer
    Currently Being Moderated
    The docs seem to suggest that full scans can go into the buffer cache and I would have thought so too from my understanding, depending on the situation.

    The data in the table might be frequently accessed so be good for sharing, like a look up table. And this is where you would get a db file scattered read wait for a table being fully scanned or an index fast full scan. It's also why ther might be a keep pool, to allow all the data to be kept in cache.

    If, on the other hand, the data being read was going to be sorted straight away I imagine that it would read directly into the PGA (direct path read), for order by, sorts, etc. That's what i thought, anyway...

    Now that I think about it, I'm not 100% sure what would happen in every scenario...I will try to find out. What are your thoughts?
  • 17. Re: To keep query in buffer cache
    Justin_Mungal Journeyer
    Currently Being Moderated
    Sachin Bhise wrote:
    i have recently completed the ORACLE DBA courseware and in an interview the interviewer asked me that if he want to place a query explicitly in buffer cache what will i do?
    Wow, not only is that a dumb question (in my opinion), it's also a trick question. I certainly hope questions like this aren't the norm in DBA interviews. We ask for candidates to describe scenarios for us, to get an idea of their thought processes and how they handle things from a bigger picture perspective.
    as a fresher my knowledge is limited and i am trying to improve it continuously..
    while the interviewer was a senior oracle DBA, according to my knowledge i told him that we can keep objects like tables indexes in keep cache by declaring so or if the same query is running continuously it will automatically remain in LRU .putting a query is a new stuff i was searching for it but i wasn't getting answer for it so finally i decided to put this question on forum...
    i am trying hard to get a job as a DBA but all in vain i am nt getting one really it's becoming frustrating for me i have great hopes on oracle and besides hope i like learning oracle from beginning of my graduation thats y i decided to make my career in oracle and specially a DBA...
    Don't give up Sachin, hopefully all interviewers aren't this myopic.
    now let's see what happens....
  • 18. Re: To keep query in buffer cache
    992982 Newbie
    Currently Being Moderated
    thanks justin i am not giving up...and yes i will try hard so that i should fulfill my dream of becoming a dba
  • 19. Re: To keep query in buffer cache
    jgarry Guru
    Currently Being Moderated
    My thoughts are it is very version (and [url http://nigelnoble.wordpress.com/2010/07/05/10-2-0-5-keep-pool-serial-direct-read/]patch) dependent. Google: direct path reads oracle for lots of good information (and perhaps some bad). One interesting thing from the doc is if you are using async i/o, you'll see lots of direct path waits, but the wait is [url http://docs.oracle.com/cd/B16240_01/doc/doc.102/e16282/oracle_database_help/oracle_database_wait_bottlenecks_direct_path_read_pct.html]misleading if you use sync.

    So, it depends.
  • 20. Re: To keep query in buffer cache
    Dropbear67 Newbie
    Currently Being Moderated
    I know it's only an interview question (and a dumb one at that) but here is a quite good chat by Tom on this very issue.


    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1590999000346302363
  • 21. Re: To keep query in buffer cache
    Rob_J Journeyer
    Currently Being Moderated
    Thanks for the info, jgarry and Dropbear67. It seems very usual for the answer to be "it depends" for a lot of subjects because there are so many variables and Oracle is very smart and complex.

    The Ask Tom link was good to read.

    Cheers,
    Rob
  • 22. Re: To keep query in buffer cache
    jgarry Guru
    Currently Being Moderated
    "99.999999999999% of the world does not need, want, nor desire a keep and recycle pool..." Tom, as usual, says it all.
  • 23. Re: To keep query in buffer cache
    Justin_Mungal Journeyer
    Currently Being Moderated
    jgarry wrote:
    "99.999999999999% of the world does not need, want, nor desire a keep and recycle pool..." Tom, as usual, says it all.
    Haha, I love that. I agree though; I'll just let the buffer cache and its LRU algorithm handle it.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points