DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: buffer gets.

  1. #1
    Join Date
    Apr 2010
    Posts
    12

    buffer gets.

    Can somebody tell me why these queries are written two different results and why?

    desc employees;

    employee_id number(6);
    first_name varchar2(20)
    last_name varchar2(25)
    email varchar2(25)
    phone_number number(10)
    job_id number(4)
    salary number(8,2)


    1)select * from employees;

    Buffrer-gets----165

    2)select job_id,salary,email,first_name from employees

    BUFFER_GETS----17

    In second query it is accessing only few fields that's why it has accessed only few blocks, but oracle normal does block wise read not row or column wise read , so why difference has come.

  2. #2
    Join Date
    Dec 2002
    Posts
    74
    Are there other columns in the table? Do you see row_chaining in the table?

  3. #3
    Join Date
    Apr 2010
    Posts
    12
    Quote Originally Posted by skhanal View Post
    Are there other columns in the table? Do you see row_chaining in the table?
    select * from chained_rows where table_name='EMPLOYEES'
    ---------
    no rows.

    That means no chained rows..i think so

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by jayasankar View Post
    select * from chained_rows where table_name='EMPLOYEES'
    ---------
    no rows.

    That means no chained rows..i think so
    It depends on how statistics are gathered. Analyze command updates chained_rows but dbms_stats doesn't.

    Eitherway buffer_gets should be identical for both queries - assuming query is doing full table scan for each one of them.

    You don't have index on (job_id,salary,email,first_name) - don't you?

    Would you mind in doing/posting...

    sqlplus /
    set autotrace traceonly;
    select * from employees;
    select job_id,salary,email,first_name from employees;

    Thank you.
    Last edited by PAVB; 10-04-2010 at 04:25 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width