SH: sale history
explain plan for
SELECT prod_category, AVG(amount_sold)
FROM sh.sales s, sh.products p
WHERE p.prod_id = s.prod_id
GROUP BY prod_category;


select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display('',null,'ALL'));
select * from table(dbms_xplan.display('',null,'basic +cost +predicate'));
select * from table(dbms_xplan.display('',null,'typical -cost -bytes'));
select * from table(dbms_xplan.display('',null,'basic +note'));




test 01
SELECT prod_category, AVG(amount_sold)
FROM sh.sales s, sh.products p
WHERE p.prod_id = s.prod_id
GROUP BY prod_category;




? EXPLAIN PLAN command
? V$SQL_PLAN
? Automatic Workload Repository (AWR)
? SQL Tuning Set (STS)
? SQL Plan Baseline (SPM)


explain plan for select ...
select * from table(dbms_xplan.display);
select * from table(dbms_xplan.display('',null,'ALL'));


select ... --真正执行SQL之后，
select * from table(dbms_xplan.display_cursor(null,null,'basic'));  
select * from table(dbms_xplan.display_cursor(null,null,'ALL'));    




dbms_xplan.display: basic/typical/ALL




explain plan for
SELECT employee_id, last_name, job_id
FROM hr.employees
WHERE job_id = 'AD_VP';


select * from table(dbms_xplan.display('',null,'ALL'));


page 11
histograms


exec dbms_stats.delete_column_stats(user,'EMPLOYEES','JOB_ID');
select * from dba_histograms where table_name='EMPLOYEES' and column_name='JOB_ID';