RE: SQLs from Documentum use distinct column alias
Date: Thu, 17 Dec 2009 18:12:31 +0000
Message-ID: <7C4BF3B32B80CC44AE37D31B172415933A5DB00E7F_at_GVW1337EXC.americas.hpqcorp.net>
Is dms_prod part of INFPLAN? Seems to be a poor application design decision that you should report to the product vendor.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yong Huang
Sent: Wednesday, December 16, 2009 4:26 PM
To: oracle-l_at_freelists.org
Subject: SQLs from Documentum use distinct column alias
Maybe somebody knowing both EMC Documentum and Oracle can help. The ticket with EMC was opened in August and recently closed with an enhancement request (and no other progress). I asked the support analyst if there was some parameter on the content server to suppress distinct column alias in the SQLs and the answer is No. Here's the description.
DMS_PROD is launched by this command on the content server:
E:\Dctm\product\5.3\bin\documentum.exe -docbase_name dms_prod ...
Inside the Oracle database (version 10.2.0.4), we see this shared pool memory usage:
SQL> select parsing_schema_name, sum(sharable_mem)/1048576 mb
2 from v$sql
3 group by parsing_schema_name
4 having sum(sharable_mem)/1048576 > 20
5 order by 2;
PARSING_SCHEMA_NAME MB
------------------------------ ----------
FAX_OWNER 25.7209263 SYS 26.4383869 CRIS_OWNER 30.8399334 PLASTIC_USER 235.490922 DMS_PROD 2788.31081
The dms_prod uses way too much memory. The SQLs parsed by dms_prod *are* using bind variables (as opposed to literal strings). But each SQL is distinct because some useless column aliases are added and the aliases differ from one SQL to another:
SQL> select sql_text from v$sql where parsing_schema_name = 'DMS_PROD' SQL> and sql_text like 'DELETE FROM dms_prod.MDA_INBOX_S %' and rownum SQL> <= 2;
SQL_TEXT
DELETE FROM dms_prod.MDA_INBOX_S RIELB_FMD_DMD_ WHERE (RIELB_FMD_DMD_.R_OBJECT_ID=:handle AND RIELB_FMD_DMD_.I_VSTAMP=:version) DELETE FROM dms_prod.MDA_INBOX_S TRTRB_FMD_DMD_ WHERE (TRTRB_FMD_DMD_.R_OBJECT_ID=:handle AND TRTRB_FMD_DMD_.I_VSTAMP=:version)
Can't you use RIELB_FMD_DMD_ alias for both?
BUSINESS IMPACT:
More than half of our database server shared pool is dedicated to this user, squeezing other users' shared pool usage to bare minimum, severely impacting their performance. Besides, this wasteful usage of shared memory by dms schema himself also limits his own performance, spending time in unnecessary parsing.
Product: Content Server
Version: 5.3
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 17 2009 - 12:12:31 CST