1. After re-examining the performance problem set out at the start of the chapter, answer the following questions:

a. Define Joe Doe’s problem in your own words.

i. Joe has a big table that he needs to cache in memory because it is frequently accessed. He states that he gets a good buffer hit ratio after he starts the instance and select form the big table and then the hit ratio drops to an unacceptable value.
ii. Joe also inquires what is considered a big table.
iii. Joe has to bounce the database every time he needs to do this

b. What does he mean by saying the BUFFER_POOL KEEP?

CREATE TABLE and ALTER TABLE statements have BUFFER_POOL option to specify what buffer pool the table should be cached. The values for BUFFER_POOL options are DEFAULT, KEEP and RECYCLE. KEEP has the highest priority to stay cached in memory.

c.Do you think bouncing the database is acceptable to solve this problem?

Absolutely not acceptable especially when it is frequent even if the business operation hours or the type of application allows it. It is healthier to keep the database open for better statistical readings, since shutting down the database is shutdown flushes all performance statistics.
It is also unacceptable to bounce the database because of this problem even it is infrequent. Bounce the database is really not solving the It appears to Joe after he starts the database and loads the data that hit ratio is 100% because the database has not reached stability yet and therefore this hit ratio is misleading

d.What would you recommend as a solution?

500MB table is not considered small especially when it is almost 40% of the size of the buffer cache Joe allocated. Increasing the buffer cache (if memory is available) can help keep the table cached a little longer but long enough to meet Joe’s requirements.
If memory is available, then Joe should allocate memory for the KEEP buffer pool just before loading the table and deallocate memory taken by the KEEP buffer cache when done.  However, Joe stated that the table is growing in size therefore there will come a time when no more memory is available and Joe will face the same problem again. Therefore this solution solves the problem temporarily. For a long term solution Joe and the development should look into the design of the application.

2.Write a query that displays a list of all dynamic performance views and initialization parameters that are related to the buffer cache. This list should resemble the two lists presented in the Chapter Summary.

COLUMN "Object/Parameter Type" FORMAT A25
BREAK ON "Object/Parameter Type"
SELECT 'Parameters' "Object/Parameter Type", NAME "Name"
  FROM V$PARAMETER
 WHERE NAME LIKE '%cache%'
    OR NAME LIKE '%block%'
 UNION ALL
SELECT 'Views',  VIEW_NAME
  FROM V$FIXED_VIEW_DEFINITION
WHERE  VIEW_NAME LIKE 'V$%BUFFER%'
  OR   VIEW_NAME LIKE 'V$%CACHE%'
  OR   VIEW_NAME LIKE 'V$%COMPONENT%'
  OR   VIEW_NAME = 'V$BH';

Object/Parameter Type     Name
------------------------- ------------------------------
Parameters                db_block_buffers
                          db_block_checksum
                          db_block_size
                          db_keep_cache_size
                          db_recycle_cache_size
                          db_2k_cache_size
                          db_4k_cache_size
                          db_8k_cache_size
                          db_16k_cache_size
                          db_32k_cache_size
                          db_cache_size
                          db_cache_advice
                          db_file_multiblock_read_count
                          db_block_checking
                          session_cached_cursors
                          object_cache_optimal_size
                          object_cache_max_size_percent
Views                     V$BH
                          V$FILE_CACHE_TRANSFER
                          V$TEMP_CACHE_TRANSFER
                          V$CLASS_CACHE_TRANSFER
                          V$ROWCACHE
                          V$ROWCACHE_PARENT
                          V$ROWCACHE_SUBORDINATE
                          V$SGA_DYNAMIC_COMPONENTS
                          V$LIBRARY_CACHE_MEMORY
                          V$LIBRARYCACHE
                          V$SUBCACHE
                          V$DB_OBJECT_CACHE
                          V$BUFFER_POOL
                          V$BUFFER_POOL_STATISTICS
                          V$SESSION_CURSOR_CACHE
                          V$SYSTEM_CURSOR_CACHE
                          V$SESSION_OBJECT_CACHE
                          V$DB_CACHE_ADVICE

Notice that this query will display more parameters and views with similar names but only the highlighted names are related to buffer cache.

3.Write a report that lists each object in the buffer cache and its owner.

COLUMN OBJECT_NAME FORMAT A30
SET LINES 120
SELECT O.OWNER,
       O.OBJECT_TYPE,
       O.OBJECT_NAME,
       COUNT(*) buffers,
       ROUND((COUNT(*)/(SELECT COUNT(*) FROM V$BH)) *100) BUFFER_PERCENT
  FROM DBA_OBJECTS O,
       V$BH B
 WHERE O.OBJECT_ID = B.OBJD
   AND O.OWNER NOT IN ('SYS','SYSTEM')
