Essential tools/scripts for performance monitoring of SQL and generating reports in Oracle database without OEM/ with OEM/ with sqldeveloper.
Visit my LinkedIn group to find more:
https://www.linkedin.com/groups/8151826
My name is Alireza Kamrani, and today I will demonstrate all methods to achieve a real time reports for a sql statement inside Oracle using some script and package.
This method will be useful when have not access to OEM or need to a html report to sending for developers or someone.
Lets starting.
Most DBAs for getting details on performance reports about specific SQL statement using two following scripts :
- awrrpt.sql – for getting a performance report out of the Automatic Workload Repository
- ashrpt.sql – for getting a similar report directly from Active Session History
- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Another useful method is : calling dbms_sqltune.Report_sql_monitor package.
The example below shows how to use DBMS_SQLTUNE.REPORT_SQL_MONITOR to generate an active report by setting “active” as the report type.
SET trimspool ON
SET TRIM ON
SET pages 0
SET linesize 32767
SET LONG 1000000
SET longchunksize 1000000
spool sqlmon_active.html
SELECT dbms_sqltune.Report_sql_monitor(SQL_ID=>'sql_id', TYPE=>'active')
FROM dual;
spool OFF
Just remember to edit the resulting sqlmon_active.html file to remove the first line and last line in the file (the spool off).
The resulting HTML file can then be viewed in any browser.
The browser must have connectivity to OTN to load the active report code.
How do I retrieve a historical SQL Monitor report in Oracle without OEM?
To manually generate a persisted SQL Monitor report for a single SQL statement, you will first need to find its REPORT_ID and then use the PL/SQL function
DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL to extract the report.
The easiest way to find the REPORT_ID is to query DBA_HIST_REPORTS and supply as much information as you can about the SQL statement and when it was executed.
In DBA_HIST_REPORTS most of the column names are self-explanatory. However, there are two columns KEY1 and KEY2 that warrant some explanation, as you are going to need to use at least one of them in order to find the correct REPORT_ID.
KEY1 is the SQL_ID for the statement
KEY2 is the SQL execution_id for the statement
Here’s an example of the query I used:
SELECT report_id
FROM dba_hist_reports
WHERE dbid = 1954845899
AND component_name = 'sqlmonitor'
AND report_name = 'main'
AND period_start_time BETWEEN
To_date('28/01/2014 17:00:00','DD/MM/YYYY HH:MI:SS')
AND
To_date('28/01/2014 17:15:00','DD/MM/YYYY HH:MI:SS')
AND key1 = 'gyn8opcx7xgon';
REPORT_ID
=========
42
Once you have the REPORT_ID, you can use the PL/SQL function DBMS_AUTO_REPORT.REPORT_REPOSITORY_DETAIL to generate the SQL Monitor report, as shown below.
SET echo ON
SET trimspool ON
SET TRIM ON
SET pages 0
SET linesize 32767
SET LONG 10000000
SET longchunksize 1000000
spool old_sqlmon.html
SELECT dbms_auto_report.Report_repository_detail(rid=>42, TYPE=>'active')
FROM dual;
spool OFF
Just remember to edit the resulting old_sqlmon.html file to remove the first line and last line in the file (the spool off). The resulting HTML file can then be viewed in any browser. The browser must have connectivity to OTN to load the active report.
Is it possible to generate SQL Monitor reports for all of the SQL statements monitored during a given period of time?
Starting in Oracle Database 12c, you can use the perfhubrpt.sql script, in the $ORACLE_HOME/rdbms/admin directory, to generate a PerfHub for a given time period, which will include SQL Monitor reports for all of the monitored SQL statements during that period.
The script will prompt you for the report level (default is typical but I would use all), the database ID (default is the database you are on), instance number (default is the instance you are on), and the time period you are interested in.
The output of the perfhubrpt.sql is an HTML file that is a historical view of the EM performance hub for the specified time period.
It gives you an interactive report that allows you to see current activity as well as being able to drill down into individual SQL’s. I like to think of this report as sitting “between” and ASH/AWR report and a SQL monitoring report, ie, instance wide activity with a focus on SQL statements generating that activity.
Ultimately, the report is just a call to the DBMS_PERF package, so you can simply write your own scripts to run the package in whichever way suits your instance.
Note: you maybe getting some errors when running this package, so in some oracle versions maybe need patch or some bug fix and need do search error on metalink(Orcale support).
For example if you got “No Data Found” error after running perfhubrpt.sql read below notice.
The Performance Hub report allows you to view all performance data available for a specified time period. The perfhubrpt.sql script asks yo you define a start and end time when you call it.
If you are getting a No Data Found message when you call the script, it’s likely to mean your database didn’t exist or wasn’t open during the period of time you specified. Or someone has disabled performance stats gathering. You can check if the database has data for your specified time period by looking in V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view.
DBMS_PERF
DBMS_PERF is the interface used by perfhubrpt.sql.
The next example demonstrates how to get a perfhub report using the PL/SQL package DBMS_PERF.
It consists of 3 functions
REPORT_PERFHUB,
REPORT_SESSION,
REPORT_SQL.
Let's use the function REPORT_PERFHUB to generate an active performance report of the entire database system for a specified time period.
Here are the arguments for DBMS_PERF.
DBMS_PERF.REPORT_PERFHUB (
is_realtime IN NUMBER DEFAULT NULL,
outer_start_time IN DATE DEFAULT NULL,
outer_end_time IN DATE DEFAULT NULL,
selected_start_time IN DATE DEFAULT NULL,
selected_end_time IN DATE DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL,
monitor_list_detail IN NUMBER DEFAULT NULL,
workload_sql_detail IN NUMBER DEFAULT NULL,
addm_task_detail IN NUMBER DEFAULT NULL,
report_reference IN VARCHAR2 DEFAULT NULL,
report_level IN VARCHAR2 DEFAULT NULL,
type IN VARCHAR2 DEFAULT 'ACTIVE',
base_path IN VARCHAR2 DEFAULT NULL);
RETURN CLOB;
DBMS_PERF.REPORT_PERFHUB (
is_realtime IN NUMBER DEFAULT NULL,
outer_start_time IN DATE DEFAULT NULL,
outer_end_time IN DATE DEFAULT NULL,
selected_start_time IN DATE DEFAULT NULL,
selected_end_time IN DATE DEFAULT NULL,
inst_id IN NUMBER DEFAULT NULL,
dbid IN NUMBER DEFAULT NULL,
monitor_list_detail IN NUMBER DEFAULT NULL,
workload_sql_detail IN NUMBER DEFAULT NULL,
addm_task_detail IN NUMBER DEFAULT NULL,
report_reference IN VARCHAR2 DEFAULT NULL,
report_level IN VARCHAR2 DEFAULT NULL,
type IN VARCHAR2 DEFAULT 'ACTIVE',
base_path IN VARCHAR2 DEFAULT NULL);
RETURN CLOB;
Once a time period is selected, the performance information is collected and presented based on performance subject areas.
When real-time data is selected, more granular data is presented because data points are available every minute.
When historical data is selected, more detailed data (broken down by different metrics) is presented, but the data points are averaged out to the AWR interval (usually an hour).
Different tabs are available in the Performance Hub, depending on whether is_real-time is 1 for real time mode or 0 for historical mode.
In our example we generate a historical report (here: is_realtime=0) with the following code.
set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool sql_details_history.html
select dbms_perf.report_perfhub (
is_realtime => 0,
outer_start_time => to_date('18-Aug-2023 12:30:00','dd-MON-YYYY hh24:mi:ss'),
outer_end_time => to_date('18-AUG-2023 13:50:00','dd-MON-YYYY hh24:mi:ss'),
selected_start_time => to_date('18-AUG-2023 12:30:00','dd-MON-YYYY hh24:mi:ss'),
selected_end_time => to_date('18-AUG-2023 13:50:00','dd-MON-YYYY hh24:mi:ss'))
from dual;
spool off
set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000
spool sql_details_history.html
select dbms_perf.report_perfhub (
is_realtime => 0,
outer_start_time => to_date('18-Aug-2023 12:30:00','dd-MON-YYYY hh24:mi:ss'),
outer_end_time => to_date('18-AUG-2023 13:50:00','dd-MON-YYYY hh24:mi:ss'),
selected_start_time => to_date('18-AUG-2023 12:30:00','dd-MON-YYYY hh24:mi:ss'),
selected_end_time => to_date('18-AUG-2023 13:50:00','dd-MON-YYYY hh24:mi:ss'))
from dual;
spool off
Note:if you need this tools to accessible for developers, you must know that running this script need DBA role, although maybe you got a access on only running dbms_perf package.
What happens on OEM about realtime reports?
On newer OEM versions you can provide a active html report from a specific sql statement and sending it for developers to review performance of written query, this html report is very useful for developers to handle potential problems in her/his codes.
What is a SQL Monitor ACTIVE report?
When choosing report type ACTIVE, you will receive an (inter)active SQL monitoring report. In addition you will be able to hover over the bar graphs, as well as other parts of the report, which highlight what the usage corresponds to. Some information such as explain plan, metrics and plan histograms is only shown when this type is selected. In Oracle Database 19c the format of SQL Monitor active reports has changed to Java JET technology and no longer uses Flash.
The following example shows an active report. Keep in mind that the report is active with clickable pages and that you can send it over to colleagues and experts who can investigate further.
The script to generate an ACTIVE report can be such as following:
set trimspool on
set trim on
set pagesize 0
set linesize 32767
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sql_monitor.report_sql_monitor(report_level=>'ALL',type=>'ACTIVE')
from dual;
Spool off
Where can I find SQL Monitoring in SQL Developer?
Use the menu item "Tools =>Real Time SQL Monitor" to receive the list of monitored statements. Then click on the statement you want to analyze. You will get a detail page with plan statistics, plan, metrics etc.
On the left side (see Image 4) you can save the report e.g. as HTML or as ACTIVE report.
If you haven't used perfhub script and package yet, give it a try!
................................
Regards,
Alireza Kamrani
Senior RDBMS Consultant.
