月归档:五月 2011

该关注的sql语句

一般来说,调优的第一手资料,很可能就是典型业务期的一个statspack报告,那么如何根据statspack报告来判断是哪些SQL消耗了最多的系统资源?哪些SQL是最需要调整的呢?这里给出了一个大致的优化思路。当然,思路是死的,人是活的,优化也需要随需应变。
  一般来说,需要关注下面四种Top SQL
  消耗最多CPU的(逻辑IO过多)
  导致过多物理I/O的
  执行次数较频繁的
  执行时间较长的
  我们知道,一个语句的响应时间有个很著名的公式:
  响应时间=服务时间+等待时间
  其中服务时间就是CPU为执行该语句花费的时间。
  服务时间=分析时间+递归时间+执行时间
  分析时间是CPU用于分析语句的时间,递归时间是CPU用于语句的递归SQL的时间,剩下的则就是CPU用于执行语句的真正时间了。
  那么,上面的这些时间信息从哪里来的?Oracle提供的系统统计信息中就有部分的时间统计信息:
  服务时间=CPU used by this session
  分析时间=parse time cpu
  递归时间=recursive cpu usage

  那么,执行时间就可以根据上面三个统计信息计算得出:
  执行时间=CPU used by this session – parse time cpu – recursive cpu usage
  如果执行时间在整个响应时间中占较大的比例,那么下一步就是找出那些造成了最多逻辑IO的SQL语句,可以从statspack报告的SQL ordered by Gets部分找到。
  如果分析时间在整个响应时间中占较大的比例,那么下一步就是查找哪些SQL分析过多,这在statspack报告中在SQL ordered by Parse Calls中列出。
  如果等待时间在整个响应时间中占较大的比例,并且主要是块读取相关的等待时,下一步就是找出哪些SQL造成了过多的物理读,可以查看statspack报告中的SQL ordered by Reads部分。
  那么,根据上面列出的一个简单的原则,我们需要关注三个关于CPU时间的统计信息: CPU used by this session, parse time cpu和recursive cpu usage,以及top5等待事件中和IO相关的等待时间。如果是其他的一些等待事件出现在Top5中,那么可能需要根据不同的等待事件来分析原因了。然后优先调优时间消耗最多的相关SQL。
  除了上面的SQL ordered by Gets(逻辑IO最多),SQL ordered by Parse Calls(软解析过多),SQL ordered by Reads(物理IO过多),statspack还按照其他的一些方式列出了Top SQL,这些Top SQL在某些情况下都是需要给予特别关注的。比如:
  SQL ordered by Executions 执行次数超过100的
  SQL ordered by Sharable Memory 占用library cache超过1M的
  SQL ordered by Version Count 子cursor超过20的
  如果没有statspack,那么根据v$sysstat/v$sesstat中的统计信息,结合v$sql/v$sqlarea,一样可以得到相关的SQL。
  v$sql对于每一个子cursor都有一行统计记录,而v$sqlarea则对同一个父cursor只有一行统计记录,也就是v$sqlarea是对v$sql按照父cursor进行group by后的一个结果。这两个视图中都有诸如buffer_gets,parse_calls,disk_reads,,executions,sharable_mem等列,和上面提到的statspack中列出Top SQL的条件对应。

发表在 Oracle | 评论关闭

insert引起的死锁

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-000a0002-00005420 68 440 X 65 496 S
TX-0001001b-00002b19 65 496 X 68 440 S
session 440: DID 0001-0044-000A2346 session 496: DID 0001-0041-000CBA54
session 496: DID 0001-0041-000CBA54 session 440: DID 0001-0044-000A2346
Rows waited on:
Session 496: obj – rowid = 00014282 – AAAUKCAAMAAAEZrAAA
(dictionary objn – 82562, file – 12, block – 18027, slot – 0)
Session 440: no row
Information on the OTHER waiting sessions:
Session 496:
pid=65 serial=58006 audsid=1896040 user: 88/VAS
O/S info: user: web_meg, term: unknown, ospid: , machine: ezg-web1
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
insert into TAB_XN_CONTENT_TEMP (key,content) values (:1,:2)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
insert into TAB_XN_CONTENT_TEMP (key,content) values (:1,:2)

我一看到这个错误,有点不明白,oracle 的insert操作竟然导致表被锁,然后查找些资料终于有了眉目:
当ORACLE执行insert等DML语句时,会首先自动在所要操作的表上申请一个TM锁,当TM锁获得后,再自动申请TX类型的锁。当两个或多个会话在表的同一条记录上执行DML语言时,第一个会话在记录上加锁,其它的会话处于等待状态,一直到第一个会话提交后TX锁释放,其它的会话才可以加锁。考虑是因为两个insert语句同时试图向一个表中插入PK或unique值相同的数据,而造成其中会话被阻塞,等待其它会话提交或回滚,因而造成死锁。这种情况,只要其中任何一个session提交,另外一个就会报出ORA-00001:违反唯一性约束条件,死锁终止;或者其中一个session回滚,另外一个即可正常执行。
通过对这段话的理解,应该是一个会话插入了一条记录未提交,然后另外一个会话继续插入主键或者唯一索引列相同的记录,导致死锁的发生。

环境模拟:
在会话1中执行下面语句
CREATE TABLE t1(ID NUMBER);
ALTER TABLE t1 ADD primary key (ID);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);

然后在会话2中执行下面语句
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);

在会话3中查询死锁情况
通过上面模拟,重现了insert死锁现象,说明红色标注出来的文字的正确性

发表在 Oracle | 一条评论

DBMS_STATS常用方法(收集oracle信息)

–收集数据库信息
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

–收集schema信息
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’, estimate_percent => 15);

–收集表信息
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);

–收集index信息
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);

–删除收集信息
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.delete_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.delete_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);

–创建备份收集信息表
begin
dbms_stats.create_stat_table(USER,stattab => ‘STAT_TABLE’);
end;

–备份收集信息
BEGIN
dbms_stats.export_table_stats(USER,tabname => ‘FEI_T’,stattab => ‘STAT_TABLE’);
END;

–删除收集信息
BEGIN
DBMS_STATS.delete_table_stats(USER,tabname => ‘FEI_T’);
END;

–导入收集信息
BEGIN
dbms_stats.IMPORT_TABLE_STATS(USER,’FEI_T’,stattab => ‘STAT_TABLE’);
END;

–说明:
当前用户可以使用user代替用户名
分析表相关对象信息cascade => true

发表在 Oracle性能优化 | 一条评论