1.Create a copy of the PFILE and save it as an original copy.

SQL> CREATE PFILE='C:\TEMP\INIT.ORA' FROM SPFILE;

2.Configure the SGA according to the following settings in the PFILE:

a. Shared Pool Memeory = 20M
b. Large Pool Memory  = 0M
c. Java Pool Memory  = 20M
d. Buffer Cache  = 4M

There should be no other buffer cache settings or SGA related setting such as SGA_MAX_SIZE.

If so remove all buffer configuration from the PFILE

Modify initialization file to set the following parameters:

      DB_16K_CACHE_SIZE=0
      DB_2K_CACHE_SIZE=0
      #DB_32K_CACHE_SIZE=0   #USED ONLY IF OS SUPPORTS 32K BLOCK SIZE
      DB_4K_CACHE_SIZE=0
      #DB_8K_CACHE_SIZE=0     #CANNOT USE THIS BECAUSE BLOCK SIZE is 8K
      DB_CACHE_SIZE=4M
      DB_KEEP_CACHE_SIZE=0
      DB_RECYCLE_CACHE_SIZE=0
      JAVA_POOL_SIZE=20M
      LARGE_POOL_SIZE=0
      SHARED_POOL_SIZE=20M

3.Shutdown the database and open the database with new settings

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP PFILE=C:\ORACLE\ORA9i\ADMIN\SAM\PFILE\INIT.ORA

4.Verify the settings in step 2 and compute the size of the SGA and buffers

SQL> SHOW PARAMETER SIZE
SQL> SELECT SUM(BYTES)/(1024*1024)
  2    FROM V$SGASTAT;

5.Display the maximum size of the SGA.

SQL> SHOW PARAMETER SGA_MAX_SIZE

6.Determine the SGA size granule

Since total size of SGA is less than 128M then the granule size is 4M

Or

SQL> SELECT * FROM V$SGA_DYNAMIC_COMPONENTS;


7.Adjust the size of the SGA_MAX_SIZE dynamically and explain what happens?

If you issue this statement
SQL> ALTER SYSTEM SET SGA_MAX_SIZE = 120M;

You would get
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
      Because SGA_MAX_SIZE parameter cannot be set dynamically

8.Shutdown the database and set SGA_MAX_SIZE to a value three times the size of the SGA

obtained in step 4 and then open the database.

Modify initialization file and set
      SGA_MAX_SIZE=180M

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP PFILE=C:\ORACLE\ORA9i\ADMIN\SAM\PFILE\INIT.ORA

9.Determine the SGA size granule

The granule size is 16M because total SGA is more than 128M

Or

SQL> SELECT * FROM V$SGA_DYNAMIC_COMPONENTS;

If you get 8M it is ok, this is a known Oracle bug


10.Open another session as TUNER and run the following query:

      SELECT SHIPMENT_METHOD.SHIPMENT_DESCRIPTION, COUNT(*)
        FROM ORDERS, SHIPMENT_METHOD
       WHERE ORDERS.PAYMENT_METHOD_ID = SHIPMENT_METHOD.SHIPMENT_METHOD_ID
         AND ORDERS.CUSTOMER_ID IN (SELECT CUSTOMER_ID
                                   FROM CUSTOMERS WHERE STATE = 'SD')
       GROUP BY SHIPMENT_METHOD.SHIPMENT_DESCRIPTION;

11.List all TUNER objects, type of each object, and the number of buffer blocks used by

each object that is residing in the buffer.

SELECT O.OBJECT_TYPE,
       O.OBJECT_NAME,
       COUNT(*) buffers
  FROM DBA_OBJECTS O,V$BH B
 WHERE O.OBJECT_ID = B.OBJD
   AND O.OWNER = 'TUNER'
GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME
ORDER BY 1, 2 DESC;

12.Explain the results of the query

· The query shows that CUSTOMERS table is consuming the most buffers.
· The query shows that one query used five different objects
· One query has consumed 119 buffers, more activities on other TUNER objects will consume

more memory.

13.From the result above, determine if the whole or a partial of the object is in the

buffer cache.

   SQL> EXEC DBMS_UTILITY.ANALYZE_SCHEMA('TUNER', 'COMPUTE');
   SQL> SELECT O.OBJECT_TYPE,
     2         O.OBJECT_NAME,
     3         T.BLOCKS,
     4         COUNT(*) buffers
     5    FROM DBA_OBJECTS O,V$BH B, DBA_TABLES T
     6   WHERE O.OBJECT_ID = B.OBJD
     7     AND O.OWNER = 'TUNER'
     8     AND O.OBJECT_NAME = T.TABLE_NAME
     9     AND O.OWNER = T.OWNER
    10   GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME, T.BLOCKS
    11   ORDER BY 1, 2 DESC;

14.Turn on the Cache Advice.

SQL> ALTER SYSTEM SET DB_CACHE_ADVICE=ON;


15.View the Cache Advice using a query and using the Oracle Enterprise Manager Console.

Explain the results

SQL> SELECT * FROM V$DB_CACHE_ADVICE;

Data displayed by the query is not helpful because there is no activities since you turned

Cache Advice feature on

