oracle 如何可靠地获取查询的 SQL_ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42800041/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reliably get the SQL_ID of a query
提问by YoYo
I know this might seem a simple question - for which you might think existing answers exist. However ...
我知道这似乎是一个简单的问题 - 您可能认为存在现有答案。然而 ...
Understand that I want it be reasonable in performance, so it allows to be logged for every single query executed - or at least the big ones - without much overhead.
理解我希望它在性能上是合理的，所以它允许记录每个执行的查询 - 或者至少是大查询 - 没有太多开销。
My first idea was this query:
我的第一个想法是这个查询：
select sid,serial#,prev_sql_id from v$session where audsid=userenv('sessionid');
My idea was if I run this right after my target query, I will capture the correct sql_idthrough prev_sql_id.
我的想法是，如果我在目标查询之后立即运行它，我将sql_id通过 prev_sql_id捕获正确的。
However ... I was not ... I was getting a different SQL ... apparently in between my target SELECT statement and the query for prev_sql_id, something else ran. In my case Auditing is enabled, and I was capturing the insert into the SYS.AUD$table. No good.
但是......我不是......我得到了一个不同的SQL......显然在我的目标SELECT语句和查询之间prev_sql_id运行了其他东西。在我的情况下启用了审计，并且我正在将插入捕获到SYS.AUD$表中。不好。
As my main purpose for this attempt was to capture the execution plan for the query (as it was executed and captured by the shared pool), I thought that instead I can simply run this query:
由于我这次尝试的主要目的是捕获查询的执行计划（因为它被共享池执行和捕获），我认为我可以简单地运行这个查询：
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
Documentation states that with NULLSQL_IDas parameter, it will run the explain plan on the most recent query ran. I was hoping that this would take care of earlier issues. However ... I got the plan for the exact same insert into the SYS.AUD$table.
文档指出，使用NULLSQL_IDas 参数，它将在最近运行的查询上运行解释计划。我希望这能解决早期的问题。但是......我得到了将完全相同的插入SYS.AUD$表的计划。
You might say, ok, then just simply put a comment in your query that allows to easily capture the SQL_ID, like in following:
您可能会说，好的，那么只需在您的查询中添加一条评论即可轻松捕获SQL_ID，如下所示：
SELECT /* SQL: 1234-12' */ FROM DUAL;
Then I can try to find the SQL_ID as follows:
然后我可以尝试查找 SQL_ID 如下：
SELECT * FROM V$SQLAREA WHERE sql_text like '%SQL: 1234-12%';
That will give me several possible candidates, of which the V$SQLAREAquery itself is also included. The problem here is that I will need to randomize every query ran, which would cause me to always have a hard-parse.
这会给我几个可能的候选人，其中V$SQLAREA查询本身也包括在内。这里的问题是我需要随机化每个运行的查询，这会导致我总是有一个硬解析。
I have tried other solutions where I go through history, but that comes at a much bigger cost. I have tried to search other solutions. They all seem to lag in some way.
我曾尝试过其他解决方案，以了解历史，但这要付出更大的代价。我试图搜索其他解决方案。他们似乎都在某种程度上滞后了。
Related Articles:
相关文章：
回答by Lukasz Szozda
You could use new SQL*Plus option:
您可以使用新的SQL*Plus 选项：
SET FEEDBACK ON SQL_ID;
SQL_ID returns the sql_id for the SQL or PL/SQL statements that are executed.The sql_id will be assigned to the predefined variable _SQL_ID. You can use this predefined variable to debug the SQL statement that was executed. The variable can be used like any other predefined variable, such as _USER and _DATE.
SQL_ID 返回执行的 SQL 或 PL/SQL 语句的 sql_id。sql_id 将分配给预定义的变量 _SQL_ID。您可以使用此预定义变量来调试已执行的 SQL 语句。该变量可以像任何其他预定义变量一样使用，例如 _USER 和 _DATE。
SQL> SET FEEDBACK ON SQL_ID
SQL> SELECT * FROM DUAL;
D
-
X
1 row selected.
SQL_ID: a5ks9fhw2v9s1
--
SQL> SELECT sql_text FROM v$sql WHERE sql_id = '&_sql_id';
SQL_TEXT
-----------------------------------------------------
SELECT * FROM DUAL
1 row selected.
