Oracle Database の SQL トレースは最初は version 5（1986年リリース） で開発者のデバッグのために実装されました。version 5 では undocumented で not supported な機能でしたが、version 6 から documented でユーザーが使える機能になりました。
以下は、2014年頃に @kouji_s_0808 さんに飲んでるときにオススメの本を聞いたら「10年くらい前に読んだ本だけど面白いですよ」と教えてもらった Oracle Insights からの引用です。文中に登場する Juan Loaiza は SQL トレースが最初に実装された頃は Oracle kernel のアーキテクトで、現在は Oracle Corporation の Executive Vice President です。
Oracle Insights: Tales of the Oak Table (Oaktable)
- 作者:Ensor, Dave
- 発売日: 2004/07/28
- メディア: ペーパーバック
The History of SQL Trace
Let me switch gears for a while and tell the story of Oracle's SQL trace feature, which would eventually become the extended SQL trace feature used today. The Oracle extended SQL trace feature exists today because, fortunately, Oracle customers aren't the only ones who have to wrestle with Oracle application performance problems. The men and women who build the Oracle kernel fight the same problems too, both in customer situations and in competitive benchmarks. The following quote is from Juan Loaiza, an Oracle kernel architect, and at the time of this writing a vice president at Oracle Corporation(you can read the full story in Chapter 2, where Juan tells of the motives for inventing SQL trace and then extending it to the state in which we know today):
I've always thought that diagnosing performance issues boils down to figuring out where the time is going in the system. If you can attribute the time correctly, then the source of the problem becomes obvious
Certainly our professional community is indebted to Juan Loaiza and his team for giving us the microscope to see how the Oracle kernel is spending all of our user's time.
Version 5
SQL trace is least as old as Oracle version 5, which was released in 1986. The syntax to turn it on and off was simple:
select trace('sql', 1) from dual select trace('sql', 0) from dualApparently, not too many people knew about SQL trace in Oracle version 5, and probably fewer than that actually used it. In an old Oracle internal document called "Optimizing", Oracle described the trace function as follows (quoted verbatim from source):
The Kernel provides a trace function to provide information about internal operations.
The trace function is essentially a debug aid for Software Development-- It is not documented.
-- it is not supported.
-- [It] will not be a function in ORACLE version 6.
（中略）
Version 6
In Oracle version 6, SQL tracing become a core, documented feature for everyone to use. In version 6, Oracle introduced the syntax that also works in 7, 8, 9, and 10:alter session set sql_trace=true alter session set sql_trace=false
80年代後半から90年代の estat/bstat の変更履歴にも jloaiza という名前が残っています。
Oracle Database Connect 2016 で Oracle ACE の id:wmo6hash さんと @discus_hamburg さんが「Japan Oracle User Group代表者が開発責任者に聞くOracleテクノロジーの今後」というトークセッションで Juan Loaiza に直接質問するという歴史的なワンシーンも有りました。
VLDB 2015 で Juan Loaiza が Exadata の話をしている動画などもあります。
