Monitor the remaining time of SQL Execution, and monitor the remaining time of SQL

Source: Internet
Author: User

Monitor the remaining time of SQL Execution, and monitor the remaining time of SQL

-- Monitor the remaining time of SQL Execution. Do you know how long the running SQL takes? Do you know that the running SQL statement has completed about a few percent? Do you know how long it will take to complete running SQL statements? V $ SESSION_LONGOPS helps you. V $ SESSION_LONGOPS is described in <span style = "font-family: Arial, Helvetica, sans-serif;"> official documentation: </span> V $ SESSION_LONGOPS displays the status of varous operations that run for longer than 6 seconds (in absolute time ). these operations currently include existing backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release. to monitor query execution progress, you must be using the cost-based optimizer and you must: Set the TIMED_STATISTICS or SQL _TRACE parameters to trueGather statistics for your objects with the DBMS_STATS package -- meaning: count the statements with each operation time greater than 6 S-prerequisites: The cost-based optimizer cboTIMED_STATISTICS or SQL _TRACE is true. The object has statistical information. Test: select * from v $ version; -- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64 biselect * from v $ parameter where name like '% timed_statistics %' VALUETRUEselect last_analyzed from user_tables where table_name = 'lhj _ 1G 'LAST _ ANALYZED2013-12-10 PM 01:19:36 -- session1: drop table t; create table t as select * from lhj_1g; -- session2: select. username, a.tar get,. sid,. SERIAL #,. opname, round (. sofar * 100/. totalwork, 0) | '%' as progress, -- progress bar time_remaining second, -- remaining time: second trunc (. time_remaining/60, 2) minute, -- remaining time: minute B. SQL _text, B. LAST_ACTIVE_TIME from v $ session_longops a, v $ sqlarea B where. time_remaining <> 0 and. SQL _address = B. address and. SQL _hash_value = B. hash_value and. username = 'ggs'


Run the preceding statement again later:



After the SQL statement is executed or canceled, the preceding query is empty.


How to monitor the efficiency of SQL Execution? What tools are used? How to monitor?

Use SQL server profiler that comes with SQL server

Monitors the cpu usage and execution duration of SQL Execution.

How does one monitor the SQL Execution progress in oracle?

Pl/SQL tool session find the corresponding session. Generally, the application is JDBC Thin Client. Then click sqltext below to see the statement executed. You can refresh the statement to see the SQL changes.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.