title: Oracle Procedure Takes long time to run but the straight sql runs quick
body: <p>I have a block of sql that runs pretty smooth outside the procedure. The moment I put the sql block in the procedure to return the ref_cursor, the procedure takes quite a bit of long time to execute the ref_cursor.</p>
  
  <p>With help from DBAs, we implemented DB profile and it worked great to speed up the procedure but then any minor change in that particular procedure make it go haywire. I am not sure what the problem.. I am running out of options. How should I go about troubleshooting this particular weird issue?</p>
  
  <p>Thank you in advance.</p>
  
  <p>Edit.. here is the query</p>
  
  <pre><code>with query_ownership as (SELECT leeo.legal_entity_id,
                             leeo.parent_le_id,
                             SUM(leeo.effective_ownership) ownership_percent
                        FROM data_ownership leeo
                       WHERE leeo.start_date &lt;=
                             to_date('12/31/2012','mm/dd/yyyy')
                         AND ((leeo.end_date &lt; &amp;lvTaxYearDate and leeo.end_date &gt; &amp;lvTaxYearBeginDate)
                             to_date('12/31/2012','mm/dd/yyyy') OR
                             leeo.end_date IS NULL)
                         and leeo.stock_type in ('E')
                       GROUP BY leeo.legal_entity_id, leeo.parent_le_id
                      HAVING SUM(leeo.effective_ownership) &gt; 0
  ),
  query_branches as ( SELECT b.branch_id       as legal_entity_id,
                             b.legal_entity_id as perent_le_id,
                             1.00              as ownership_percent
                        FROM company_branches b
                       WHERE b.tax_year = 2012),
  child_query as (select * from query_ownership
                      UNION
             select * from query_branches),
  parent_query as (select * from query_ownership
                      UNION
             select * from query_branches),                                       
  inner_query as (SELECT rownum                        as sortcode,
                     -level                        as lvl,
                     child_query.parent_le_id,
                     child_query.legal_entity_id,
                     child_query.ownership_percent
                FROM child_query
               START WITH child_query.legal_entity_id = 'AB1203'
              CONNECT BY NOCYCLE PRIOR child_query.legal_entity_id =
                          child_query.parent_le_id
                     AND child_query.ownership_percent &gt;= 0.01
                     and level = 0
              UNION
              SELECT rownum as sortcode,
                     level - 1 as lvl,
                     parent_query.parent_le_id,
                     parent_query.legal_entity_id,
                     parent_query.ownership_percent
                FROM parent_query
               START WITH parent_query.legal_entity_id = 'AB1203'
              CONNECT BY NOCYCLE
               PRIOR parent_query.parent_le_id =
                          parent_query.legal_entity_id
                     AND parent_query.ownership_percent &gt;= 0.01)
                    ,ownership_heirarchy as (
                 SELECT max(inner_query.sortcode) as sortcode,
             max(inner_query.lvl) as lvl,
             inner_query.parent_le_id,
             inner_query.legal_entity_id,
             inner_query.ownership_percent from inner_query
       GROUP BY inner_query.parent_le_id,
                inner_query.legal_entity_id,
                inner_query.ownership_percent
                )
                ,goldList as (
  SELECT lem2.legal_entity_id from ownership_heirarchy,
     company_entity_year lem1,
     company_entity_year lem2
  WHERE ownership_heirarchy.parent_le_id = lem2.legal_entity_id
  AND lem2.tax_year = 2012
  
  AND ownership_heirarchy.legal_entity_id = lem1.legal_entity_id
  AND lem1.tax_year = 2012
  AND lem1.legal_entity_type &lt;&gt; 'EXT'
  AND lem1.non_legal_entity_flag is null
  AND lem2.legal_entity_type &lt;&gt; 'EXT'
  AND lem2.non_legal_entity_flag is null
  and TRIM(lem2.alt_tax_type) is null
  and UPPER(lem2.tax_type) in ('DC', 'DPS', 'TXN')
  ),
  fulllist as (
           select * from goldList
          union
           select gc.parent_le_id from company_entity_year e,       consolidation_group gc 
  where e.LEGAL_ENTITY_ID = 'AB1203' and e.tax_year = 2012
  and e.TAX_CONSOLIDATION_GRP = gc.group_id
          union
           select e.leid from vdst_entity e where e.TAX_YEAR = 2012
           and e.ALT_TAX_TYPE in (3,8)
           and e.LEID = 'AB1203'
  ) 
  
    select  distinct dc.dcn_id      as dcnId,
           dc.dcn_name    as dcnName,
           dy.dcn_year_id dcnYearId,
           ty.tax_year_id taxYearId,
           ty.tax_year    taxYear
      from company_dcn dc, company_dcn_year dy, company_tax_year ty
     where dc.dcn_id = dy.dcn_id
       and dy.year_id = ty.tax_year_id
       and ty.tax_year = 2012
       and dc.leid in (
           select * from fulllist
                       ); 
  </code></pre>