One of the core initiatives undertaken by Oracle Database
10G, is to simplify Oracle Database Administration. As part of that effort,
many of the repetitive and manual tasks have been automated. In this section,
you will proactively manage and automate some of the mundane tasks related to
Oracle Instance Memory Configuration. By automating memory configuration, you
have more time to deal with real application or business issues that affect
your enterprise.
The Memory Advisor is an intelligent expert system within
the Oracle database that proactively determines optimal settings for various
SGA and PGA components. When automated, Oracle will automatically adjust the
settings for the various pools and caches according to the requirements of the
workload.
To change the total SGA size when in automated shared memory
management mode, you will need to make sure the maximum SGA Size is large enough.
Perform the following:
1.
Scroll down the page. Change Maximum SGA Size to 261MB
and click Apply. Note: If you receive an error, click the Refresh
button and try it again.
2.
Click Yes to confirm the change.
3.
Supply the host credentials and the database credentials
then click OK.
4.
Changing the Max SGA Size parameter requires a restart
of the database. Click Yes to confirm restart of the database.
5.
The database restart process will start. Click Refresh.
6.
Enter sys/<password>as SYSDBA and
click Login.
7.
Scroll down and click Advisor Central.
8.
Click Memory Advisor.
9.
Change the Total SGA Size parameter
to 260MB. Click Apply.
Note that the Max SGA Size parameter was automatically
adjusted to conform to the memory granule size Though you had set it to
be 261MB originally, the Oracle server automatically changed it to 264MB.
10.
Once you receive the confirmation notice that the parameter
was changed successfully, you'll also notice that memory allocation to
some of the SGA components was adjusted automatically.
To allocated memory associated with the PGA, perform the following:
1.
Click the PGA tab.
2.
Click Advice....
3.
The PGA Aggregate Target Advice graph shows the frequency
in which data is found in cache so that you do not have to access disk.
In this case,it should be noted that the current PGA Aggregate
Size is set to approximately 24MB, and over 88% of all the requested services
are gotten from memory. This also shows the overflow range which starts
around 12MB. At 12MB, the PGA requests hit the cache around 90%. The PGA
Aggregate Size implies that (based on current workloads and the number
of sessions in the database), no more than 24MB should be allocated for
all PGAs in this database.Click the OK button.
4.
Click PGA Memory Usage Details.
5.
This graph shows the usage details in memory size requests
and executions percentages for various PGA memory requests.Click
OK.
Place
the cursor on this icon to hide all screenshots.
Bookmark
