oracle执行计划--002-优快云博客

本文详细介绍了SQL查询的基本语法、性能优化方法以及使用PL/SQL进行复杂操作的技巧。通过实例展示了如何通过合理使用索引、优化查询语句、调整数据库参数等手段提升数据库性能。

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';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/89196/viewspace-1459108/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/89196/viewspace-1459108/