Hi All,
my database version is 10.2.0.4.
from v$sqlarea, I see data below,
so my question would be, is there a parsing overhead exists in my system?
we are using a SAP Data Service Designer doing the extraction and loading operation from SQL SERVER, SAPBW database to oracle, and all these sqls you see are from this tool.
and I am suspecting that Data Service Designer is causing parsing overhead, since I see many similar statements like INSERT, using literals, which makes cursor sharing impossible. And I can`t force the cursor sharing at system wide either. Also, it is taking up a lot of memory.
So, I wonder if anyone of you could tell me if it is a problem, or simply just speak out your minds.
Thanks in advance.
SELECT substr(sql_text,1,90) "SQL",count(*) "SQL Copies",
sum(executions) "TotExecs", sum(sharable_mem) "TotMemory", sum(persistent_mem) "persMemory"
FROM v$sqlarea
GROUP BY SUBSTR(SQL_TEXT,1,90) ORDER BY 2 desc;
substr(sql_text, 1, 90) SQL Copies TotExecs TotalMem PersMem
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO "AL_COLMAP_TEXT" ("DF_NAME", "TRG_TAB_NAME", "TRG_TAB_DESC", "TRG_OWNER", "TRG 11016 106807 198250644 69444864
INSERT INTO "AL_PARENT_CHILD" ("PARENT_OBJ", "PARENT_OBJ_TYPE", "PARENT_OBJ_R_TYPE", "PARE 3304 6265 55356684 21885696
select AL_LANG.OBJECT_KEY, AL_LANG.OBJECT_TYPE, AL_LANG.NAME, AL_LANG.VERSION, AL_LANG.TY 1961 22755 59258574 48879144
INSERT INTO "AL_SETOPTIONS" ("PARENT_OBJID", "PARENT_OBJTYPE", "CALL_OBJID", "CALL_OBJTYPE 1538 1538 17415345 7419312
INSERT INTO "AL_ATTR" ("PARENT_OBJID", "PARENT_OBJ_TYPE", "ATTR_KEY", "PARENT_ATTR_KEY", " 1134 1134 10956172 4681152
INSERT INTO "AL_AUDIT" ("OBJECT_KEY", "HISTORY_KEY", "DF_LANG_KEY", "STATUS", "RULEINFO") 1090 1090 14457940 5737760
INSERT INTO "AL_LANG" ("OBJECT_KEY", "OBJECT_TYPE", "NAME", "VERSION", "TYPE", "GUID", "OW 332 332 3659708 2390400
SELECT CMS_InfoObjects6.ObjectID FROM CMS_InfoObjects6 WHERE NextRunTime <= '2011 08 23 0 205 205 6469650 808144
SELECT PARENT_OBJ, PARENT_OBJ_R_TYPE FROM AL_PARENT_CHILD P WHERE DESCEN_OBJ = 'DF_ODS_ZMM 138 1354 2445008 939504
select POSITION, ATTR_KEY, ATTR_NAME,ATTR_VALUE, OVERFLOW_FLAG, PARENT_ATTR_KEY from AL_AT 55 6531 1482799 826624
select AL_SCHEMA.OBJECT_KEY, AL_SCHEMA.VERSION, AL_SCHEMA.NAME, AL_SCHEMA.OWNER, AL_SCHEM 55 29955 1873652 855360
select NORMNAME, VERSION, OBJECT_KEY from AL_SCHEMA where NORMNAME = 'DS_M1BI_TARGETZJMIBI 55 29709 1319234 523392
SELECT RELTABLE.PARENTID, RELTABLE.CHILDID, RELTABLE.RELATIONSHIPID, RELTABLE.ISMEMBER, RE 36 585 768747 190768
SELECT PREFIX_NAME FROM (SELECT ROWNUM NO,PREFIX_NAME FROM MD_ETL_NAV_TBN_PREFIX WHERE VAL 27 127 368649 123768
INSERT INTO "AL_VARPARAM" ("PARENT_OBJID", "PARENT_OBJTYPE", "VP_NAME", "VP_TYPE", "VP_SEQ 27 27 383654 122256
SELECT PREFIX_CODE FROM (SELECT ROWNUM NO,PREFIX_CODE FROM MD_ETL_NAV_TBN_PREFIX WHERE VAL 27 121 368649 119448
SELECT HEADQUARTER_FLG FROM (SELECT ROWNUM NO,HEADQUARTER_FLG FROM MD_ETL_NAV_TBN_PREFIX W 27 127 368865 118584
INSERT INTO DM_F_HR_STATFACT SELECT :B1 AS DATE_ID, ORG_ID AS ORG_KEY, :B3 AS PI_ID, :B2 A 20 1025 851552 283000
SELECT CMS_InfoObjects6.ObjectID FROM CMS_InfoObjects6 WHERE CMS_InfoObjects6.ObjName = '! 19 20 410688 82840
SELECT ObjectID, Version, LastModifyTime, CRC, Properties FROM CMS_InfoObjects6 WHERE Obje 18 613 513397 143808
SELECT CMS_InfoObjects6.ObjectID FROM CMS_InfoObjects6 WHERE (CMS_InfoObjects6.ParentID = 16 3515 470951 579456
SELECT CMS_InfoObjects6.ObjectID FROM CMS_InfoObjects6 WHERE ((CMS_InfoObjects6.TypeID = 3 13 1173 345987 51376
select NORMNAME, VERSION, OBJECT_KEY from AL_LANG where NORMNAME = 'WF_SQL_ORA_PROCESS_HEA 12 244 274244 112320
select NORMNAME, VERSION, OBJECT_KEY from AL_LANG where NORMNAME = 'WF_ODS_ZMMC_SUB_NAV_SA 12 240 274223 112320
select NORMNAME, VERSION, OBJECT_KEY from AL_LANG where NORMNAME = 'WF_ODS_ZMMC_SUB_NAV_PU 12 240 274247 112320
INSERT INTO "AL_COLMAP" ("DF_NAME", "TRG_TAB_NAME", "TRG_OWNER", "TRG_DS", "TRG_COL_NAME", 12 100 215712 66912
select NORMNAME, VERSION, OBJECT_KEY from AL_LANG where NORMNAME = 'WF_SQL_ORA_PROCESS_SUB 12 244 274148 112320
SELECT CMS_InfoObjects6.ObjectID FROM CMS_InfoObjects6 WHERE (CMS_InfoObjects6.SI_CUID = ' 11 262 329796 40640
select NORMNAME, VERSION, OBJECT_KEY from AL_LANG where NORMNAME = 'DF_ODS_ZMMC_SUB_NAV_PU 10 1677 212334 93600
select count(*) from md_etl_m1bi_wf_status where job_name='JOB_M1BI_ZMMC_NAV_ODS' and work 10 299 141015 44360
update md_etl_m1bi_wf_status set (status,upd_dt)=(select 1,20110822 from dual) where job_n 10 295 188312 65440
select col.*, com.Comments from sys.all_tab_columns col, sys.all_col_comments com whe 9 64 1239835 912096
select NORMNAME, VERSION, OBJECT_KEY from AL_LANG where NORMNAME = 'WF_ODS_ZMMC_NAV_DETAIL 9 180 205689 84240