Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

Locating Tables and Indexes for the Oracle KEEP Pool

According to Oracle documentation, “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”. More concisely, a small table that is in high demand is a good candidate for KEEP caching.

Internally, it is critical to cache small-table full-table scans because the Oracle data buffer does not increase the touch count when blocks from full-table scans are referenced. Hence, small-table full-table scan blocks will age-out of the data buffers very quickly, causing unnecessary disk I/O.

How can we locate small tables that are subject to full-table scans? The best method is to search the SQL that is currently in the library cache. Oracle can then generate a report that lists all the full-table scans in the database at that time.

The script below examines the execution plans of plan9i.sql and reports on the frequency of full-table scans.

See code depot for full scripts

--**************************************************************

-- Object Access script report

--

-- © 2003 by Donald K. Burleson

--

--   No part of this SQL script may be copied. Sold or distributed

--   without the express consent of Donald K. Burleson

--**************************************************************

 

-- ********************************************************

-- Report section

-- ********************************************************

 

. . .

 

select

. . .

from

   v$sql_plan

where

. . .

order by

   ct desc

;

--spool access.lst;

 

select

   p.owner,

. . .

from

   dba_tables   t,

   dba_segments s,

   v$sqlarea    a,

   (select distinct

. . .

where

. . .

   sum(a.executions) desc;

 

select

. . .

from

   dba_tables t,

   v$sqlarea s,

  (select distinct

. . .

order by

   sum(s.executions) desc;

These reports use the following columns:

  • OWNER - The schema owner for the table.

  • NAME - The table name from dba_tables.

  • NUM_ROWS - The number of rows in the table as of the last compute statistics from dba_tables.

  • C (Oracle7 only) – An Oracle7 column that displays Y if the table is cached, N if it is not cached.

  • K (Oracle8+ only) – Displays “K” if the table is assigned to the KEEP pool.

  • BLOCKS – Number of blocks in the table as defined in dba_segments .

  • NBR_FTS – The number of full-table scans against the table.

This report gives all the information needed to select candidate tables for the KEEP pool. The database will benefit from placing small tables (less than 2 percent of db_cache_size) that are subject to frequent full-table scans in the KEEP pool. The report from an Oracle Applications database below shows full-table scans on both large and small tables (Listing 3.3).

                  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     
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     

Listing 3.3 – Full-table scans and counts for current SQL

Examining this report, we can quickly identify three files that should be moved to the KEEP pool by selecting the tables with less than 50 blocks that have no “K” designation.

 

The above text is an excerpt from "Creating a Self Tuning Oracle Database", by Rampant TechPress.  It is only $9.95 and all scripts in this tips can be immediately downloaded.


Complete Oracle Script Collection Available

BC shares their personal arsenal of Oracle data dictionary scripts in this comprehensive download. With decades of experience using Oracle monitoring scripts and Oracle tuning scripts, we shares our secrets for navigating the Oracle data dictionary.

Packed with more than 680 ready-to-use Oracle scripts, this is the definitive collection for every senior Oracle DBA.

It would take man-years to develop these scripts from scratch, making this download the best value in the Oracle industry.

http://www.rampant-books.com/download_adv_mon_tuning.htm

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.