Learning from a DBA

I have a good friend who was visiting for a conference and we had a chance to catchup on things and he is a total enthusiast for technology and database and was kind enough to go over some tips for tuning DB for development environments.

1) Problem statement, Oracle 10g instance on windows takes by default around 600MB RAM? Reduce to 300MB

Login as:
sqlplus \ as sysdba

SQL>show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 584M
sga_target big integer 584M


SQL>alter system set sga_target = 300M scope=both;

SQL>alter system set sga_max_size = 300M scope=spfile;

Dynamic changes in Oracle are defined by giving the parameter scope=both and changes that need a restart are defined by scope=spfile;

SQL>shutdown immediate
SQL>startup


show parameter sga;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 300M
sga_target big integer 300M


2) Problem statement tuning Dev Oracle Server to work with Application server:

Oracle 10G captures snapshots by default every hour. We can take a look at 3 kinds of reports

Go to command prompt under following directory
C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN

sqlplus \ as sysdba

These are the 3 kinds of reports, the first one is very elaborate and it lists out the top queries and also if there is any resource contention.

SQL>@ashrpt

SQL>@awrrpt

SQL>@addmrpt

From looking at the resource contention he identified that it was writing to 3 control files and that is not required for a development database, so we changed to 1 and the log file needed to be increased.

SQL> alter system set control_files='C:\ORACLE\ORADATA\ORCL10G\CONTROL01.CTL' scope=spfile;

SQL> shutdown immediate

SQL> startup