Добрый вечере, коллеги!
Есть проблема куда-то постоянно девается PGA. Источник пролемы локализован, но что делать не понятно.
CREATE OR REPLACE TYPE t_Obj_List_sqlru AS OBJECT
(
row_types anytype,
row_was_returned NUMBER,
col_count NUMBER,
user_prop NUMBER,
STATIC FUNCTION Get(user_prop NUMBER) RETURN anydataset
PIPELINED USING t_Obj_List_sqlru,
STATIC FUNCTION ODCITableDescribe(pro_record_table OUT anytype,user_prop NUMBER) RETURN NUMBER,
STATIC FUNCTION ODCITablePrepare(pro_sctx OUT t_Obj_List_sqlru, pr_tab_func_info IN sys.ODCITabFuncInfo,user_prop NUMBER) RETURN NUMBER,
STATIC FUNCTION ODCITableStart(pro_sctx IN OUT NOCOPY t_Obj_List_sqlru,user_prop NUMBER) RETURN NUMBER,
MEMBER FUNCTION ODCITableFetch(SELF IN OUT NOCOPY t_Obj_List_sqlru, pr_nrows IN NUMBER, pro_record_out OUT anydataset) RETURN NUMBER,
MEMBER FUNCTION ODCITableClose(SELF IN t_Obj_List_sqlru) RETURN NUMBER
)
/
CREATE OR REPLACE TYPE BODY t_Obj_List_sqlru AS
STATIC FUNCTION ODCITableDescribe(pro_record_table OUT anytype,user_prop NUMBER) RETURN NUMBER IS
l_record_structure anytype;
l_type PLS_INTEGER;
l_prec PLS_INTEGER;
l_Len PLS_INTEGER;
l_scale PLS_INTEGER;
BEGIN
-- The type that describes the structure of a record is created. This type
-- must be of dbms_types.typecode_object.
anytype.begincreate(dbms_types.typecode_object, l_record_structure);
-- Adding some attributes to the type being created. These attributes specify the
-- column names, their datatype and the precision
--
-- The first column (named col1) will be a varchar2(10)
--pro_ColCount := 0;
FOR l_cnt IN 1 .. 5
LOOP
l_record_structure.addattr(aname => 'COL' || l_cnt,
typecode => dbms_types.TYPECODE_VARCHAR2,
prec => l_prec,
scale => l_scale,
len => 1024,
csid => NULL,
csfrm => NULL);
END LOOP;
l_record_structure.endcreate;
-- Of course, in this example, I didn't really create a dynamic return type
-- since all three columns will always be the same. But I thought I just want to
-- show the idea.
-- Now, after creating the record structure, I also need a to create a nested table
-- of that record structure type. This is indicated with dbms_types.typecode_table.
anytype.begincreate(dbms_types.typecode_table, pro_record_table);
pro_record_table.setinfo(NULL, NULL, NULL, NULL, NULL, l_record_structure, dbms_types.typecode_object, 0);
pro_record_table.endcreate();
l_record_structure := NULL;
RETURN odciconst.success;
EXCEPTION
WHEN OTHERS THEN
-- indicate that an error has occured somewhere.
RETURN odciconst.error;
END;
-- ODCITablePrepare creates an instance of t_Attrs and returns it through the sctx out parameter.
STATIC FUNCTION ODCITablePrepare(pro_sctx OUT t_Obj_List_sqlru, pr_tab_func_info IN sys.ODCITabFuncInfo,user_prop NUMBER) RETURN NUMBER IS
l_prec PLS_INTEGER;
l_scale PLS_INTEGER;
l_len PLS_INTEGER;
l_csid PLS_INTEGER;
l_csfrm PLS_INTEGER;
l_record_desc anytype;
l_aname VARCHAR2(30);
l_dummy PLS_INTEGER;
BEGIN
-- this is a bit mystic, imho: Through tab_func_info.RetType, it's possible to access the record_table that
-- was created in ODCITableDescribe.
-- With GetAttrElemInfo, I can get the record_structure that was created in ODCITableDescribe. This record_structure
-- is returned in the out parameter record_desc.
-- The parameters prec, scale, len, csid, csfrm and aname are ignored.
l_dummy := pr_tab_func_info.RetType.GetAttrElemInfo(NULL, l_prec, l_scale, l_len, l_csid, l_csfrm, l_record_desc, l_aname);
-- Now, I am ready to construct an instance of t_Attrs.
-- The first parameter will be stored in the member row_types, the second in row_was_returned.
pro_sctx := t_Obj_List_sqlru(l_record_desc, 0,0,user_prop);
RETURN odciconst.success;
END;
STATIC FUNCTION ODCITableStart(pro_sctx IN OUT NOCOPY t_Obj_List_sqlru,user_prop NUMBER) RETURN NUMBER IS
BEGIN
RETURN odciconst.success;
END;
MEMBER FUNCTION ODCITableFetch(SELF IN OUT NOCOPY t_Obj_List_sqlru, pr_nrows IN NUMBER, pro_record_out OUT anydataset) RETURN NUMBER IS
BEGIN
IF row_was_returned = 1 THEN
-- record_out being null indicates last record was already fetched
RETURN ODCIconst.success;
END IF;
SELF.row_was_returned := 1;
anydataset.begincreate(dbms_types.typecode_object, SELF.row_types, pro_record_out);
pro_record_out.endcreate;
RETURN odciconst.success;
END;
MEMBER FUNCTION ODCITableClose(SELF IN t_Obj_List_sqlru) RETURN NUMBER IS
BEGIN
RETURN odciconst.success;
END;
END;
/
Скрипт демонстрирующий утечку:select name, m.value/1024/1024 from v$mystat m, v$statname s where m.STATISTIC# = s.STATISTIC# and s.NAME LIKE '%pga%';
declare
l_crs SYS_REFCURSOR;
l_desc_tab dbms_sql.desc_tab;
c number;
begin
for i in 1.. 300 loop
OPEN l_crs FOR 'select 1 from TABLE(OS_LIB.t_Obj_List_sqlru.Get('||i||'))';
FETCH l_crs INTO c;
CLOSE l_crs;
end loop;
end;
/
select name, m.value/1024/1024 from v$mystat m, v$statname s where m.STATISTIC# = s.STATISTIC# and s.NAME LIKE '%pga%';
declare
l_crs SYS_REFCURSOR;
l_desc_tab dbms_sql.desc_tab;
c number;
begin
for i IN 301.. 600 loop
OPEN l_crs FOR 'select 1 from TABLE(OS_LIB.t_Obj_List_sqlru.Get('||i||'))';
FETCH l_crs INTO c;
CLOSE l_crs;
end loop;
end;
/
select name, m.value/1024/1024 from v$mystat m, v$statname s where m.STATISTIC# = s.STATISTIC# and s.NAME LIKE '%pga%';
declare
l_crs SYS_REFCURSOR;
l_desc_tab dbms_sql.desc_tab;
c number;
begin
for i in 601.. 900 loop
OPEN l_crs FOR 'select 1 from TABLE(OS_LIB.t_Obj_List_sqlru.Get('||i||'))';
FETCH l_crs INTO c;
CLOSE l_crs;
end loop;
end;
/
select name, m.value/1024/1024 from v$mystat m, v$statname s where m.STATISTIC# = s.STATISTIC# and s.NAME LIKE '%pga%';Вопрос: КАК БЫТЬ???????