After startup trigger on RAC

Recently, a colleague of mine had some problems with pinning application procedures into the shared pool. His database was upgraded to RAC and he wanted to pin the application procedures and packages into shared pool upon the database startup.
The non-RAC version of the database was using a shell script, something like $ORACLE_HOME/local/scripts/pin_procs.sh in the script that was starting DB when the system was booted
However, RAC starts the database automatically and no user scripts are invoked during startup. The alternative mechanism is to use a startup database trigger. The only question that remains is whether the trigger will file once for each instance or once for the database? The trigger will fire once for each instance. Let’s demonstrate the statement:

mgogala@umajor:~$ ssh oracle@rac1
Last login: Sat Aug 8 19:36:48 2015
[oracle@rac1 ~]$ srvctl start db -d orcl
[oracle@rac1 ~]$ srvctl status db -d orcl
Instance orcl2 is running on node rac1
Instance orcl1 is running on node rac2
[oracle@rac1 ~]$ srvctl config db -d orcl
Database unique name: orcl
Database name: orcl
Oracle home: /home/oracle/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/orcl/spfileorcl.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: orcl
Database instances: orcl2,orcl1
Disk Groups: DATA,REDO
Mount point paths:
Services:
Type: RAC
Database is administrator managed
[oracle@rac1 ~]$

This is a little 11.2.0.4 RAC database, running on two nodes. The proof will be achieved by creating a trigger which inserts the instance id and the host name into a table. If the table contains only a single record, the trigger have fired only once. If there are two records, with two different instance numbers and machine names, the trigger has fired twice, just as was the original statement. So, let’s first create a table in the SCOTT schema:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create table startup(
2 inst_num integer,
3 inst_host varchar2(128));

Table created.

Elapsed: 00:00:00.11
SQL> grant insert on startup to system;

Grant succeeded.

Elapsed: 00:00:00.01

Next comes the trigger in the SYSTEM schema, because login triggers can only be created by the schema with the DBA privilege.

SQL> connect system@rac11
Enter password:
Connected.
CREATE OR REPLACE TRIGGER system.start_me_up AFTER startup ON DATABASE
DECLARE
instance_num INTEGER;
host VARCHAR2(128);
BEGIN
SELECT instance_number,host_name INTO INSTANCE_NUM,HOST FROM v$instance;
INSERT INTO scott.startup VALUES (instance_num,host);
END;
9 /

Trigger created.

Elapsed: 00:00:00.26

Now, the moment of truth. Let’s restart the database and see what is in the SCOTT.STARTUP table. First, the restart:

[oracle@rac1 ~]$ srvctl stop db -d orcl -o immediate
[oracle@rac1 ~]$ srvctl status db -d orcl
Instance orcl2 is not running on node rac1
Instance orcl1 is not running on node rac2
[oracle@rac1 ~]$ srvctl start db -d orcl
[oracle@rac1 ~]$ srvctl status db -d orcl
Instance orcl2 is running on node rac1
Instance orcl1 is running on node rac2
[oracle@rac1 ~]$

Now, let’s check SCOTT.STARTUP:
mgogala@umajor:~$ sqlplus scott/tiger@rac11

SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 12 18:32:01 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> column inst_host format a10;
SQL> select * from startup;

INST_NUM INST_HOST
---------- ----------
2 rac1
1 rac2

Elapsed: 00:00:00.01
SQL>

The table contains two records, once for each instance. That proves that the startup trigger will fire once for each instance. This also provides the mechanism for doing different things after starting different instances because it would be trivial to add IF … THEN clause based on the instance number. However, in this particular case, the task was to pin the same set of the application procedures in both instances. This was accomplished by the database trigger.

Posted in Uncategorized | Leave a comment

ASM 12c, OCR and voting devices II

I finished the installation of 12c cluster and noticed that I haven’t been asked for voting and registry devices as was previously the case. I had to create a single disk group. John Franklin, from LinkedIn RAC SIG suggested running ocrcheck command, so I did:

