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 :)
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 
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
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
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
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!
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
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!
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!
=;-)
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.
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
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
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.
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.
=?-)