GROUP BY O.OWNER, O.OBJECT_TYPE, O.OBJECT_NAME
ORDER BY 1, 2 DESC;

4.Write two queries that display the following:

a.The number of buffers and size for each type of the Keep buffers

SELECT NAME, BUFFERS, BLOCK_SIZE
  FROM V$BUFFER_POOL
/

b.The hit ratio for each buffer pool

SELECT NAME BUFFER_NAME,
       (1-(PHYSICAL_READS)/
       ((DB_BLOCK_GETS+CONSISTENT_GETS)*100)) BUFFER_HIT_RATIO
  FROM V$BUFFER_POOL_STATISTICS
WHERE DB_BLOCK_GETS+CONSISTENT_GETS <> 0
      /

5.Suppose you were asked by a colleague to assist in writing a query that shows buffer cache hit ratio for a session. Write down the query that you would issue.

SELECT PHY.SID,
       S.USERNAME,
       1 - (PHY.VALUE)/(CUR.VALUE + CON.VALUE) BUFFER_HITRATIO
  FROM V$SESSTAT PHY, V$SESSTAT CUR, V$SESSTAT CON,
       V$STATNAME S1, V$STATNAME S2, V$STATNAME S3,
       V$SESSION S
WHERE S1.NAME = 'physical reads'
   AND S2.NAME = 'db block gets'
   AND S3.NAME = 'consistent gets'
      AND PHY.STATISTIC# = S1.STATISTIC#
   AND CUR.STATISTIC# = S2.STATISTIC#
   AND CON.STATISTIC# = S3.STATISTIC#
   AND CUR.VALUE <> 0
   AND CON.VALUE <> 0
   AND PHY.SID = CUR.SID
   AND PHY.SID = CON.SID
   AND PHY.SID = S.SID
   AND S.USERNAME = UPPER('&USERNAME');

6.Your manager just read an article about looking inside the buffers. He calls you over to his office and asks you to write a daily report and to collect statistics on what data objects are in memory and how many buffer blocks they are consuming. Write down the query you would need to issue to generate this report as well as the approach that you would take to store this statistical data.

Query to generate daily report:

COLUMN OBJECT_NAME FORMAT A30
SET LINES 120
SET PAGES 60
SELECT O.OWNER,
       O.OBJECT_TYPE,
       O.OBJECT_NAME,
       COUNT(*) buffers
  FROM DBA_OBJECTS O,
       V$BH B
 WHERE O.OBJECT_ID = B.OBJD
   AND O.OWNER NOT IN ('SYS','SYSTEM')
GROUP BY O.OWNER, O.OBJECT_TYPE, O.OBJECT_NAME
ORDER BY 1, 2 DESC;

Approach to store statistical data: You must run these steps as SYS user or a user that will have direct SELECT privilege on the underlying tables of V$BH view.

i.Create a table with the following structureCREATE TABLE CACHED_OBJECTS
(
   RUN_ID        NUMBER,
   RUN_DTTM      DATE,
   OWNER         VARCHAR2(30),
   OBJECT_TYPE   VARCHAR2(30),
   OBJECT_NAME   VARCHAR2(30),
   BUFFERS       NUMBER,
   TOTAL_BUFFERS NUMBER
) 
/

ii.Create a sequence to use for the primary key

CREATE SEQUENCE SQ_CACHED_OBJECTS;

iii.Create a stored procedure to collect the statistics

            CREATE OR REPLACE PROCEDURE COLLECT_CACHED_OBJECTS IS
              V_RUN_ID        NUMBER;
              V_RUN_DTTM      DATE := SYSDATE;
              V_BUFFERS_TOTAL NUMBER;

           BEGIN
              SELECT SQ_CACHED_OBJECTS.NEXTVAL
                INTO V_RUN_ID
                FROM DUAL;
              SELECT COUNT(*)
                INTO V_BUFFERS_TOTAL
                FROM V$BH;

              INSERT INTO CACHED_OBJECTS
                 (
                  RUN_ID,
                  RUN_DTTM,
                  OWNER,
                  OBJECT_TYPE,
                  OBJECT_NAME,
                  TOTAL_BUFFERS,
                  BUFFERS
                 )
              SELECT V_RUN_ID,
                 V_RUN_DTTM,
                 O.OWNER,
                 O.OBJECT_TYPE,
                 O.OBJECT_NAME,
                 V_BUFFERS_TOTAL,
                 COUNT(*)
            FROM DBA_OBJECTS O, V$BH B
           WHERE O.OBJECT_ID = B.OBJD
        

             AND O.OWNER NOT IN ('SYS','SYSTEM')
           GROUP BY V_RUN_ID, V_RUN_DTTM, O.OWNER, O.OBJECT_TYPE,
                    O.OBJECT_NAME, V_BUFFERS_TOTAL;

           DBMS_OUTPUT.PUT_LINE('NUMBER OF ROWS INSERTED = ' ||
                               SQL%ROWCOUNT);
           COMMIT;

        EXCEPTION
           WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE(SQLERRM);
        END;
        /

