传统上，如果你想捕获SQL语句的优化器跟踪文件（10053），你会在会话上发出alter session的命令来打开10053的跟踪，然后发出你想跟踪的SQL语句。一旦语句执行完成，就可以退出会话来关掉跟踪了。你可以在USER_DUMP_DEST 中设置的目录中看到该跟踪文件。
但是，如果你兴趣的SQL语句，实际上是作为PL/SQL包的一部分来调用的，该怎么办呢？
Oracle Database 11g引入了一个新的检测事件架构，它可以极大的简化为PL/SQL包中特定语句生成10053跟踪的工作。你只需要知道你感兴趣的SQL语句的SQL_ID。现在，你可以用打开对特定SQL_ID的跟踪，来代替对整个会话的跟踪。当语句在该会话中被执行时，Oracle会对相应的SQL捕获10053跟踪文件。要注意，为了产生10053的跟踪，目标SQL仍然需要发生硬解析才可以。让我们从创建一个名为 'cal_total_sales’的PL/SQL包来开始我们的示例。
我们关注的SQL与我们在初始示例中的SQL是相同的：
SELECT SUM(AMOUNT_SOLD) FROM SALES WHERE CUST_ID = :B1.
我们需要知道该SQL语句的SQL_ID来设置跟踪，其可以在v$SQL中找到。
现在，我们有了生成跟踪所需要的全部。
最后，你可以在USER_DUMP_DEST指定的目录中，查找带有你所设定名称的跟踪文件。
原文链接：https://blogs.oracle.com/optimizer/post/how-do-i-capture-a-10053-trace-for-a-sql-statement-called-in-a-plsql-package
How do I capture a 10053 trace for a SQL statement called in a PL/SQL package?
January 1, 2020 | 2 minute read
Maria Colgan
Distinguished Product Manager
Traditionally if you wanted to capture an Optimizer trace (10053) for a SQL statement you would issue an alter session command to switch on a 10053 trace for that entire session, and then issue the SQL statement you wanted to capture the trace for. Once the statement completed you would exit the session to disable the trace. You would then look in the USER_DUMP_DEST directory for the trace file.
But what if the SQL statement you were interested in was actually called as part of a PL/SQL package? Oracle Database 11g, introduced a new diagnostic events infrastructure, which greatly simplifies the task of generating a 10053 trace for a specific SQL statement in a PL/SQL package. All you will need to know is the SQL_ID for the statement you are interested in. Instead of turning on the trace event for the entire session you can now switch it on for a specific SQL ID. Oracle will then capture a 10053 trace for the corresponding SQL statement when it is issued in that session. Remember the SQL statement still has to be hard parsed for the 10053 trace to be generated. Let’s begin our example by creating a PL/SQL package called ‘cal_total_sales’.
The SQL statement we are interested in is the same as the one in our original example, SELECT SUM(AMOUNT_SOLD) FROM SALES WHERE CUST_ID = :B1. We need to know the SQL_ID of this SQL statement to set up the trace, and we can find in V$SQL.
We now have everything we need to generate the trace.
Finally you would look in the USER_DUMP_DEST directory for the trace file with the name you specified.
