Performance Tuning: shared memory

From: Helen <hhjin_at_yahoo.com>
Date: 15 Oct 2001 14:56:32 -0700
Message-ID: <ebcac1e2.0110151356.3fdb226f@posting.google.com>

EE 8.1.5 on Sun Solaris 2.6

After running utlbstat and utlestat, I see most contention coming from shared memory (latch and library cache wait high). SQL area hit ratio in the 60% range. Our application is using a lot of literal SQLs, we know that need to be change. I am wondering what I can do within RDBMS to improve the performance (such as increasing shared_pool).

I also checked v$sql and shared pool, result as of the following, any suggestions?

Helen

1 SELECT substr(sql_text,1,40) "Stmt", count(*),

2 sum(sharable_mem) "Mem", 
3 sum(users_opening) "Open", 
4 sum(executions) "Exec" 

5 FROM v$sql
6 GROUP BY substr(sql_text,1,40)
7* HAVING sum(sharable_mem) >4000000

Stmt                                     COUNT(*) Mem Open Exec 
---------------------------------------- -------- -------- ---- ----- 
INSERT INTO CONTENTITEM_PRODUCTAFFSECTIO 1492     90559184 14  74789 
SELECT DISTINCT TYPE_CLASS FROM CONTENTI 2389     11891107 4  108249 
SELECT SEQUENCE, CONTENTITEM_ID, RELATED 1007      5009981 1   17979 
SELECT t0.LAST_MODIFIED_TIME, t0.BIRTHDA 633      18513634 0     633 
SELECT t0.TYPE_CLASS, t0.CREATE_TIME, t0 2319     25022996 6   92163 
insert into PORTERTEMP values (:1,:2,:3, 642      27221774 0     694 
-------------------------------------------------------------------- 

Obj mem: 88367134 bytes
Shared sql: 81172542 bytes
Cursors: 201375 bytes
Free memory: 33713464 bytes (32.15MB)
Shared pool utilization (total): 203689261 bytes (194.25MB) Shared pool allocation (actual): 140000000 bytes (133.51MB) Percentage Utilized: 145%

---

in shared pool,

sqlplus > set serveroutput on size 4000
sqlplus > exec dbms_shared_pool.sizes(0)

13898 insert into PORTERTEMP values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,: 11,:12,:13,:14,:15,:16,:17,:18,:19,:20,empty_clob(),:21,:22 ,:23,:24,:25,empty_clob(),:26,:27,:28,:29)
(99A83A40,2421300843) (CURSOR)

12084 insert into contentitem (id, contentitemtype_id, contentitemsta te_id, source_id, productaffiliate_id, display_time, expire _time, create_time, slug, title) values (:1, :2, :3, :4, :5 , :6, :7, :8, :9, :10)
(9F9C6F50,873585448) (CURSOR)

4846 insert into shovelware (contentitem_id, publication_name, publi cation_date, section, edition, page_number, zone, run_date, priority, original_slug) values (:1, :2, :3, :4, :5, :6, : 7, :8, :9, :10)
(9FDFBF44,100835854) (CURSOR)
Received on Mon Oct 15 2001 - 16:56:32 CDT