| Sizing Shared pool - A Question [message #90501] |
Fri, 12 December 2003 02:26  |
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  |
Frank Naude
Messages: 4581
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
