在一个存储过程里有insert和delete操作,然后在在toad的session里刷新观察,发现insert和delete都很慢,大概一秒钟执行一次,非常奇怪,因为处理的表都没有索引,没有索引不是会快吗?怎么这么慢呢?而且我看了表空间还有很大空间。请问这是怎么回事呢?帮我分析分析啊

解决方案 »

  1.   

    转载一篇:
    Oracle创建索引对SQL语句执行的影响
    在SQL开始执行之前,Oracle会确定SQL语句的执行计划,并按照执行计划的步骤访问相应的表和索引。一旦执行计划确定下来,Oracle会按照这个执行计划完成SQL语句的执行,在SQL语句执行开始之后建立的索引不会改变SQL语句的执行计划。因此,创建索引不会对执行计划有任何的影响,也就不会对运行中的SQL语句有影响。下面通过一个例子简单验证一下: 
    SQL> CREATE TABLE TEST (ID NUMBER, FID NUMBER, NAME VARCHAR2(30), BID_COLUMNS CHAR(2000));Table created.SQL> INSERT INTO TEST VALUES (1, 0, 'OBJECT', '0');1 row created.SQL> INSERT INTO TEST VALUES (2, 1, 'TABLE', '0');1 row created.SQL> INSERT INTO TEST VALUES (3, 1, 'INDEX', '0');1 row created.SQL> INSERT INTO TEST VALUES (4, 1, 'VIEW', '0');1 row created.SQL> INSERT INTO TEST VALUES (5, 1, 'SYNONYM', '0');1 row created.SQL> INSERT INTO TEST VALUES (6, 1, 'SOURCE', '0');1 row created.SQL> INSERT INTO TEST SELECT 20000+ROWNUM, 2, TABLE_NAME, '0' FROM DBA_TABLES;874 rows created.SQL> INSERT INTO TEST SELECT 30000+ROWNUM, 3, INDEX_NAME, '0' FROM DBA_INDEXES;1074 rows created.SQL> INSERT INTO TEST SELECT 40000+ROWNUM, 4, VIEW_NAME, '0' FROM DBA_VIEWS;2929 rows created.SQL> INSERT INTO TEST SELECT 50000+ROWNUM, 5, TABLE_NAME, '0' FROM DBA_SYNONYMS;2437 rows created.SQL> INSERT INTO TEST SELECT 60000+ROWNUM, 6, NAME, '0' FROM DBA_SOURCE;99717 rows created.SQL> COMMIT;Commit complete.SQL> set timing onSQL> SELECT COUNT(*) FROM TEST2 START WITH ID = 13 CONNECT BY PRIOR ID = FID;COUNT(*)----------107037Elapsed: 00:02:03.84构造一个树状查询,然后记录这个树状查询的运行时间。SQL> SELECT COUNT(*) FROM TEST2 START WITH ID = 13 CONNECT BY PRIOR ID = FID;COUNT(*)----------107037Elapsed: 00:05:26.15再次运行查询,在查询运行开始,马上在另一个SESSION创建索引。通过观察执行时间可以发现,创建索引不会对运行中的SQL语句带来性能提升,而且很可能由于系统资源的争用造成查询速度变慢。如果在IO分布的比较合理的系统中,可以看到,创建索引可以很快完成,而且随后执行同样的查询由于会使用索引,也会很快的返回结构,但是索引的创建不会加快已经处于运行状态的语句的速度。SESSION2:SQL> SET TIMING ONSQL> CREATE INDEX IND_TEST_ID ON TEST(ID) TABLESPACE USERS;索引已创建。已用时间:000: 01: 56.92SQL> CREATE INDEX IND_TEST_FID ON TEST(FID) TABLESPACE USERS;索引已创建。已用时间: 00: 02: 00.57建立索引后,同样的查询速度得到明显的提升。SQL> SELECT COUNT(*) FROM TEST2 START WITH ID = 13 CONNECT BY PRIOR ID = FID;COUNT(*)----------107037已用时间: 00: 01: 02.11 上面建立两个索引的语句和查询语句是在单独的SESSION2上运行的。SESSION2上的三个操作——创建两个索引和执行相同的查询语句——都执行完成了,而第一个会话的的运行结果仍然没有返回。二、创建索引对ORACLE内部机制的影响上面通过一个简单的例子说明,创建索引不会改变已经运行的SQL的执行计划。但是并不是说,创建索引不能给已经运行的SQL语句带来性能的提升。下面看一个比较特殊的例子:
    SQL> CREATE TABLE TEST AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;表已创建。SQL> CREATE TABLE TEST1 AS SELECT ROWNUM ID, ROWNUM FID, A.* FROM DBA_SYNONYMS A;表已创建。SQL> ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (ID);表已更改。SQL> ALTER TABLE TEST1 ADD CONSTRAINT FK_TEST1_FID FOREIGN KEY (FID) REFERENCES TEST(ID);表已更改。SQL> INSERT INTO TEST1 SELECT * FROM TEST1;已创建1616行。SQL> INSERT INTO TEST1 SELECT * FROM TEST1;已创建3232行。SQL> INSERT INTO TEST1 SELECT * FROM TEST1;已创建6464行。SQL> INSERT INTO TEST1 SELECT * FROM TEST1;已创建12928行。SQL> INSERT INTO TEST1 SELECT * FROM TEST1;已创建25856行。SQL> COMMIT;提交完成。SQL> DELETE TEST1;已删除51712行。SQL> COMMIT;提交完成。SQL> SET TIMING ONSQL> DELETE TEST;已删除6208行。已用时间: 00: 00: 17.03SQL> ROLLBACK;回退已完成。已用时间: 00: 00: 00.06 构造两张表,TEST1的FID建立了参考TEST表ID列的外键。但是这里并没有在外键列上建立索引。向TEST和TEST1表中填入一定数据量的数据,开始测试。这里测试的是删除TEST表的执行时间。首先将TEST1用DELETE命令删除,提交后计算删除TEST表的时间,大约需要17秒,然后将数据回滚。  下面准备进行第二次删除测试,所不同的是,在删除操作开始后,马上在另一个SESSION中给外键列增加索引,通过测试可以发现,几乎在索引创建完的同时,第一个SESSION就返回了结果,删除需要的时间缩短到了3秒。
    第一个SESSION的删除语句:SQL> DELETE TEST;已删除6208行。已用时间:? 00: 00: 03.00第二个SESSION的索引创建语句:SQL> CREATE INDEX IND_TEST1_FID ON TEST1(FID);索引已创建 这个测试中索引的创建影响到了已经在运行的SQL语句,并明显地提高了执行效率。这个现象和上一篇文章中描述的观点并不冲突。对于用户发出的SQL语句,Oracle的执行计划是不变的,但是为了执行用户发出的SQL语句,Oracle在内部做了大量的操作,包括权限的检查、语法的检查、目标对象是否存在,以及维护数据的完整性等等。这个例子中,用户发出的SQL语句的执行计划没有改变,发生改变的是Oracle内部维护操作语句的执行计划。如果在第一个SESSION执行DELETE操作的同时,通过下面的SQL语句检查第一个SESSION正在运行的语句,会发现下面的结果(9i及以前版本,如果是10g,则只能看到DELETE TEST)。 
    SQL> SELECT SQL_TEXT FROM V$SESSION A, V$SQL B2 WHERE A.SQL_HASH_VALUE = B.HASH_VALUE3 AND A.SQL_ADDRESS = B.ADDRESS4 AND A.SID = 17;SQL_TEXT----------------------------------------------------------------------------select /**//*+ all_rows */ count(1) from "YANGTK"."TEST1" where "FID" = :1这个SQL语句就是Oracle用来维护完整性的内部SQL。 回想一下我们的例子,建立了外键,但是没有建立索引。当每删除一条TEST的记录,Oracle都要检查这个主键是否在TEST1中被引用。由于没有索引,Oracle只能通过全表扫描来寻找TEST1中的记录。虽然TEST1没有记录,但是删除TEST时使用的是DELETE而不是TRUNCATE,因此TEST1的高水位线并没有下降,也就是说,每删除一条TEST的记录,都需要全表扫描一张拥有5万条数据的表,这就是为什么那个DELETE操作执行很慢的原因。而我们建立的索引正是加快了这个步骤,Oracle内部维护的SQL语句在索引可用后选择了索引扫描,因此DELETE操作在索引创建后迅速返回。三、小结创建索引对于用户已发出的正在运行的SQL不会带来性能的提升。这是由于用户执行的语句要按照执行计划来运行,而执行计划在运行开始的时候就确定下来了,且不会在SQL语句的运行过程中发生变化。对于SQL执行过程中,Oracle内部执行的用于维护的SQL语句,是有可能从新创建的索引中获得性能提升的。
      

  2.   

    delete慢倒有时候还正常.insert你确认慢吗?dbms_profiler 跟踪你这个procedure,看看哪几个步骤比较慢.
    delete之后马上就是insert吗?
    中间有commit没有?你的表空间什么类型?是assm还是mssm?
      

  3.   

    1、用手工方式进行insert,delete操作,是否情况也一样?
    2、如果也慢,看表数据量,对表进行索引、整理优化;
    3、如果1中很快,则看存储过程的相关代码
      

  4.   

    另外,在insert过程中,首先把v$session_wait中的event看看什么情况.如果是多个并发,可能出在段头的竞争上. 当然也有可能是assm的bug.
      

  5.   

    是insert这个过程慢还是两个insert之间的过程慢啊先搞清楚是数据库慢还是存储过程慢
      

  6.   


    把中间每个过程所花的时间打印出来,检查下在哪一步速度比较慢?不一定是insert或者delete这个sql语句执行的时候慢的!
      

  7.   

    dbms_profiler 跟踪你这个procedure,看看哪几个步骤比较慢. 
      

  8.   

    delete要查找相应的记录,有索引才会快
    insert加上/*+append*/试试