Oracle-SQL性能讲座.xls

Oracle性能优化 1 21 Oracle性能优化 1 Oracle表的访问方式 1 全表扫描 2 全表扫描 并行读入 3 索引访问 4 快速完全索引扫描 并行读入 2 原则 1 小表用全表扫描 并还可放入KEEP池中 2 对排序表 访问数据少于40 用索引访问 多于 40 用全表扫描 并行读入 3 对非排序表 访问数据少于7 用索引访问 多于7 用全表扫描 并行读入 3 两种优化器 1 基于规则的优化 RBO 2 基于成本的优化 CBO 3 优化器模式 4 RBO的调整 5 CBO的调整 6 重排序表记录 以减少输入输出 7 驱动表位置 4 关于索引 5 调整表连接 SQL调整的核心问题 1 等连接 2 外部连接 3 自连接 4 反连接 5 半连接 TKPROF使用方法 2 21 SQL alter session set sql trace true SQL alter session set timed statistics true LOG文件输出目录 ORACE HOME rdbms log ora xxxx ora5 trc 可用ls ltr列出list Session ID UX HP tkprof ORACE HOME rdbms log ora xxxx ora5 trc mytrace log 输出在当前目录中 3 21 CBO 使用 ANALYSE Estimate統計 行数 1000 ANALYZE TABLE ops laprocFC WSIT1031 ESTIMATE STATISTICS SAMPLE 1000 ROWS Compute統計 項目全 ANALYZE TABLE ops laprocFC WSIT1031 COMPUTE STATISTICS FOR ALL INDEXED COLUMNS Compute統計 ANALYZE INDEX ops laprocFC WSIT1031 PRKY COMPUTE STATISTICS 用CBO建的两个表 4 21 sample TABLE1 CREATE TABLE TABLE1 A1 CHAR 03 A2 CHAR 03 A3 CHAR 03 PCTFREE 5 STORAGE INITIAL 100K NEXT 8K MAXEXTENTS UNLIMITED PCTINCREASE 0 TABLESPACE tbs ora5 wsi01 ALTER TABLE TABLE1 ADD CONSTRAINT TABLE1 PRKY PRIMARY KEY A1 USING INDEX STORAGE INITIAL 40K NEXT 4K MAXEXTENTS UNLIMITED PCTINCREASE 0 TABLESPACE idx ora5 wsi01 CREATE INDEX TABLE1 IX01 ON TABLE1 A2 STORAGE INITIAL 40K NEXT 4K MAXEXTENTS UNLIMITED PCTINCREASE 0 TABLESPACE idx ora5 wsi01 sample TABLE2 CREATE TABLE TABLE2 B1 CHAR 03 B2 CHAR 03 用CBO建的两个表 5 21 B3 CHAR 03 PCTFREE 5 STORAGE INITIAL 100K NEXT 8K MAXEXTENTS UNLIMITED PCTINCREASE 0 TABLESPACE tbs ora5 wsi01 ALTER TABLE TABLE2 ADD CONSTRAINT TABLE2 PRKY PRIMARY KEY B1 USING INDEX STORAGE INITIAL 40K NEXT 4K MAXEXTENTS UNLIMITED PCTINCREASE 0 TABLESPACE idx ora5 wsi01 CREATE INDEX TABLE2 IX01 ON TABLE2 B2 STORAGE INITIAL 40K NEXT 4K MAXEXTENTS UNLIMITED PCTINCREASE 0 TABLESPACE idx ora5 wsi01 analyze table TABLE1 compute statistics analyze table TABLE2 compute statistics 使用快速完全索引扫描例 6 21 create index customer idx on customer last name select last name from customer order by last name 注 以上Select文使用快速完全扫描 能够获得很快的速度 因为它仅访问了customer idx 关于NOTIN的使用 7 21 NOT IN 01 A1 B1 主 SELECT A1 A2 FROM TABLE1 WHERE A1 NOT IN SELECT B1 FROM TABLE2 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 30 FILTER Cost 0 Cardinality 0 Bytes 0 TABLE ACCESS FULL CAI TABLE1 ANALYZED Cost 1 Cardinality 1 Bytes 30 INDEX UNIQUE SCAN CAI TABLE2 PRKY ANALYZED Cost 0 Cardinality Bytes 5 NOT IN 02 A2 B2 INDEX SELECT A1 A2 FROM TABLE1 WHERE A2 NOT IN SELECT B2 FROM TABLE2 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 30 FILTER Cost 0 Cardinality 0 Bytes 0 TABLE ACCESS FULL CAI TABLE1 ANALYZED Cost 1 Cardinality 1 Bytes 30 TABLE ACCESS FULL CAI TABLE2 ANALYZED Cost 1 Cardinality 1 Bytes 15 换种方法 SELECT T1 A1 T1 A2 FROM TABLE1 T1 TABLE2 T2 WHERE T1 A2 T2 B2 AND T2 B2 IS NULL THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 900 FILTER Cost 0 Cardinality 0 Bytes 0 NESTED LOOPS OUTER Cost 0 Cardinality 0 Bytes 0 关于NOTIN的使用 8 21 TABLE ACCESS FULL CAI TABLE1 ANALYZED Cost 1 Cardinality 1 Bytes 600 INDEX RANGE SCAN CAI TABLE2 IX01 ANALYZED Cost 0 Cardinality Bytes 300 NOT IN 03 A3 B3 主 INDEX SELECT A1 A2 FROM TABLE1 WHERE A3 NOT IN SELECT B3 FROM TABLE2 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 45 FILTER Cost 0 Cardinality 0 Bytes 0 TABLE ACCESS FULL CAI TABLE1 ANALYZED Cost 1 Cardinality 1 Bytes 45 TABLE ACCESS FULL CAI TABLE2 ANALYZED Cost 1 Cardinality 1 Bytes 5 换方法 SELECT T1 A1 T1 A2 FROM TABLE1 T1 TABLE2 T2 WHERE T1 A3 T2 B3 AND T2 B3 IS NULL THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 3 Cardinality 3 Bytes 1200 FILTER Cost 0 Cardinality 0 Bytes 0 HASH JOIN OUTER Cost 0 Cardinality 0 Bytes 0 TABLE ACCESS FULL CAI TABLE1 ANALYZED Cost 1 Cardinality 1 Bytes 900 TABLE ACCESS FULL CAI TABLE2 ANALYZED Cost 1 Cardinality 1 Bytes 300 关于EXISTS的使用 9 21 直接関連 B3普通 SELECT DISTINCT A2 FROM TABLE1 T1 TABLE2 T2 WHERE T1 A1 T2 B1 AND T2 B3 1 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 14 Cardinality 14 Bytes 1092 SORT UNIQUE Cost 14 Cardinality 14 Bytes 1092 HASH JOIN Cost 4 Cardinality 4 Bytes 1200 TABLE ACCESS FULL CAI TABLE1 ANALYZED Cost 2 Cardinality 2 Bytes 600 TABLE ACCESS FULL CAI TABLE2 ANALYZED Cost 1 Cardinality 1 Bytes 600 直接関連 B2INDEX SELECT DISTINCT A2 FROM TABLE1 T1 TABLE2 T2 WHERE T1 A1 T2 B1 AND T2 B2 1 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 14 Cardinality 14 Bytes 1092 SORT UNIQUE Cost 14 Cardinality 14 Bytes 1092 HASH JOIN Cost 4 Cardinality 4 Bytes 1092 TABLE ACCESS FULL CAI TABLE2 ANALYZED Cost 2 Cardinality 2 Bytes 546 TABLE ACCESS FULL CAI TABLE1 ANALYZED Cost 1 Cardinality 1 Bytes 600 EXISTS SELECT A2 FROM TABLE1 T1 WHERE EXISTS SELECT X FROM TABLE2 关于EXISTS的使用 10 21 WHERE T1 A1 B1 AND B3 1 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 30 FILTER Cost 0 Cardinality 0 Bytes 0 TABLE ACCESS FULL CAI TABLE1 ANALYZED Cost 1 Cardinality 1 Bytes 30 TABLE ACCESS BY INDEX ROWID CAI TABLE2 ANALYZED Cost 1 Cardinality 1 Bytes 10 INDEX UNIQUE SCAN CAI TABLE2 PRKY ANALYZED Cost 0 Cardinality Bytes 0 EXISTS代替IN 11 21 信発資産担保 TBL SN SYHT SISAN TANP SUM B 信用 建玉余力用評価損益 前日時価 信用 建玉余力用諸経費 前日 更新 UPDATE SN SYHT SISAN TANP SUM B VW1 SET 信用 建玉余力用評価損益 前日時価 信用 建玉余力用諸経費 前日 SELECT SUM CEIL DECODE VW2 新規売買区分 0 VW2 数量 VW4 当日時価 VW2 単価 1 VW2 数量 VW2 単価 VW4 当日時価 0 SUM FLOOR DECODE VW2 新規売買区分 0 VW2 新規手数料 VW2 書換料 VW2 管理料 TRUNC VW2 逆日歩 TRUNC VW2 確定順日歩 VW2 未確定順日歩 TRUNC VW2 確定貸株料 VW2 未確定貸株料 VW2 消費税 新規手数料 1 VW2 新規手数料 VW2 書換料 VW2 管理料 TRUNC VW2 逆日歩 TRUNC VW2 確定順日歩 VW2 未確定順日歩 TRUNC VW2 確定貸株料 VW2 未確定貸株料 VW2 消費税 新規手数料 0 FROM SN SYHT TGYOK MEISAI OLED B VW2 SN KOKNAI KBSIKI MEG A VW4 WHERE VW2 決済済 0 AND VW2 取消済 0 AND VW2 処理済 0 AND VW2 新規約定日 基準日管理TBL T 1日 AND VW2 信発区分 3 AND VW2 VW1 AND VW2 口座番号 VW1 口座番号 AND VW4 銘柄 VW2 銘柄 AND VW4 VW2 AND VW4 国内外国区分 0 AND VW4 商品区分 1 GROUP BY VW2 VW2 口座番号 更新 TO NUMBER TO CHAR SYSDATE YYYYMMDDHH24MISS 00 速度 遅 方法 更新 ID WSH3760 ID WSH3760 基準日管理TBL T 1日 WHERE EXISTS WHERE 口座番号 IN SELECT X SELECT 口座番号 FROM SN SYHT TGYOK MEISAI OLED B VW5 FROM SN SYHT TGYOK MEISAI OLED B VW5 SN KOKNAI KBSIKI MEG A VW6 SN KOKNAI KBSIKI MEG A VW6 WHERE VW5 決済済 0 WHERE VW5 決済済 0 AND VW5 取消済 0 AND VW5 取消済 0 AND VW5 処理済 0 AND VW5 処理済 0 AND VW5 新規約定日 基準日管理TBL T 1日 AND VW5 新規約定日 基準日管理TBL T 1日 AND VW5 信発区分 3 AND VW5 信発区分 3 AND VW5 銘柄 VW6 銘柄 AND VW5 銘柄 VW6 銘柄 AND VW5 VW6 AND VW5 VW6 AND VW6 国内外国区分 0 AND VW6 国内外国区分 0 AND VW6 商品区分 1 AND VW6 商品区分 1 AND VW5 VW1 AND VW5 口座番号 VW1 口座番号 MINUS代替NOT IN 12 21 一般金銭余力 TBL 存在 口座追加 系 SN IPN KINSEN YRK SUM B INSERT INTO SN IPN KINSEN YRK SUM B 口座番号 通貨 受渡日 部店 基準日 作成 更新 更新 ID ID 更新端末 更新社員 SELECT VW2 VW2 口座番号 VW2 通貨 VW1 RDATE NVL VW2 部店 基準日管理TBL 日 TO NUMBER TO CHAR SYSDATE YYYYMMDDHH24MISS 00 TO NUMBER TO CHAR SYSDATE YYYYMMDDHH24MISS 00 WSI3350 WSI3350 基準日管理TBL 日 SPACE 15 SPACE 8 FROM SN KYK KNJO ZDAK A VW2 SELECT 基準日管理TBL T M1 HOST変数 RDATE FROM DUAL UNION SELECT 基準日管理TBL T 0 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 1 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 2 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 3 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 4 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 5 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 6 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 7 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 8 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 9 HOST変数 FROM DUAL UNION SELECT 基準日管理TBL T 10 HOST変数 FROM DUAL速度 遅 VW1 SELECT distinct 口座番号 通貨 部店 where VW2 USER CD VW2 KOZA NO VW2 TUKA CD not in FROM SN KYK KNJO ZDAK A select USER CD KOZA NO TUKA CD WHERE 口座番号 通貨 IN from SN IPN KINSEN YRK SUM B SELECT 口座番号 通貨 FROM SN KYK KNJO ZDAK A group by VW2 USER CD VW2 KOZA NO VW2 TUKA CD VW1 RDATE MINUS SELECT 口座番号 通貨 FROM SN IPN KINSEN YRK SUM B GROUP BY 口座番号 通貨 VW2 代替NULL的方法分析 13 21 例 表TABLE2中B2是index 但允许NULL 代替前 SELECT B2 FROM TABLE2 WHERE B2 IS NULL THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 5 TABLE ACCESS FULL CAI TABLE2 ANALYZED Cost 1 Cardinality 1 Bytes 5 代替后 update TABLE2 set B2 N A where B2 IS NULL SELECT B2 FROM TABLE2 WHERE B2 N A THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 5 INDEX RANGE SCAN CAI TABLE2 IX01 ANALYZED Cost 1 Cardinality 1 Bytes 5 LIKE使用方法 14 21 LIKE 01 SELECT B2 FROM TABLE2 WHERE B2 LIKE 96 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 5 INDEX RANGE SCAN CAI TABLE2 IX01 ANALYZED Cost 0 Cardinality Bytes 5 LIKE 02 SELECT B2 FROM TABLE2 WHERE B2 LIKE 96 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 5 INDEX FULL SCAN CAI TABLE2 IX01 ANALYZED Cost 0 Cardinality Bytes 5 LIKE 03 ORACLE VER 8 1 7 以上 ORACLE SUPER権限 必要 解決方法 CREATE INDEX TABLE2 IX01 ON TABLE2 REVERSE B2 STORAGE INITIAL 40K NEXT 4K MAXEXTENTS UNLIMITED PCTINCREASE 0 TABLESPACE idx ora5 wsi01 SELECT B2 FROM TABLE2 WHERE REVERSR B2 LIKE 69 函数使索引无效 15 21 CASE01 SELECT X FROM TABLE3 WHERE B2 SYSDATE 7 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 9 INDEX RANGE SCAN CAI TABLE3 IX01 ANALYZED Cost 0 Cardinality Bytes 9 CASE02 SELECT X FROM TABLE3 WHERE TO CHAR B2 YYYY MM SYSDATE 7 THE QUERY PLAN SELECT STATEMENT CHOOSE Cost 1 Cardinality 1 Bytes 9 TABLE ACCESS FULL CAI TABLE3 ANALYZED Cost 1 Cardinality 1 Bytes 9 解决方法 建立基于函数的索引 DETERMINISTIC SQLExpert使用例 修正前 16 21 代用掛目率 代用評価用時価更新 UPDATE SN SAKIOP KKABU MSD A T SET DAIY KAKEME RTU DAIY HYOKYO JIKA SELECT LEAST T1 DAIYO KKMK2 RATE DECODE SUBSTR T BRAND CD 3 1 0 T2 F KAKE KABU 2 T2 F KAKE KABU 3 T2 F KAKE TENTOU 4 T2 F KAKE TOUSIN K 0 T1 JIKA TOU FROM SN KOKNAI KBSIKI MEG A T1 SN GYOM STS 2 1 A T2 WHERE T USER CD T1 USER CD AND T USER CD T2 USER CD AND T SEC CD T1 MG CD2 AND T1 KUNI KBN2 0 AND T1 SHOHIN KBN2 1 UP TIMESTNP TO NUMBER TO CHAR SYSDATE YYYYMMDDHH24MISS 00 UP PROG ID WSH3715 TRNSCT ID WSH3715 20030130 WHERE SQLExpert使用例 修正前 17 21 PRINC AGENT 5 AND MANAGE PLACE IN 21 22 23 AND DAIY FLG 1 AND USER CD SEC CD IN SELECT T1 USER CD T1 MG CD2 FROM SN KOKNAI KBSIKI MEG A T1 SN GYOM STS 2 1 A T2 WHERE T1 USER CD T2 USER CD AND T1 KUNI KBN2 0 AND T1 SHOHIN KBN2 1 AND T1 DAIYO KBN 1 AND T1 DAIYO STOCK KBN UPDATE STATEMENT CHOOSE Cost 14036 Cost 14036 Cardinality 2 Bytes 166 UPDATE OPS LAPROCTA WSHT4202 MERGE JOIN Cost 14036 Cardinality 2 Bytes 166 VIEW SYS VW NSO 1 Cost 14034 Cardinality 6726 Bytes 147972 SORT UNIQUE Cost 14034 Cardinality 6726 Bytes 511176 NESTED LOOPS Cost 9035 Cardinality 75468 Bytes 5735568 HASH JOIN Cost 9035 Cardinality 30678 Bytes 1932714 HASH JOIN Cost 1431 Cardinality 35741 Bytes 1536863 TABLE ACCESS FULL OPS LAPROCK7 WMKTK001 GN ANALYZED Cost 86 Cardinality 4757 Bytes 85626 TABLE ACCESS FULL OPS LAPROCK7 WMKTK002 GN ANALYZED Cost 917 Cardinality 71467 Bytes 1786675 INDEX FAST FULL SCAN OPS LAPROCK7 WMKTK003 GN IX01 ANALYZED Cost 131 Cardinality 285790 Bytes 5715800 INDEX UNIQUE SCAN OPS LAPROCK7 WUATCB06 IX01 GN Cardinality 246 Bytes 3198 SORT JOIN Cost 3 Cardinality 56 Bytes 3416 TABLE ACCESS FULL OPS LAPROCTA WSHT4202 ANALYZED Cost 1 Cardinality 56 Bytes 3416 NESTED LOOPS Cost 9 Cardinality 1 Bytes 118 NESTED LOOPS Cost 9 Cardinality 1 Bytes 101 SQLExpert使用例 修正前 18 21 NESTED LOOPS Cost 7 Cardinality 1 Bytes 74 TABLE ACCESS BY INDEX ROWID OPS LAPROCK7 WUATCB06 GN Cost 1 Cardinality 3 Bytes 156 INDEX RANGE SCAN OPS LAPROCK7 WUATCB06 IX01 GN Cost 1 Cardinality 3 TABLE ACCESS BY INDEX ROWID OPS LAPROCK7 WMKTK003 GN ANALYZED Cost 2 Cardinality 1 Bytes 22 INDEX UNIQUE SCAN OPS LAPROCK7 WMKTK003 GN IX01 ANALYZED Cost 1 Cardinality 1 TABLE ACCESS BY INDEX ROWID OPS LAPROCK7 WMKTK002 GN ANALYZED Cost 2 Cardinality 1 Bytes 27 INDEX UNIQUE SCAN OPS LAPROCK7 WMKTK002 GN IX01 ANALYZED Cost 1 Cardinality 1 INDEX UNIQUE SCAN OPS LAPROCK7 WMKTK001 GN IX01 ANALYZED Cardinality 1 Bytes 17 SQLExpert使用例 修正后 19 21 代用掛目率 代用評価用時価更新 UPDATE SN SAKIOP KKABU MSD A T SET DAIY KAKEME RTU DAIY HYOKYO JIKA SELECT LEAST T1 DAIYO KKMK2 RATE DECODE SUBSTR T BRAND CD 3 1 0 T2 F KAKE KABU 2 T2 F KAKE KABU 3 T2 F KAKE TENTOU 4 T2 F KAKE TOUSIN K 0 T1 JIKA TOU FROM SN KOKNAI KBSIKI MEG A T1 SN GYOM STS 2 1 A T2 WHERE T USER CD T1 USER CD AND T USER CD T2 USER CD AND T SEC CD T1 MG CD2 AND T1 KUNI KBN2 0 AND T1 SHOHIN KBN2 1 UP TIMESTNP TO NUMBER TO CHAR SYSDATE YYYYMMDDHH24MISS 00 UP PROG ID WSH3715 TRNSCT ID WSH3715 20030130 WHERE SQLExpert使用例 修正后 20 21 PRINC AGENT 5 AND MANAGE PLACE IN 21 22 23 AND DAIY FLG 1 AND EXISTS SELECT X FROM SN KOKNAI KBSIKI MEG A T1 SN GYOM STS 2 1 A T2 WHERE T1 USER CD T2 USER CD AND T1 KUNI KBN2 0 AND T1 SHOHIN KBN2 1 AND T1 DAIYO KBN 1 AND T1 DAIYO STOCK KBN AND T USER CD T1 USER CD AND T SEC CD T1 MG CD2 UPDATE STATEMENT CHOOSE Cost 1 Cost 1 Cardinality 3 Bytes 183 UPDATE OPS LAPROCTA WSHT4202 FILTER TABLE ACCESS FULL OPS LAPROCTA WSHT4202