oracle 优化

为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用.可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询. 数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须 完全相同(包括空格,换行等).

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

评论

7 楼 ericFang 2010-12-08  

生成性能报表步骤：
1、  进入SQL提示：SQL> exec dbms_workload_repository.create_snapshot;
2、  进行试验，可以执行下运行慢的语句，多执行几遍，持续个几分钟。
3、  再次执行：SQL> exec dbms_workload_repository.create_snapshot;
4、  执行SQL> select SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot;
其中字段begin_interval_time表示SNAP_ID开始的时间，end_interval_time表示SNAP_ID结束的时间。例如，要生成10点多的性能报表，则找到begin_interval_time为10点对应的SNAP_ID，例如为5，则确定Begin Snap_Id为5，End Snap_Id为6
5、生成AWR报表和ADDM报表：
SQL>@?/rdbms/admin/awrrpt.sql;
SQL>@?/rdbms/admin/addnrpt.sql;
注意，报表类型选择text。如果要分析的是今天的，那么天数就输入1。Begin Snap_Id输入前面查到的值，End Snap_Id输入前面查到的值，报表名称不需要输入，使用系统自动生成的名称即可。生成的报表文件位于当前目录下。

6 楼 ericFang 2010-11-16  

Oracle 应用进程 CPU100%
问题描述：
（1）
f0s0:~ # date
Sat Jul 12 15:18:27 CGP 2008

top - 15:24:58 up  6:16, 12 users,  load average: 3.19, 3.30, 3.08
Tasks: 253 total,   3 running, 250 sleeping,   0 stopped,   0 zombie
Cpu(s): 27.0%us,  1.7%sy,  0.0%ni, 69.7%id,  1.4%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:  12471668k total, 11613420k used,   858248k free,   142248k buffers
Swap:  2104472k total,        0k used,  2104472k free,  9745056k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14592 oracle    25   0 1665m  27m  25m R   99  0.2 278:53.50 oracleomu (LOCAL=NO)
24200 oracle    25   0 1664m  24m  22m R   99  0.2  39:29.36 oracleomu (LOCAL=NO)

(4) 进入 sqlplus 工具
    sqlplus cgp1/cgp1 as sysdba
(5) 根据oracle占用率进程pid 如下面进程pid为： 14592 查询信息
select B.SQL_TEXT
from v$session A, v$sqltext B, v$process C
where A.SQL_HASH_VALUE = B.HASH_VALUE
and A.SQL_ADDRESS = B.ADDRESS
and A.PADDR = C.ADDR
and C.SPID = 14592;

