6 Replies Latest reply: Feb 6, 2013 7:22 AM by IvanBlanarik RSS

    explain plan

    982895
      Hi All,

      Can anyone Please tel me how to check the cost ,estimation time etc for a package . I am tryng to execute explain plan after opening the package but I am gettng nothing from that. Please help me.


      Thanks,
        • 1. Re: explain plan
          _Karthick_
          979892 wrote:
          Hi All,

          Can anyone Please tel me how to check the cost ,estimation time etc for a package . I am tryng to execute explain plan after opening the package but I am gettng nothing from that. Please help me.


          Thanks,
          You mean to say for the SQL inside the package correct? You can enable [url http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams205.htm#REFRN10208]SQL_TRACE in your session and use TKPROF to convert the trace file into a readable file. Please read [url http://docs.oracle.com/cd/B19306_01/server.102/b14211/sqltrace.htm#i4640]Understanding SQL Trace and TKPROF
          • 2. Re: explain plan
            IvanBlanarik
            Hi,
            there is no way how to predicate the time duration of procedures, packages, etc. If you want to optimise your code the best way will be the dbms_profiler.
            • 3. Re: explain plan
              982895
              Could you please explain me in detail how to enable that SQL trace and TKPROF
              • 4. Re: explain plan
                982895
                Actually I need to find the execution time of all packages in a schema. And suggest what can we do to tune the package/Query. I am in dilemma how to proceed with this requirement. It will be grt help if anyone explains me how to resolve..
                • 5. Re: explain plan
                  IvanBlanarik
                  You can use SQL trace, but you it has a performance impact. Be carefull with that on your production database :)
                  The relevant information about performance could be selected from these views:
                  --statistics about statements that are right now in the SGA
                  select * from v$sqlstats
                  order by elapsed_time desc;
                  
                  or
                  
                  --statistics collected from the last 40 days
                  --use this only when you have purchased Oracle Tuning Pack
                  select * from dba_hist_sqlstat
                  order by elapsed_time desc;
                  These columns are important: sql_fulltext, disk_reads, buffer_gets, elapsed_time.
                  • 6. Re: explain plan
                    IvanBlanarik
                    ... and I should install Statspack on your database. It collects information from V$ views and can be used instead of the Automatic Workload Repository (dba_hist... views).
                    It's a free tool and provides almost all the necessary data for database performance tunning ;)