This content has been marked as final. Show 6 replies
979892 wrote: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
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.
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.
Could you please explain me in detail how to enable that SQL trace and TKPROF
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..
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:
These columns are important: sql_fulltext, disk_reads, buffer_gets, elapsed_time.
--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;
... 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 ;)