You can leverage the standard Oracle tuning tools for investigating set based SQL performance and PLSQL performance. There are reports in the utility exchange (see
here) for reporting on the results after using the DBMS_PROFILER.The PLSQL profiles can
be queried (in master detail form - view map profiles, drill to top 20, profile summary etc). The following reports are available;
- Top 20 statements (in terms of most
time spent) - Summary report giving time taken for
the package and dependent functions/procedures outside of package. - Profile detail, line by line detail of
occurrences and time (exportable to Excel etc.) - % code coverage
The start/stop of PLSQL profiling has to be manually performed before/after the map execution, see the download for instructions.
For analysis of set based SQL from the design environment, there were some changes in OWB 10.2.0.3 that included the ability to obtain access explain plans/statistics/tuning advisor from within the mapping editor for SQL within the map. There is a viewlet describing some of this here.
In order to utilize statistics and SQL Tuning there are additional privs required.....
1. For statistics the following must be granted to the schema the map is being deployed to:
grant select on V_$SQL_PLAN to <tgt_schema>;
grant select on V_$SQL_PLAN_STATISTICS_ALL to <tgt_schema>;
grant select on V_$SQL to <tgt_schema>;
2. For SQL Tuning the advisor role is required:
grant advisor to <tgt_schema>;
If you want runtime analysis of SQL, the Oracle Database Release 11 has introduced a new performance view v$sql_monitor. You can use v$sql_monitor for near real-time monitoring of SQL statements.
Customers who license the Enterprise Manager Tuning Pack are allowed to use this view. SQL monitoring is automatically started when a SQL statement runs parallel or when it has consumed at least 5 seconds of CPU or I/O time.
In Oracle Database Release 11 the way to get output in a readable format is through PL/SQL or grid control:
variable my_rept clob ;
begin
:my_rept := dbms_sqltune.report_sql_monitor() ;
end ;
/
print :my_rept
Full documentation for v$sql_monitor is in the Database Reference and the Database Performance Tuning Guide.