[grid@rac1 ~]$ ocrcheck -details
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1612
Available space (kbytes) : 407956
ID : 1368218241
Device/File Name : +DATA/scan11/OCRFILE/registry.255.886021581
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

[grid@rac1 ~]$

So OCR file is still there, only lumped as a single file in the disk group created during the installation. So, let’s see what else is there:

ASMCMD> ls +DATA
ASM/
ORCL/
_MGMTDB/
orapwasm
scan11/

ORCL is the name of my Oracle 11.2.0.4 database, scan11 is the cluster name. Registry is precisely where ocrcheck said it would be:

ASMCMD> ls scan11
ASMPARAMETERFILE/
OCRFILE/
ASMCMD> ls scan11/OCRFILE
REGISTRY.255.886021581
ASMCMD>

What in the world is MGMTDB? Turns out that it is pretty well documented:

Mike Dietrich’s blog

Management database contains cluster health information and is a full fledged Oracle database which uses 4.5GB:

I finished the installation of 12c cluster and noticed that I haven’t been asked for voting and registry devices as was previously the case. I had to create a single disk group. John Franklin, from LinkedIn RAC SIG suggested running ocrcheck command, so I did:

[grid@rac1 ~]$ ocrcheck -details
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1612
Available space (kbytes) : 407956
ID : 1368218241
Device/File Name : +DATA/scan11/OCRFILE/registry.255.886021581
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

[grid@rac1 ~]$

So OCR file is still there, only lumped as a single file in the disk group created during the installation. So, let’s see what else is there:

ASMCMD> ls +DATA
ASM/
ORCL/
_MGMTDB/
orapwasm
scan11/

ORCL is the name of my Oracle 11.2.0.4 database, scan11 is the cluster name. Registry is precisely where ocrcheck said it would be:

ASMCMD> ls scan11
ASMPARAMETERFILE/
OCRFILE/
ASMCMD> ls scan11/OCRFILE
REGISTRY.255.886021581
ASMCMD>

What in the world is MGMTDB? Turns out that it is pretty well documented:

Mike Dietrich’s blog

Management database contains cluster health information and is a full fledged Oracle database which uses 4.5GB:

ASMCMD> du _MGMTDB
Used_MB Mirror_used_MB
4365 4365
ASMCMD>

Ti instance is, of course running:

[grid@rac1 ~]$ ps -ef|grep smon
grid 3579 1 0 10:02 ? 00:00:00 asm_smon_+ASM2
root 4818 1 0 10:02 ? 00:00:17 /app/grid/12.1.0/bin/osysmond.bi
grid 5388 1 0 10:03 ? 00:00:00 mdb_smon_-MGMTDB

So, let’s see what’s in there:

