数据库优化：
一.oracle数据库软件系统调整
1.oracle初始化参数文件。
2.oracle动态视图：v$sql v$sqlarea v$sqltext v$session+v$session_wait v$process v$sysstat v$system_event v$parameter
3.oracle sga调整.
二.oracle sql语句优化
1.常见的sql技巧：
*不用*代替所有列名
*用truncate代替delete
*在确保完整性的情况下多用commit语句
*尽量减少表的查询次数
*用not exists 替代not in
*用exitst替代in
*用exists替代distinct
*有效利用共享游标
*以合理的方式使用函数
2.表的连接方法（扫描右到左 ，表少右，表多左）
3.有效的使用索引.
4.oracle的优化器与执行计划
*运行explain plan
例：
EXPLAIN PLAN
SET STATEMENT_ID = 'demo01' FOR
SELECT e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
Plan hash value: 2933537672
-------------------------------------------------------------------------------
| Id| Operation| Name| Rows| Bytes | Cost (%CPU|
--------------------------------------------------------------------------------
|0 | SELECT STATEMENT||106 |2862 |6(17|
|1 |MERGE JOIN||106 |2862 |6(17|
|2 |TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |27 |432 |2(0|
|3 |INDEX FULL SCAN| DEPT_ID_PK|27 ||1(0|
|*4 |SORT JOIN||107 |1177 |4(25|
|5 |TABLE ACCESS FULL| EMPLOYEES|107 |1177 |3(0|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
18 rows selected.
*Sql trace and tkprof
*Sql*plus autotrace feature
*Oracle sql analyze
sql_trace:
alter session set sql_trace = true;
Run SQL's for which TKPROF needs to begenerated
alter session set sql_trace = false;
select vpar.value || '/' || instance || '_ora_' ||ltrim(to_char(vp.spid,'fm99999')) || '.trc'
from v$process vp, v$sessionvs, v$parameter vpar, v$thread vthr
where vp.addr = vs.paddr
and vs.audsid =userenv('sessionid')
and vpar.name ='user_dump_dest'
本文转自东方之子736651CTO博客，原文链接： http://blog.51cto.com/ecloud/1399443，如需转载请自行联系原作者