iv.Execute the stored procedure to collect statistics

SET SERVEROUTPUT ON
      EXECUTE COLLECT_CACHED_OBJECTS

7.After you did so well in collecting statistical data in Question 5, your manager asked you to draw observations from the data. Describe how would you analyze the data and whether it is beneficial or not to keep running this process. Explain your answer in details including analysis and queries.

i.You should be looking for any object that is consuming more than 20% of the buffers:

SELECT OBJECT_NAME, OWNER,
       (BUFFERS/TOTAL_BUFFERS)*100 BUFFER_USAGE_PCT
  FROM CACHED_OBJECTS
ORDER BY 3;

ii.Look for object usage trend, observer if the same object is cached when a reading is collected. This will indicate the object is used and accessed more frequent than others:

SELECT OBJECT_NAME, COUNT(*) FREQUENCY
  FROM CACHED_OBJECTS
GROUP BY OBJECT_NAME;

iii.You can determine number object types are cached and how many:

SELECT OBJECT_TYPE, COUNT(*)
  FROM CACHED_OBJECTS
GROUP BY OBJECT_TYPE;

iv.At any day or time that you suspect or experience performance problems, you could find out what objects are in cache and accessed.

v.At any time you could find out how buffer usage percentage

SELECT DISTINCT A.RUN_ID,
       (TOTAL_USAGE/TOTAL_BUFFERS)*100 USAGE_PCT
  FROM CACHED_OBJECTS A,
       (SELECT RUN_ID, SUM(BUFFERS) TOTAL_USAGE
          FROM CACHED_OBJECTS
         GROUP BY RUN_ID
       ) B
WHERE A.RUN_ID = B.RUN_ID;

You should keep collecting these statistical data, but make sure you purge CACHED_OBJECTS table every two weeks or one month depending on its growth. You could also create a summary table and aggregate statistics before purging them. The table would have the following structure:

CREATE TABLE CACHED_OBJECTS_SUMMARY
(
  RUN_DTTM      DATE,
  BUFFERS_USED  NUMBER,
  TOTAL_BUFFERS NUMBER
);

Before purging the table you would execute the following:

INSERT INTO CACHED_OBJECTS_SUMMARY
   SELECT DISTINCT A.RUN_DTTM, TOTAL_USAGE, A.TOTAL_BUFFERS
     FROM CACHED_OBJECTS A,
          (SELECT RUN_ID, SUM(BUFFERS) TOTAL_USAGE
             FROM CACHED_OBJECTS
            GROUP BY RUN_ID
          ) B
WHERE A.RUN_ID = B.RUN_ID;

To purge CACHED_OBJECTS table:

   TRUNCATE TABLE CACHED_OBJECTS;

8. Interpret the following output from running a query against $DB_CACHE_ADVICE. What would the graph look like if you plot the values of ESTD_PHYSICAL_READS against CACHE_SIZE?

       CACHE_SIZE PERCENT    BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
--------- ---------- ---------- -------------------- ------------------------- -------------------
                   4 33%                         500                    5.7617              908838
                   8 66%                        1000                    1.4044              221529
==>               12 100%                       1500                         1              157739
                  16 133%                       2000                     .8984              141709
                  20 166%                       2500                     .7019              110723
                  24 200%                       3000                     .5285               83373
                  28 233%                       3500                     .3929               61972
                  32 266%                       4000                     .3614               57014
                  36 300%                       4500                     .3143               49577
                  40 333%                       5000                     .3117               49164
                  44 366%                       5500                     .3106               48999
                  48 400%                       6000                     .3101               48916
                 52 433%                       6500                      .308               48586
                  56 466%                       7000                      .308               48586
                  60 500%                       7500                      .308               48586
                  64 533%                       8000                      .308               48586
                  68 566%                       8500                      .308               48586
                  72 600%                       9000                      .308               48586
                  76 633%                       9500                     .3075               48503
                  80 666%                      10000                     .3075              48503

 Open HandsOn_08.XLS using MS EXCEL, you will see a chart showing a plot of the ESTD_PHYSICAL_READS. As it is expected increasing cache size beyond 44M will not decrease number of physical reads.