[grid@rac1 ~]$ export ORACLE_SID=”-MGMTDB”
[grid@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 27 10:37:43 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Automatic Storage Management and Advanced Analytics options

SQL> select username from dba_users;

USERNAME
——————————————————————————–
ANONYMOUS
DBSNMP
WMSYS
XDB
APPQOSSYS
GSMADMIN_INTERNAL
GSMCATUSER
SYSBACKUP
OUTLN
DIP
SYSDG

USERNAME
——————————————————————————–
ORACLE_OCM
SYSKM
XS$NULL
GSMUSER
AUDSYS
SYSTEM
SYS

18 rows selected.

SQL> alter session set current_schema=GSMADMIN_INTERNAL;

Session altered.

SQL> desc database
Name Null? Type
—————————————– ——– —————————-
NAME NOT NULL VARCHAR2(30)
POOL_NAME VARCHAR2(30)
REGION_NUM NUMBER
GSM_PASSWORD NOT NULL VARCHAR2(30)
CONNECT_STRING NOT NULL VARCHAR2(256)
DATABASE_NUM NOT NULL NUMBER
STATUS NOT NULL CHAR(1)
SCAN_ADDRESS VARCHAR2(256)
ONS_PORT NUMBER
NUM_ASSIGNED_INSTANCES NOT NULL NUMBER
SRLAT_THRESH NUMBER
CPU_THRESH NUMBER
VERSION VARCHAR2(30)
DB_TYPE CHAR(1)
ENCRYPTED_GSM_PASSWORD RAW(2000)

SQL> select name,version,db_type from database;

no rows selected

SQL> select * from database;

no rows selected

SQL> select * from verify_history
2 /

no rows selected

SQL>

So, nothing is in there yet. I will have to follow the documentation and set up monitoring and diagnostic data collection.  I am slightly worried because another full fledged EE Oracle instance is added to my RAC nodes, thereby increasing CPU and memory consumption. On the other hand, the metrics described in the manual page looks very useful. A system monitoring repository can be a treasure trove of the information. My feelings are mixed, let’s wait and see.

Posted in Uncategorized | Leave a comment

ASM 12c – no voting/quorum device and OCR?

I installed my first 12c ASM (database is still 11.2.0.4) and I made the 11G disk layout: 2GB for voting/quorum, 2 GB for OCR. However, the installation procedure did not ask me for the voting and OCR devices. ASM came right up without them. So, the devices are not needed for 12c? What about upgrading from 11g, which did need the devices? The “crsctl” utility confirms that voting and OCR devices are not there:

——————————————————————————–
Name Target State Server State details
——————————————————————————–
Local Resources
——————————————————————————–
ora.ACFS.ORASHARE.advm
ONLINE ONLINE rac1 Volume device /dev/a
sm/orashare-453 is o
nline,STABLE
ONLINE ONLINE rac2 Volume device /dev/a
sm/orashare-453 is o
nline,STABLE
ora.ACFS.dg
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.DATA.dg
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.FRA.dg
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.REDO.dg
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.acfs.orashare.acfs
ONLINE ONLINE rac1 mounted on /orashare
,STABLE
ONLINE ONLINE rac2 mounted on /orashare
,STABLE
ora.asm
ONLINE ONLINE rac1 Started,STABLE
ONLINE ONLINE rac2 Started,STABLE
ora.net1.network
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
ora.ons
ONLINE ONLINE rac1 STABLE
ONLINE ONLINE rac2 STABLE
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE rac1 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE rac1 169.254.244.71 10.1.
1.10,STABLE
ora.cvu
1 ONLINE ONLINE rac2 STABLE
ora.mgmtdb
1 ONLINE ONLINE rac1 Open,STABLE
ora.oc4j
1 ONLINE ONLINE rac2 STABLE
ora.orcl.db
1 ONLINE ONLINE rac2 Open,STABLE
2 ONLINE ONLINE rac1 Open,STABLE
ora.rac1.vip
1 ONLINE ONLINE rac1 STABLE
ora.rac2.vip
1 ONLINE ONLINE rac2 STABLE
ora.scan1.vip
1 ONLINE ONLINE rac1 STABLE
ora.scan2.vip
1 ONLINE ONLINE rac1 STABLE
——————————————————————————–

That is a very big departure from the classic architecture. Voting and registry devices have been used since the OPS times. The question, still unanswered, is how will CRS determine which node to evict in case of failure. Voting device was used, just as in the “Survivor” show to determine who gets voted off the island.

Posted in Uncategorized | 2 Comments

Oracle Database Transactions and Locking Revealed, by Tom Kyte and Darl Kuhn

I recently came across the book called “Oracle Database Transactions and Locking Revealed” by Tom Kyte and Darl Kuhn. Usually, any new book by Tom Kyte is a big deal and acquires a huge following very quickly. Strangely enough there was only a single review on the Amazon, before I added my own.

For the reasons unknown to me, this book is literally ignored. I haven’t seen posts on the oracle-l about it, on the LinkedIn forums or Usenet. This book is relatively new, became available in November 2014, but it’s enough time for the book to be widely commented or recommended. There are no reasons not to recommend the book. The book is very short, 150 pages, and is not one of those 700+ pages monsters that Oracle books of today frequently turn into. I would wholeheartedly recommend the book to developers, developing Oracle applications. It deals with critically important subjects like locking, transactions, undo and redo.  The only thing missing from the book, which in my opinion should have been there, is flashback.

However, the book is primarily dedicated to applications developers, not meant as an in-depth study for the aspiring DBA hopefuls. The main strength of the book lies precisely in its brevity and clarity. It covers the topics of critical importance for the application development. How many times did I hear the myth about the need to commit frequently? How many times did I see “WHEN OTHERS” exception handler in its most atrocious form (“WHEN OTHERS THEN NULL”)? Tom and Darl do an excellent job explaining these things. They also provide tools to investigate redo and undo, all in 150 pages. Excellent job, must have for any application developer.

Posted in Uncategorized | 1 Comment

PostgreSQL Hints and DBMS_STATS

For those who don’t know,‭ ‬it is now possible to use hints on PgSQL.‭ ‬Here
is how things work:

-‭ ‬Download and install the extension from the home page:

‭  ‬http://sourceforge.jp/projects/pghintplan/

If you are using Red Hat derivative,‭ ‬like me,‭ ‬it’s a simple RPM package.‭ ‬Check the content of the package,‭ ‬like this:

[‬mgogala@pg91‭ ~]‬$‭ ‬rpm‭ ‬-qa|grep pg_hint

pg_hint_plan93-1.1.3-1.el6.x86‭_‬64‭
[‬mgogala@pg91‭ ~]‬$
‭[‬mgogala@pg91‭ ~]‬$‭ ‬rpm‭ ‬-ql pg_hint_plan93-1.1.3-1.el6.x86‭_‬64
/usr/pgsql-9.3/lib/pg_hint_plan.so
/usr/pgsql-9.3/share/extension/pg_hint_plan–1.0–1.1.1.sql
/usr/pgsql-9.3/share/extension/pg_hint_plan–1.1.1–1.1.2.sql
/usr/pgsql-9.3/share/extension/pg_hint_plan–1.1.2–1.1.3.sql
/usr/pgsql-9.3/share/extension/pg_hint_plan–1.1.3.sql
/usr/pgsql-9.3/share/extension/pg_hint_plan.control

Put the following parameters into your postgresql.conf:

shared_preload_libraries‭ = '‬/usr/pgsql-9.3/lib/pg_hint_plan.so‭'
pg_hint_plan.enable_hint_tables‭ = ‬on

Now you need to restart your PostgreSQL and you’re done,‭ ‬the extension is ready to use.‭ ‬It is a good practice to run the following statement:

postgres‭=‬#‭ ‬create extension if not exists pg_hint_plan‭;
CREATE EXTENSION

Now,‭ ‬users can do the following:

mgogala‭=‬#‭ ‬explain select‭ * ‬from emp where empno‭=‬7934‭;
QUERY PLAN
----------------------------------------------------
‭ ‬Seq Scan on emp‭  (‬cost‭=‬0.00..1.18‭ ‬rows‭=‬1‭ ‬width‭=‬43‭)
Filter:‭ (‬empno‭ = ‬7934‭)
(2‭ ‬rows‭)

Without any hints,‭ ‬the planner chooses sequential scan.‭ ‬Now,‭ ‬let’s try with a hint:

mgogala‭=‬#‭ ‬explain select‭ ‬/‭*‬+‭ ‬IndexScan(emp emp_pkey‭) *‬/‭ * ‬from emp where
empno‭=‬7934‭;
QUERY PLAN
---------------------------------------------------------------------
‭ ‬Index Scan using emp_pkey on emp‭  (‬cost‭=‬0.14..8.15‭ ‬rows‭=‬1‭ ‬width‭=‬43‭)
Index Cond:‭ (‬empno‭ = ‬7934‭)
(2‭ ‬rows‭)

Voila,‭ ‬the optimizer hints are ready to use.‭ ‬This works on‭ ‬9.3.6:

mgogala‭=‬#‭ ‬select version‭();

version

——————————————————————————–
——————————-
‭ ‬PostgreSQL‭ ‬9.3.6‭ ‬on x86‭_‬64-unknown-linux-gnu,‭ ‬compiled by gcc‭ (‬GCC‭)
4.4.7‭ ‬20120‭ ‬313‭ (‬Red Hat‭ ‬4.4.7-11‭)‬,‭ ‬64-bit‭ (‬1‭ ‬row‭)

There is also RPM package for‭ ‬9.4.‭ ‬Please note that the planner has correctly told you that the cost of an index scan is higher than the cost of sequential scan.‭ ‬In this particular case,‭ ‬it is correct.‭ ‬The whole table fits into a single block and sequential scan of that block is the fastest method.‭ ‬Index scan needs to read the index root block,‭ ‬then the index leaf block and only then the table block.‭  ‬The optimizer has incorrectly estimated the cost of a sequential scan,‭ ‬because it cannot be‭ “‬0.00‭”‬.‭ ‬That is an example of an optimizer bug and yet another reason to have hints at hand.
There are also cases when the optimizer plan itself goes awry.‭ ‬Any help that can be provided in such situations is beneficial. Hints give you better granularity then‭ “‬set enable_indexscan‭” ‬or similar commands which affect the entire session and can negatively impact the subsequent SQL commands.‭ ‬Hints are not ideal and do require some maintenance between versions,‭ ‬but are sometimes the only solution when performance of your SQL has to be improved quickly.‭ ‬You know your data better than the planner and hints give you the means of overriding an incorrect plan for a single SQL statement.‭ ‬Hints are simply a tool that can be used in fire fighting situations.

It is encouraging to see such extension in a community which was up in arms against the proponents of hints not so long ago.‭ ‬There is still the ridiculous‭ “‬we don’t want hints‭” ‬part on the PostgreSQL wiki,‭ ‬but there is also the‭  ‬extension which allows anyone who does want them to use them.‭ ‬PostgreSQL‭ community has just discovered democracy.

Another interesting PostgreSQL package by the same author is the following:

http://pgdbmsstats.sourceforge.jp/pg_dbms_stats-en.html

That package offers some,‭ ‬but not all,‭ ‬functionality found in Oracle’s DBMS_STATS package,‭ ‬in particular locking and exporting the table stats.‭ ‬That functionality has not been offered by VACUUM utility which PostgreSQL uses to gather stats on the tables.‭

Posted in Uncategorized | Tagged | 3 Comments

Opatch Integration

Oracle 12c came up with one nice new feature: Opatch, the tool known to all database administrators is now integrated into the database in the form of DBMS_QOPATCH package. If the database contains pluggable databases, the package must be executed from the root (CDB$ROOT) level, as user sys.
Also, a minor inconvenience is that there are no tabular results, results are returned as XML. It is a bit cumbersome to work with and I do prefer the traditional Opatch for now. So, let’s make an example:

Screenshot - 11102014 - 05:40:22 PM

The query is “select dbms_qopatch.get_opatch_list() from dual” and it returns results in one monstrous line. There is no nice way to look at the output XML in the SQL Developer, one must cut & paste the entire monstrosity into a 3rd party XML editor:
Screenshot - 11102014 - 05:51:11 PM
This looks like a story similar to V$DIAG_ALERT_EXT, which was available in the last versions of 10G software as an X$ table, to be made both available and useful in version 11G. This XML output is completely useless and very hard to read. The good, old “opatch lsinventory” is still preferred, but it is going to be integrated with the database in not so distant future. For now, DBA_REGISTRY_HISTORY will have to do.

Posted in Uncategorized | Leave a comment

Poor Man’s In-Memory Caching

Introduction

It is common knowledge, or at least it should be, that Oracle has
released version 12.1.0.2 in June 2014. which has a feature called
“Oracle In-Memory”. The name is a tad confusing, since many
features were called “in-memory” before, from “Very Large
Memory” to Times 10 product.

This “In Memory” option is a licensed product featuring
columnar storage, in addition to the normal buffer storage, almost
identical to IBM’s “Blu acceleration” product, released for DB2
in 2013. Essentially, it’s like creating an in-memory bitmap index on
the low selectivity column, but without adverse effects when it comes
to locking. Obviously, as In Memory option is a licensed product, it
cannot be what the title is referring to.

There is another caching new feature built into 12.1.0.2, which is
much less spectacular but still useful, if properly understood. It is
not unlike the KEEP buffer pool, known from the previous versions,
although it is a bit less granular. This feature is controlled by the
init parameter named DB_BIG_TABLE_CACHE_PERCENT_TARGET, with very
suggestive and self-explanatory name and can be monitored by the two
V$ tables, namely V$BT_SCAN_CACHE and V$BT_SCAN_OBJ_TEMPS. Both the
parameter and the V$ tables are described in the reference manual.

Essentially, by setting this parameter on a single instance
database, the instance allocates a percent of the buffer cache for
caching large tables. What are large tables? Apparently, the same
criteria as for direct reads is applied: if the table is larger than
2% of SGA, it is considered a large table. This is explained in
detail by Tanel Poder in his excellent article.
The same criteria is used to decide whether the table is big or not.

Details

It is not possible to control this feature on the table basis, by
specifying something like BUFFER_POOL KEEP in STORAGE clause for the
table. All large tables are cached, when a scan is executed. It
doesn’t necessarily need to be a full table scan

SQL>
startup

ORACLE
instance started.

Total
System Global Area 838860800 bytes

Fixed
Size 2929936 bytes

Variable
Size 335547120 bytes

Database
Buffers 494927872 bytes

Redo
Buffers 5455872 bytes

Database
mounted.

Database
opened.

SQL>
alter session set container=pdbtest;

Session
altered.

SQL>
select object_count from V$BT_SCAN_CACHE;

OBJECT_COUNT

————


0

SQL>
set autotrace on

SQL>
select count(*) from scott.t1;

COUNT(*)

———-

2000000

Execution
Plan

———————————————————-

Plan
hash value: 227768458

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

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

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

|
0 | SELECT STATEMENT | | 1 | 211 (6)| 00:00:01 |

|
1 | SORT AGGREGATE | | 1 | | |

|
2 | INDEX FAST FULL SCAN| SYS_C0011355 | 2565K| 211 (6)|
00:00:01 |

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

Note

—–


dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-

947
recursive calls


0 db block gets

11489
consistent gets

4191
physical reads


0 redo size

542
bytes sent via SQL*Net to client

552
bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client

698
sorts (memory)


0 sorts (disk)


1 rows processed

SQL>
select object_count from V$BT_SCAN_CACHE;

OBJECT_COUNT

————


1

So, the cache was populated by the full index scan. The same
caching goes into effect if a range scan is used. Caching takes
place when the big table is accessed, not only by the full table
scan. Also, optimizer is unaware that the table is cached. This claim
will be proven later. First, let’s check that the object is the right
one.

SQL>
select object_count from v$bt_scan_cache;

OBJECT_COUNT

————


1

SQL>
select ts#,dataobj#,policy from V$BT_SCAN_OBJ_TEMPS;

TS#
DATAOBJ# POLICY

———-
———- ———-

196612 96464
MEM_ONLY

SQL>
select owner,object_name,object_type

2
from dba_objects

3
where data_object_id=96464;

OWNER

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

OBJECT_NAME

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

OBJECT_TYPE

———————–

SCOTT

T1

TABLE

Sure enough, it’s our table SCOTT.T1, which has 2M generated rows
and takes around 64M of disk space. The TS# column is supposed to be
the number of the tablespace, but the number is obviously wrong since
I have only 7 tablespaces in PDBTEST and 5 in CDB$ROOT. Connection ID
in the V$BT_SCAN_OBJ_TEMPS is also consistently 0, despite the fact
that the object resides in CON_ID 4.. Now, it is time to prove that
optimizer doesn’t know about caching. The first thing to execute is
counting without any hints:

SQL>
set timing on

SQL>
select ts#,dataobj#,con_id from V$BT_SCAN_OBJ_TEMPS;

TS#
DATAOBJ# CON_ID

———-
———- ———-

196612 96464
0

Elapsed:
00:00:00.00

SQL>
set autotrace on

SQL>
select count(*) from scott.t1;

COUNT(*)

———-

2000000

Elapsed:
00:00:00.07

Execution
Plan

———————————————————-

Plan
hash value: 227768458

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

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

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

|
0 | SELECT STATEMENT | | 1 | 211 (6)| 00:00:01 |

|
1 | SORT AGGREGATE | | 1 | | |

|
2 | INDEX FAST FULL SCAN| SYS_C0011355 | 2565K| 211 (6)|
00:00:01 |

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

Note

—–


dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-


0 recursive calls


0 db block gets

3911
consistent gets


0 physical reads


0 redo size

542
bytes sent via SQL*Net to client

552
bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


1 rows processed

Elapsed:
00:00:00.08

The plan still reflects the normal situation, without any caching.
The path selected is the fast full index scan of the primary key
index. Now, let’s try forcing the full scan, using a hint:

SQL>
select /*+ full(t1) */ count(*) from scott.t1;

COUNT(*)

———-

2000000

Elapsed:
00:00:00.03

Execution
Plan

———————————————————-

Plan
hash value: 3724264953

——————————————————————-

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

——————————————————————-

|
0 | SELECT STATEMENT | | 1 | 596 (33)| 00:00:01 |

|
1 | SORT AGGREGATE | | 1 | | |

|
2 | TABLE ACCESS FULL| T1 | 2565K| 596 (33)| 00:00:01 |

——————————————————————-

Note

—–


dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-


0 recursive calls


0 db block gets

7883
consistent gets


0 physical reads


0 redo size

542
bytes sent via SQL*Net to client

552
bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


0 sorts (memory)


0 sorts (disk)


1 rows processed

From the statistics, it is visible that the cache was used, since
there were no physical reads. Full table scan used the cache, it
should have been selected by the optimizer. The index scan will also
be selected even if the table cache is populated by the full table
scan and index blocks are not cached, which means that optimizer
doesn’t take into account the big table caching

Now, the next interesting thing happens when the cache is
cleaned. Flushing the buffer cache cleans the big table cache, but
the V$ tables do not recognize that:

SQL>
alter system flush buffer_pool all;

System
altered.

Elapsed:
00:00:00.01

SQL>
select ts#,dataobj#,con_id from V$BT_SCAN_OBJ_TEMPS;

TS#
DATAOBJ# CON_ID

———-
———- ———-

196612 96464
0

Elapsed:
00:00:00.00

SQL>
set autotrace on

SQL>
select /*+ full(t1) */ count(*) from scott.t1;

COUNT(*)

———-

2000000

Elapsed:
00:00:01.97

Execution
Plan

———————————————————-

Plan
hash value: 3724264953

——————————————————————-

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

——————————————————————-

|
0 | SELECT STATEMENT | | 1 | 596 (33)| 00:00:01 |

|
1 | SORT AGGREGATE | | 1 | | |

|
2 | TABLE ACCESS FULL| T1 | 2565K| 596 (33)| 00:00:01 |

——————————————————————-

Note

—–


dynamic statistics used: dynamic sampling (level=2)

Statistics

———————————————————-


79 recursive calls


0 db block gets

8058
consistent gets

8194
physical reads


0 redo size

542
bytes sent via SQL*Net to client

552
bytes received via SQL*Net from client


2 SQL*Net roundtrips to/from client


6 sorts (memory)


0 sorts (disk)


1 rows processed

SQL>

The V$BT_SCAN_OBJ_TEMPS reports that the SCOTT.T1 table is
cached, but the query causes a bunch of physical reads, which
couldn’t have happened if the table was really cached.

Conclusion

Big table caching is less granular than using the KEEP buffer
pool, which would be possible on the per-table basis and on read
block basis so it would be less efficient than using the KEEP pool.
On the other hand, it is much simpler to use, just allocate percent
of memory and forget it. The thing that bothers me most is that this
feature is also activated by a range scan and that the decision is
made solely on the basis of the table size. This guarantees bringing
into the cache some tables that I wouldn’t normally want to bring
into the cache and thereby making the cache less useful. Other than
that, this looks like an interesting new feature.

Posted in Uncategorized | Tagged | Leave a comment