OraFAQ Forum: Performance Tuning » Problem with insert statements sharing in shared pool

Home » RDBMS Server » Performance Tuning » Problem with insert statements sharing in shared pool

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with insert statements sharing in shared pool [message #162484]

Fri, 10 March 2006 08:38

sach_sumit
Messages: 9
Registered: September 2005

Junior Member

HI

I have some confusion need some clarification

In OEM > performance manager > sql >sql hard parse analysis > similar sql statements >
DOubts r :

1. in it I have a insert statement like :

INSERT INTO REPLICATE_PRODUCT_TRAN (
DOC_NO,DOC_REF_NO,DIV_CODE,PROD_CODE,PROD_SLNO,ACTUAL_SLNO,DOC_SLNO,MOVE_TYPE,DATE_TIME,ISSUE_WH_CODE,RECEIVE_WH_CODE,RATE,STATUS,DOC _TYPE,BLK_DOC_NO,CONFIRM_STATUS,SOURCE_STATE ) VALUES ( :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b16,:b17)

and count is 2038 for the same.
Does it means total inserts fired is 2038 from morning, if instance started today?

2. The help for it tells the statments that can be shared and can be re written as have same sql code.

How can it be done in the case of this insert statement as it is same alerady when it fired using bind variables?

3. When I see the details for that statement it shows 7 sql's(i.e insert statement given above) similar with same hash_value.

If they r same then why r they not getting shared?

Also all sql's fired r from our applcations only.

how can I now rewrite the insert to reduce the count?

######################
these r the related stats

SQL> select count(1) from v$sql where sql_text like 'INSERT INTO REPLICATE_PRODUCT_TRAN%';

COUNT(1)
----------
2038

SQL> select count(1) from v$sqlarea where sql_text like 'INSERT INTO REPLICATE_PRODUCT_TRAN%';

COUNT(1)
--------
1

SQL> select * from v$sqlarea where sql_text like 'INSERT INTO REPLICATE_PRODUCT_TRAN%';
(original and truncated output)

VERSION_COUNT 2038
LOADED_VERSIONS 2038
OPEN_VERSIONS 0
USERS_OPENING 0
EXECUTIONS 3468
USERS_EXECUTING 0
LOADS 2038
FIRST_LOAD_TIME 2006-03-10/09:55:57
INVALIDATIONS 0
PARSE_CALLS 2038
COMMAND_TYPE 2
OPTIMIZER_MODE MULTIPLE CHILDREN PRESENT PARSING_USER_ID 103
PARSING_SCHEMA_ID 103
KEPT_VERSIONS 0
ADDRESS 7EDA69D8
HASH_VALUE 421927903

Regards
Sumit Sachdeva

Report message to a moderator

Previous Topic:	MERGE INTO with a hint
Next Topic:	Oracle 8 rebuild index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 15:00:00 CST 2024