Problem Description: 1.每个表的结构及主键索引情况 2.每个表的count(*)记录是多少 3.对于创建索引的列，索引的类型是什么？count(distinct indexcol)的值是多少？ 4.最后一次对表进行分析是在什么时间，分析后，是否又对相关表做过大的操作 5.索引最后一次rebuild，是在什么时间，此后对表的操作类型又是什么状况？索引中浪费的空间是多少？6.这些表的存储情况，表的存储参数，表空间的类型，存储参数等 7.执行该SQL语句时，系统等候的资源是什么？ Trace SQL语句的执行过程 8.另一台执行相似SQL速度很快的机器上的相关表的如上信息是什么？ 一：SQL tuning 类 1:列举几种表连接方式 hash join/merge join/nest loop(cluster join)/index join 2:不借助第三方工具，怎样查看sql的执行计划 set autotrace on set autotrace traceonly explain plan set statement_id = &item_id for &sql; select * from table(dbms_xplan.display); http://download-west.oracle.com/ ... /b10752/ex_plan.htm 3:如何使用CBO,CBO与RULE的区别 在optimizer_mode=choose时,如果表有统计信息（分区表外）,优化器将选择CBO,否则选RBO。 RBO遵循简单的分级方法学,使用15种级别要点，当接收到查询，优化器将评估使用到的要点数目, 然后选择最佳级别（最少的数量）的执行路径来运行查询。 CBO尝试找到最低成本的访问数据的方法,为了最大的吞吐量或最快的初始响应时间,计算使用不同 的执行计划的成本，并选择成本最低的一个,关于表的数据内容的统计被用于确定执行计划。 4:如何定位重要(消耗资源多)的SQL select sql_text from v$sql where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000); 5:如何跟踪某个session的SQL exec dbms_system.set_sql_trace_in_session(sid,serial#,&sql_trace); select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1); exec dbms_system.set_ev(&sid,&serial#,&event_10046,&level_12,''); 6:SQL调整最关注的是什么 查看该SQL的response time(db block gets/consistent gets/physical reads/sorts (disk)) 7:说说你对索引的认识（索引的结构、对dml影响、为什么提高查询性能） b-tree index/bitmap index/function index/patitional index(local/global) 索引通常能提高select/update/delete的性能,会降低insert的速度, 8:使用索引查询一定能提高查询的性能吗？为什么 索引就是为了提高查询性能而存在的, 如果在查询中索引没有提高性能, 只能说是用错了索引,或者讲是场合不同 9:绑定变量是什么？绑定变量有什么优缺点？ 绑定变量是相对文本变量来讲的,所谓文本变量是指在SQL直接书写查询条件， 这样的SQL在不同条件下需要反复解析,绑定变量是指使用变量来代替直接书写条件， 查询bind value在运行时传递，然后绑定执行。 优点是减少硬解析,降低CPU的争用,节省shared_pool 缺点是不能使用histogram,sql优化比较困难 10:如何稳定(固定)执行计划 query_rewrite_enabled = true star_transformation_enabled = true optimizer_features_enable = 9.2.0 创建并使用stored outline http://download-west.oracle.com/ ... /outlines.htm#26854 这个贴子: http://www.cnoug.org/viewthread.php?tid=27598 11:和排序相关的内存在8i和9i分别怎样调整，临时表空间的作用是什么 8i中sort_area_size/sort_area_retained_size决定了排序所需要的内存 如果排序操作不能在sort_area_size中完成,就会用到temp表空间 9i中如果workarea_size_policy=auto时, 排序在pga内进行,通常pga_aggregate_target的1/20可以用来进行disk sort; 如果workarea_size_policy=manual时,排序需要的内存由sort_area_size决定 在执行order by/group by/distinct/union/create index/index rebuild/minus等操作时, 如果在pga或sort_area_size中不能完成,排序将在临时表空间进行（disk sort）, 临时表空间主要作用就是完成系统中的disk sort. 12:存在表T(a,b,c,d),要根据字段c排序后取第21—30条记录显示，请给出sql create table t(a number(,b number(,c number(,d number(); / begin for i in 1 .. 300 loop insert into t values(mod(i,2),i/2,dbms_random.value(1,300),i/4); end loop; end; / select * from (select c.*,rownum as rn from (select * from t order by c desc) c) where rn between 21 and 30; / select * from (select * from test order by c desc) x where rownum < 30 minus select * from (select * from test order by c desc) y where rownum < 20 order by 3 desc 相比之 minus性能较差 二：数据库基本概念类 1:pctused and pctfree 表示什么含义有什么作用 pctused与pctfree控制数据块是否出现在freelist中, pctfree控制数据块中保留用于update的空间,当数据块中的free space小于pctfree设置的空间时, 该数据块从freelist中去掉,当块由于dml操作free space大于pct_used设置的空间时,该数据库块将 被添加在freelist链表中。 --PCTFREE存储参数 PCTFREE存储参数告诉ORACLE什么时候应该将数据块从对象的空闲列表中移出。ORACLE的默认参数是PCTFREE=10； 也就是说，一旦一个INSERT操作使得数据块的90%被使用，这个数据块就从空闲列表(free list)中移出。 --PCTUSED存储参数 PCTUSED存储参数告诉ORACLE什么时候将以前满的数据块加到空闲列表中。当记录从数据表中删除时，数据库的数 据块就有空间接受新的记录，但只有当填充的空间降到PCTUSED值以下时，该数据块才被连接到空闲列表中，才可以往 其中插入数据。PCTUSED的默认值是PCTUSED=40。 --存储参数规则小结 （1）PCTUSED较高意味着相对较满的数据块会被放置到空闲列表中，从而有效的重复使用数据块的空间，但会导致 I/O消耗。PCTUSED低意味着在一个数据块快空的时候才被放置到空闲列表中，数据块一次能接受很多的记录，因此可以 减少I/O消耗，提高性能。 （2）PCTFREE的值较大意味着数据块没有被利用多少就从空闲列表中断开连接，不利于数据块的充分使用。PCTFREE 过小的结果是，在更新时可能会出现数据记录迁移(Migration)的情况。(注：数据记录迁移(Migration)是指记录在是 UPDATE操作扩展了一个VARCHAR2类型的列或BLOB列后,PCTFREE参数所指定的空间不够扩展，从而记录被ORACLE强制迁移到 新的数据块，发生这种情况将较严重的影响ORACLE的性能，出现更新缓慢)。 （3）在批量的插入、删除或者更新操作之前，先删除该表上的索引，在操作完毕之后在重新建立，这样有助于提高 批量操作的整体速度，并且保证B树索引在操作之后有良好的性能。 --------------------------------------------------------------------------------------------------------------------------------------------------- --MartriWang@gmail.com 17/05/2007-- --表的pctfree和pctused两个参数进行估算的方法 对于不同的应用系统，表的pctfree 和pctused两个参数有不同的设计原则，以下是根据特定的应用系统进行估算的例子，从中可以掌握基本的估算方法。 表的存储参数调整，一般情况，设置为pctfree 5 pctused 85即可（缺省为pctfree 10 pctused 40） 1.对于Pctfree参数 除了可以按字段及字段长度估算平均行长外，下面的方面可以根据已有数据分析出平均行长和每块行数 例： analyze table 病人信息 compute statistics for table for all indexes for all indexed columns; Select Num_Rows,Blocks,Round(Num_Rows / Blocks) Avg_Rows_Block, Avg_Row_Len From User_Tables Where Table_Name = '病人信息' NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN 857291 14161 61 117 对于一般8192的块，实际可用空间为8100左右. 假设以前该表的pctfree为15，改为5后，pctfree减少10，就可以再存入约7行. 相同的1万4千块就可以多存放约10万行数据， 这10万行数据，如果按每块60行算，就可以少占用约1700块(约13M的空间) 如果全表扫描该表的话，少读1700块数据，少106次IO操作(按缺省db_file_multiblock_read_count=16计算) 少占13M的内存 另外，需要考虑的两个因素 1。更新操作时，数据增长量大不大，例如：主要是把状态字段由1改为3，还是把摘要由空改为一段文字 2。并发事务的多少，因为一个事务信息在块中要占用约24Byte，如果有10个并发事务的话，至少额外考虑240Byte的空闲空间。 2.对于Pctused参数 主要考虑删除后插入数据的情况多不多，以及平均行长大小 例如： 病人费用记录，医保如果存在校对操作的话，是先产生预交结算数据，正式结算时，删除这些数据再重新生成 所以，病人预交记录,Pctused不能设置太高，否则重用那些低于Pctused的块，只能插入少量数据行，增加了IO操作 analyze table 病人预交记录 compute statistics for table for all indexes for all indexed columns; Select Num_Rows,Blocks,Round(Num_Rows / Blocks) Avg_Rows_Block, Avg_Row_Len From User_Tables Where Table_Name = '病人预交记录' NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN 181758 2147 85 83 如果设置pctfree 5 pctused 85，那么当删除一些行使块的已用空间低于85%时，块会被重用，但是因为要预留5%的空闲空间， 所以,对于已用空间刚刚低于85%的块，重用空间就只有10%,对于8K的块，可用810Byte,平均行长83,可以再放入9行，所以这个参数也是可以的。 但是，如果是病人费用记录,平均行长229，这样设置，只能放下3行，这个参数就不太合适了 NUM_ROWS BLOCKS AVG_ROWS_BLOCK AVG_ROW_LEN 925133 38278 24 229 根据分析，病人费用记录的数据更新量不大，但是并发操作比较大，最好把Pctfree设置高一点 所以，可以设置为pctfree 10 pctused 75（重用的块至少可以放5行，约1-2张单据）,甚至pctused 70也是可以的。 如果一个块的数据行数太多，可能造成热块争用，但是相对于减少存储，减少IO，减少内存占用带来的好处来说，热块不是特别突出的情况下可以不考虑。 --MartriWang@gmail.com 17/05/2007-- PCTFREE=(Average Row Size-Initial Row Size)*100/Average Row Size PCTUSED=(100-PCTFREE) -Average Row Size * 100/Availabe Data Space Oracle的其中一个优点时它可以管理每个表空间中的自由空间。Oracle负责处理表和索引的空间管理，这样就可以让我们无需懂得Oracle的表和索引的 内部运作。不过，对于有经验的Oracle调优专家来说，他需要懂得Oracle是如何管理表的extent和空闲的数据块。对于调整拥有高的insert或者update的系 统来说，这是非常重要的。 要精通对象的调整，你需要懂得freelists和freelist组的行为，它们和pctfree及pctused参数的值有关。这些知识对于企业资源计划（ERP）的应用是 特别重要的，因为在这些应用中，不正确的表设置通常是DML语句执行慢的原因。 对于初学者来说，最常见的错误是认为默认的Oracle参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设置表的pctfree和pctused 参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到freelists中。当这些设置不正确时，那些得到的freelists也是"dead"块， 因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。 Freelists对于有效地重新使用Oracle表空间中的空间是很重要的，它和pctfree及pctused这两个存储参数的设置直接相关。如果将pctused设置为一个高的值， 这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整Oracle的表格和索引时，需要认真考虑究竟需要高性能还是有效的空 间重用，并且据此来设置表的参数。以下我们来看一下这些freelists是如何影响Oracle的性能的。 当有一个请求需要插入一行到表格中时，Oracle就会到freelist中寻找一个有足够的空间来容纳一行的块。你也许知道，freelist串是放在表格或者索引的第 一个块中，这个块也被称为段头（segment header）。pctfree和pctused 参数的唯一目的就是为了控制块如何在freelists中进出。虽然freelist link和 unlink 是简单的Oracle功能，不过设置freelist link (pctused) 和unlink (pctfree) 对Oracle的性能确实有影响。 由DBA的基本知识知道，pctfree参数是控制freelist un-links的（即将块由freelists中移除）。设置pctfree=10 意味着每个块都保留10%的空间用作行扩展。 pctused参数是控制freelist re-links的。设置pctused=40意味着只有在块的使用低于40%时才会回到表格的freelists中。 许多新手对于一个块重新回到freelists后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到freelist中，它将会一直保留在freelist中 即使空间的使用超过了60%，只有在到达pctfree时才会将数据块由freelist中移走。 表格和索引存储参数设置的要求总结 以下的一些规则是用来设置freelists, freelist groups, pctfree和pctused存储参数的。你也知道，pctused和pctfree的值是可以很容易地通过alter table 命令修改的，一个好的DBA应该知道如何设置这些参数的最佳值。 有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾： . 对于需要有效地重新使用空间，可以设置一个高的pctused值，不过副作用是需要额外的I/O。一个高的pctused值意味着相对满的块都会放到freelist中。因 此，这些块在再次满之前只可以接受几行记录，从而导致更多的I/O。 . 追求高性能的话，可以将pctused设置为一个低的值，这意味着Oracle不会将数据块放到freelists中直到它几乎是空的。那么块将可以在满之前接收更多的行， 因此可以减少插入操作的I/O。要记住Oracle扩展新块的性能要比重新使用现有的块高。对于Oracle来说，扩展一个表比管理freelists消耗更少的资源。 让我们来回顾一下设置对象存储参数的一些常见规则： ．经常将pctused设置为可以接收一条新行。对于不能接受一行的free blocks对于我们来说是没有用的。如果这样做，将会令Oracle的性能变慢，因为Oracle将 在扩展表来得到一个空的块之前，企图读取5个"dead" 的free block。 ．表格中chained rows的出现意味着pctfree太低或者是db_block_size太少。在很多情况下，RAW和LONG RAW列都很巨大，以至超过了Oracle的最大块的大小， 这时chained rows是不可以避免的。 ．如果一个表有同时插入的SQL语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个freelist中，而没有其它包含有任何 空闲块的freelists出现。 ．freelist参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有20个用户执行插入的操作，那么该表的参数应该设置为freelists=20。 应记住的是freelist groups参数的值只是对于Oracle Parallel Server和Real Application Clusters才是有用的。对于这类Oracle，freelist groups应该设置 为访问该表格的Oracle Parallel Server实例的数目。 --------------------------------------------------------------------------------------------------------------------------------------------------- 2:简单描述table / segment / extent / block之间的关系 table创建时,默认创建了一个data segment, 每个data segment含有min extents指定的extents数, 每个extent据据表空间的存储参数分配一定数量的blocks 3:描述tablespace和datafile之间的关系 一个tablespace可以有一个或多个datafile,每个datafile只能在一个tablespace内, table中的数据,通过hash算法分布在tablespace中的各个datafile中, tablespace是逻辑上的概念,datafile则在物理上储存了数据库的种种对象。 4:本地管理表空间和字典管理表空间的特点，ASSM有什么特点 本地管理表空间（Locally Managed Tablespace简称LMT） 8i以后出现的一种新的表空间的管理模式，通过位图来管理表空间的空间使用。 字典管理表空间（Dictionary-Managed Tablespace简称DMT） 8i以前包括以后都还可以使用的一种表空间管理模式，通过数据字典管理表空间的空间使用。 动段空间管理（ASSM）， 它首次出现在Oracle920里有了ASSM，链接列表freelist被位图所取代，它是一个二进制的数组， 能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质， ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB 段）。 5:回滚段的作用是什么 事务回滚：当事务修改表中数据的时候，该数据修改前的值（即前影像）会存放在回滚段中， 当用户回滚事务（ROLLBACK）时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。 事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中， ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。 读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。 当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改（语句级读一致性） 当ORACLE执行SELECT语句时，ORACLE依照当前的系统改变号（SYSTEM CHANGE NUMBER-SCN） 来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时， 若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。 http://www.itpub.net/showthread. ... E
