SELECT
S.LAST_ACTIVE_TIME,
S.MODULE,
S.SQL_FULLTEXT,
S.SQL_PROFILE,
S.EXECUTIONS,
S.LAST_LOAD_TIME,
S.PARSING_USER_ID,
S.SERVICE
FROM
SYS.V_$SQL S,
SYS.ALL_USERS U
WHERE
S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('oracle user name here')
ORDER BY TO_DATE(S.LAST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS') desc;
Step1. connect to DB with an admin user using PLSQL or sqldeveloper or any other query interface
Step2. run the script bellow; in the S.SQL_TEXT column, you will see the executed queries
SELECT
S.LAST_ACTIVE_TIME,
S.MODULE,
S.SQL_FULLTEXT,
S.SQL_PROFILE,
S.EXECUTIONS,
S.LAST_LOAD_TIME,
S.PARSING_USER_ID,
S.SERVICE
FROM
SYS.V_$SQL S,
SYS.ALL_USERS U
WHERE
S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('oracle user name here')
ORDER BY TO_DATE(S.LAST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS') desc;
The only issue with this is that I can't find a way to show the input parameters values(for function calls), but at least we can see what is ran in Oracle and the order of it without using a specific tool.
当然，您还可以使用Explain PLAN FOR、TRACE 工具和大量其他工具化方法。企业管理器中有一些针对最昂贵的 SQL 查询的报告。您还可以搜索缓存中保存的最近查询。
You can use The Oracle Enterprise Manager to monitor the active sessions, with the query that is being executed, its execution plan, locks, some statistics and even a progress bar for the longer tasks.
And, of course you can also use Explain PLAN FOR, TRACE tool and tons of other ways of instrumentalization. There are some reports in the enterprise manager for the top most expensive SQL Queries. You can also search recent queries kept on the cache.
alter session set events '10046 trace name context forever, level 12';
--turn off：
alter session set events '10046 trace name context off';
-- 包含原始跟踪信息的文件将位于：
select value from v$parameter p
where name='user_dump_dest'
-- 文件名（*.trc）将包含 spid：
select p.spid from v$session s, v$process p
where s.paddr=p.addr
and ...your_search_params...
--您也可以自己设置名称：
alter session set tracefile_identifier='UniqueString';
--最后，使用 TKPROF 使跟踪文件更具可读性：
C:\ORACLE\admin\databaseSID\udump>
C:\ORACLE\admin\databaseSID\udump>tkprof my_trace_file.trc output=my_file.prf
TKPROF: Release 9.2.0.1.0 - Production on Wed Sep 22 18:05:00 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
C:\ORACLE\admin\databaseSID\udump>
--查看跟踪文件的状态 使用：
set serveroutput on size 30000;
declare
ALevel binary_integer;
begin
SYS.DBMS_SYSTEM.Read_Ev(10046, ALevel);
if ALevel = 0 then
DBMS_OUTPUT.Put_Line('sql_trace is off');
else
DBMS_OUTPUT.Put_Line('sql_trace is on');
end if;
end;
/
alter session set timed_statistics=true --if want to trace your own session
-- must be big enough:
select value from v$parameter p
where name='max_dump_file_size'
-- Find out sid and serial# of session you interested in:
select sid, serial# from v$session
where ...your_search_params...
--you can begin tracing with 10046 event, the fourth parameter sets the trace level(12 is the biggest):
begin
sys.dbms_system.set_ev(sid, serial#, 10046, 12, '');
end;
--turn off tracing with setting zero level:
begin
sys.dbms_system.set_ev(sid, serial#, 10046, 0, '');
end;
/*possible levels: 0 - turned off 1 - minimal level. Much like set sql_trace=true 4 - bind variables values are added to trace file 8 - waits are added 12 - both bind variable values and wait events are added */
--same if you want to trace your own session with bigger level:
alter session set events '10046 trace name context forever, level 12';
--turn off:
alter session set events '10046 trace name context off';
--file with raw trace information will be located:
select value from v$parameter p
where name='user_dump_dest'
--name of the file(*.trc) will contain spid:
select p.spid from v$session s, v$process p
where s.paddr=p.addr
and ...your_search_params...
--also you can set the name by yourself:
alter session set tracefile_identifier='UniqueString';
--finally, use TKPROF to make trace file more readable:
C:\ORACLE\admin\databaseSID\udump>
C:\ORACLE\admin\databaseSID\udump>tkprof my_trace_file.trc output=my_file.prf
TKPROF: Release 9.2.0.1.0 - Production on Wed Sep 22 18:05:00 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
C:\ORACLE\admin\databaseSID\udump>
--to view state of trace file use:
set serveroutput on size 30000;
declare
ALevel binary_integer;
begin
SYS.DBMS_SYSTEM.Read_Ev(10046, ALevel);
if ALevel = 0 then
DBMS_OUTPUT.Put_Line('sql_trace is off');
else
DBMS_OUTPUT.Put_Line('sql_trace is on');
end if;
end;
/
It's a Tools for Oracle to capture queries executed similar to the SQL Server Profiler. Indispensable tool for the maintenance of applications that use this database server.
you can download it from the official site iacosoft.com
Try PL/SQL Developer it has a nice user friendly GUI interface to the profiler. It's pretty nice give the trial a try. I swear by this tool when working on Oracle databases.
Seeing as I've just voted a recent question as a duplicate and pointed in this direction . . .
A couple more - in SQL*Plus - SET AUTOTRACE ON - will give explain plan and statistics for each statement executed.
TOAD also allows for client side profiling.
The disadvantage of both of these is that they only tell you the execution plan for the statement, but not how the optimiser arrived at that plan - for that you will need lower level server side tracing.
Another important one to understand is Statspack snapshots - they are a good way for looking at the performance of the database as a whole. Explain plan, etc, are good at finding individual SQL statements that are bottlenecks. Statspack is good at identifying the fact your problem is that a simple statement with a good execution plan is being called 1 million times in a minute.
select aft.hash_value
from sql_exec_after aft
left outer join sql_exec_before bef
on aft.hash_value = bef.hash_value
where aft.executions > bef.executions
or bef.executions is null;
/
select hash_value, sql_text
from v$sqltext
where hash_value in (
select aft.hash_value
from sql_exec_after aft
left outer join sql_exec_before bef
on aft.hash_value = bef.hash_value
where aft.executions > bef.executions
or bef.executions is null;
)
order by
hash_value, piece
/
。 整理完成后，不要忘记删除快照表：
drop table sql_exec_before
/
drop table sql_exec_after
/
The Catch is Capture all SQL run between two points in time. Like the way SQL Server also does.
There are situations where it is useful to capture the SQL that a particular user is running in the database. Usually you would simply enable session tracing for that user, but there are two potential problems with that approach.
The first is that many web based applications maintain a pool of persistent database connections which are shared amongst multiple users.
The second is that some applications connect, run some SQL and disconnect very quickly, making it tricky to enable session tracing at all (you could of course use a logon trigger to enable session tracing in this case).
A quick and dirty solution to the problem is to capture all SQL statements that are run between two points in time.
The following procedure will create two tables, each containing a snapshot of the database at a particular point. The tables will then be queried to produce a list of all SQL run during that period.
If possible, you should do this on a quiet development system - otherwise you risk getting way too much data back.
Take the first snapshot Run the following sql to create the first snapshot:
create table sql_exec_before as
select executions,hash_value
from v$sqlarea
/
Get the user to perform their task within the application.
Take the second snapshot.
create table sql_exec_after as
select executions, hash_value
from v$sqlarea
/
Check the results Now that you have captured the SQL it is time to query the results.
This first query will list all query hashes that have been executed:
select aft.hash_value
from sql_exec_after aft
left outer join sql_exec_before bef
on aft.hash_value = bef.hash_value
where aft.executions > bef.executions
or bef.executions is null;
/
This one will display the hash and the SQL itself: set pages 999 lines 100 break on hash_value
select hash_value, sql_text
from v$sqltext
where hash_value in (
select aft.hash_value
from sql_exec_after aft
left outer join sql_exec_before bef
on aft.hash_value = bef.hash_value
where aft.executions > bef.executions
or bef.executions is null;
)
order by
hash_value, piece
/
5. Tidy up Don't forget to remove the snapshot tables once you've finished:
drop table sql_exec_before
/
drop table sql_exec_after
/
Oracle, along with other databases, analyzes a given query to create an execution plan. This plan is the most efficient way of retrieving the data.
Oracle provides the 'explain plan' statement which analyzes the query but doesn't run it, instead populating a special table that you can query (the plan table).
The syntax (simple version, there are other options such as to mark the rows in the plan table with a special ID, or use a different plan table) is:
explain plan for <sql query>
The analysis of that data is left for another question, or your further research.
Apparently there is no small simple cheap utility that would help performing this task. There is however 101 way to do it in a complicated and inconvenient manner.
发布评论
评论（11）
我找到了一个简单的解决方案
Step1。使用 PLSQL 或 sqldeveloper 或任何其他查询接口以管理员用户连接到数据库
第 2 步。运行下面的脚本；在 S.SQL_TEXT 列中，您将看到执行的查询
唯一的问题是我找不到显示输入参数值（用于函数调用）的方法，但至少我们可以看到 Oracle 中运行的内容及其顺序，而无需使用特定工具。
I found an easy solution
Step1. connect to DB with an admin user using PLSQL or sqldeveloper or any other query interface
Step2. run the script bellow; in the S.SQL_TEXT column, you will see the executed queries
The only issue with this is that I can't find a way to show the input parameters values(for function calls), but at least we can see what is ran in Oracle and the order of it without using a specific tool.
您可以使用 Oracle Enterprise Manager 来监视活动会话，包括正在执行的查询、其执行计划、锁、一些统计信息，甚至是较长任务的进度条。
请参阅：http://download.oracle。 com/docs/cd/B10501_01/em.920/a96674/db_admin.htm#1013955
转到实例 ->会话并观察每个会话的 SQL 选项卡。
还有其他方法。企业管理器只是将特殊视图中已有的内容涂上漂亮的颜色，如下所示：
http://www.oracle.com/pls/db92/db92。 Catalog_views?remark=homepage
当然，您还可以使用Explain PLAN FOR、TRACE 工具和大量其他工具化方法。企业管理器中有一些针对最昂贵的 SQL 查询的报告。您还可以搜索缓存中保存的最近查询。
You can use The Oracle Enterprise Manager to monitor the active sessions, with the query that is being executed, its execution plan, locks, some statistics and even a progress bar for the longer tasks.
See: http://download.oracle.com/docs/cd/B10501_01/em.920/a96674/db_admin.htm#1013955
Go to Instance -> sessions and watch the SQL Tab of each session.
There are other ways. Enterprise manager just puts with pretty colors what is already available in specials views like those documented here:
http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage
And, of course you can also use Explain PLAN FOR, TRACE tool and tons of other ways of instrumentalization. There are some reports in the enterprise manager for the top most expensive SQL Queries. You can also search recent queries kept on the cache.
--或者
-- 必须足够大：
-- 找出您感兴趣的会话的 sid 和序列号：
--您可以从 10046 事件开始跟踪，第四个参数设置跟踪级别（12 是最大）：
--通过设置零级别关闭跟踪：
/*可能的级别：
0 - 关闭
1 - 最低级别。很像设置 sql_trace=true
4 - 绑定变量值添加到跟踪文件
8 - 添加等待
12 - 添加绑定变量值和等待事件
*/
--如果您想以更大的级别跟踪自己的会话，则相同：
--turn off：
-- 包含原始跟踪信息的文件将位于：
-- 文件名（*.trc）将包含 spid：
--您也可以自己设置名称：
--最后，使用
TKPROF使跟踪文件更具可读性：--查看跟踪文件的状态 使用：
只是翻译的 http://www.sql.ru/faq/faq_topic.aspx?fid=389 原文更完整，但无论如何这比其他人发布的恕我直言要好
--or
-- must be big enough:
-- Find out sid and serial# of session you interested in:
--you can begin tracing with 10046 event, the fourth parameter sets the trace level(12 is the biggest):
--turn off tracing with setting zero level:
/*possible levels:
0 - turned off
1 - minimal level. Much like set sql_trace=true
4 - bind variables values are added to trace file
8 - waits are added
12 - both bind variable values and wait events are added
*/
--same if you want to trace your own session with bigger level:
--turn off:
--file with raw trace information will be located:
--name of the file(*.trc) will contain spid:
--also you can set the name by yourself:
--finally, use
TKPROFto make trace file more readable:--to view state of trace file use:
Just kind of translated http://www.sql.ru/faq/faq_topic.aspx?fid=389 Original is fuller, but anyway this is better than what others posted IMHO
GI Oracle Profiler v1.2
它是 Oracle 捕获执行的查询的工具，类似于SQL Server 探查器。
维护使用该数据库服务器的应用程序不可或缺的工具。
您可以从官方网站 iacosoft.com 下载它
GI Oracle Profiler v1.2
It's a Tools for Oracle to capture queries executed similar to the SQL Server Profiler.
Indispensable tool for the maintenance of applications that use this database server.
you can download it from the official site iacosoft.com
尝试一下 PL/SQL Developer，它有一个很好的用户友好的 GUI 界面来分析器。尝试一下真是太好了。在使用 Oracle 数据库时，我非常信赖这个工具。
http://www.allroundautomations.com/plsqldev.html?gclid=CM6pz8e04p0CFQjyDAodNXqPDw
Try PL/SQL Developer it has a nice user friendly GUI interface to the profiler. It's pretty nice give the trial a try. I swear by this tool when working on Oracle databases.
http://www.allroundautomations.com/plsqldev.html?gclid=CM6pz8e04p0CFQjyDAodNXqPDw
鉴于我刚刚将最近的一个问题投票为重复项并指出了这个方向。 。 。
还有一些 - 在 SQL*Plus 中 - SET AUTOTRACE ON - 将为每个执行的语句提供解释计划和统计信息。
TOAD 还允许进行客户端分析。
这两者的缺点是它们只告诉您语句的执行计划，而不告诉您优化器如何达到该计划 - 为此您将需要较低级别的服务器端跟踪。
另一个需要了解的重要内容是 Statspack 快照 - 它们是查看整个数据库性能的好方法。解释计划等擅长发现成为瓶颈的单个 SQL 语句。 Statspack 擅长识别您的问题是一个具有良好执行计划的简单语句在一分钟内被调用 100 万次。
Seeing as I've just voted a recent question as a duplicate and pointed in this direction . . .
A couple more - in SQL*Plus - SET AUTOTRACE ON - will give explain plan and statistics for each statement executed.
TOAD also allows for client side profiling.
The disadvantage of both of these is that they only tell you the execution plan for the statement, but not how the optimiser arrived at that plan - for that you will need lower level server side tracing.
Another important one to understand is Statspack snapshots - they are a good way for looking at the performance of the database as a whole. Explain plan, etc, are good at finding individual SQL statements that are bottlenecks. Statspack is good at identifying the fact your problem is that a simple statement with a good execution plan is being called 1 million times in a minute.
捕获点是捕获两个时间点之间运行的所有 SQL。就像 SQL Server 所做的那样。
在某些情况下，捕获特定用户在数据库中运行的 SQL 很有用。通常，您只需为该用户启用会话跟踪，但该方法存在两个潜在问题。
解决该问题的一个快速但肮脏的解决方案是捕获两个时间点之间运行的所有 SQL 语句。
以下过程将创建两个表，每个表都包含数据库在特定点的快照。然后将查询这些表以生成该期间运行的所有 SQL 的列表。
如果可能的话，您应该在安静的开发系统上执行此操作 - 否则您可能会面临获取太多数据的风险。
拍摄第一张快照
运行以下sql来创建第一个快照：
让用户在应用程序中执行他们的任务。
拍摄第二张快照。
检查结果
现在您已经捕获了 SQL，是时候查询结果了。
第一个查询将列出已执行的所有查询哈希：
该查询将显示哈希和 SQL 本身：
设置页数 999 行 100
在 hash_value 5 处中断
。
整理完成后，不要忘记删除快照表：
The Catch is Capture all SQL run between two points in time. Like the way SQL Server also does.
There are situations where it is useful to capture the SQL that a particular user is running in the database. Usually you would simply enable session tracing for that user, but there are two potential problems with that approach.
A quick and dirty solution to the problem is to capture all SQL statements that are run between two points in time.
The following procedure will create two tables, each containing a snapshot of the database at a particular point. The tables will then be queried to produce a list of all SQL run during that period.
If possible, you should do this on a quiet development system - otherwise you risk getting way too much data back.
Take the first snapshot
Run the following sql to create the first snapshot:
Get the user to perform their task within the application.
Take the second snapshot.
Check the results
Now that you have captured the SQL it is time to query the results.
This first query will list all query hashes that have been executed:
This one will display the hash and the SQL itself:
set pages 999 lines 100
break on hash_value
5.
Tidy up Don't forget to remove the snapshot tables once you've finished:
Oracle 与其他数据库一起分析给定的查询以创建执行计划。该计划是检索数据的最有效方法。
Oracle 提供了“
explain plan”语句，该语句分析查询但不运行它，而是填充一个可以查询的特殊表（计划表）。语法（简单版本，还有其他选项，例如使用特殊 ID 标记计划表中的行，或使用不同的计划表）是：
对该数据的分析留给另一个问题或您进一步的研究。
Oracle, along with other databases, analyzes a given query to create an execution plan. This plan is the most efficient way of retrieving the data.
Oracle provides the '
explain plan' statement which analyzes the query but doesn't run it, instead populating a special table that you can query (the plan table).The syntax (simple version, there are other options such as to mark the rows in the plan table with a special ID, or use a different plan table) is:
The analysis of that data is left for another question, or your further research.
有一个商业工具 FlexTracer 可用于跟踪 Oracle SQL 查询
There is a commercial tool FlexTracer which can be used to trace Oracle SQL queries
这是一个 Oracle 文档，解释如何跟踪 SQL 查询，包括几个工具（SQL Trace 和 tkprof）
链接
This is an Oracle doc explaining how to trace SQL queries, including a couple of tools (SQL Trace and tkprof)
link
显然，没有任何小型简单廉价的实用程序可以帮助执行此任务。然而，有 101 种方法可以以复杂且不方便的方式完成此操作。
下面的文章介绍了几个。大概还有几十个……
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
Apparently there is no small simple cheap utility that would help performing this task. There is however 101 way to do it in a complicated and inconvenient manner.
Following article describes several. There are probably dozens more...
http://www.petefinnigan.com/ramblings/how_to_set_trace.htm