Getting the most out of Oracle SQL Monitor.pdf

384

18页

0次

2021-08-04

10墨值下载

2021/6/15

Getting the most out of Oracle SQL Monitor

https://sqlmaria.com/2017/08/01/getting-the-most-out-of-oracle-sql-monitor/

1/18

Getting the most out of Oracle SQL

Monitor

I’m often asked what is the best tool for viewing execution plans and for me, the

answer is always SQL Monitor (included in the Oracle Tuning Pack). It really is the

most invaluable tool if you need to determine what is happening during the

execution of any long-running SQL statements.

In order to help you get the very most out of using SQL Monitor, I wanted to share

with you some of the tips and tricks I‘ve learnt over the years from the original

Database Manageability team, especially Cecilia Grant!

So, why do I love it so much?

There are so many reasons to love SQL Monitor; it’s hard for me to know where to

begin. So, instead of giving you an exhaustive list I’ve put together a short video

to demonstrate how I use SQL Monitor (be sure to set your resolution to 720p).

2021/6/15

Getting the most out of Oracle SQL Monitor

https://sqlmaria.com/2017/08/01/getting-the-most-out-of-oracle-sql-monitor/

2/18

How do I control what statements are monitored?

By default, a SQL statement that either runs in parallel or has consumed at least 5

seconds of combined CPU and I/O time in a single execution will be monitored.

It is also possible to force monitoring to occur for any SQL statement by simply

adding the MONITOR hint to the statement.

SELECT /*+ MONITOR */ col1, col2, col3

FROM t1

WHERE col1=5;

If however, you can’t modify the SQL statement because it’s coming for a third

part application etc. you can still force monitoring to occur by setting the event

“sql_monitor” with a list of SQL_IDs for the statements you want to monitor at

the system level.

ALTER SYSTEM SET EVENTS 'sql_monitor [sql: 5hc07qvt8v737|sql: 9ht3ba3arrzt3] force=true';

of 18

10墨值下载

关注