16.Take a reading of the buffer cache hit ratio and recorded it here

SQL> SELECT ROUND( (1 - (PHY.VALUE/
  2                (CUR.VALUE + CON.VALUE)))*100, 1)||'%' ratio
  3    FROM V$SYSSTAT PHY, V$SYSSTAT CUR, V$SYSSTAT CON
  4   WHERE PHY.NAME = 'physical reads'
  5     AND CUR.NAME = 'db block gets'
  6     AND CON.NAME = 'consistent gets';

   Reading #1: ____________

17.Download dbSessions tools to simulate multiple sessions, Documentation on how to use the

tool is included with setup for the tool. Here is the URL to download the tool:

       http://dba-cyberspace.com/docs/dbSessions_book.zip

After the tool is installed, use dbSessions to establish 20 sessions and run it with the

parameters listed in Figure 2-9b. (These settings will run around 45 minutes, if you need

more time, you adjust interval value to 10 seconds. It is not recommended to reduce the

time interval value to less than 3 seconds)

i.    Run dbSessions

ii.    Click on Query Maintenance

iii.    Load queries using chapter_02 – dbSessions_queries.qry

iv.    Close QUERY MAINTENANCE Screen

v.    Click on CONNECT button enter following values and press OK

vi.    Run sessions by click on the RUN QUERIES button


18.While dbSessions is running take another reading of the buffer cache hit ratio and

record it here

SQL> SELECT ROUND( (1 - (PHY.VALUE/
  2                (CUR.VALUE + CON.VALUE)))*100, 1)||'%' ratio
  3    FROM V$SYSSTAT PHY, V$SYSSTAT CUR, V$SYSSTAT CON
  4   WHERE PHY.NAME = 'physical reads'
  5     AND CUR.NAME = 'db block gets'
  6     AND CON.NAME = 'consistent gets';

   Reading #2: ____________

19.While dbSessions is running view the Cache Advice statistics, do you see any change in

the data or the graph? Explain if more buffers are added will there be an improvement in

the memory efficiency and database performance?

· You can use OEM or V$DB_CACHE_ADIVCE.
· Yes there is change in the Cache Advice advisory data
· Adding more buffer will definitely help as shown in Figure below

20.Take another reading of the buffer cache hit ratio

SQL> SELECT ROUND( (1 - (PHY.VALUE/
  2                (CUR.VALUE + CON.VALUE)))*100, 1)||'%' ratio
  3    FROM V$SYSSTAT PHY, V$SYSSTAT CUR, V$SYSSTAT CON
  4   WHERE PHY.NAME = 'physical reads'
  5     AND CUR.NAME = 'db block gets'
  6     AND CON.NAME = 'consistent gets';

      Reading #3: ____________

21.When dbSessions has completed, take a final reading of the buffer cache hit ratio and

record it here

SQL> SELECT ROUND( (1 - (PHY.VALUE/
  2                (CUR.VALUE + CON.VALUE)))*100, 1)||'%' ratio
  3    FROM V$SYSSTAT PHY, V$SYSSTAT CUR, V$SYSSTAT CON
  4   WHERE PHY.NAME = 'physical reads'
  5     AND CUR.NAME = 'db block gets'
  6     AND CON.NAME = 'consistent gets';

      Reading #4: ____________

22.Analyze the results from the buffer cache hit ratio readings

You should notice that hit ratio values declined as more activities were generated by

dbSessions. However, the hit ratio showed that it was consistently above 90 percent

indicating that buffer cache is properly configured.

23.Increase the buffer cache size by one granule and verify the new size.

      SQL> ALTER SYSTEM SET DB_CACHE_SIZE = 20M;

24.Check the Cache Advice value and explain what happened

SQL> SELECT * FROM V$DB_CACHE_ADVICE;

The output of this query shows that when the DB_CACHE_SIZE parameter was changed the Cache

Advice advisory data was flushed and reset.

25.Determine the object that has the average number of blocks and cache it in the KEEP

buffer.

SQL> SELECT O.OBJECT_TYPE,
  2         O.OBJECT_NAME,
  3         COUNT(*) buffers
  4    FROM DBA_OBJECTS O,V$BH B
  5   WHERE O.OBJECT_ID = B.OBJD
  6     AND O.OWNER = 'TUNER'
  7   GROUP BY O.OBJECT_TYPE, O.OBJECT_NAME
  8  HAVING COUNT(*) > (SELECT AVG(COUNT(*))
  9                      FROM  DBA_OBJECTS O1,V$BH B1
10                      WHERE O1.OBJECT_ID = B1.OBJD
11                        AND O1.OWNER = 'TUNER'
12                      GROUP BY B1.OBJD);

SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 4M;
SQL> ALTER TABLE TUNER.CUSTOMERS STORAGE(BUFFER_POOL KEEP);
SQL> ALTER TABLE TUNER.CUSTOMERS CACHE;

26.Verify how many KEEP buffer blocks are consumed by step 25.

SQL> SELECT SUM(BUFFERS) "KEEP BUFFERS"
  2    FROM V$BUFFER_POOL
  3   WHERE NAME = 'KEEP';



Oracle9i Performance Tuning: Optimizing Database Productivity