1 2 Previous Next 23 Replies Latest reply: Feb 27, 2013 1:06 PM by Justin_Mungal Go to original post RSS
      • 15. Re: To keep query in buffer cache
        jgarry
        These days aren't full table scans likely to go to the pga instead?
        • 16. Re: To keep query in buffer cache
          Rob_J
          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
            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
              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
                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
                  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
                    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
                      "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
                        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