9. You just took over a database that was administered by another DBA. As part of your routine tasks, you ran a query to get the buffer cache hit ratio and found that it was 45%. Answer the following:

a. What is the query you submitted to get the hit ratio?

COLUMN RATIO HEADING "Buffer Cache Hitratio" FORMAT A30
SELECT ROUND( (1 - (PHY.VALUE/
              (CUR.VALUE + CON.VALUE)))*100, 1)||'%' ratio
  FROM V$SYSSTAT PHY, V$SYSSTAT CUR, V$SYSSTAT CON
WHERE PHY.NAME = 'physical reads'
   AND CUR.NAME = 'db block gets'
   AND CON.NAME = 'consistent gets';

b. What other query would you issue to investigate the problem?
You should issue the following query to find out the hit ratio per session in order to determine the sessions that is impacting performance

SELECT PHY.SID,
       S.USERNAME,
       1 - (PHY.VALUE)/(CUR.VALUE + CON.VALUE) BUFFER_HITRATIO
  FROM V$SESSTAT PHY, V$SESSTAT CUR, V$SESSTAT CON,
       V$STATNAME S1, V$STATNAME S2, V$STATNAME S3,
       v$SESSION S
WHERE S1.NAME = 'physical reads'
   AND S2.NAME = 'db block gets'
   AND S3.NAME = 'consistent gets'
   AND PHY.STATISTIC# = S1.STATISTIC#
   AND CUR.STATISTIC# = S2.STATISTIC#
   AND CON.STATISTIC# = S3.STATISTIC#
   AND CUR.VALUE <> 0
   AND CON.VALUE <> 0
   AND PHY.SID = CUR.SID
   AND PHY.SID = CON.SID
   AND PHY.SID = S.SID;

c. What other analysis or steps would you take to decide on a corrective action?
You should monitor the ratio over a period of time preferably 3 to 5 days during low and peak time.

a.If the trend shows low buffer cache hit ratios, you should look what objects are being accessed the most and the number of buffers consumed by them. A consistent low hit ratio is an indication that the buffer cache is not adequately sized meaning too small.

b.If hit ratio is not consistent, then you should determine what activity was happening that made it high.

4. If corrective action is necessary, what would you do after your investigation and analysis, and when?

Here are the steps you would take for each analysis in step C:

a. Increase the buffer cache by 20% of current size if memory is available immediately.
ALTER SYSTEM SET DB_CACHE_SIZE= N (size in MB)

b. If the hit ratio is not consistent then you should inquire application activities that were running that caused the ratio to go low.

10. You were hired as a DBA consultant to assist the DBA team in administering all databases in production. While you were becoming familiar with the database, you came across a table that is too large to be placed in the KEEP buffer pool. Answer the following:

a. What is your observation about the finding?
Find out how often it is being accessed and the percentage of usage of the total buffers
b. What would you do about it?
If the table is accessed frequently then you should make sure you have enough cache in the KEEP buffer pool
c. Suppose that another DBA asked you to fix the situation so that the table could be placed in the KEEP buffer pool? Write down the steps you would take to perform the task.

i.    ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = N (where N the size in MB)
ii.    ALTER TABLE table_name STORAGE(BUFFER_POOL KEEP);
iii.    ALTER TABLE table_name CACHE;

d.Suppose that you decided to unkeep the table and cache it only in the default buffers. Write down your steps to perform this task

i.    ALTER TABLE table_name STORAGE(BUFFER_POOL DEFAULT);
ii.    ALTER TABLE table_name NOCACHE;

11. You are asked to recommend a database block size for a new, large table that will be used mainly for reporting purposes. You have decided that it would be best if you place it on a tablespace with 16K block size or higher. After checking with the system administrator, you determined that the operating system platform limitation for the maximum block size is 16K.  Write the steps for creating the tablespace for the table to reside on and allocating memory for this task. Use the following table definition

Name                                                      Null?           Type
----------------------------------------- ------------ --------
INVOICE_ITEM_ID                           NOT NULL  NUMBER
INVOICE_ID                                                            NUMBER
AMOUNT                                           NOT NULL   NUMBER(12,2)
DESCRIPTION                                   NOT NULL  VARCHAR2(255)
    
