PL,SQL中SQL语句10053创建方法

预计阅读时间：5分钟

《》介绍了使用alter session … 10053 trace和alter session … trace [SQL_Compiler.*]，以及DBMS_SQLDIAG包的存储过程DUMP_TRACE，三种生成10053跟踪文件的方法。

有朋友问到，

是否能生成一段PL/SQL包中的某条SQL对应的10053跟踪文件？

上面文章介绍了，11g引入了新的诊断事件方法，可以实现创建PL/SQL包中的某条具体SQL语句对应的跟踪文件。前提是需要知道SQL_ID。但要注意的是，SQL语句会执行硬解析操作。

如下创建测试package，

SQL create or replace package cal_total_sales as 
 2    function get_total_sales(n_cust_id NUMBER)
 3    return NUMBER;
 4  end cal_total_sales;
 5  /
 
SQL create or replace package body cal_total_sales as 
2         function get_total_sales(n_cust_id NUMBER) return NUMBER is
3           t_sales NUMBER(8,2);
4         begin
5           select sum(amount_sold)
6           into t_sales
7           from sales
8           where cust_id = n_cust_id;
9           return t_sales;
10       
11         exception
12           when NO_DATA_FOUND then
13             return null;
14           when TOO_MANY_ROWS then
15             return null;
16         end;
17       end cal_total_sales;
18       /

其中我们需要创建这条SQL对应的10053，

select sum(amount_sold) from sales where cust_id = n_cust_id;

通过v$sql视图，可以得出sql_id，假设是3bnxc7htmf2ad，

select sql_id, sql_text from v$sql where sql_text like 'SELECT SUM(AMOUNT_SOLD) FROM%';

使用alter session … trace [SQL_Compiler.*]，加上sql:sql_id，再执行此package，

SQL alter session set tracefile_identifier = 'MYTEST';
Session altered.

SQL alter session set events 'trace[rdbms.SQL_Optimizer.*][sql:3bnxc7htmf2ad]';
Session altered.

SQL declare
 2    t_sales number(8,2);
 3  begin
 4    t_sales := cal_total_sales.get_total_sales(1000);
 5    dbms_output.put_line('total_sales for customer is: ' || t_sales);
 6  end;
 7  /
total_sales for customer is: 10000.01
PL/SQL procedure successfully completed.

SQL alter session set envents 'trace [SQL_Optimizer.*] off';
Session altered.

就可以在USER_DUMP_DEST指定路径中，找到为这条SQL生成的10053跟踪文件，

SQL NAME          TYPE     VALUE
---------------- ----------- ---------------------------------------------------------
user_dump_dest   string    /DATA/oracle/u01/app/oracle/diag/rdbms/bisal/BISAL/trace

SQL !ls -lrht /DATA/oracle/u01/app/oracle/diag/rdbms/bisal/BISAL/trace
...
BISAL_ora_10000_MYTEST.trc

参考文献：

《How do I capture a 10053 trace for a SQL statement called in a PL/SQL package?》

https://blogs.oracle.com/optimizer/how-do-i-capture-a-10053-trace-for-a-sql-statement-called-in-a-plsql-package

如果您觉得本文有帮助，欢迎关注转发：bisal的个人杂货铺