Tunning an Application in 11g

While tuning in development and load test environments, I ask developers to close their sessions (reset the connection pool in weblogic environment) after the required work is traced to get the row source operation in traces to see what "really" happened.

If we experience a performance problem in production (I think every DBA must have faced this), it may not be feasible to close the connections or reset the connection pools.
Is there a way to get the row source operation in production environments without closing the sessions or resetting the connection pools?

When the sessions were not closed, sometimes I used to get row source operation and some times not.

prior to 11g you need to close the session. starting with 11g you don't.  Here is the demo of that.

run this script as such in 10g database.

set serveroutput off;
alter session set sql_trace=true;
select * from emp where deptno = 10;

host
tkprof 
exit
select * from dept where dname is not null;
host
tkprof

and Tkprof shows this.

********************************************************************************

select * from emp
where deptno = 10
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.37          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04       0.37          0          4          0           3
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 
Rows     Row Source Operation
-------  ---------------------------------------------------
      3  TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=24 us)

********************************************************************************

select * from dept
where dname is not null
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 


********************************************************************************

you can see the " row source operation " is missing for the second sql , because the session has not closed. if you close the session you can see "row source operation" in 10g database.

but you don't need that to be done in 11g database. (here is Tkprof output from 11g instance)

********************************************************************************
select * from emp
where deptno = 10
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.03          0          4          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.03          0          4          0           3
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84 
Rows     Row Source Operation
-------  ---------------------------------------------------
      3  TABLE ACCESS FULL EMP (cr=4 pr=0 pw=0 time=0 us cost=3 size=114 card=3)

********************************************************************************

select * from dept
where dname is not null
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          4          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84 
Rows     Row Source Operation
-------  ---------------------------------------------------
      4  TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=0 us cost=3 size=80 card=4)

********************************************************************************