oracle query execution sql

In database management, Oracle is a very commonly used and important database. In operation and maintenance, it is often necessary to monitor the execution of the database, especially the executed SQL statements, which can greatly help us track and optimize SQL performance. In this article, we will introduce how to query the SQL statements being executed in the Oracle database.

Tools for querying active SQL statements

Oracle provides some tools to help us query active SQL statements. These tools include:

1. Oracle Enterprise Manager (OEM) - This is a visualization tool provided by Oracle, through which we can easily view the execution of the database, including active SQL statements.
2. Oracle SQL Developer - This is a popular development tool that includes a practical monitoring and analysis tool that can query executing SQL statements and analyze SQL performance.
3. SQL scripts that come with Oracle - Oracle provides some SQL scripts for querying active SQL statements. These scripts can be downloaded from the Oracle website.

SQL script to query the SQL statement being executed

In the Oracle database, we can use the two system views V$SESSION and V$SQL to query the active SQL statement .

The V$SESSION view contains all currently active session information, including the SQL_ID of each session, which is the identifier of the SQL statement being executed by each session.

V$SQL view contains information about all compiled SQL statements, including SQL_ID, SQL_TEXT, etc.

Here are some SQL scripts for querying the SQL statement being executed:

1. Querying the SQL_ID of the SQL statement being executed

SELECT s. sid, s.serial#, s.sql_id, s.sql_child_number, t.sql_text
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.status = 'ACTIVE'
AND s.username <> 'SYS';

This script will return the SQL_ID and corresponding SQL text executed by all active sessions.

2. Query the detailed information of the SQL statement being executed

SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t .sql_text,
s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.status = 'ACTIVE'
AND s.username <> 'SYS';

This script will return the SQL_ID executed by the active session and detailed SQL information, including user name , status, recent running time, program name, Action, SQL text, etc.

3. Query the SQL statements executed by a user

SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t .sql_text,
s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.status = 'ACTIVE'
AND s.username = 'your_username';

This script will return the SQL statement being executed by a user and the corresponding detailed information.

4. Query the first executed SQL statement

SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t.sql_text ,
s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.STATUS = 'ACTIVE'
AND s.username <> 'SYS'
AND s.sql_id IN (
SELECT MIN(sql_id)
FROM v$session
WHERE username <> 'SYS'
AND STATUS = 'ACTIVE'
GROUP BY username
);

This script will return the first executed SQL statement, including user name and status , recent running time, program name, Action, SQL text, etc.

5. Query the last executed SQL statement

SELECT s.sid, s.serial#, s.username, s.status, t.sql_id, t.sql_text,
s.last_call_et, s.logon_time, s.program, s.module, s.action
FROM v$session s, v$sqltext_with_newlines t
WHERE s.sql_id = t.sql_id
AND s.STATUS = 'ACTIVE'
AND s.username <> 'SYS'
AND s.sql_id IN (
SELECT MAX(sql_id)
FROM v$session
WHERE username <> 'SYS'
AND STATUS = 'ACTIVE'
GROUP BY username
);

This script will return the last executed SQL statement, including user name, status, recent Running time, program name, Action, SQL text, etc.

Summary

In Oracle database management, we often need to query the SQL statements being executed. Oracle provides system views for querying active SQL statements. By querying these views and using SQL scripts, we can easily query active SQL statements and related information to optimize and monitor SQL performance. At the same time, Oracle Enterprise Manager and Oracle SQL Developer also provide the function of querying SQL statements, which can be selected and used according to personal needs.

The above is the detailed content of oracle query execution sql. For more information, please follow other related articles on the PHP Chinese website!