SQL Tracker (Toad for Oracle) with Oracle Reports Builder not returning any results

Toad for Oracle - Beta
1

Hi All,

I am trying to do some performance tuning on an existing Oracle Report Builder report that exists (rep1.rdf) but the SQL Tracker feature of Toad for Oracle is not returning any results. Any ideas?

This is what I am doing:

  1. I launched Toad for Oracle

  2. In Toad for Oracle, start the Monitoring Tool by selecting Database->Monitor->SQL Tracker

  3. I launched the Oracle Reports Builder and connected and opened the rep1.rdf report

  4. In Toad for Oracle SQL Tracker Tool (window title shows Monitoring Tool for Database Clients), I pressed Refresh button and the clicked on the rwbuilder.exe entry to highlight it. Then I clicked on the Start Monitoring button. “Yes” no appears in the highlighted entry under Monitoring.

5.In the Oracle Reports Builder, i ran the report by pressing the Run Paper Layout button. The report is executed and displayed successfully.

  1. In Toad for Oracle SQL Tracker Tool, the rwbuilder.exe(pid) window does not display any of the SQL statements that were executed. I cannot seem to be able to capture them and the timings. The tool works successfully with Oracle Forms and Toad itself.

I am running within a VM in Windows 7 environment. The database is 11g and the Oracle Development suite is 10g. The Toad for Oracle version is 12.10. However, i have also tried with previous versions of Toad for Oracle (12.8, 12.9) with the same results.

-----Details----

ORACLE 10g suite

Report Builder 10.1.2.3.0
ORACLE Server Release 10.1.0.5.0
Oracle Procedure Builder 10.1.2.3.0
Oracle ORACLE PL/SQL V10.1.0.5.0 - Production
Oracle CORE 10.1.0.5.0 Production
Oracle Tools Integration Services 10.1.2.0.2
Oracle Tools Common Area 10.1.2.0.2
Oracle Toolkit 2 for Windows 32-bit platforms 10.1.2.0.2
Resource Object Store 10.1.2.0.2
Oracle Help 10.1.2.0.2
Oracle Sqlmgr 10.1.2.3.0
Oracle Query Builder 10.1.2.3.0 - Production
PL/SQL Editor © WinMain Software (www.winmain.com), v1.0 (Production)
Oracle ZRC 10.1.2.3.0
Oracle XML Developers Kit 10.1.0.5.0 - Production
Oracle Virtual Graphics System 10.1.2.0.2
Oracle Image 10.1.2.0.2
Oracle Multimedia Widget 10.1.2.0.2
Oracle Tools GUI Utilities 10.1.2.0.2

Toad for Oracle (32 bit Xpert full version) v. 12.10.0.30

Dell SQL Tracker v. 12.10.0.30

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2

Does Oracle Report Builder use OCI or JDBC to connect? SQL Tracker only monitors OCI connections.

On Oct 6, 2016, at 2:34 PM, Denis.P.Daley bounce-DenisPDaley@toadworld.com wrote:

SQL Tracker (Toad for Oracle) with Oracle Reports Builder not returning any results

Thread created by Denis.P.Daley
Hi All,

I am trying to do some performance tuning on an existing Oracle Report Builder report that exists (rep1.rdf) but the SQL Tracker feature of Toad for Oracle is not returning any results. Any ideas?

This is what I am doing:

  1. I launched Toad for Oracle
  1. In Toad for Oracle, start the Monitoring Tool by selecting Database->Monitor->SQL Tracker
  1. I launched the Oracle Reports Builder and connected and opened the rep1.rdf report
  1. In Toad for Oracle SQL Tracker Tool (window title shows Monitoring Tool for Database Clients), I pressed Refresh button and the clicked on the rwbuilder.exe entry to highlight it. Then I clicked on the Start Monitoring button. "Yes" no appears in the highlighted entry under Monitoring.

5.In the Oracle Reports Builder, i ran the report by pressing the Run Paper Layout button. The report is executed and displayed successfully.

  1. In Toad for Oracle SQL Tracker Tool, the rwbuilder.exe(pid) window does not display any of the SQL statements that were executed. I cannot seem to be able to capture them and the timings. The tool works successfully with Oracle Forms and Toad itself.

I am running within a VM in Windows 7 environment. The database is 11g and the Oracle Development suite is 10g. The Toad for Oracle version is 12.10. However, i have also tried with previous versions of Toad for Oracle (12.8, 12.9) with the same results.

-----Details----

ORACLE 10g suite

Report Builder 10.1.2.3.0

ORACLE Server Release 10.1.0.5.0

Oracle Procedure Builder 10.1.2.3.0

Oracle ORACLE PL/SQL V10.1.0.5.0 - Production

Oracle CORE 10.1.0.5.0 Production

Oracle Tools Integration Services 10.1.2.0.2

Oracle Tools Common Area 10.1.2.0.2

Oracle Toolkit 2 for Windows 32-bit platforms 10.1.2.0.2

Resource Object Store 10.1.2.0.2

Oracle Help 10.1.2.0.2

Oracle Sqlmgr 10.1.2.3.0

Oracle Query Builder 10.1.2.3.0 - Production

PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)

Oracle ZRC 10.1.2.3.0

Oracle XML Developers Kit 10.1.0.5.0 - Production

Oracle Virtual Graphics System 10.1.2.0.2

Oracle Image 10.1.2.0.2

Oracle Multimedia Widget 10.1.2.0.2

Oracle Tools GUI Utilities 10.1.2.0.2

Toad for Oracle (32 bit Xpert full version) v. 12.10.0.30

Dell SQL Tracker v. 12.10.0.30

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta Forum notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

3

It is using OCI.

It is working successfully with my Oracle Forms and Toad itself but does not work with Oracle Reports Builder.

4

I'm not familiar with report builder but SQL Tracker will intercept SQL that passes from the client to the server via OCI. It the queries are executed server side, we would have no way to see them.

On Oct 6, 2016, at 2:49 PM, Denis.P.Daley bounce-DenisPDaley@toadworld.com wrote:

RE: SQL Tracker (Toad for Oracle) with Oracle Reports Builder not returning any results

Reply by Denis.P.Daley
It is using OCI.

It is working successfully with my Oracle Forms and Toad itself but does not work with Oracle Reports Builder.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta Forum notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

5

This is just a shot in the dark…I just remember from days with working with these (many, many moons ago) that when installing, sometimes they created separate Oracle Homes (1 for Reports, and 1 for Forms). I don’t know if that’s the way you have yours setup (or even if it’s done that way anymore), but if you have separate Oracle Homes, it might be worth a shot trying to switch homes to see if that makes a difference. Other than than, I don’t know why one would work and one wouldn’t.

On a different note - if you are trying to do tuning, it might just be easier to extract out the main query (generally the culprit) of the report and fine tune it in the editor. I remember what a pain some of the bigger reports could be, especially to follow and determine what is being called, when, etc., so I can sympathize. But, at the end of the day, I always found it easier to just get my hands dirty and start breaking it down query by query, bock by block, etc.

6

Hi Guys,

Thanks for all the help and suggestions. I have used the following approach:

In Oracle Reports Builder, under Program->Tracing, you can specify a tracing file name that will contain all the timing, sql’s, bind variables, etc.

You can use that information to extract the problem SQL statements and then tune them with TOAD and re-integrate the required change back into your report to increase performance.

Denis