如何查看正在执行的实际 Oracle SQL 语句

Question

提问by mdy

I'm using a custom-built inhouse application that generates a standard set of reports on a weekly basis. I have no access to the source code of the application, and everyone tells me there is no documentation available for the Oracle database schema. (Aargh!)

我正在使用一个定制的内部应用程序，它每周生成一组标准的报告。我无法访问应用程序的源代码，每个人都告诉我没有适用于 Oracle 数据库架构的文档。（啊！）

I've been asked to define the specs for a variant of an existing report (e.g., apply additional filters to constrain the data set, and modify the layout slightly). This sounds simple enough in principle, but is difficult without any existing documentation.

我被要求为现有报告的变体定义规范（例如，应用额外的过滤器来限制数据集，并稍微修改布局）。这在原则上听起来很简单，但如果没有任何现有文档，就很难。

It's my understanding that the logs can't help me because the report only queries the database; it does not actually insert, delete, or update database values, so there is nothing to log (is this correct?).

我的理解是日志对我没有帮助，因为报告只查询数据库；它实际上并不插入、删除或更新数据库值，因此没有任何记录（这是正确的吗？）。

So my question is this: is there a tool or utility (Oracle or otherwise) that I can use to see the actual SQL statement that is being executed while the report generation job is still running? I figure, if I can see what tables are actually being accessed to produce the existing report, I'll have a very good starting point for exploring the schema and determining the correct SQL to use for my own report.

所以我的问题是：是否有工具或实用程序（Oracle 或其他）可以用来查看在报告生成作业仍在运行时正在执行的实际 SQL 语句？我想，如果我能看到实际访问了哪些表来生成现有报告，我将有一个很好的起点来探索模式并确定用于我自己的报告的正确 SQL。

Answer 1

采纳答案by Mark Nold

On the data dictionary side there are a lot of tools you can use to such as Schema Spy

在数据字典方面，您可以使用很多工具，例如Schema Spy

To look at what queries are running look at views sys.v_$sql and sys.v_$sqltext. You will also need access to sys.all_users

要查看正在运行的查询，请查看视图 sys.v_$sql 和 sys.v_$sqltext。您还需要访问 sys.all_users

One thing to note that queries that use parameters will show up once with entries like

需要注意的一件事是，使用参数的查询将显示一次，其中包含以下条目

and TABLETYPE=':b16'

while others that dont will show up multiple times such as:

而其他人不会出现多次，例如：

and TABLETYPE='MT'

An example of these tables in action is the following SQL to find the top 20 diskread hogs. You could change this by removing the WHERE rownum <= 20and maybe add ORDER BY module. You often find the module will give you a bog clue as to what software is running the query (eg: "TOAD 9.0.1.8", "JDBC Thin Client", "runcbl@somebox (TNS V1-V3)" etc)

使用这些表的一个示例是以下 SQL，用于查找前 20 个磁盘读取猪。您可以通过删除WHERE rownum <= 20并添加ORDER BY 模块来更改此设置。您经常会发现该模块会为您提供有关运行查询的软件的线索（例如：“TOAD 9.0.1.8”、“JDBC 瘦客户端”、“runcbl@somebox (TNS V1-V3)”等）

SELECT 
 module, 
 sql_text, 
 username, 
 disk_reads_per_exec, 
 buffer_gets, 
 disk_reads, 
 parse_calls, 
 sorts, 
 executions, 
 rows_processed, 
 hit_ratio, 
 first_load_time, 
 sharable_mem, 
 persistent_mem, 
 runtime_mem, 
 cpu_time, 
 elapsed_time, 
 address, 
 hash_value 
FROM 
  (SELECT
   module, 
   sql_text , 
   u.username , 
   round((s.disk_reads/decode(s.executions,0,1, s.executions)),2)  disk_reads_per_exec, 
   s.disk_reads , 
   s.buffer_gets , 
   s.parse_calls , 
   s.sorts , 
   s.executions , 
   s.rows_processed , 
   100 - round(100 *  s.disk_reads/greatest(s.buffer_gets,1),2) hit_ratio, 
   s.first_load_time , 
   sharable_mem , 
   persistent_mem , 
   runtime_mem, 
   cpu_time, 
   elapsed_time, 
   address, 
   hash_value 
  FROM
   sys.v_$sql s, 
   sys.all_users u 
  WHERE
   s.parsing_user_id=u.user_id 
   and UPPER(u.username) not in ('SYS','SYSTEM') 
  ORDER BY
   4 desc) 
WHERE
 rownum <= 20;

Note that if the query is long .. you will have to query v_$sqltext. This stores the whole query. You will have to look up the ADDRESS and HASH_VALUE and pick up all the pieces. Eg:

请注意，如果查询很长.. 你将不得不查询 v_$sqltext。这存储了整个查询。您将不得不查找 ADDRESS 和 HASH_VALUE 并拿起所有碎片。例如：

SELECT
 *
FROM
 sys.v_$sqltext
WHERE
 address = 'C0000000372B3C28'
 and hash_value = '1272580459'
ORDER BY 
 address, hash_value, command_type, piece
;

Answer 2

回答by Ethan Post

Sorry for the short answer but it is late. Google "oracle event 10046 sql trace". It would be best to trace an individual session because figuring which SQL belongs to which session from v$sql is no easy if it is shared sql and being used by multiple users.

