 |
|
Locating Tables and Indexes for the KEEP Pool
Oracle Tips by Burleson |
The Oracle documentation states, “A good
candidate for a segment to put into the KEEP pool is a segment that is
smaller than 10% of the size of the DEFAULT buffer pool and has
incurred at least 1% of the total I/Os in the system.” In other words,
small, highly accessed tables are good candidates for caching.
So, how do we identify small-table full table
scans? The best method is to explain all of the SQL that is currently
in your library cache and then generate a report showing all of the
full table scans in your database at that time. We will introduce a
very important script called access.sql that will produce these
reports in Chapter 15, but for now, let's just assume that we have
already explained all of the SQL in our library cache.
The access_report Script
This script interrogates the execution plans
from the output of access.sql and produces a report of the frequency
of full table scans. The details for running this script are explained
in Chapter 11.
access_report.sql
--
********************************************************
-- Report section
-- ********************************************************
set echo off;
set feedback on
set pages 999;
column nbr_FTS format 999,999
column num_rows format 999,999,999
column blocks format 999,999
column owner format a14;
column name format a24;
column ch format a1;
column K format a1;
spool access.lst;
set heading off;
set feedback off;
ttitle 'Total SQL found in library cache'
select count(distinct statement_id) from plan_table;
ttitle 'Total SQL that could not be explained'
select count(distinct statement_id) from plan_table where remarks is
not null;
set heading on;
set feedback on;
ttitle 'full table scans and counts| |Note that "?" indicates in
the table is cached.'
select
p.owner,
p.name,
t.num_rows,
ltrim(t.cache) ch,
decode(t.buffer_pool,'KEEP','K','DEFAULT',' ') K,
s.blocks blocks,
sum(s.executions) nbr_FTS
from
dba_tables t,
dba_segments s,
sqltemp s,
(select distinct
statement_id stid,
object_owner owner,
object_name name
from
plan_table
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
s.addr||':'||TO_CHAR(s.hashval) = p.stid
and
t.owner = s.owner
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
having
sum(s.executions) > 9
group by
p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
sum(s.executions) desc;
The first section of the access_report.sql
script interrogates the plan table and produces a report showing all
full table scans and their frequency. We also have two columns showing
if a table is marked as cached (Oracle7) or assigned to the KEEP pool
(Oracle8).
This report shows the following columns:
-
OWNER The
owner of the table.
-
NAME This
is the table name.
-
NUM_ROWS The
number of rows in the table as of the last compute statistics.
-
C (Oracle7
only) This is an Oracle7 column and will display Y if the
table is cached, and N if it is not cached.
-
K (Oracle8
only) This is an Oracle8 column that will display a “K” if
the table exists in the KEEP pool.
-
BLOCKS This
is the number of blocks in the table as defined by the
dba_segments view.
-
NBR_FTS This
is the number of full table scans against this table from the data
currently in the library cache.
This should give us all of the information we
need to identify tables for the KEEP pool. Any small tables (for
example, less than 2% of db_cache_size) that have a high number
of full table scans will benefit from being added to the KEEP pool. In
the following report, we see output from an Oracle Applications
database, and we see full table scans on both large and small tables.
Mon Jan
22
page 1
Total SQL found in library cache
4600
Mon Jan
22
page 1
Total SQL that could not be explained
786
Mon Jan
22
page 1
full table scans and counts
OWNER NAME NUM_ROWS C K BLOCKS
NBR_FTS
---------- ------------------------ ------------ - - --------
--------
APPLSYS FND_CONC_RELEASE_DISJS 39 N K 2
98,864
APPLSYS FND_CONC_RELEASE_PERIODS 39 N K 2
98,864
APPLSYS FND_CONC_RELEASE_STATES 1 N K 2
98,864
SYS DUAL N K 2
63,466
APPLSYS FND_CONC_PP_ACTIONS 7,021 N 1,262
52,036
APPLSYS FND_CONC_REL_CONJ_MEMBER 0 N K 22
50,174
APPLSYS FND_CONC_REL_DISJ_MEMBER 39 N K 2
50,174
APPLSYS FND_FILE_TEMP 0 N 22
48,611
APPLSYS FND_RUN_REQUESTS 99 N 32
48,606
INV MTL_PARAMETERS 6 N K 6
21,478
APPLSYS FND_PRODUCT_GROUPS 1 N 2
12,555
APPLSYS FND_CONCURRENT_QUEUES_TL 13 N K 10
12,257
AP AP_SYSTEM_PARAMETERS_ALL 1 N K 6
4,521
APPLSYS FND_CONCURRENT_QUEUES 13 N K 10
4,078
From examining this report, we identify the following
files for addition to the KEEP pool. We select those tables with less
than 50 blocks that are not already in the KEEP pool (the “K” column).
CAUTION: Remember that identifying tables for the
KEEP pool is an iterative process. These reports only show you the SQL
that happens to reside in your library cache at the time you ran the
report.
OWNER NAME NUM_ROWS C K BLOCKS
NBR_FTS
-------------- ------------------------ ------------ - - --------
--------
PPLSYS FND_FILE_TEMP 10 N 22
48,611
APPLSYS FND_RUN_REQUESTS 99 N 32
48,606
APPLSYS FND_PRODUCT_GROUPS 1 N 2
12,555
Remember, our goal is for the data buffer hit ratio for
the KEEP pool to always be 100 percent. Every time we add a table to
the KEEP pool, we must also add the number of blocks in the table to
the KEEP pool parameter in our Oracle file.
Once you have explained all of the SQL in your
library cache, you will have a plan table with all of the execution
plans and a table named sqltemp with all of the SQL source code
(see Chapter 11 for details on explaining all of your SQL). Once these
tables are populated, you can run a script to generate the KEEP syntax
for you. Let's take a look at this script:
access_keep_syntax.sql
select
'alter table '||p.owner||'.'||p.name||' storage (buffer_pool
keep);'
from
dba_tables t,
dba_segments s,
sqltemp s,
(select distinct
statement_id stid,
object_owner owner,
object_name name
from
plan_table
where
operation = 'TABLE ACCESS'
and
options = 'FULL') p
where
s.addr||':'||TO_CHAR(s.hashval) = p.stid
and
t.table_name = s.segment_name
and
t.table_name = p.name
and
t.owner = p.owner
and
t.buffer_pool <> 'KEEP'
having
s.blocks < 50
group by
p.owner, p.name, t.num_rows, s.blocks
order by
sum(s.executions) desc;
To make it easy, we can simply run this script and let
Oracle generate the KEEP syntax on our behalf:
SQL> @access_keep_syntax
alter table APPLSYS.FND_FILE_TEMP storage (buffer_pool keep);
alter table APPLSYS.FND_RUN_REQUESTS storage (buffer_pool keep);
alter table APPLSYS.FND_PRODUCT_GROUPS storage (buffer_pool keep);
The final step is to increase the buffer_pool_keep
parameter by the total number of blocks in our tables. The prior
value is 1400, and we see that these three tables add 56 blocks to the
prior value. Hence we change our Oracle parameter as follows:
BUFFER_POOL_KEEP=(1456, 3)
We are now ready to bounce the database and then execute
the output from the access_keep_syntax script.
STATSPACK Tables and KEEP Pool Data
If you want to get extremely sophisticated, you
can try to explain the SQL statements that are stored in the
stats$sql_summary table. If you are taking the default level-5
STATSPACK snapshots, the stats$sql_summary table will contain the top
SQL statements that were in your library cache at the time of each
hourly snapshot.
You can simply modify the access.sql
script from Chapter 11 to use the stats$sql_summary table instead of
the v$sqltext.
Advanced KEEP Pool Candidate Identification
In addition to small-table full table scan
tables, the KEEP buffer pool may be a good place to keep data blocks
from segments that are used frequently and occupy a significant amount
of blocks in the data buffer. These are commonly blocks within small
reference tables that are accessed via an index and do not appear in
our full table scan report.
There is only one window into the internals of
the Oracle database buffers: the x$bh internal view. The x$bh internal
view contains a great deal of detailed information about the internal
operations within the data buffer pools. From the x$bh table, we can
count the number of objects in a specific type and the number of
“touches” for that object type. It is even possible to use this view
to create a picture of all data blocks that are in the buffer.
The following query uses the x$bh view to
identify those objects whose blocks average more than five touches and
occupy more than twenty blocks in the cache. This will identify tables
and indexes that are frequently referenced, and therefore become
candidates for inclusion in the KEEP pool.
hot_buffers.sql
--
hot_buffers.sql
-- Written by Donald K. Burleson
-- 1/22/2001
-- *********************************
-- You MUST connect as SYS
connect sys/manager;
set lines 80;
set pages 999;
column avg_touches format 999
column myname heading 'Name' format a30
column mytype heading 'Type' format a10
column buffers format 999,999
SELECT
object_type mytype,
object_name myname,
blocks,
COUNT(1) buffers,
AVG(tch) avg_touches
FROM
sys.x$bh a,
dba_objects b,
dba_segments s
WHERE
a.obj = b.object_id
and
b.object_name = s.segment_name
and
b.owner not in ('SYS','SYSTEM')
GROUP BY
object_name,
object_type,
blocks,
obj
HAVING
AVG(tch) > 5
AND
COUNT(1) > 20;
NOTE: The hot_buffers.sql script will not
run on releases lower than Oracle8i because the tch column is a new
addition in Oracle 8.1.6.
Next is the output from this script. The
hot_buffers.sql script identifies those objects that are active
within the data buffers, both as a function of the number of data
blocks and the number of touches.
Type
Name BLOCKS BUFFERS AVG_TOUCHES
------- ------------------------------ ---------- --------
-----------
TABLE PAGE 104 107
44
TABLE SUBSCRIPTION 192 22
52
INDEX SEQ_KEY_IDX 40 34
47
TABLE SEC_SESSIONS 80 172
70
TABLE SEC_BROWSER_PROPERTIES 80 81
58
TABLE EC_USER_SESSIONS 96 97
77
INDEX SYS_C008245 32 29
270
Once you identify hot objects using this script, you are
faced with the decision of segregating the objects into the KEEP pool.
As a general rule, you should have enough available RAM storage for
the entire table or index. For example, if you wanted to add the page
table to the KEEP pool, you would need to add 104 blocks to the
buffer_pool_keep Oracle parameter.
Because of the transient nature of the data
buffers, the results from this script will be different every time you
execute the script. Consequently, some DBAs will schedule this script
to execute every minute whenever they need to see exactly what is
happening inside the data buffers.
Now that we have covered the internals of the
KEEP pool, let's turn our attention to the identification of
candidates for the RECYCLE pool.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
