Re: Performance Tuning: shared memory

Your SQL area Hit ratio is really too low.If you 've consistently large amount of free memory available in v$sgstat then increasing Shared_Poll size would'nt do any good probabaly.IMHO single most helpful factor will be use of bind variable OR u can try Cursor_Sharing =Force init.ora parameter which forces Optimizer to use Shared SQl but this must be tested on Dev throughly as some clients reported getting ORA-600 error messages.

Anwer

Helen wrote in message ...
>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 Wed Oct 17 2001 - 17:10:05 CDT