Pre-Loading Oracle Text indexes into Memory
Pre-Loading Oracle Text indexes into Memory
Introduction
Oracle Text indexes are stored in Oracle Database relational tables. These tables normally
reside on disk. Systems with large memory configurations generally benefit greatly from
Oracle caching - a second or subsequent search for a particular term is generally much
faster than the first search.
But what about the first search? Can we pre-load the database cache
in order to make sure that most of what we need is already in memory
before the first search?
This paper explores some techniques which make this possible.
Prerequisites
This paper assumes a good understanding of the various underlying tables
of an Oracle Text index. If you are not aware of the functions of the various
tables ($I, $K etc), then the paper should still offer some useful techniques,
but to gain full benefit you should review documents such as
this.
The Keep Pool
Normally the database buffer cache size is set with the parameter DB_CACHE_SIZE.
If our intention is to load all of our indexes into memory and keep them there,
we would probably be better off putting them into the "keep pool". This is a
buffer cache from which objects will never be aged out. The size of the keep pool
is determined by the parameter DB_KEEP_CACHE_SIZE.
A table or index may be forced to reside in the keep cache using the
commands
ALTER TABLE <tablename> STORAGE (BUFFER POOL KEEP);
ALTER INDEX <indexname> STORAGE (BUFFER_POOL KEEP);
What to cache?
If we have unlimited memory available, then we would want to put all
of the following into the keep pool:
- $I token table
- $X index on $I table
- $R table
- The base table itself (assuming we are selecting more than just ROWID, or use a sort on a real column).
If our table uses local partition indexes, we would need to store
these tables for each partition. Additionally, if our application uses
functional lookups in the text table we would need the $K table, and
if we're using prefix indexing we would need the $P table.
However, in real life it very likely that all of these tables will
exceed the amount of memory we can afford to allocate to the keep
pool. We would therefore need to select only certain tables. If we are
using local partition indexes, and our queries are normally sorted by
the partition key, it might make sense to just cache the tables for
the first few partitions.
Alternatively, if our table isn't partitioned, it might be better to
cache certain tables and not others.
In decreasing order of importance, we could rank them as
- $X
- $R
- equivalence ($I, $K, base table)
- $P (but way below)
Now usually, $R is very small, and $X is small compared to the size of
the base table and $I, so it is suggested that these two ($R andf $X)
always be loaded.
$I, base table and $K are perhaps equally important,
- though the sizes are clearly not comparable, and different usage
scenarios would tend to favor different approaches. In the normal case
(with little or no functional invocation), we would suggest base-table,
$I, $K in that order. If the index is badly fragmented, but we're
still not using functional invocation, then we'd flip the base table
and $I. If there's a large amount of functional invocation being used,
$K goes to the head of the list.
Determining the size of an index
The CTX_REPORT index package can be used to determine the size of a index, which
will aid us in planning how much we can cache.
For testing purposes, I built a local partition index on a table called
TEST with one million rows - each row consisting of just the unique key ID,
and a filename pointing to an external XML file.
The output of CTX_REPORT for this table is as follows:
SQL> set long 50000
SQL> select ctx_report.index_size('test_idx') from dual;
CTX_REPORT.INDEX_SIZE('TEST_IDX')
--------------------------------------------------------------------------------
===========================================================================
INDEX SIZE FOR TESTUSER.TEST_IDX
===========================================================================
...
TOTALS FOR INDEX TESTUSER.TEST_IDX
---------------------------------------------------------------------------
CTX_REPORT.INDEX_SIZE('TEST_IDX')
--------------------------------------------------------------------------------
TOTAL BLOCKS ALLOCATED: 206608
TOTAL BLOCKS USED: 198235
TOTAL BYTES ALLOCATED: 1,692,532,736 (1,614.13 MB)
TOTAL BYTES USED: 1,623,941,120 (1,548.71 MB)
So we can see that the total index size is about 1.6GB. Since the
machine has "only" 2GB of memory, we can't really afford to put all of
that into memory. So we're going to cache a subset.
Determining what is in the buffer pool
One way to find out what we need to cache is to make the normal buffer
cache as large as possible, bounce the database, run a
representative query, and find out what ended up in the buffer cache.
SQL> connect sys as sysdba
SQL> alter system set db_cache_size = 1G;
SQL> shutdown
SQL> startup
SQL> connect testuser/testuser
SQL> select /* FIRST_ROWS */ * from (
select id, filename from test
where contains (filename, 'district and region) > 0
order by id desc )
where rownum <= 20;
...
Note that this query fetches only the top 20 hits, ordered by "id", which
is the partition key for my table. This means that a query for a common
word (or words) will generally only have to hit one - or a few - partitions
of the index.
After running this query, we can find out what's in the buffer cache by running the following SQL
as a dba:
COLUMN OBJECT_NAME FORMAT A30
COLUMN OBJECT_TYPE FORMAT A15
COLUMN OWNER FORMAT A15
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999
SELECT o.OBJECT_NAME, o.OBJECT_TYPE, o.OWNER, COUNT(*) NUMBER_OF_BLOCKS
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD
AND o.OWNER NOT IN
('SYS', 'SYSTEM', 'SYSMAN', 'XDB', 'IX', 'WMSYS', 'CTXSYS')
GROUP BY o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE
ORDER BY COUNT(*);
This will tell us which user tables are now in the cache. If you want to see
system / data dictionary tables as well, you can vary the "NOT IN" list.
After running the previous CONTAINS query, this gives me:
OBJECT_NAME OBJECT_TYPE OWNER NUMBER_OF_BLOCKS
------------------------------ --------------- --------------- ----------------
DR#TEST_IDX0009$R TABLE TESTUSER 2
DR#TEST_IDX0010$R TABLE TESTUSER 2
SYS_LOB0000056864C00002$$ LOB TESTUSER 4
DR#TEST_IDX0009$X INDEX TESTUSER 12
DR#TEST_IDX0010$X INDEX TESTUSER 12
SYS_LOB0000056876C00002$$ LOB TESTUSER 17
TEST TABLE PARTITION TESTUSER 22
DR#TEST_IDX0010$I TABLE TESTUSER 459
DR#TEST_IDX0009$I TABLE TESTUSER 494
9 rows selected.
So what does that tell us?
Firstly, we can see from the last two lines that the majority of
blocks are from two $I tables - those for the last two partitions of
the table (since it has ten partitions). Then, moving up a bit, we see
22 blocks from the base table TEST. Note that this doesn't tell us
which partitions of the table these blocks come from, but it's
reasonable to assume that they come from the last two partitions as
well. There are also a few blocks from the $R table.
We'll be covering the SYS_LOB... objects in a moment. For now, we're
going to ignore them as tracking them down will be a useful exercise
coming up.
Pre-Loading the tables
There is no simple command in Oracle to load data into the buffer cache. You must run
a SQL command (normally a query, though DML may do it as well).
We need to load both the tables, and the indexes for indexed tables. We can do this by running
queries with appropriate hints. Rather than actually fetching every row which would require
lots of traffic between the kernel and client, we can make use of aggregate functions so
that the rows are fetched only to the kernel:
SELECT /*+ FULL(ITAB) */ SUM(TOKEN_COUNT), SUM(LENGTH(TOKEN_INFO)) FROM DR#TEST_IDX0010$I ITAB;
SELECT /*+ FULL(ITAB) */ SUM(TOKEN_COUNT), SUM(LENGTH(TOKEN_INFO)) FROM DR#TEST_IDX0009$I ITAB;
SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(TOKEN_TEXT)) FROM DR#TEST_IDX0010$I ITAB;
SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(TOKEN_TEXT)) FROM DR#TEST_IDX0009$I ITAB;
SELECT SUM(ROW_NO) FROM DR#TEST_IDX0010$R;
SELECT SUM(ROW_NO) FROM DR#TEST_IDX0009$R;
SELECT /*+ FULL(BTAB) */ SUM(ID) FROM TEST BTAB WHERE ID >= 900000;
There's a couple of points to note here. The final select from the base table is fetching
specific rows that I know are in the last two partitions. You would need to vary this
according to your system design.
Also note that the $R loading is incomplete. This is related to those
system LOB objects we saw earlier - and we'll be coming back to that
later.
Running these queries on my system took just over a minute. That's to
load one-fifth of the partitions for a million-row table. Here's the
output after rebooting the machine to avoid any effects of disk
caching:
SUM(TOKEN_COUNT) SUM(LENGTH(TOKEN_INFO))
---------------- -----------------------
6963707 37472389
Elapsed: 00:00:27.31
SUM(TOKEN_COUNT) SUM(LENGTH(TOKEN_INFO))
---------------- -----------------------
6587719 35349528
Elapsed: 00:00:27.77
SUM(LENGTH(TOKEN_TEXT))
-----------------------
8994078
Elapsed: 00:00:03.74
SUM(LENGTH(TOKEN_TEXT))
-----------------------
8855116
Elapsed: 00:00:03.21
SUM(ID)
----------
9.5001E+10
Elapsed: 00:00:01.74
SUM(ROW_NO)
-----------
231
Elapsed: 00:00:00.12
SUM(ROW_NO)
-----------
231
Now we'll run our query against V$BH again to see what's been cached. Remember,
this is after pre-loading the various tables rather than running a simple query.
OBJECT_NAME OBJECT_TYPE OWNER NUMBER_OF_BLOCKS
------------------------------ --------------- --------------- ----------------
DR#TEST_IDX0009$R TABLE TESTUSER 2
DR#TEST_IDX0010$R TABLE TESTUSER 2
TEST TABLE PARTITION TESTUSER 612
DR#TEST_IDX0009$X INDEX TESTUSER 5,905
DR#TEST_IDX0010$X INDEX TESTUSER 6,018
DR#TEST_IDX0009$I TABLE TESTUSER 14,607
DR#TEST_IDX0010$I TABLE TESTUSER 15,090
7 rows selected.
Now this looks similar to our previous output, except that the number of blocks
is greater for most of the tables (because we've loaded ALL blocks, rather than
just the ones needed by the query), and we don't have those mysterious SYS_LOB
objects (again we'll come back to those later).
We're going to try to put all those tables into the keep pool, so we need
to know how much space they're taking to decide how big to make our keep pool.
We can sum up the NUMBER_OF_BLOCKS column above, or use:
SELECT COUNT(*) TOTAL_BLOCKS, COUNT(*)*8/1024 MEGABYTES
FROM DBA_OBJECTS o, V$BH bh
WHERE o.DATA_OBJECT_ID = bh.OBJD
AND o.OWNER NOT IN
('SYS', 'SYSTEM', 'SYSMAN', 'XDB', 'IX', 'WMSYS', 'CTXSYS')
ORDER BY COUNT(*);
TOTAL_BLOCKS MEGABYTES
------------ ----------
49503 386.742188
So we need just under 400MB of keep pool for these tables. We'll adjust
this via the DB_KEEP_CACHE_SIZE parameter. I'm going to run out of memory
if I just bump that up without reducing my large DB_CACHE_SIZE, so I'll
do that at the same time. I'm using an spfile rather than init.ora, so
I'll do:
ALTER SYSTEM SET DB_CACHE_SIZE = 500M SCOPE=SPFILE;
-- Down from 1G before
ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 400M SCOPE=SPFILE;
And then of course I'll bounce the database.
Forcing Objects to the KEEP POOL
Next we need to ensure that our tables go into the Keep Pool rather
than the normal buffer cache. We do that with ALTER TABLE or ALTER INDEX
statements as appropriate:
alter table DR#TEST_IDX0010$I storage (buffer_pool keep);
alter table DR#TEST_IDX0009$I storage (buffer_pool keep);
alter index DR#TEST_IDX0010$X storage (buffer_pool keep);
alter index DR#TEST_IDX0009$X storage (buffer_pool keep);
alter table DR#TEST_IDX0010$R storage (buffer_pool keep);
alter table DR#TEST_IDX0009$R storage (buffer_pool keep);
alter table DR#TEST_IDX0010$R storage (buffer_pool keep);
alter table DR#TEST_IDX0009$R storage (buffer_pool keep);
alter table TEST modify partition p10 storage (buffer_pool keep);
alter table TEST modify partition p9 storage (buffer_pool keep);
We'll run these statements, bounce our database, then run our
pre-loading queries again.
It would be handy if we could use the query against V$BH to make sure
that the tables ARE in fact in the keep pool, but this information is not
stored in V$BH - the various buffer caches are considered together.
Using TKPROF to monitor disk reads
Now we're going to run our query again and see what effect it has.
This time, we're going to generate a trace file and examine it too see
the number of physical I/O's. In SQL*Plus:
@?/rdbms/admin/utlxplan.sql -- avoid tkprof plan table error
alter session set sql_trace=true;
-- Use a bind variable to cut down on recursive SQL from optimizer:
variable bnd varchar2(4000)
exec :bnd := 'district and region'
select /* FIRST_ROWS */ * from (
select id, filename from test
where contains (filename, :bnd) > 0
order by id desc )
where rownum <= 20;
Then, in our udump directory (typically $ORACLE_HOME/admin//udump), we
need to find the latest tracefile, and do something like:
tkprof mysid_ora_01234.trc traceout.txt sort=exeela table=testuser.plan_table
This will generate a formatted output file "traceout.txt" which we can examine for evidence
of physical I/O's. To do this we need to look at the summary for each SQL
statement, which might look like this:
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.02 0.03 0 402 0 0
Fetch 3 0.00 0.11 34 65 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.02 0.15 34 467 0 20
The "disk" column is the one we're interested in. In this case, the SQL statement
above this summary has fetched 34 database blocks from disk. The next column, query,
is the number of block fetches, but most of these will have been from cache.
Now if we look at the end of the file, we will see:
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 3 0.03 0.06 0 402 0 1
Fetch 3 0.00 0.11 34 65 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.03 0.17 34 467 0 21
Misses in library cache during parse: 2
Misses in library cache during execute: 2
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 41 0.02 0.01 0 0 0 0
Execute 66 0.08 0.08 0 543 0 7
Fetch 59 0.00 0.02 2 190 0 61
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 166 0.10 0.12 2 733 0 68
This tells us that of our 34 disk block reads, only two are during recursive
statements. Checking back through the tracefile finds the culprit:
select /*+ rule */ bucket, endpoint, col#, epvalue
from
histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 2 6 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.01 2 6 0 11
That's clearly some kind of system SQL, and it's only 2 blocks anyway, so we won't
worry about it (TODO: Are these the same as the two "Misses in library cache"?)
That leaves us with 32 unexplained disk reads from our main SQL
statement.
To figure out where these come from, we need to dig a bit deeper.
We'll bounce the database again, re-preload the cache, but this time
set an event flag before running our query:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';
This will write a large trace file into the udump directory (typically $ORACLE_HOME/admin//udump).
The trace file will list all the "wait" events, which will include the physical reads.
To extract just the reads, we could do:
grep WAIT sid_ora_1234.trc | grep read
which on my system gave me:
WAIT #15: nam='db file sequential read' ela= 53 p1=1 p2=55741 p3=1
WAIT #15: nam='db file sequential read' ela= 29 p1=1 p2=55740 p3=1
WAIT #1: nam='db file sequential read' ela= 35 p1=7 p2=168890 p3=1
WAIT #1: nam='db file scattered read' ela= 63 p1=7 p2=191027 p3=2
WAIT #1: nam='db file scattered read' ela= 53 p1=7 p2=191030 p3=3
WAIT #1: nam='db file scattered read' ela= 68 p1=7 p2=191034 p3=4
WAIT #1: nam='db file scattered read' ela= 50 p1=7 p2=191038 p3=3
WAIT #1: nam='db file sequential read' ela= 7 p1=7 p2=191067 p3=1
WAIT #1: nam='db file sequential read' ela= 6 p1=7 p2=206475 p3=1
WAIT #1: nam='db file sequential read' ela= 23 p1=7 p2=206506 p3=1
WAIT #1: nam='db file scattered read' ela= 59 p1=7 p2=206552 p3=3
WAIT #1: nam='db file scattered read' ela= 104 p1=7 p2=206555 p3=6
WAIT #1: nam='db file sequential read' ela= 16 p1=7 p2=150156 p3=1
WAIT #1: nam='db file sequential read' ela= 8 p1=7 p2=150162 p3=1
WAIT #1: nam='db file sequential read' ela= 8 p1=7 p2=169084 p3=1
WAIT #1: nam='db file sequential read' ela= 9 p1=7 p2=184356 p3=1
WAIT #1: nam='db file sequential read' ela= 9 p1=7 p2=184461 p3=1
WAIT #1: nam='db file sequential read' ela= 23 p1=7 p2=77 p3=1
WAIT #1: nam='db file sequential read' ela= 21 p1=7 p2=5434 p3=1
WAIT #1: nam='db file sequential read' ela= 19 p1=7 p2=5545 p3=1
WAIT #1: nam='db file sequential read' ela= 20 p1=7 p2=5614 p3=1
The meanings of the arguments here are as follows:
- ela: time waited in miliseconds (?)
- p1: file number (used in dba_files)
- p2: block number (used in dba_segments)
- p3: number of blocks read
We're not too concerned with the difference between scattered read
and sequential read - they both represent physical I/O's.
In order to translate these file and block numbers into useful
information, we need to run check DBA_EXTENTS, like this:
column owner format a30
column segment_name format a30
select owner, segment_name
from dba_extents
where &1 = file_id
and &2 between block_id and block_id + blocks - 1
/
If we want to be really clever, we might save this in a file called seg.sql (together with
SET VERIFY OFF and any necessary column formatting) and then do:
cat $ORACLE_HOME/admin/$ORACLE_SID/udump/`ls -tr $ORACLE_HOME/admin/$ORACLE_SID/udump | tail -1` \
| sed -n -e 's/^WAIT.*read.*p1=\(.*\).*p2=\(.*\) .*/\1 \2/p' \
| xargs -l1 sqlplus -s "/ as sysdba" @seg.sql
This extracts all the file numbers and block numbers from the latest trace file in udump,
then feeds them into seg.sql (note this works for me with the bash shell in Linux, it may not work for you!)
The start of the output might look like this:
OWNER SEGMENT_NAME
---------- ------------------------------
SYS C_OBJ#_INTCOL#
OWNER SEGMENT_NAME
---------- ------------------------------
TESTUSER SYS_IL0000056876C00002$$
OWNER SEGMENT_NAME
---------- ------------------------------
TESTUSER SYS_LOB0000056876C00002$$
We'll ignore the SYS segment, and look at the other two. Clearly they are system generated object names
- and at least one of them matches the segment name we saw in our original query against V$BH (the reason we ignored them earlier was precisely so we could go through this diagnostic process).
We know the segment names, but what actual database object do they represent?
select segment_name, segment_type from dba_segments
where segment_name in ('SYS_IL0000056876C00002$$', 'SYS_LOB0000056876C00002$$');
SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IL0000056876C00002$$ LOBINDEX
SYS_LOB0000056876C00002$$ LOBSEGMENT
No we're getting somewhere - they're both LOB related. Now we need to find out what table these
LOBS are associated with. So how about:
select table_name from dba_indexes where owner = 'TESTUSER' and index_name = 'SYS_IL0000056876C00002$$';
TABLE_NAME
------------------------------
DR#TEST_IDX0010$R
select table_name from dba_lobs where owner = 'TESTUSER' and segment_name = 'SYS_LOB0000056876C00002$$';
TABLE_NAME
------------------------------
DR#TEST_IDX0010$R
So - clearly our earlier attempts at preloading the $R table into the keep pool did not work
perfectly. Why would that be?
In order to understand that we need to know a bit more about
LOBs. LOBs can be either In-Line or Out-of-Line, meaning that the
actual LOB is stored either in the same segment as the rest of the
table blocks, or is stored its own segment. By default, lobs are
stored in-line. However, if the LOB size exceeds 4K, it will
automatically be stored out-of-line. You can force all blocks to be
stored out-of-line using the LOB storage clause "DISABLE STORAGE IN
ROW".
In the case of the $I tables, the LOB lengths are restricted by the
Text code to 4K, to make sure they can always be stored in-line to
minimize I/O requirements. Hence there should never be any requirement
for separate LOB segments for the $I tables.
However, in the case of $R tables, things are different. The $R table
consists of a BLOB (or BLOBs) which are basically a list of ROWIDs for
the base table, ordered by DOCID. In a small table with less than
about 220 rows, this BLOB will be less than 4K and will be stored
in-line. However when it exceeds that size, it will me moved to
out-of-line storage in a new segment.
To ensure that we get the $R LOB segments pre-cached in the keep pool,
we need to do two things. First, we need to change our ALTER TABLE
statements for the $R tables:
alter table DR#TEST_IDX0010$R storage (buffer_pool keep) modify lob (data) (storage (buffer_pool keep));
alter table DR#TEST_IDX0009$R storage (buffer_pool keep) modify lob (data) (storage (buffer_pool keep));
(Of course, we could have done this by altering the initial R_STORAGE_CLAUSE attribute
when creating our text index).
Secondly, we need to force the LOB blocks to be loaded, as well as the
main table blocks for the $R. This is a little more complicated. We
might think we could do:
SELECT SUM(ROW_NO), SUM(LENGTH(DATA)) FROM DR#TEST_IDX0010$R; /* WRONG!! */
but that won't, in fact, work. The length of the LOB is held in the header block - it's not necessary
to read the whole LOB to get its length. Instead, we must physically read the LOB - and read it at
enough points along its length to make sure we've pulled in all the LOBs. This requires a PL/SQL
procedure. The following procedure will load ALL the $R tables for a local partitioned index -
it could be simplified for a non-partitioned index, and you MIGHT decide you didn't want to
preload all the partitions. $R tables are relatively small (13MB in total for my system),
so often you may as well load the lot.
set serveroutput on size 1000000
create or replace procedure loadAllDollarR (idx_name varchar2) is
v_idx_name varchar2(30) := upper(idx_name);
type c_type is ref cursor;
c2 c_type;
s varchar2(2000);
b blob;
buff varchar2(100);
siz number;
off number;
cntr number;
begin
for c1 in (select table_name t from user_tables
where table_name like 'DR_'||v_idx_name||'%$R') loop
dbms_output.put_line('loading from table '||c1.t);
s := 'select data from '||c1.t;
open c2 for s;
loop
fetch c2 into b;
exit when c2%notfound;
siz := 10;
off := 1;
cntr := 0;
if dbms_lob.getlength(b) > 0 then
begin
loop
dbms_lob.read(b, siz, off, buff);
cntr := cntr + 1;
off := off + 4096;
end loop;
exception when no_data_found then
if cntr > 0 then
dbms_output.put_line('4K chunks fetched: '||cntr);
end if;
end;
end if;
end loop;
end loop;
end;
/
You would call this procedure from SQL*Plus like this:
exec LoadAllDollarR('test_idx')
So now we have everything we need to preload the first two partitions of our index.
Finishing Up
Let's run back over our our full set of commands for moving tables to the keep pool,
and then loading them into memory. I'm going to do all of this each time I restart
the database (the ALTER TABLES only really need to be run once, but they're quick and
this will catch any situation where the index - or individual tables - have been rebuilt).
-- This script preloads the last two partitions from a 10-way
-- partitioned table and its text index. The table is called TEST,
-- the index is called TEST_IDX. It is owned by user TESTUSER.
connect testuser/testuser
--------------------------------------------------------
-- First make sure all tables go into the keep buffer --
--------------------------------------------------------
alter table DR#TEST_IDX0010$I storage (buffer_pool keep);
alter table DR#TEST_IDX0009$I storage (buffer_pool keep);
alter index DR#TEST_IDX0010$X storage (buffer_pool keep);
alter index DR#TEST_IDX0009$X storage (buffer_pool keep);
alter table DR#TEST_IDX0010$R storage (buffer_pool keep);
alter table DR#TEST_IDX0009$R storage (buffer_pool keep);
alter table DR#TEST_IDX0010$R storage (buffer_pool keep)
modify lob (data) (storage (buffer_pool keep));
alter table DR#TEST_IDX0009$R storage (buffer_pool keep)
modify lob (data) (storage (buffer_pool keep));
alter table TEST storage (buffer_pool keep);
set timing on
--------------------------------------------------------------
-- Then perform the necessary queries to preload the tables --
--------------------------------------------------------------
SELECT /*+ FULL(ITAB) */ SUM(TOKEN_COUNT), SUM(LENGTH(TOKEN_INFO))
FROM DR#TEST_IDX0010$I ITAB;
SELECT /*+ FULL(ITAB) */ SUM(TOKEN_COUNT), SUM(LENGTH(TOKEN_INFO))
FROM DR#TEST_IDX0009$I ITAB;
SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(TOKEN_TEXT))
FROM DR#TEST_IDX0010$I ITAB;
SELECT /*+ INDEX(ITAB) */ SUM(LENGTH(TOKEN_TEXT))
FROM DR#TEST_IDX0009$I ITAB;
SELECT /*+ FULL(BTAB) */ SUM(ID)
FROM TEST WHERE ID >= 900000;
SELECT SUM(ROW_NO) FROM DR#TEST_IDX0010$R;
SELECT SUM(ROW_NO) FROM DR#TEST_IDX0009$R;
exec LoadAllDollarR('test_idx')
If we run this after bouncing the database and before running our test query,
we should find that now there are NO reads
from the $R tables at all. In fact, in my testcase I found the system reads went away as
well, and my query now completed without any physical I/O at all.
Conclusion
Hopefully this paper will have provided you with a better understanding of
the issues around caching tables in memory, together with some diagnostic tools
to help figure out where disk reads are occuring, and why.
The author welcomes feedback and corrections on this paper. Email him at
roger.ford@oracle.com
Last modified: 31 August 2004 by Roger Ford
|
Bookmark
