When I get a tkprof output, I see "starts=1" data in the execution plan area, between the time and cost data. So what does that mean?
Basically, this area : (cr=15 pr=0 pw=0 time=514 us starts=1 cost=3 size=7383 card=107)
********************************************************************************
SQL ID: 7jk33n4f4mpy9 Plan Hash: 1445457117
select *
from
hr.employees
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.04 0.03 0 351 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 9 0.00 0.00 0 15 0 107
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.04 0.03 0 366 0 107
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
107 107 107 TABLE ACCESS FULL EMPLOYEES (cr=15 pr=0 pw=0 time=514 us starts=1 cost=3 size=7383 card=107)
********************************************************************************
STARTS is the number of times that line in the plan started. Easier to see when using (say) a join. Here's an example
SQL> select /*+ leading(d) use_nl(e) gather_plan_statistics */
2 e.ename, d.dname
3 from scott.dept d,
4 scott.emp e
5 where e.deptno = d.deptno
6 and e.sal > 1000;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
ADAMS RESEARCH
FORD RESEARCH
ALLEN SALES
WARD SALES
MARTIN SALES
BLAKE SALES
TURNER SALES
12 rows selected.
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
--------------------------------
SQL_ID 37nwzk5qypud3, child number 0
-------------------------------------
select /*+ leading(d) use_nl(e) gather_plan_statistics */
e.ename, d.dname from scott.dept d, scott.emp e where
e.deptno = d.deptno and e.sal > 1000
Plan hash value: 4192419542
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 12 |00:00:00.01 | 32 |
| 1 | NESTED LOOPS | | 1 | 13 | 12 |00:00:00.01 | 32 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 3 | 12 |00:00:00.01 | 25 |
-------------------------------------------------------------------------------------
We scanned DEPT and got 4 rows. For each of those 4 rows, we then did a full scan of EMP, hence line 3 started 4 times.