如何查看正在执行的实际Oracle SQL语句 [英] How to see the actual Oracle SQL statement that is being executed

问题描述

我正在使用一个定制的内部应用程序，该应用程序每周生成一组标准报告.我无权访问该应用程序的源代码，并且每个人都告诉我没有适用于Oracle数据库模式的文档. (啊！)

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!)

我被要求为现有报告的变体定义规格(例如，应用其他过滤器来约束数据集，并略微修改布局).原则上，这听起来很简单，但是如果没有任何现有文档，这很难做到.

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?).

所以我的问题是:在报表生成作业仍在运行时，是否可以使用工具或实用程序(Oracle或其他方式)查看正在执行的实际SQL语句?我认为，如果可以看到实际上访问了哪些表以生成现有报告，那么我将有一个很好的起点来探索模式并确定要用于我自己的报告的正确SQL.

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.

推荐答案

在数据字典方面，您可以使用很多工具，例如架构间谍

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

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

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

需要注意的一件事是，使用参数的查询将与

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’

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

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 <= 20 and 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)

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;

请注意，如果查询很长..，则必须查询v_ $ sqltext.这将存储整个查询.您将必须查找ADDRESS和HASH_VALUE并提取所有内容.例如:

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:

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

这篇关于如何查看正在执行的实际Oracle SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！