How to get execution time for nested cursors inside SQL?

Toad for Oracle
1

Hello,
I have AOP reports which have nested sections. These sections are printed out using cursors inside SQL like this:

Select
  'file1' As "filename",
  Cursor(
    Select
    
      Cursor( -- Dept
        Select
          D.DeptNo,
          D.DName,
          
          Cursor( -- Emp
            Select
              E.EName,
              E.EmpNo
            From
              SCOTT.EMP E
            Where 1=1
              And E.DeptNo =  D.DeptNo
            Order By
              E.EName
          ) "rec_emp"
          
        From
          SCOTT.DEPT D
        Where 1=1
        Order By
          D.DeptNo Asc
      ) "rec_dept"
      
    From
      Dual
  ) "data"
From
  Dual
;

If I select code inside cursor "rec_dept" and press F9 I got in status bar elapsed time only for this main cursor, not for the whole SQL. How to also get elapsed time for rec_emp and all other nested cursors and total time? This cursor rec_emp is executed only after I click on it inside returned recordset rec_dept. And I have nested cursors more then one level like this example is.

Regards,
Blacksmith

2

You could enable auto-trace.

  1. Enable Oracle tracing on your session before you run your SQL
  2. Run your SQL
  3. Disable Oracle tracing on your session
  4. Fetch the trace file from the database
  5. Load it into TKPROF (if you do not have DBA Module in Toad), or Toad's Trace File Browser (if you do have DBA Module in Toad)

Just set this and then run your SQL with F9 as usual.

image
image410×553 23.9 KB

I just checked your demo statement (thank you for that) with both TKProf and Trace File Browser, and could see details on the sub-cursor for each

image
image1883×576 49.5 KB

TKProf results come up in notepad, they are attached.

orcl18_ora_1012.OUT.txt (21.8 KB)

3

Hi John,
I check my PC and I don't have on my drive file TKProf.exe. I also have just Toad for Oracle Base Edition which as you mention doesn't have included Trace File Browser.
Look like I could not see this window as you paste it. Can our DBA give me some files so I can copy it to my local drive? Which one exactly? Probably not just TKProf.exe.
I have to more explore that, because I never use this before.

4

Hi Blacksmith.

You need to install the Full (Not Instant) Oracle client to get it.

Go Here, then click "Individual Component Downloads" next to the version you want. 19c is good, 21 will work also. Then on the next page, download the Oracle client that is the same bitness (32/64) as Toad.

You don't actually have to use the client with Toad - it just needs to be installed so you can tell Toad where it is in Options -> Executables.

-John