This discussion is archived
6 Replies Latest reply: Oct 22, 2013 3:09 PM by user13785220 RSS

How to execute DBMS_SQLTUNE.report_sql_monitor from SQL Developer 4.0

user13785220 Newbie
Currently Being Moderated

Hello -

We'd like to make new SQL Developer 4.0 as a single stop for all performance tuning reports.

(So far we do know how to generate AWR, ASH and ADDM directly from SQL DEV 4.0 and we can do as NON-privileged user)

Now, the question is how to execute DBMS_SQLTUNE.report_sql_monitor API from SQL DEV 4.0 ?

I've tried this way:

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

SELECT DBMS_SQLTUNE.report_sql_monitor(

  sql_id       => :SQL_ID

  type         => 'TEXT',

  report_level => 'ALL') AS myreport

FROM dual;

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

and then right button mouse click on "myreport" line and EXPORT to "text" from pull down menu and save to the .TSV file on the local file system.

It works this way as expected but seems to be a lot of hassle: too many steps.

 

Then I have also tried HTML format:

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

SELECT DBMS_SQLTUNE.report_sql_monitor(

  sql_id       => :SQL_ID

  type         => 'HTML',

  report_level => 'ALL') AS myreport

FROM dual;

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

and it did NOT work nicely at all from SQL Dev 4.0

 

Any help will be greatly appreciated.

(again, the idea here is to setup our development and QA groups with full access to all performance tuning reports w/o having them call DBAs for help in producing any SQL tuning report).

 

Thank you,

vr

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points