blocks|key|1521130|text|Oracle与其他数据库一起分析给定的查询以创建执行计划。此计划是检索数据的最有效方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1521131|Oracle提供了'explain+plan‘语句，该语句分析查询，但不运行它，而是填充一个您可以查询的特殊表(+plan表)。|offset|length|style|CODE|1521132|语法(简单版本，还有其他选项，如用特殊ID标记计划表中的行，或使用不同的计划表)是：|1521133|explain+plan+for+<sql+query>|code-block|syntax|javascript|1521134|对这些数据的分析留给另一个问题，或者你的进一步研究。|1521135|entityMap^0|0|A|C|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|W|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|X|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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 '<code>explain plan</code>' 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:

<pre><code>explain plan for &lt;sql query&gt;
</code></pre>

The analysis of that data is left for another question, or your further research.

blocks|key|1736227|text|这是一个Oracle文档，解释了如何跟踪SQL查询，包括几个工具(SQL跟踪和tkprof)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1736228|link|offset|length|1736229|entityMap|0|LINK|mutability|MUTABLE|url|http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#sthref2019^0|0|0|4|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

This is an Oracle doc explaining how to trace SQL queries, including a couple of tools (SQL Trace and tkprof)

<a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#sthref2019" rel="nofollow noreferrer">link</a>

blocks|key|1521234|text|您可以使用Oracle+Enterprise+Manager监视活动会话，包括正在执行的查询、其执行计划、锁、一些统计信息，甚至是较长任务的进度条。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1521235|请参阅：http://download.oracle.com/docs/cd/B10501_01/em.920/a96674/db_admin.htm#1013955|offset|length|1521236|转到Instance+session并查看每个会话的->选项卡。|1521237|还有其他方法。企业管理器只是把已经在特殊视图中可用的东西加上了漂亮的颜色，就像这里记录的那些：http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage|1521238|当然，您还可以使用Explain+PLAN+FOR、TRACE工具和大量其他的工具化方法。企业管理器中有一些针对开销最大的SQL查询的报告。您还可以搜索保存在缓存中的最新查询。|1521239|entityMap|0|LINK|mutability|MUTABLE|url|http://download.oracle.com/docs/cd/B10501_01/em.920/a96674/db_admin.htm#1013955|1|http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage^0|0|4|27|0|0|0|1B|1T|1|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@$D|X|E|Y|1|Z]]|A|$]]|$1|F|3|G|5|6|7|10|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@$D|12|E|13|1|14]]|A|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]|T|$5|O|P|Q|A|$R|U]]]]

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: <a href="http://download.oracle.com/docs/cd/B10501_01/em.920/a96674/db_admin.htm#1013955" rel="nofollow noreferrer">http://download.oracle.com/docs/cd/B10501_01/em.920/a96674/db_admin.htm#1013955</a>

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: 
<a href="http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage" rel="nofollow noreferrer">http://www.oracle.com/pls/db92/db92.catalog_views?remark=homepage</a>

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.

blocks|key|1521262|text|试试PL/SQL+Developer，它有一个很好的用户友好的GUI界面。这是非常好的尝试。当我在Oracle数据库上工作时，我对这个工具非常信赖。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1521263|http://www.allroundautomations.com/plsqldev.html?gclid=CM6pz8e04p0CFQjyDAodNXqPDw|offset|length|1521264|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|29|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

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.

<a href="http://www.allroundautomations.com/plsqldev.html?gclid=CM6pz8e04p0CFQjyDAodNXqPDw" rel="noreferrer">http://www.allroundautomations.com/plsqldev.html?gclid=CM6pz8e04p0CFQjyDAodNXqPDw</a>

blocks|key|1736406|text|正如我刚刚投票给最近的一个问题一样，它是一个重复的问题，指向这个方向。。。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1736407|还有一些-在SQL*Plus中-将AUTOTRACE设置为ON+-将为执行的每个语句提供explain+plan和统计信息。|1736408|TOAD还允许客户端分析。|1736409|这两种方法的缺点都是它们只告诉您语句的执行计划，而不是优化器如何得出该计划-因此，您将需要较低级别的服务器端跟踪。|1736410|另一个需要理解的重要问题是Statspack快照--它们是查看数据库整体性能的好方法。Explain+plan等，擅长查找成为瓶颈的单个SQL语句。Statspack擅长识别这样一个事实，即您的问题是一个具有良好执行计划的简单语句立即被调用了100万次。|1736411|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

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.

