Re: Query tuning help

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Wed, 6 Feb 2013 06:30:50 +0000
Message-ID: <CABe10saN6qKY8AJx5azrAAq6jKH1LC-+eJ2wNn=XiV_OGausYw_at_mail.gmail.com>

That's a pretty good suggestion, but does assume that the extra cost diagnostics and tuning packs are licensed for this machine. If they aren't for whatever reason running the query with the GATHER_PLAN_STATISTICS hint and then immediately afterwards running
set lines 200 trimspool on pages 0
select * from table(dbms_xplan.display(null, format => 'ALLSTATS LAST'));

should give us enough information (as Ric suggested) and won't cost anything.

On Wed, Feb 6, 2013 at 6:04 AM, Sriram Kumar <k.sriramkumar_at_gmail.com>wrote:

> Hi,
> What is version of the DB?. Given that WFCTOTAL is the biggest table in the
> SQL, how selective are the conditions?
>
> AND A7.NOTPAIDSW =0
> AND A7.APPLYDTM >=A5.PPSTARTDATEDTM
> AND A7.APPLYDTM <=A5.PPENDDATEDTM
>
> if you are on 11gr1/11gr2, could you run this with a /*+MONITOR*/ hint and
> once the query is complete, from the same session could you spool and
> execute
>
> select dbms_sqltune.report_sql_monitor
> (session_id=>sys_context('userenv','sid'), report_level=>'ALL') as
> report from dual;
>
> and send the output?
>
> Best regards
>
> Sriram kumar
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Feb 06 2013 - 07:30:50 CET