CODEWITHSUNDEEP
oracle-databaseoracle12cdatabase-administration
oramas
oramas

Reputation: 911

What is the Starts data in TKPROF output?

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)

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

Upvotes: 0

Views: 229

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

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.

Upvotes: 2

Related Questions