blocks|key|1521422|text|有一个商业工具FlexTracer可以用来跟踪Oracle+SQL查询|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1521423|entityMap|0|LINK|mutability|MUTABLE|url|http://www.easymatica.com/flextracer/^0|7|A|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

There is a commercial tool <a href="http://www.easymatica.com/flextracer/" rel="nofollow noreferrer">FlexTracer</a> which can be used to trace Oracle SQL queries

blocks|key|1521493|text|alter+system+set+timed_statistics=true|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1521494|--或者|unstyled|1521495|alter+session+set+timed_statistics=true+--if+want+to+trace+your+own+session|1521496|--必须足够大：|1521497|select+value+from+v$parameter+p
where+name='max_dump_file_size'+|1521498|--查找您感兴趣的会话的sid和serial#：|1521499|+select+sid,+serial#+from+v$session
+where+...your_search_params...|1521500|--您可以从10046事件开始跟踪，第四个参数设置跟踪级别(12是最大的)：|1521501|+begin
++++sys.dbms_system.set_ev(sid,+serial#,+10046,+12,+'');
+end;|1521502|--关闭跟踪并将级别设置为零：|1521503|begin
+++sys.dbms_system.set_ev(sid,+serial#,+10046,+0,+'');
end;|1521504|/*possible级别:0-关闭1-最低级别。与set+sql_trace=true非常类似，4-将绑定变量值添加到跟踪文件8-添加等待12+-同时添加绑定变量值和等待事件*/|1521505|--如果你想用更大的级别跟踪你自己的会话，也一样：|1521506|alter+session+set+events+'10046+trace+name+context+forever,+level+12';|1521507|--关闭：|1521508|alter+session+set+events+'10046+trace+name+context+off';|1521509|--将找到包含原始跟踪信息的文件：|1521510|+select+value+from+v$parameter+p
+where+name='user_dump_dest'|1521511|--文件名(*.trc)将包含spid：|1521512|+select+p.spid+from+v$session+s,+v$process+p
+where+s.paddr=p.addr
+and+...your_search_params...|1521513|--您也可以自行设置名称：|1521514|alter+session+set+tracefile_identifier='UniqueString';+|1521515|--最后，使用TKPROF使跟踪文件更具可读性：|offset|length|style|CODE|1521516|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>|1521517|--要查看跟踪文件的使用状态：|1521518|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;
/|1521519|翻译后的http://www.sql.ru/faq/faq_topic.aspx?fid=389原版更完整，但无论如何这比其他人发布的要好。|1521520|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sql.ru/faq/faq_topic.aspx?fid=389^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|7|6|0|0|0|0|4|18|0|0^^$0|@$1|2|3|4|5|6|7|26|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|27|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|28|8|@]|9|@]|A|$B|C]]|$1|I|3|J|5|F|7|29|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|2A|8|@]|9|@]|A|$B|C]]|$1|M|3|N|5|F|7|2B|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|2C|8|@]|9|@]|A|$B|C]]|$1|Q|3|R|5|F|7|2D|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|2E|8|@]|9|@]|A|$B|C]]|$1|U|3|V|5|F|7|2F|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|2G|8|@]|9|@]|A|$B|C]]|$1|Y|3|Z|5|F|7|2H|8|@]|9|@]|A|$]]|$1|10|3|11|5|F|7|2I|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|2J|8|@]|9|@]|A|$B|C]]|$1|14|3|15|5|F|7|2K|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|2L|8|@]|9|@]|A|$B|C]]|$1|18|3|19|5|F|7|2M|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|2N|8|@]|9|@]|A|$B|C]]|$1|1C|3|1D|5|F|7|2O|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|2P|8|@]|9|@]|A|$B|C]]|$1|1G|3|1H|5|F|7|2Q|8|@]|9|@]|A|$]]|$1|1I|3|1J|5|6|7|2R|8|@]|9|@]|A|$B|C]]|$1|1K|3|1L|5|F|7|2S|8|@$1M|2T|1N|2U|1O|1P]]|9|@]|A|$]]|$1|1Q|3|1R|5|6|7|2V|8|@]|9|@]|A|$B|C]]|$1|1S|3|1T|5|F|7|2W|8|@]|9|@]|A|$]]|$1|1U|3|1V|5|6|7|2X|8|@]|9|@]|A|$B|C]]|$1|1W|3|1X|5|F|7|2Y|8|@]|9|@$1M|2Z|1N|30|1|31]]|A|$]]|$1|1Y|3|-4|5|F|7|32|8|@]|9|@]|A|$]]]|1Z|$20|$5|21|22|23|A|$24|25]]]]

<pre><code>alter system set timed_statistics=true
</code></pre>

--or 

<pre><code>alter session set timed_statistics=true --if want to trace your own session
</code></pre>

-- must be big enough:

<pre><code>select value from v$parameter p
where name='max_dump_file_size' 
</code></pre>

-- Find out sid and serial# of session you interested in:

<pre><code> select sid, serial# from v$session
 where ...your_search_params...
</code></pre>

--you can begin tracing with 10046 event, the fourth parameter sets the trace level(12 is the biggest):

<pre><code> begin
 sys.dbms_system.set_ev(sid, serial#, 10046, 12, '');
 end;
</code></pre>

--turn off tracing with setting zero level:

<pre><code>begin
 sys.dbms_system.set_ev(sid, serial#, 10046, 0, '');
end;
</code></pre>

/*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:

<pre><code>alter session set events '10046 trace name context forever, level 12';
</code></pre>

--turn off:

<pre><code>alter session set events '10046 trace name context off';
</code></pre>

--file with raw trace information will be located:

<pre><code> select value from v$parameter p
 where name='user_dump_dest'
</code></pre>

--name of the file(*.trc) will contain spid:

<pre><code> select p.spid from v$session s, v$process p
 where s.paddr=p.addr
 and ...your_search_params...
</code></pre>

--also you can set the name by yourself:

<pre><code>alter session set tracefile_identifier='UniqueString'; 
</code></pre>

--finally, use <code>TKPROF</code> to make trace file more readable:

<pre><code>C:\ORACLE\admin\databaseSID\udump&gt;
C:\ORACLE\admin\databaseSID\udump&gt;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&gt;
</code></pre>

--to view state of trace file use:

<pre><code>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;
/
</code></pre>

Just kind of translated <a href="http://www.sql.ru/faq/faq_topic.aspx?fid=389" rel="noreferrer">http://www.sql.ru/faq/faq_topic.aspx?fid=389</a> Original is fuller, but anyway this is better than what others posted IMHO

blocks|key|1736628|text|我找到了一个简单的解决方案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1736629|Step1。使用PLSQL或sqldeveloper或任何其他查询界面，以管理员用户身份连接到数据库|1736630|Step2。运行下面的脚本；在S.SQL_TEXT列中，您将看到执行的查询|1736631|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;|code-block|syntax|javascript|1736632|唯一的问题是，我找不到一种方法来显示输入参数值(用于函数调用)，但至少我们可以看到在Oracle中运行的内容以及它的顺序，而不需要使用特定的工具。|1736633|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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

<pre><code>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;
</code></pre>

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.

blocks|key|1736668|text|GI+Oracle+Profiler+v1.2|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1736669|它是一种用于Oracle捕获查询的工具，类似于SQL+Server事件探查器。用于维护使用此数据库服务器的应用程序的不可或缺的工具。|1736670|你可以从官方网站iacosoft.com下载|1736671|entityMap|0|LINK|mutability|MUTABLE|url|http://www.iacosoft.com/home/tools.asp#PROFILER^0|0|N|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

<a href="http://www.iacosoft.com/home/tools.asp#PROFILER" rel="noreferrer">GI Oracle Profiler v1.2</a> 

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

blocks|key|1521733|text|显然，没有一个小的、简单的、便宜的实用程序可以帮助完成这项任务。然而，有101种方法可以以一种复杂和不方便的方式做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1521734|下面的文章描述了几个。可能还有几十个……http://www.petefinnigan.com/ramblings/how_to_set_trace.htm|offset|length|1521735|entityMap|0|LINK|mutability|MUTABLE|url|http://www.petefinnigan.com/ramblings/how_to_set_trace.htm^0|0|K|1M|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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...
<a href="http://www.petefinnigan.com/ramblings/how_to_set_trace.htm" rel="nofollow">http://www.petefinnigan.com/ramblings/how_to_set_trace.htm</a>

i work with sql server, but i must migrate to an application with Oracle DB.
for trace my application queries, in Sql Server i use wonderful Profiler tool. is there something of equivalent for Oracle?

Oracle: is there a tool to trace queries, like Profiler for sql server?

Oracle数据库

SQL Server

数据库

服务器

我使用sql server，但我必须迁移到使用Oracle DB的应用程序。为了跟踪我的应用程序查询，在Sql Server中，我使用了非常棒的Profiler工具。甲骨文有没有类似的东西？

问Oracle:有没有跟踪查询的工具，比如Profiler for sql server？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle:有没有跟踪查询的工具，比如Profiler for sql server？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle:有没有跟踪查询的工具，比如Profiler for sql server？
EN