文章摘抄-收获，不止Oracle
/*摘抄收获，不止Oracle*/--从一条UPDATE语句看Oracle体系结构SQLPLUS ADMIN/ADMINUPDATE TT1 SET ID = 6 WHERE OBJECT_ID = 12;/*1.执行SQLPLUS ADMIN/ADMIN,Oracle会在PGA内存区域开辟一个私有进程/线程，保存此SESSION的环境配置，登陆权限等。2.当用户发出UPDATE语句，Oracle首先在针对此SQL生成唯一的HASH值，并通过此HASH值在SHARE_POOL对比，判断是否已解析过此SQL 若没有，Oracle会硬解析此SQL，包括SQL语句的正确性，及执行计划等，缓存到SHARE_POOL中。此动作会引发Oracle产生LATCH锁，及递归读(recursive call)。 若已硬解析过，则直接使用解析的结果开始执行。3.Oracle查看是否已缓存查询的数据，若已缓存，则直接从数据库缓存中查询。否则需要从硬盘中读取，获取后会保存在数据缓冲区中。4.更新之前，Oracle会在回滚表空间分配到空间，并在数据缓存区创建更新前镜像，前镜像的数据也由DBWR写入回滚表空间的数据文件。此步骤会写入redo日志文件。5.当用户发出COMMIT，Oracle出发LGWR后台进程把log buffer写入到日志文件中。而数据是否写入数据文件，是依赖于后台进程CKPT,它会触发 DBWR进程写数据文件。数据文件不同步，是考虑批量写数据性能更高。 提交后，Oracle会把回滚段事务标记为非激活状态，表示允许重写。5.若用户发出ROLLBACK,这Oracle会利用UNDO段记录的前镜像进行恢复。 */--LGWR由于需要顺序记录情况下保留的日志才有意义，因此LGWR只能采用单线程。LGWR的触发机制1.每隔3秒，LGWR触发一次2.COMMIT触发3.DBWR要把数据写入磁盘，触发LGWR运行一次。4.日志缓冲区满三分之一或满1MB，触发一次4.联机日志文件切换也触发LGWR。数据块结构包括：1.数据块头：此块的概要信息，例如块地址及此数据块所属的段的类型(表还是索引)2.表目录：行数据所在表的信息。3.行目录：存放插入行的地址。4.可用表空间：由Oracle PCTFREE控制。如果是10，则保留10%的空余空间。5.行数据区域：存储具体的行的信息或者索引的信息。----------------------------------------------------------------临时表空间组的使用/*Oracle可以为每个用户指定不同的临时表，每个临时表的数据文件都在磁盘的不同位置上，可以有效避免IO竞争。Oracle 10g推出的临时表空间组，可以做到为同一用户的不同session设置不同的临时表空间，为缓解IO竞争再次迈出一大步*/CREATE TEMPORARY TABLESPACE TEMP1_1 TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP1_1.DBF' SIZE 1000M TABLESPACE GROUP TMP_GRP1;CREATE TEMPORARY TABLESPACE TEMP1_2 TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP1_2.DBF' SIZE 1000M TABLESPACE GROUP TMP_GRP1;CREATE TEMPORARY TABLESPACE TEMP1_3 TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP1_3.DBF' SIZE 1000M TABLESPACE GROUP TMP_GRP1;--查看表空间组admin@ORCL> select * from dba_tablespace_groups;GROUP_NAME TABLESPACE_NAME------------------------------ ------------------------------TMP_GRP1 TEMP1_1TMP_GRP1 TEMP1_2TMP_GRP1 TEMP1_3--指定用户使用表空间组alter user admin temporary tablespace tmp_grp1;--开启3个admin session，执行大的排序动作后，查询如下--当PGA的排序区容纳不下排序的数据，才会利用到临时表空间admin@ORCL> SELECT USERNAME,SESSION_NUM,TABLESPACE,CONTENTS,SEGTYPE FROM V$SORT_USAGE;USERNAME SESSION_NUM TABLESPACE CONTENTS SEGTYPE------------------------------ ----------- ------------------------------- --------- ---------ADMIN 35 TEMP1_1 TEMPORARY SORTADMIN 32 TEMP1_2 TEMPORARY SORTADMIN 30 TEMP1_3 TEMPORARY SORT-----------------------------------------------------------------测试下，表空间自动扩展与非自动扩展，大表创建的效率--TBS_C执行效率最快，是因为表空间的初始化空间足够表够用，而不用进行扩展。扩展是十分消耗时间的--TBS_B比TBS_A执行效率快，是因为每次扩展的extent是越来越大，而TBS_A是每次64K的单位去申请空间--TBS_D执行比TBS_C执行快，是因为预先在表空间中申请了位置。CREATE TABLESPACE TBS_A DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_A.DBF' SIZE 1M AUTOEXTEND ON UNIFORM SIZE 64K;CREATE TABLESPACE TBS_B DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_B.DBF' SIZE 1M AUTOEXTEND ON;CREATE TABLESPACE TBS_C DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_C.DBF' SIZE 1G;CREATE TABLE TB_A (ID INT) TABLESPACE TBS_A;CREATE TABLE TB_B (ID INT) TABLESPACE TBS_B;CREATE TABLE TB_C (ID INT) TABLESPACE TBS_C;CREATE TABLE TB_D (ID INT) TABLESPACE TBS_CSTORAGE ( INITIAL 120M MINEXTENTS 1 MAXEXTENTS UNLIMITED );sys@ORCL> insert into tb_a select rownum from dual connect by level <= 10000000;已创建10000000行。已用时间: 00: 02: 41.98sys@ORCL> insert into tb_b select rownum from dual connect by level <= 10000000;已创建10000000行。已用时间: 00: 01: 22.75sys@ORCL> insert into tb_c select rownum from dual connect by level <= 10000000;已创建10000000行。 已用时间: 00: 00: 36.31sys@ORCL> insert into tb_d select rownum from dual connect by level <= 10000000;已创建10000000行。已用时间: 00: 00: 32.02-----------------------------------------------------------行迁移的处理与优化--什么是行移动？/*row chain：When a row is too large to fit into any block, row chaining occurs. In this case, the Oracle devide the row into smaller chunks. each chunk is stored in a block along with the necessary poiters to retrive and assemble the entire row.如何或知行迁移（行链接）严重的表呢？DBA_TABLES视图的CHAINED_CNT列，该列有该表的链接行计数。row migration：when a row is to be updated and it cannot find the necessary free space in its block, the Oracle will move the entire row into a new block and leave a pointer from the orginal block to the new location. This process is called row migration.对性能的影响：读一行要读两个块，也就是要两次逻辑读。*/CREATE TABLE EMPLOYEES AS SELECT * FROM HR.EMPLOYEES;--取第二次trace结果admin@ORCL> select * from EMPLOYEES;已选择107行。已用时间: 00: 00: 00.01执行计划----------------------Plan hash value: 1445457117-------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------| 0 | SELECT STATEMENT | | 107 | 14231 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 14231 | 3 (0)| 00:00:01 |-------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 12 consistent gets 0 physical reads 0 redo size 9779 bytes sent via SQL*Net to client 462 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 107 rows processed--修改表中数据，增大原有的数据，导致行迁移ALTER TABLE EMPLOYEES MODIFY FIRST_NAME VARCHAR2(200);ALTER TABLE EMPLOYEES MODIFY LAST_NAME VARCHAR2(200);ALTER TABLE EMPLOYEES MODIFY EMAIL VARCHAR2(200);ALTER TABLE EMPLOYEES MODIFY PHONE_NUMBER VARCHAR2(200);UPDATE EMPLOYEES SET FIRST_NAME = LPAD('1',200,'*'),LAST_NAME = LPAD('1',200,'*'),EMAIL=LPAD('1',200,'*'),PHONE_NUMBER=LPAD('1',200,'*');--查询,修改后的逻辑读为31admin@ORCL> select * from EMPLOYEES;已选择107行。执行计划----------------------Plan hash value: 1445457117-------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------| 0 | SELECT STATEMENT | | 107 | 52323 | 7 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 52323 | 7 (0)| 00:00:01 |-------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 31 consistent gets 0 physical reads 0 redo size 92977 bytes sent via SQL*Net to client 462 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 107 rows processed 107 rows processed--消除行迁移,逻辑读从31变为24CREATE TABLE EMPLOYEES_BAK AS SELECT * FROM EMPLOYEES;SELECT * FROM EMPLOYEES_BAK;admin@ORCL> SELECT * FROM EMPLOYEES_BAK;已选择107行。执行计划----------------------Plan hash value: 3604099949----------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time----------------------------------------------| 0 | SELECT STATEMENT | | 107 | 52323 | 6 (0)| 00:00:01| 1 | TABLE ACCESS FULL| EMPLOYEES_BAK | 107 | 52323 | 6 (0)| 00:00:01----------------------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 92437 bytes sent via SQL*Net to client 462 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 107 rows processed--如何检查表的行迁移sys@ORCL>@F:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlchain.sqlANALYZE TABLE EMPLYESS LIST CHAINED ROWS INTO CHAINED_ROWS;--统计表上的行迁移动作SELECT COUNT(*) FROM CHAINED_ROWS WHERE TABLE_NAME = 'EMPLYESS';--对当前用户所有表做分析SELECT 'ANALYZE TABLE'||TABLE_NAME||'LIST CHAINED ROWS INTO CHAINED_ROWS;' FROM USER_TABLES;-----------------------------------------------------------测试下BLOCK的大小对表查询的影响--对于OLAP应用，倾向于BLOCK尽量大，而LOTP应用，BLOCK不要太大--索引读返回少量记录这样的OLTP环境下，块大小对性能影响不大。但考虑到如果块太大，容易导致大量并发查询机更新--操作都指向同一个数据块，从而产生热点块竞争。SELECT * FROM DBA_DATA_FILES;ALTER SYSTEM SET DB_16K_CACHE_SIZE = 400M;--测试时，确保分配给的db cache够大，能容纳表T_16KCREATE TABLESPACE TBS_A DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_A.DBF' SIZE 1G;CREATE TABLESPACE TBS_B BLOCKSIZE 16K DATAFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TBS_B.DBF' SIZE 1G;--创建两个300w+的表，分别在8K,CREATE TABLE T_8K TABLESPACE TBS_A AS SELECT * FROM DBA_OBJECTS;INSERT INTO T_8K SELECT * FROM T_8K;SELECT COUNT(*) FROM T_8K;CREATE TABLE T_16K TABLESPACE TBS_B AS SELECT * FROM DBA_OBJECTS;INSERT INTO T_16K SELECT * FROM T_16K;--16k的查询结果,效率几乎提高了一倍fundz_dw@ORCL> select count(*) from T_16K; COUNT(*)---------- 3382016已用时间: 00: 00: 00.08执行计划----------------------Plan hash value: 3599734656--------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |--------------------------------| 0 | SELECT STATEMENT | | 1 | 6402 (2)| 00:01:17 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T_16K | 3433K| 6402 (2)| 00:01:17 |--------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 23165 consistent gets 0 physical reads 0 redo size 411 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed--8K的查询结果是fundz_dw@ORCL> select count(*) from T_8K; COUNT(*)---------- 3381952已用时间: 00: 00: 00.12执行计划----------------------Plan hash value: 576579961-------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------| 0 | SELECT STATEMENT | | 1 | 10331 (2)| 00:02:04 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| T_8K | 3213K| 10331 (2)| 00:02:04 |-------------------------------Note----- - dynamic sampling used for this statement统计信息---------------------- 0 recursive calls 0 db block gets 46575 consistent gets 0 physical reads 0 redo size 411 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed?