Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

May 16, 2007

Another Oracle ACE was our guest in Istanbul

Filed under: Oracle Performance,Oracle Seminars — H.Tonguç Yılmaz @ 3:22 am

After Mr.Jonathan Lewis and Mr.Julian Dyke, Mr.Lutz Hartmann was our guest for a two days seminar. I had very nice time with Lutz, and the seminar was very useful, I will be sharing my notes’ highlights like I did before for the other seminars.

First of all I want open some questions I noted during the seminar for your comments which I will be researching;
Q1> what affect does cache option has in lru list positioning?
Q2> in plsql if we define a variable with the same name of a column and use it at where clause will it raise an error or bring all rows; “declare empid …; begin … select …. where empid = empid; ..”
Q3> does alter system buffer cache flushes nK pools also or just the default one?
Q4> how does a pkey affect a count(*)
Q5> is %10 stale option hard-coded, any hidden parameter for this
Q6> is also dictionary stats included to the auto stale statistics gathering job

And here are my notes from this very nice seminar;

Seminar Notes : Inside Oracle 10g by Lutz Hartmann

After the seminar let me say it again if you are still not using or planning to use 10gR2 you are missing so much, also you need to use RMAN and EM, these are great and they will ease your life believe me.

Lutz thank you again for this seminar, hope to meet you again :)

About these ads

13 Comments »

  1. Hi Tonguç,
    question authority!
    You are right!
    Here is my demo for Q2:

    HR @ ora10gR2 SQL>
    DECLARE
    department_id DEPARTMENTS.DEPARTMENT_ID%TYPE :=80;
    v_department_id DEPARTMENTS.DEPARTMENT_ID%TYPE;
    BEGIN
    SELECT department_id
    INTO v_department_id
    FROM departments
    WHERE department_id=department_id;
    END;
    HR @ ora10gR2 SQL> /
    DECLARE
    *
    ERROR at line 1:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at line 5

    =;-)
    Lutz

    Comment by Lutz Hartmann — May 16, 2007 @ 9:49 am | Reply

  2. Here is a partial demo for Q3 even with keep cache, I will send one for nk caches later, o.k.:
    HR @ ora10gR2 SQL> alter table departments storage(buffer_pool keep);
    Table altered.

    HR @ ora10gR2 SQL> conn / as sysdba
    Connected.
    SYS AS SYSDBA @ ora10gR2 SQL> alter system flush buffer_cache;

    System altered.

    SYS AS SYSDBA @ ora10gR2 SQL> select * from hr.departments;

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    10 Administration 200 1700
    20 Marketing 201 1800
    30 Purchasing 114 1700
    40 Human Resources 203 2400
    50 Shipping 121 1500
    60 IT 103 1400
    70 Public Relations 204 2700
    80 Sales 145 2500
    90 Executive 100 1700
    100 Finance 108 1700
    110 Accounting 205 1700

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    120 Treasury 1700
    130 Corporate Tax 1700
    140 Control And Credit 1700
    150 Shareholder Services 1700
    160 Benefits 1700
    170 Manufacturing 1700
    180 Construction 1700
    190 Contracting 1700
    200 Operations 1700
    210 IT Support 1700
    220 NOC 1700

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    230 IT Helpdesk 1700
    240 Government Sales 1700
    250 Retail Sales 1700
    260 Recruiting 1700
    270 Payroll 1700

    27 rows selected.

    SYS AS SYSDBA @ ora10gR2 SQL> alter system flush buffer_cache;

    System altered.

    SYS AS SYSDBA @ ora10gR2 SQL> set autotrace trace
    SYS AS SYSDBA @ ora10gR2 SQL> select * from hr.departments;

    27 rows selected.

    Execution Plan
    ———————————————————-
    Plan hash value: 4167016233

    ———————————————————————————

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    |

    ———————————————————————————

    | 0 | SELECT STATEMENT | | 27 | 1512 | 3 (0)| 00:00:01
    |

    | 1 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 1512 | 3 (0)| 00:00:01
    |

    ———————————————————————————

    Note
    —–
    – dynamic sampling used for this statement

    Statistics
    ———————————————————-
    0 recursive calls
    0 db block gets
    9 consistent gets
    6 physical reads
    0 redo size
    1447 bytes sent via SQL*Net to client
    396 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    27 rows processed

    SYS AS SYSDBA @ ora10gR2 SQL> r
    1* select * from hr.departments

    27 rows selected.

    Execution Plan
    ———————————————————-
    Plan hash value: 4167016233

    ———————————————————————————

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    |

    ———————————————————————————

    | 0 | SELECT STATEMENT | | 27 | 1512 | 3 (0)| 00:00:01
    |

    | 1 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 1512 | 3 (0)| 00:00:01
    |

    ———————————————————————————

    Note
    —–
    – dynamic sampling used for this statement

    Statistics
    ———————————————————-
    0 recursive calls
    0 db block gets
    9 consistent gets
    0 physical reads
    0 redo size
    1447 bytes sent via SQL*Net to client
    396 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    27 rows processed

    SYS AS SYSDBA @ ora10gR2 SQL>

    =;-)
    Lutz

    Comment by Lutz Hartmann — May 16, 2007 @ 10:05 am | Reply

  3. About Q4 zou are right and here is the demo which shows that a COUNT uses the INDEX of the PK if there is one:
    HR @ ora10gR2 SQL> create table t (col1 number) tablespace lutz_4k;

    Table created.

    HR @ ora10gR2 SQL>
    begin
    for i in 1..1000000 loop
    insert into t values(i);
    end loop;
    end;
    /

    HR @ ora10gR2 SQL> alter table t add constraint t_pk primary key (col1);
    Table altered.

    HR @ ora10gR2 SQL> exec dbms_stats.gather_table_stats(‘HR’,’T’)
    PL/SQL procedure successfully completed.

    HR @ ora10gR2 SQL> select count(*) from t;

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    ———————————————————————-
    | 0 | SELECT STATEMENT | | 1 | 466 (2)| 00:00:06 |
    | 1 | SORT AGGREGATE | | 1 | | |
    | 2 | INDEX FAST FULL SCAN| T_PK | 991K| 466 (2)| 00:00:06 |
    ———————————————————————-

    Statistics
    ———————————————————-
    1 recursive calls
    0 db block gets
    2102 consistent gets
    0 physical reads
    0 redo size
    411 bytes sent via SQL*Net to client
    385 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    HR @ ora10gR2 SQL> alter table t drop primary key;
    Table altered.

    HR @ ora10gR2 SQL> select count(*) from t;

    Execution Plan
    ———————————————————-
    Plan hash value: 2966233522

    ——————————————————————-
    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    ——————————————————————-
    | 0 | SELECT STATEMENT | | 1 | 616 (2)| 00:00:08 |
    | 1 | SORT AGGREGATE | | 1 | | |
    | 2 | TABLE ACCESS FULL| T | 991K| 616 (2)| 00:00:08 |
    ——————————————————————-

    Statistics
    ———————————————————-
    163 recursive calls
    0 db block gets
    3143 consistent gets
    2204 physical reads
    0 redo size
    411 bytes sent via SQL*Net to client
    385 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    4 sorts (memory)
    0 sorts (disk)
    1 rows processed

    Comment by Lutz Hartmann — May 16, 2007 @ 10:46 am | Reply

  4. Hey thanks for posting your notes – there’s a ton of good info in there. For example I didn’t know the parameter that disables bind variable peeking. :) I’m going to have to start taking better notes for presentations I attend!

    Comment by Jeremy Schneider — May 16, 2007 @ 4:43 pm | Reply

  5. About Q3.
    Here is the proof that ALTER SYSTEM FLUSH BUFFER_CACHE not only flushes nK_CACHEs but also KEEP_CACHE:

    SYS AS SYSDBA @ ora10gR2 SQL> show parameter cache_s

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_16k_cache_size big integer 0
    db_2k_cache_size big integer 0
    db_32k_cache_size big integer 0
    db_4k_cache_size big integer 8M
    db_8k_cache_size big integer 0
    db_cache_size big integer 0
    db_keep_cache_size big integer 12M
    db_recycle_cache_size big integer 0

    SYS AS SYSDBA @ ora10gR2 SQL> create tablespace lutz_4k datafile size 10m blocksize 4096;
    Tablespace created.

    SYS AS SYSDBA @ ora10gR2 SQL> alter table hr.departments move tablespace lutz_4k ;

    Table altered.

    SYS AS SYSDBA @ ora10gR2 SQL> SELECT buffer_pool, tablespace_name
    FROM dba_tables
    WHERE owner=’HR’
    AND table_name=’DEPARTMENTS’
    SYS AS SYSDBA @ ora10gR2 SQL> /

    BUFFER_ TABLESPACE_NAME
    ——- ——————————
    KEEP LUTZ_4K

    SYS AS SYSDBA @ ora10gR2 SQL> select count(*) from hr.departments;

    COUNT(*)
    ———-
    27

    Execution Plan
    ———————————————————-
    Plan hash value: 1270001327

    ————————————————————————–
    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | | |
    | 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 2 (0)| 00:00:01 |
    ————————————————————————–

    Note
    —–
    – dynamic sampling used for this statement

    Statistics
    ———————————————————-
    5 recursive calls
    0 db block gets
    9 consistent gets
    14 physical reads
    116 redo size
    411 bytes sent via SQL*Net to client
    385 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    SYS AS SYSDBA @ ora10gR2 SQL> alter system flush buffer_cache;

    System altered.

    SYS AS SYSDBA @ ora10gR2 SQL> select * from hr.departments;

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    10 Administration 200 1700
    20 Marketing 201 1800
    30 Purchasing 114 1700
    40 Human Resources 203 2400
    50 Shipping 121 1500
    60 IT 103 1400
    70 Public Relations 204 2700
    80 Sales 145 2500
    90 Executive 100 1700
    100 Finance 108 1700
    110 Accounting 205 1700

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    120 Treasury 1700
    130 Corporate Tax 1700
    140 Control And Credit 1700
    150 Shareholder Services 1700
    160 Benefits 1700
    170 Manufacturing 1700
    180 Construction 1700
    190 Contracting 1700
    200 Operations 1700
    210 IT Support 1700
    220 NOC 1700

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    230 IT Helpdesk 1700
    240 Government Sales 1700
    250 Retail Sales 1700
    260 Recruiting 1700
    270 Payroll 1700

    27 rows selected.

    Execution Plan
    ———————————————————-
    Plan hash value: 4167016233

    ——————————————————————————–

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    |

    ——————————————————————————–

    | 0 | SELECT STATEMENT | | 27 | 1512 | 2 (0)| 00:00:01
    |

    | 1 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 1512 | 2 (0)| 00:00:01
    |

    ——————————————————————————–

    Note
    —–
    – dynamic sampling used for this statement

    Statistics
    ———————————————————-
    4 recursive calls
    0 db block gets
    9 consistent gets
    14 physical reads
    0 redo size
    1447 bytes sent via SQL*Net to client
    396 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    27 rows processed

    SYS AS SYSDBA @ ora10gR2 SQL> select * from hr.departments;

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    10 Administration 200 1700
    20 Marketing 201 1800
    30 Purchasing 114 1700
    40 Human Resources 203 2400
    50 Shipping 121 1500
    60 IT 103 1400
    70 Public Relations 204 2700
    80 Sales 145 2500
    90 Executive 100 1700
    100 Finance 108 1700
    110 Accounting 205 1700

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    120 Treasury 1700
    130 Corporate Tax 1700
    140 Control And Credit 1700
    150 Shareholder Services 1700
    160 Benefits 1700
    170 Manufacturing 1700
    180 Construction 1700
    190 Contracting 1700
    200 Operations 1700
    210 IT Support 1700
    220 NOC 1700

    DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
    ————- —————————— ———- ———–
    230 IT Helpdesk 1700
    240 Government Sales 1700
    250 Retail Sales 1700
    260 Recruiting 1700
    270 Payroll 1700

    27 rows selected.

    Execution Plan
    ———————————————————-
    Plan hash value: 4167016233

    ——————————————————————————–

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    |

    ——————————————————————————–

    | 0 | SELECT STATEMENT | | 27 | 1512 | 2 (0)| 00:00:01
    |

    | 1 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 1512 | 2 (0)| 00:00:01
    |

    ——————————————————————————–

    Note
    —–
    – dynamic sampling used for this statement

    Statistics
    ———————————————————-
    0 recursive calls
    0 db block gets
    5 consistent gets
    0 physical reads
    0 redo size
    1447 bytes sent via SQL*Net to client
    396 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    27 rows processed

    Comment by Lutz Hartmann — May 16, 2007 @ 5:41 pm | Reply

  6. Here is the proof that a table identifier is stronger than a variable identifier in PL/SQL:

    HR @ ora10gR2 SQL>
    DECLARE
    employees VARCHAR2(30);
    v_empno NUMBER;
    BEGIN
    SELECT employee_id
    INTO v_empno
    FROM employees
    WHERE last_name=’Gietz';
    DBMS_OUTPUT.PUT_LINE(v_empno);
    END;
    /
    206

    Anyway, I would STRONGLY RECOMMEND not to use variables with the same names as DATABASE OBJECTs although it theoretically and practically works with table names!
    This is bad programming style!
    And it is hard to debug in case!

    Comment by Lutz Hartmann — May 16, 2007 @ 7:21 pm | Reply

  7. O.k. let’s have look again at comment #5:
    The example I have given was in fact misleading, because there is no such thing as an nK_KEEP_CACHE. The KEEP CACHE only aplies to block with DEFAULT BLOCK SIZE.

    The misleding fact is that DBA_TABLES returns the info:
    BUFFER_ TABLESPACE_NAME
    ——- ——————————
    KEEP LUTZ_4K
    But looking at v$buffer_pool we can see that there is only one KEEP CACHE with 8k buffers!

    SYS AS SYSDBA @ ora10gR2 SQL> select id, name, block_size, current_size, buffers
    from v$buffer_pool;
    ID NAME BLOCK_SIZE CURRENT_SIZE BUFFERS
    ———- ——————– ———- ———— ———-
    1 KEEP 8192 12 1497
    3 DEFAULT 8192 332 40927
    5 DEFAULT 4096 8 1958

    SYS AS SYSDBA @ ora10gR2 SQL> show parameter db_block_size

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_block_size integer 8192

    This means that the table from the 4k tablespace simply cannot risede in the KEEP CACHE!
    =;-)

    Comment by Lutz Hartmann — May 16, 2007 @ 8:27 pm | Reply

  8. Jeremy thank you for your comment, I am a reader of your blog, really a nice one, so waiting for your presenation notes :)

    Lutz thank you for your time with the comments, I have got 7 comments of yours related to the questions. Now it is my time to research for the quesitons, I will be commenting, best regards.

    Comment by H.Tonguç Yılmaz — May 19, 2007 @ 12:37 pm | Reply

  9. For Q3> does alter system buffer cache flushes nK pools also or just the default one? Lutz showed with examples in his several comments that the blocks of a table in the keep pool and in 4k cache pool is going out with “alter system flush buffer_cache;” sql*plus autotrace options statistics physical reads. I will try to summarize the scripts, and use 10046 event instead of autorace.

    Also Lutz showed optimizer uses index for count operations, so no addings needed for Q4> how does a pkey affect a count(*) I believe.

    And yet three to go :)

    Q1> what affect does cache option has in lru list positioning?
    Q5> is %10 stale option hard-coded, any hidden parameter for this
    Q6> is also dictionary stats included to the auto stale statistics gathering job

    Comment by H.Tonguç Yılmaz — May 19, 2007 @ 1:48 pm | Reply

  10. related to Q2> in plsql if we define a variable with the same name of a column and use it at where clause will it raise an error or bring all rows; “declare empid …; begin … select …. where empid = empid; ..”

    for comment 1 “ORA-01422: exact fetch returns more than requested number of rows” with INTO usage is exceptable yes, but what about a ref cursor;

    conn hr/hr
    set serveroutput on
    CREATE OR REPLACE PACKAGE pkg_tst AS
    TYPE type_refc IS REF CURSOR;
    PROCEDURE prc_get_all(wrefc OUT type_refc);
    END pkg_tst;
    /
    CREATE OR REPLACE PACKAGE BODY pkg_tst IS

    PROCEDURE prc_get_all(wrefc OUT type_refc) IS
    department_id departments.department_id%TYPE := 80;
    TYPE type_refc IS REF CURSOR;
    BEGIN
    OPEN wrefc FOR
    SELECT department_id FROM departments WHERE department_id = department_id;
    END prc_get_all;

    END pkg_tst;
    /
    DECLARE
    xrefc pkg_tst.type_refc;
    TYPE drec IS RECORD(
    department_id departments.department_id%TYPE);
    cr drec;
    BEGIN
    pkg_tst.prc_get_all(xrefc);
    LOOP
    FETCH xrefc
    INTO cr;
    EXIT WHEN xrefc%NOTFOUND;
    dbms_output.put_line(cr.department_id);
    END LOOP;
    CLOSE xrefc;
    END;
    /
    10
    20
    ..
    260
    270

    PL/SQL procedure successfully completed

    for comment 6, I think this one may be a better example;

    conn hr/hr
    set serveroutput on
    DECLARE
    employees VARCHAR2(30);
    BEGIN
    SELECT employee_id INTO employees FROM employees WHERE last_name = ‘Gietz’;
    dbms_output.put_line(employees);
    END;
    /
    206

    PL/SQL procedure successfully completed

    at the end I completely agree with Lutz, this way of development is none sense, every project should have some conventions in naming variables and no variable is accepted to be same named with a database object.

    ps: all tests were made on 10gR2

    Comment by H.Tonguç Yılmaz — May 19, 2007 @ 1:51 pm | Reply

  11. Concerning Q1:
    If you do a full table scan, the blocks are normally placed at the cold end of the LRU-List and kicked out immediately by new blocks comming in.
    If you define a table with CACHE option this will be different.
    Here is what the doku says:
    CACHE for data that is accessed frequently, this clause indicates that the blocks retrieved for this table are placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables.
    This is: the blocks will be aged out by the touch count mechanism, but are at the MRU end first.
    And here is what it says about BUFFER_POOL KEEP:
    KEEP takes precedence over any NOCACHE clause you specify for a table, cluster, materialized view, or materialized view log.

    This is: these blocks will be kind of nailed in the cache forever (lifetime of the instance)

    Hope this makes the difference clear enough.

    Comment by Lutz Hartmann — May 21, 2007 @ 7:48 pm | Reply

  12. About Q5:
    the 10% threshold seems to be hard-coded.
    I have asked a lot of experts in the past few days and nobody knew of any parameter.
    I will keep on checking.
    =?-)

    Comment by Lutz Hartmann — May 22, 2007 @ 6:24 am | Reply

  13. [...] Oracle geek Hasan Tonguc Yilmaz wrote his impressions about the Lutz Hartmans Seminar in Istanbul, he also upload his personal notes into his entry. He has another entry about championship of his [...]

    Pingback by Monthly Blogroll Report (May 2007) « Coskans Approach to Oracle — May 30, 2007 @ 12:02 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: