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';
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';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/89196/viewspace-1459108/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/89196/viewspace-1459108/