SQL_TEXT
----------------------------------------------------------------
PI_NEWSUBTASKNUM=>:PI_NEWSUBTASKNUM); end;
begin SP_HW_PM_CHECKSUBTASKNUM(PI_CHECKRESULT=>:PI_CHECKRESULT,

SQL> select
sid "sid",
substr(s.osuser,1,9) "O/S user",
substr(s.program,1,30) "program"
from
v$process p, v$session s
where
p.addr=s.paddr
and p.spid = 14592 ;


SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address
and a.sid= 14592
order by address, piece;

select count(*) from v$transaction;
select count(*) from v$transaction;
  COUNT(*)
----------
         0
原因分析：
SQL_TEXT
----------------------------------------------------------------
PI_NEWSUBTASKNUM=>:PI_NEWSUBTASKNUM); end;
begin SP_HW_PM_CHECKSUBTASKNUM(PI_CHECKRESULT=>:PI_CHECKRESULT,

通过PID定位出来占用CPU最高的语句有异常.
请检查该语句的调用过程以及该语句中涉及的存储过程实际上是需要执行什么样的操作.

5 楼 ericFang 2010-08-28  

16、	查看某表的约束条件:
select constraint_name, constraint_type,search_condition, r_constraint_name
from user_constraints where table_name = upper('&table_name');
17、	查看函数和过程的状态:
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
18、	查看函数和过程的源代码:
select text from all_source where owner=user and name=upper('&plsql_name');
19、	查看表空间的名称及大小:
    select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d  where t.tablespace_name = d.tablespace_name  group by t.tablespace_name;
20、	查看表空间物理文件的名称及大小:
 select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space from dba_data_files   order by tablespace_name;
21、	查看回滚段名称及大小:
 select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;
22、	查看控制文件:  select name from v$controlfile;
23、	查看日志文件: select member from v$logfile;
24、	查看表空间的使用情况:
select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name;
    SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"  FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
25、	查看数据库的版本:
    Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle';
26、	用系统管理员，查看当前数据库有几个用户连接：
 select username,sid,serial# from v$session;
如果要停某个连接用
alter system kill session 'sid,serial#';
如果这命令不行,找它UNIX的进程数
select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;
说明：21是某个连接的sid数,然后用 kill 命令杀此进程号。
27、	查看还没提交的事务
select * from v$locked_object;select * from v$transaction;
28、	捕捉运行很久的SQL:
column username format a12
column opname format a16
column progress format a8
select username,sid,opname, round(sofar*100 / totalwork,0) || '%' as progress,  time_remaining,sql_textfrom v$session_longops , v$sql where time_remaining <> 0 and sql_address = address and sql_hash_value = hash_value
29、	sid查对应的sql:
select SID,SQL_TEXT from v$open_cursor where SID='128';
30、	查看等待（wait）情况
SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
FROM v$waitstat, v$sysstat WHERE v$sysstat.name IN ('db block gets',
'consistent gets') group by v$waitstat.class, v$waitstat.count
31、	查看sga情况
SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC
32、	查找oracle性能瓶颈sql:
select sql_text,spid,v$session.program,process from 
v$sqlarea,v$session,v$process
where v$sqlarea.address=v$session.sql_address
and v$sqlarea.hash_value=v$session.sql_hash_value
and v$session.paddr=v$process.addr
and v$process.spid in (操作系统PID);


select sid,event,p1,p1text from v$session_wait;
33、	CREATE SEQUENCE 序列号的名称   INCREMENT BY 1  START  WITH  1
MAXVALUE  99999  CYCLE  NOCACHE;

4 楼 ericFang 2010-08-28  

1、	startup 强制启动方式
2、	startup force
3、	startup nomount 非安装启动，这种方式启动下可执行：重建控制文件、重建数据库
4、	查看当前用户的缺省表空间:select username,default_tablespace from user_users;
5、	 查看当前用户的角色:select * from user_role_privs;
6、	查看用户下所有的表:select * from user_tables;
7、	查看名称包含log字符的表:
select object_name,object_id from user_objects   where instr(object_name,'LOG')>0;
8、	查看某表的创建时间:
 select object_name,created from user_objects where object_name=upper('&table_name');
9、	查看某表的大小:
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&table_name');
10、	查看放在ORACLE的内存区里的表:
select table_name,cache from user_tables where instr(cache,'Y')>0;
11、	查看索引个数和类别:
select index_name,index_type,table_name from user_indexes order by table_name;
12、	查看索引被索引的字段:
select * from user_ind_columns where index_name=upper('&index_name');
13、	查看索引的大小
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('&index_name');
14、	查看序列号，last_number是当前值:select * from user_sequences;
15、	查看视图的名称:select view_name from user_views;

3 楼 ericFang 2010-08-24  

调整SQL语句、使用视图、增加索引、使用基于性能的优化策略;
执行计划explain plan for xxxx;
     select plan_table_output from table(dbms_xplan.display());
得到执行计划后，重点查看是否使用了索引扫描，是否有全表扫描。如果该使用索引扫描时没有使用说明索引建立不合理，如果有全表扫描说明没有建立索引或者索引建立不合理。
ORACLE可以基于性能或者基于吞吐量两种方式进行自动优化，前者牺牲系统资源换取最大性能，后者牺牲性能换取最大吞吐量。alter system set optimizer_mode='irst_rows_1'

AWR报告是ORACLE对一段时间内操作的一个统计，里面记录了消耗时间最多的SQL语句，记录了ORACLE内部最高的等待事件，同时记录了内存使用状况和建议。
每隔几分钟执行一次
exec dbms_workload_repository.create_snapshot();
然后生成AWR报告，按照提示操作就可以了
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
  a)使用分区:分区是把物理表逻辑划分成多个逻辑表，大表切成小表减少表访问等待时间
  b)使用单表查询:表连接，耗费太多时间，需要改成单表查询

查看TOP事件
select event,TIME_WAITED,TOTAL_WAITS,WAIT_CLASS  from v$system_event where TIME_WAITED>1000 and WAIT_CLASS not in ('Idle') order by TIME_WAITED desc;
在不生成AWR报告的情况下，该SQL语句直接找到ORACLE的等待事件

2 楼 ericFang 2010-06-24  

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费

1 楼 ericFang 2010-06-24  

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.
除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来 存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.
定期的重构索引是有必要的. ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

出处 http://www.dbasupport.com