Hi,
My database version : ORacle 11G
I kind of hunt down the execution plan using the following steps:
1) I put a fake hint and within that I put a string that would be absolutely unique in my query, example:
select /*+findme109090*/ from hr.employees where employee_id < 150;
2) further then I look out for that fake hint string using a query on the V$sqlstats, like:
select sql_id from v$sqlstats where sql_text like '%findme109090%';
so basically, I see the output and make sure that the sql_id I have hunted belongs to my query only.
3) Then I use the sql_id found in the prior step and look for that sql_id in V$sql_plan view , and I easily get the execution steps which oracle takes to execute this query:
select * from v$sql_plan where sql_id = '<the sql id I got from prior step>';
Now I have run into some questions:
1) I am using this way for couple of months and I was under impression that these are the execution steps oracle takes to run my query, but after reading an article I now got under impression that these steps might not be 100% the execution steps and the right execution steps are stored in the trace files, is it true, I would just like to clear this ambiguity, please help me with this doubt.
2) Certain DBA uses the TKPROF to read trace files and format the output, so basically, I got to know that they generate the execution plan(100% correct execution plan) using this step, is it true?
PS: I have been working for the last few months on interpreting the execution plans and now I just have a feeling that my hunted execution plan may not be the execution steps that are used by oracle to run my query.
Any help with my doubts is appreciated.
Thanks
Rahul