i) CREATE TABLESPACE REPORTING_16K
               DATAFILE
              'C:\ORACLE\ORADATA\SAM\REPORTING_DATA_16K.ORA' SIZE 100M 
              BLOCKSIZE 16384 
              EXTENT MANAGEMENT LOCAL;
(make sure you replace path of the data file to proper path for your database)

    
ii) CREATE TABLE REPORTING
             (
             INVOICE_ITEM_ID NUMBER NOT NULL,
             INVOICE_ID      NUMBER,
             AMOUNT          NUMBER(12,2) NOT NULL,
             DESCRIPTION     VARCHAR2(255) NOT NULL
            ) TABLESPACE REPORTING;

iii) ALTER SYSTEM SET DB_16K_CACHE_SIZE = n (where n is size in MB)

iv) Save new parameters setting to PFILE and SPFILE using
    CREATE PFILE and CREATE SPFILE commands

12. A developer for an OLTP database application that went to production about six months ago told you that there is a reference table that is constantly queried and that they would like to cache it in memory. Write down the steps for each of the following items:

a. Determine the number buffer block that the table would take.
    
   SET VERIFY OFF
   SELECT O.OBJECT_NAME,
     COUNT(*) buffers
     FROM DBA_OBJECTS O, V$BH B
    WHERE O.OBJECT_ID = B.OBJD
      AND O.OWNER = UPPER('&TABLE_OWNER')
      AND O.OBJECT_NAME = UPPER('&TABLE_NAME')
      AND O.OBJECT_TYPE = 'TABLE'
    GROUP BY O.OBJECT_NAME;

b· Allocate memory for the Keep buffers by adding enough buffers to fit the new table: Set the Keep buffers if they not configured or add buffers if it is already set but are too low

   ALTER SYSTEM SET db_keep_cache_size=xxxxk;

c. Cache the table in the Keep buffers

I.    ALTER TABLE table_name STORAGE(BUFFER_POOL KEEP);
II.    ALTER TABLE table_name CACHE;

In Oracle9i has automatic caching, if the table you need to cache meet the small table criteria then you would not need to cache it. However, placing the table in KEEP will increase it’s chance to be kept longer


13.    Use the following script to create the table and populate it with data
 
      SQL> CREATE TABLE KEEP_DATA_REF
        2  (
        3    CODE       NUMBER(4),
        4    DECRIPTION VARCHAR2(80)
        5* )
      SQL> /

      SQL> BEGIN
        2     FOR I IN 1..1000 LOOP
        3        INSERT INTO KEEP_DATA_REF VALUES
        4           (I, 'THIS IS DESCRIPTION TEXT ' || I);
        5     END LOOP;
        6  END;
        7  /

Perform the following steps to answer the questions below:
a. Turn on the Cache Advice on. If it was already on, turn it off and then turn on.
·    To turn it off:
ALTER SYSTEM SET DB_CACHE_ADVICE=OFF;

·    To turn it on:
ALTER SYSTEM SET DB_CACHE_ADVICE=ON;

b. Look at the collected statistics.
In  order to look at the statistics you need to issue the following query:
SELECT * FROM V$DB_CACHE_ADVICE;
If you were to issue this query right after you turn on the Cache Advice feature, there will be no useful statistics. You should generate SQL statements and activities in order to get useful advisory data. In order to generate activities, you could create three different sessions in and issues statements like this:

·    SELECT *
     FROM KEEP_DATA_REF;

·    SELECT CODE
        FROM KEEP_DATA_REF;

·    SELECT *
     FROM KEEP_DATA_REF
                       WHERE CODE > 500;

·    UPDATE KEEP_DATA_REF SET
     DESCRIPTION = DESCRIPTION || ‘z’;

·    UPDATE KEEP_DATA_REF SET
DESCRIPTION = TRIM(DESCRIPTION);
·    COMMIT;


c. Having done step (a) and (b), what do you observe when you do step (c) and (d)

d. Increase or reduce the Cache Buffer size

To increase or decrease the cache size you would issue:
ALTER SYSTEM SET  DB_CACHE_SIZE = n (where n is size MB)

e. Check the DB_CACHE_ADVICE parameter value

After running activities on the database if you query V$DB_CACHE_SIZE you would more useful advisory data that you can use to draw some conclusion

What conclusion would do you draw from all the steps you performed in this project?
You should conclude that Cache Advice feature provides better results when many activities is happening on the database.