Friday, April 17, 2020

Viewing the actual explain plan of Query

step1: add /*+ gather_plan_statistics */ hint in select statement as shown below.

select /*+ gather_plan_statistics */
e.first_name,d.department_name,j.job_title,j.job_id
from
hr.employees e,
hr.departments d,
hr.jobs j
where
e.job_id=j.job_id
and e.department_id=d.department_id
and j.job_title in ('Purchasing Clerk','Marketing Manager','Administration Assistant')
--and e.first_name in ('Sigal','Alexander','Jennifer','Susan')
;


step2: execute
 SELECT *  FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;
A-rows shows actually resulted query where as E-rows shows estimated row count.

No comments:

Post a Comment