抱歉回复太短，但已经晚了。谷歌“oracle 事件 10046 sql 跟踪”。最好跟踪单个会话，因为如果它是共享 sql 并被多个用户使用，那么从 v$sql 确定哪个 SQL 属于哪个会话并不容易。

If you want to impress your Oracle DBA friends, learn how to set an oracle trace with event 10046, interpret the meaning of the wait events and find the top cpu consumers.

如果您想给您的 Oracle DBA 朋友留下深刻印象，请学习如何使用事件 10046 设置 oracle 跟踪，解释等待事件的含义并找到顶级 cpu 消费者。

Quest had a free product that allowed you to capture the SQL as it went out from the client side but not sure if it works with your product/version of Oracle. Google "quest oracle sql monitor" for this.

Quest 有一个免费产品，它允许您在 SQL 从客户端发出时捕获它，但不确定它是否适用于您的产品/版本的 Oracle。为此，谷歌“寻求 oracle sql 监视器”。

Good night.

晚安。

Answer 3

回答by David Crow

I think the V$SQLAREAtable contains what you're looking for (see columns SQL_TEXTand SQL_FULLTEXT).

我认为V$SQLAREA表包含您要查找的内容（请参阅列SQL_TEXT和SQL_FULLTEXT）。

Answer 4

回答by Hobo

Yep, that's definitely possible. The v$sql views contain that info. Something like this piece of codeshould point you in the right direction. I haven't tried that specific piece of code myself - nowhere near an Oracle DB right now.

是的，这绝对有可能。v$sql 视图包含该信息。像这段代码这样的东西应该指向正确的方向。我自己还没有尝试过那段特定的代码——现在还远不及 Oracle DB。

[Edit] Damn two other answers already. Must type faster next time ;-)

[编辑] 该死的另外两个答案。下次必须打得更快;-)

Answer 5

回答by EvilTeach

-- i use something like this, with concepts and some code stolen from asktom.
-- suggestions for improvements are welcome

- 我使用这样的东西，概念和一些从asktom窃取的代码。
-- 欢迎提出改进建议

WITH
sess AS
(
SELECT *
FROM V$SESSION
WHERE USERNAME = USER
ORDER BY SID
)
SELECT si.SID,
si.LOCKWAIT,
si.OSUSER,
si.PROGRAM,
si.LOGON_TIME,
si.STATUS,
(
SELECT ROUND(USED_UBLK*8/1024,1)
FROM V$TRANSACTION,
sess
WHERE sess.TADDR = V$TRANSACTION.ADDR
AND sess.SID = si.SID

) rollback_remaining,

(
SELECT (MAX(DECODE(PIECE, 0,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 1,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 2,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 3,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 4,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 5,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 6,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 7,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 8,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 9,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 10,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 11,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 12,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 13,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 14,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 15,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 16,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 17,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 18,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 19,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 20,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 21,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 22,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 23,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 24,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 25,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 26,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 27,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 28,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 29,SQL_TEXT,NULL)))
FROM V$SQLTEXT_WITH_NEWLINES
WHERE ADDRESS = SI.SQL_ADDRESS AND
PIECE < 30
) SQL_TEXT
FROM sess si;

WITH
sess AS
(
SELECT *
FROM V$SESSION
WHERE USERNAME = USER
ORDER BY SID
)
SELECT si.SID,
si.LOCKWAIT,
si.OSUSER,
si.PROGRAM,
si.LOGON_TIME,
si.STATUS,
(
SELECT ROUND(USED_UBLK*8) /1024,1)
FROM V$TRANSACTION,
sess
WHERE sess.TADDR = V$TRANSACTION.ADDR
AND sess.SID = si.SID

) rollback_remaining,

(
SELECT (MAX(DECODE(PIECE, 0,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 1,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 2,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 3,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 4,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 5,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 6,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 7,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 8,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 9,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 10,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 11,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 12,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 13,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 14,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 15,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 16,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 17,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 18,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 19,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 20,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 21,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 22,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 23,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 24,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 25,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 26,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 27,SQL_TEXT,NULL)) ||
MAX(解码(PIECE, 28,SQL_TEXT,NULL)) ||
MAX(DECODE(PIECE, 29,SQL_TEXT,NULL)))
FROM V$SQLTEXT_WITH_NEWLINES
WHERE ADDRESS = SI.SQL_ADDRESS AND
PIECE < 30
) SQL_TEXT
FROM sess ;

Answer 6

回答by metadave

I had (have) a similar problem in a Java application. I wrote a JDBC driver wrapper around the Oracle driver so all output is sent to a log file.

我在 Java 应用程序中遇到了类似的问题。我围绕 Oracle 驱动程序编写了一个 JDBC 驱动程序包装器，以便将所有输出发送到日志文件。

如何查看正在执行的实际 Oracle SQL 语句

提问by mdy

采纳答案by Mark Nold

回答by Ethan Post

回答by David Crow

回答by Hobo

回答by EvilTeach

回答by metadave

相关推荐

最近更新

标签

如何查看正在执行的实际 Oracle SQL 语句

提问by mdy

采纳答案by Mark Nold

回答by Ethan Post

回答by David Crow

回答by Hobo

回答by EvilTeach

回答by metadave

相关推荐

SQL 从sql查询的结果中删除重复记录

SQL 标记数据库设计

SQL 如何在 Group By 中使用 Case Statement 列

如何在 SQL Server 中创建外键？

相关推荐

最近更新

标签