Home » Developer & Programmer » Designer » Sizing Shared pool - A Question
Sizing Shared pool - A Question [message #90501] Fri, 12 December 2003 02:26 Go to next message
Harry
Messages: 17
Registered: March 2001
Junior Member
Hi all,

I have a question to all. As you know the SHARED_POOL contains the Lirary Cache + Dictionary_Cache.

Now, how do we come to the right sizing of the SHARED_POOL?
I understand that the ideal size of a SHARED_POOL should be such that all the packages/procedures/functions must be able to remain in the library cache and the definition of all the objects must also be able to remain in the SHARED_POOL so that these are cached.

Now take an example of a DB having only only 3 objects :

1. A Function called say, TEST_FUNCTION

CREATE OR REPLACE FUNCTION TEST_FUNCTION (p_codekey NUMBER, p_locale CHAR)
RETURN VARCHAR2
IS
l_retval VARCHAR2 (200);
BEGIN
......
......

END

2. A Table called say, TEST_TABLE

CREATE TABLE TCODE (
CODEKEY NUMBER (10) NOT NULL,
TYPE2TTYPE NUMBER (10) NOT NULL,
CODENAME VARCHAR2 (600) NOT NULL,
CODEDESC VARCHAR2 (4000) NOT NULL,
DEFAULTCODEFLAG CHAR (1) NOT NULL,
ACTIVEFLAG CHAR (1) NOT NULL,
CREATEDDATE DATE DEFAULT SYSDATE NOT NULL,
CREATEDBY2TUSER NUMBER (10) NOT NULL,
UPDATEDDATE DATE DEFAULT SYSDATE NOT NULL,
UPDATEDBY2TUSER NUMBER (10) NOT NULL,
STATUS2TRECORDSTATUS CHAR (1) DEFAULT 'A' NOT NULL)

3. An Index called say, TEST_INDEX

create index TEST_INDEX on TEST_TABLE (CODENAME);

Now, what should be the size of the shared_pool if i have only these objects in my database. This will give an idea as to how to size it.

Regards
Harry.
Re: Sizing Shared pool - A Question [message #90507 is a reply to message #90501] Mon, 22 December 2003 03:09 Go to previous message
Frank Naude
Messages: 4564
Registered: April 1998
Senior Member
Hi,

You can use the DBMS_SHARED_POOL.SIZES() procedure to see how much space an object takes in the shared pool. Look at this example:
SQL> -- First create the required package...
SQL> @?/rdbms/admin/dbmspool.sql
[snip...]

SQL> -- Now, list objects in SHARED_POOL taking more than 50K...
SQL> set serveroutput on size 1000000
SQL> exec dbms_shared_pool.sizes(50);
SIZE(K) KEPT   NAME
------- ------ ---------------------------------------------------------------
501        SYS.STANDARD                  (PACKAGE)
255        SYS.DBMS_REPCAT_UTL           (PACKAGE BODY)
190        SYS.DBMS_REPCAT_UTL           (PACKAGE)
151        SYS.DBMS_SQL                  (PACKAGE)
143        SELECT ...
[snip...]


Best regards.

Frank
Previous Topic: Tuning Question
Next Topic: Normalization & De-Normalization
Goto Forum:
  


Current Time: Wed Jul 15 04:58:22 CDT 2020