3 Replies Latest reply on Aug 16, 2015 8:35 AM by yoonas

    Performance issue with lexical parameter in before report in R12


      Dear All,


      I have issue as below.


      I have a XML publisher report  in which i am passing the multiple where conditions(16 conditions) dynamically from before trigger, so the issue is when i am running the report multiple times and checked statistics of table space from below query suddenly the table space used size is increasing and getting the error  in application as unable to extend table space.


      SELECT   A.tablespace_name tablespace1, D.mb_total,

      SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

      (D.mb_total -  SUM (A.used_blocks * D.block_size) / 1024 / 1024) mb_free

      FROM     v$sort_segment A,


      SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

      FROM     v$tablespace B, v$tempfile C

      WHERE    B.ts#= C.ts#

      GROUP BY B.name, C.block_size

      ) D

      WHERE    A.tablespace_name = D.name

      GROUP by A.tablespace_name, D.mb_total


      But when i am passing normal where conditions all programs are completed with normal.


      So just wanted to check how lexical parameter works in bofore report respective to performance, please give me some pointer on this to understand.