How to use sql monitor to get a more detailed execution plan

This article mainly explains "how to use sql monitor to get a more detailed implementation plan". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use sql monitor to get a more detailed implementation plan".

Execute the SQL statement on the database, which can be queried by the following statement after or during execution

SQL_ID .

Select * from v$sql

Where sql_text like'% SELECT BOOK.DESCRIPTION AS%'

Order by first_load_time desc

(2)。 Execute the following statement on the database.

Select dbms_sqltune.report_sql_monitor (type= > 'TEXT', sql_id= >' 4t6jwa8nrg0dpads publication reportability level = > 'ALL') monitor_report from dual

Click on the query value of "HUGECLOB" to see the detailed execution plan in TEXT format (it is best to save

Open it with the ultraEdit tool after txt, you can see it more clearly, and it won't be posted here. Generally run on SQL

The result can be obtained within the last 1-3 minutes. After the SQL execution exceeds a certain time, the query cannot find the execution plan (which has been deleted).

Note: not all SQL will be monitor. If you don't see the execution plan, you can add it to the SQL.

Prompt / * + monitor*/ to force monitoring of SQL.

-

In versions prior to Oracle 11g, long-running SQL can be observed by monitoring v$session_longops, when a

If the execution time of each operation is more than 6 seconds, it will be recorded in v$session_longops, and the full table scan can usually be monitored.

Operations such as full index scanning, hash joins, parallel queries, etc., while in Oracle 11g, when SQL executes in parallel, it immediately

It is monitored in real time, or when a SQL single process is running, it will also be monitored if it consumes more than 5 seconds of CPU or Icano time.

Under control. The monitoring data is recorded in the v$sql_monitor view, or through the new package of Oracle 11g

DBMS_MONITOR to proactively perform monitoring deployments on SQL.

The information collected by v$sql_monitor is refreshed every second, which is close to real-time. When the execution of SQL is finished, the information will not be immediately removed from the

Delete it in v$sql_monitor and retain the execution plan information in the v$sql_plan_monitor view for at least 1 minute.

They are also updated every second, and when the SQL is finished, they are also retained for at least 1 minute. Check the execution plan type has

A variety of:

Type= > 'TEXT'

Type= > 'HTML'

Type= > 'ACTIVE'

-

Thank you for your reading, the above is the content of "how to use sql monitor to get a more detailed implementation plan". After the study of this article, I believe you have a deeper understanding of how to use sql monitor to get a more detailed implementation plan, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!