Performance tuning mappings

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;

  1. Top 20 statements (in terms of most
    time spent)
  2. Summary report giving time taken for
    the package and dependent functions/procedures outside of package.
  3. Profile detail, line by line detail of
    occurrences and time (exportable to Excel etc.)
  4. % 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.

Comments:

The linked in above piece is not working. Could you provide us the working links?

Posted by guest on November 08, 2012 at 10:12 AM PST #

The link should be fixed now. I have changed the URL to the sourceforge site where some users have been sharing experts and the like.

Cheers
David

Posted by David on November 08, 2012 at 10:26 AM PST #

Thanks for your update. I know, we have to follow oracle Data base tuning after doing the steps above. Do you know a similar blog that guide/focus through Oracle 11g database tuning?

Posted by Ram Iyer on November 12, 2012 at 09:46 AM PST #

Hi Ram

Maria Colgan has a good blog on the Oracle Optimizerm, she is the Oracle PM for the Oracle Optimizer and is a frequent speaker at user conferences;
https://blogs.oracle.com/optimizer/

For an external blog, Jonathan Lewis (http://jonathanlewis.wordpress.com/) has a lot of in-depth articles. That aside, I use google (or specific OTN forum searches) for searching specific challenges.

Cheers
David

Posted by David on November 13, 2012 at 09:36 AM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today