一次Oracle性能优化过程

背景:

一张2000万的数据表和一张50万的数据表关联查询,关联的条件是=号和>号,还有一些其他等值条件,查询时间要15秒,关键这条语句要查询好多次,在SQL ordered by Reads、SQL ordered by User I/O Wait Time、SQL ordered by Elapsed Time中都能看到这条语句的身影,所以必须要优化。

在家里模拟了相关过程

第一步创建模拟表

第二步分析数据分布状况

第三步不加索引看执行计划

第四步保持原有索引看执行计划

第五步改变为组合索引看执行计划

最后结论是走组合索引方式。

代码示例

要执行的SQL语句
SELECT count(*) from (SELECT a.tid,max(b.sysncdate) FROM maintable a,othertable b WHERE a.tid=b.logid AND a.sysncdate>b.sysncdate AND b.randomi=1 AND a.owner='SYS' GROUP BY a.tid);--创建模拟表
CREATE TABLE maintable asSELECT a.*, DBMS_RANDOM.STRING('l',4) tid, SYSDATE-TRUNC(DBMS_RANDOM.VALUE(1,10001))/24/60 sysncdateFROM dba_tables a,(SELECT level,ROWNUM rn FROM DUALCONNECT BY ROWNUM<=3000);
CREATE TABLE othertable asSELECT TRUNC(DBMS_RANDOM.VALUE(1,101)) as randomi, DBMS_RANDOM.string('~',15) as random_, DBMS_RANDOM.string('l',15) as randoml, DBMS_RANDOM.string('a',15) as randoma, DBMS_RANDOM.string('A',15) as randomuppera, DBMS_RANDOM.string('u',15) as randomu, DBMS_RANDOM.string('U',15) as randomupperu, DBMS_RANDOM.string('x',15) as randomx, DBMS_RANDOM.string('X',15) as randomupperx, DBMS_RANDOM.string('p',15) as randomp, DBMS_RANDOM.string('P',15) as randomupperp, a.tid as logid, SYSDATE-TRUNC(DBMS_RANDOM.VALUE(1,10001))/24/60 sysncdateFROM (SELECT DISTINCT tid FROM maintable ) a--分析表和列的分布
SELECT A.owner,a.table_name,a.num_rows/1024/1024,a.blocks,a.avg_row_len,a.last_analyzed FROM DBA_TABLES A WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');SELECT A.owner,a.segment_name,a.segment_type,a. bytes/1024/1024,a.blocks FROM DBA_SEGMENTS A WHERE A.SEGMENT_NAME IN ('MAINTABLE','OTHERTABLE');SELECT A.owner,a.table_name,a.column_name,a.data_type,a.num_distinct,a.density,a.num_nulls FROM DBA_TAB_COLUMNS A WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');SELECT a.owner,a.index_name,a.index_type,a.table_name,a.clustering_factor,a.num_rows,a.degree,a.last_analyzed FROM DBA_INDEXES A WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as usera@ORCLSQL> SQL> SELECT A.owner,a.table_name,a.num_rows/1024/1024,a.blocks,a.avg_row_len,a.last_analyzed 2 FROM DBA_TABLES A 3 WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');OWNER TABLE_NAME A.NUM_ROWS/1024/1024 BLOCKS AVG_ROW_LEN LAST_ANALYZED------------------------------ ------------------------------ -------------------- ---------- ----------- -------------USERA MAINTABLE 8.11150646209717 311289 259 2020-11-22 0:USERA OTHERTABLE 0.441422462463379 11869 179 2020-11-22 0:
SQL> SELECT A.owner,a.segment_name,a.segment_type,a. bytes/1024/1024,a.blocks 2 FROM DBA_SEGMENTS A 3 WHERE A.SEGMENT_NAME IN ('MAINTABLE','OTHERTABLE');
OWNER SEGMENT_NAME SEGMENT_TYPE A.BYTES/1024/1024 BLOCKS------------------------------ -------------------------------------------------------------------------------- ------------------ ----------------- ----------USERA OTHERTABLE TABLE 96 12288USERA MAINTABLE TABLE 2432 311296
SQL> SELECT A.owner,a.table_name,a.column_name,a.data_type,a.num_distinct,a.density,a.num_nulls 2 FROM DBA_TAB_COLUMNS A 3 WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY NUM_NULLS------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------ ---------- ----------USERA OTHERTABLE SYSNCDATE DATE 10016 9.98402555 0USERA OTHERTABLE LOGID VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMUPPERP VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMP VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMUPPERX VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMX VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMUPPERU VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMU VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMUPPERA VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMA VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOML VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOM_ VARCHAR2 462865 2.16045715 0USERA OTHERTABLE RANDOMI NUMBER 100 0.01 0USERA MAINTABLE SYSNCDATE DATE 10000 0.0001 0USERA MAINTABLE TID VARCHAR2 439709 2.27423136 0USERA MAINTABLE RESULT_CACHE VARCHAR2 1 1 0USERA MAINTABLE SEGMENT_CREATED VARCHAR2 3 0.33333333 0USERA MAINTABLE READ_ONLY VARCHAR2 1 1 0USERA MAINTABLE DROPPED VARCHAR2 1 1 0USERA MAINTABLE COMPRESS_FOR VARCHAR2 2 0.5 7673823USERA MAINTABLE COMPRESSION VARCHAR2 2 0.5 192533USERA MAINTABLE DEPENDENCIES VARCHAR2 1 1 0USERA MAINTABLE CLUSTER_OWNER VARCHAR2 1 1 8393696USERA MAINTABLE MONITORING VARCHAR2 2 0.5 0USERA MAINTABLE SKIP_CORRUPT VARCHAR2 1 1 0USERA MAINTABLE DURATION VARCHAR2 2 0.5 8171771USERA MAINTABLE USER_STATS VARCHAR2 1 1 0USERA MAINTABLE GLOBAL_STATS VARCHAR2 2 0.5 0USERA MAINTABLE ROW_MOVEMENT VARCHAR2 2 0.5 0USERA MAINTABLE CELL_FLASH_CACHE VARCHAR2 1 1 0USERA MAINTABLE FLASH_CACHE VARCHAR2 1 1 0USERA MAINTABLE BUFFER_POOL VARCHAR2 1 1 0USERA MAINTABLE NESTED VARCHAR2 2 0.5 0USERA MAINTABLE SECONDARY VARCHAR2 2 0.5 0USERA MAINTABLE TEMPORARY VARCHAR2 2 0.5 0USERA MAINTABLE IOT_TYPE VARCHAR2 2 0.5 7870239USERA MAINTABLE PARTITIONED VARCHAR2 2 0.5 0USERA MAINTABLE LAST_ANALYZED DATE 539 0.00185528 417582USERA MAINTABLE SAMPLE_SIZE NUMBER 369 0.00271002 417582USERA MAINTABLE TABLE_LOCK VARCHAR2 1 1 0USERA MAINTABLE CACHE VARCHAR2 1 1 0USERA MAINTABLE INSTANCES VARCHAR2 2 0.5 0USERA MAINTABLE DEGREE VARCHAR2 1 1 0USERA MAINTABLE NUM_FREELIST_BLOCKS NUMBER 1 1 896846USERA MAINTABLE AVG_SPACE_FREELIST_BLOCKS NUMBER 1 1 417582USERA MAINTABLE AVG_ROW_LEN NUMBER 236 0.00423728 417582USERA MAINTABLE CHAIN_CNT NUMBER 1 1 417582USERA MAINTABLE AVG_SPACE NUMBER 6 0.16666666 417582USERA MAINTABLE EMPTY_BLOCKS NUMBER 6 0.16666666 896846USERA MAINTABLE BLOCKS NUMBER 94 0.01063829 896846USERA MAINTABLE NUM_ROWS NUMBER 378 0.00264550 417582USERA MAINTABLE BACKED_UP VARCHAR2 1 1 0USERA MAINTABLE LOGGING VARCHAR2 2 0.5 678189USERA MAINTABLE FREELIST_GROUPS NUMBER 1 1 6237550USERA MAINTABLE FREELISTS NUMBER 1 1 6237550USERA MAINTABLE PCT_INCREASE NUMBER 0 1.17570554 8505531USERA MAINTABLE MAX_EXTENTS NUMBER 1 1 3531023USERA MAINTABLE MIN_EXTENTS NUMBER 1 1 3531023USERA MAINTABLE NEXT_EXTENT NUMBER 6 0.16666666 3515751USERA MAINTABLE INITIAL_EXTENT NUMBER 18 0.05555555 3515751USERA MAINTABLE MAX_TRANS NUMBER 2 0.5 192533USERA MAINTABLE INI_TRANS NUMBER 6 0.16666666 192533USERA MAINTABLE PCT_USED NUMBER 3 0.33333333 5748851USERA MAINTABLE PCT_FREE NUMBER 6 0.16666666 192533USERA MAINTABLE STATUS VARCHAR2 1 1 0USERA MAINTABLE IOT_NAME VARCHAR2 50 0.02 8355896USERA MAINTABLE CLUSTER_NAME VARCHAR2 10 0.1 8393696USERA MAINTABLE TABLESPACE_NAME VARCHAR2 9 0.11111111 1014992USERA MAINTABLE TABLE_NAME VARCHAR2 2812 0.00035561 0USERA MAINTABLE OWNER VARCHAR2 30 0.03333333 0
70 rows selected
SQL> SELECT a.owner,a.index_name,a.index_type,a.table_name,a.clustering_factor,a.num_rows,a.degree,a.last_analyzed 2 FROM DBA_INDEXES A 3 WHERE A.TABLE_NAME IN ('MAINTABLE','OTHERTABLE');
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME CLUSTERING_FACTOR NUM_ROWS DEGREE LAST_ANALYZED------------------------------ ------------------------------ --------------------------- ------------------------------ ----------------- ---------- ---------------------------------------- -------------SQL> EXPLAIN PLAN FOR 2 SELECT count(*) from ( 3 SELECT a.tid,max(b.sysncdate) 4 FROM maintable a,othertable b 5 WHERE a.tid=b.logid 6 AND a.sysncdate>b.sysncdate 7 AND b.randomi=1 8 AND a.owner='SYS' 9 GROUP BY a.tid);
Explained
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2942004947--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 87839 (1)| 00:17:| 1 | SORT AGGREGATE | | 1 | | || 2 | VIEW | | 3085 | | 87839 (1)| 00:17:| 3 | HASH GROUP BY | | 3085 | 92550 | 87839 (1)| 00:17:|* 4 | HASH JOIN | | 3085 | 92550 | 87838 (1)| 00:17:|* 5 | TABLE ACCESS FULL| OTHERTABLE | 4629 | 60177 | 3229 (1)| 00:00:|* 6 | TABLE ACCESS FULL| MAINTABLE | 283K| 4706K| 84607 (1)| 00:16:--------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("A"."TID"="B"."LOGID") filter("A"."SYSNCDATE">"B"."SYSNCDATE") 5 - filter("B"."RANDOMI"=1)
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- 6 - filter("A"."OWNER"='SYS')
21 rows selected
SQL> SQL> SQL> create index maintable_index_tid on maintable (tid);Index created
SQL> create index maintable_index_sysncdate on maintable (sysncdate);Index createdSQL> create index othertable_index_logid on othertable (logid);Index createdSQL> create index othertable_index_sysncdate on othertable (sysncdate);Index createdSQL> analyze table maintable estimate statistics sample 5 percent;Table analyzedSQL> analyze table othertable estimate statistics sample 5 percent;Table analyzed
SQL> EXPLAIN PLAN FOR 2 SELECT count(*) from ( 3 SELECT a.tid,max(b.sysncdate) 4 FROM maintable a,othertable b 5 WHERE a.tid=b.logid 6 AND a.sysncdate>b.sysncdate 7 AND b.randomi=1 8 AND a.owner='SYS' 9 GROUP BY a.tid);
Explained

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2942004947--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 88495 (2)| 00:17:| 1 | SORT AGGREGATE | | 1 | | || 2 | VIEW | | 3085 | | 88495 (2)| 00:17:| 3 | HASH GROUP BY | | 3085 | 92550 | 88495 (2)| 00:17:|* 4 | HASH JOIN | | 3085 | 92550 | 88494 (2)| 00:17:|* 5 | TABLE ACCESS FULL| OTHERTABLE | 4629 | 60177 | 3236 (1)| 00:00:|* 6 | TABLE ACCESS FULL| MAINTABLE | 283K| 4706K| 85255 (2)| 00:17:--------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - access("A"."TID"="B"."LOGID") filter("A"."SYSNCDATE">"B"."SYSNCDATE") 5 - filter("B"."RANDOMI"=1)
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- 6 - filter("A"."OWNER"='SYS')
21 rows selected

SQL> EXPLAIN PLAN FOR 2 select count(*) from ( 3 select /*+INDEX(a maintable_index_tid) INDEX(b othertable_index_logid)*/ a.tid,max(b.sysncdate) 4 from maintable a,othertable b 5 where a.tid=b.logid 6 and a.sysncdate>b.sysncdate 7 and b.randomi=1 8 and a.owner='SYS' 9 group by a.tid);
Explained

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1035171181--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Byte--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 || 1 | SORT AGGREGATE | | 1 || 2 | VIEW | | 3085 || 3 | SORT GROUP BY NOSORT | | 3085 | 9255| 4 | NESTED LOOPS | | || 5 | NESTED LOOPS | | 3085 | 9255|* 6 | TABLE ACCESS BY INDEX ROWID| OTHERTABLE | 4629 | 6017| 7 | INDEX FULL SCAN | OTHERTABLE_INDEX_LOGID | 469K||* 8 | INDEX RANGE SCAN | MAINTABLE_INDEX_TID | 19 ||* 9 | TABLE ACCESS BY INDEX ROWID | MAINTABLE | 1 | 1--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- 6 - filter("B"."RANDOMI"=1) 8 - access("A"."TID"="B"."LOGID") 9 - filter("A"."OWNER"='SYS' AND "A"."SYSNCDATE">"B"."SYSNCDATE")
23 rows selected
SQL> drop index maintable_index_tid;Index droppedSQL> drop index maintable_index_sysncdate;Index droppedSQL> drop index othertable_index_sysncdate;Index droppedSQL> drop index othertable_index_logid;Index droppedSQL> create index maintable_index on maintable (tid,sysncdate);Index createdSQL> create index othertable_index on othertable (logid,sysncdate);Index createdSQL> analyze table maintable estimate statistics sample 5 percent;Table analyzedSQL> analyze table othertable estimate statistics sample 5 percent;Table analyzed
SQL> SQL> explain plan for 2 select count(*) from ( 3 select a.tid,max(b.sysncdate) 4 from maintable a,othertable b 5 where a.tid=b.logid 6 and a.sysncdate>b.sysncdate 7 and b.randomi=1 8 and a.owner='SYS' 9 group by a.tid);
Explained

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 2209219092--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 5650| 1 | SORT AGGREGATE | | 1 | || 2 | VIEW | | 3085 | | 5650| 3 | HASH GROUP BY | | 3085 | 92550 | 5650| 4 | NESTED LOOPS | | | || 5 | NESTED LOOPS | | 3085 | 92550 | 5650|* 6 | TABLE ACCESS FULL | OTHERTABLE | 4629 | 60177 | 323|* 7 | INDEX RANGE SCAN | MAINTABLE_INDEX | 10 | ||* 8 | TABLE ACCESS BY INDEX ROWID| MAINTABLE | 1 | 17 | 1--------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 6 - filter("B"."RANDOMI"=1)
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- 7 - access("A"."TID"="B"."LOGID" AND "A"."SYSNCDATE">"B"."SYSNCDATE" AND "A"."SYSNCDATE" IS NOT NULL) 8 - filter("A"."OWNER"='SYS')
23 rows selected
SQL> explain plan for 2 select count(*) from ( 3 select /*+INDEX(a maintable_index) INDEX(b othertable_index)*/ a.tid,max(b.sysncdate) 4 from maintable a,othertable b 5 where a.tid=b.logid 6 and a.sysncdate>b.sysncdate 7 and b.randomi=1 8 and a.owner='SYS' 9 group by a.tid);
Explained
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 3283341491--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Co--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | || 1 | SORT AGGREGATE | | 1 | || 2 | VIEW | | 3085 | || 3 | SORT GROUP BY NOSORT | | 3085 | 92550 || 4 | NESTED LOOPS | | | || 5 | NESTED LOOPS | | 3085 | 92550 ||* 6 | TABLE ACCESS BY INDEX ROWID| OTHERTABLE | 4629 | 60177 || 7 | INDEX FULL SCAN | OTHERTABLE_INDEX | 464K| | 1|* 8 | INDEX RANGE SCAN | MAINTABLE_INDEX | 10 | ||* 9 | TABLE ACCESS BY INDEX ROWID | MAINTABLE | 1 | 17 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------- 6 - filter("B"."RANDOMI"=1) 8 - access("A"."TID"="B"."LOGID" AND "A"."SYSNCDATE">"B"."SYSNCDATE" AND "A". IS NOT NULL) 9 - filter("A"."OWNER"='SYS')
24 rows selected

SQL>

来源:python与大数据分析,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/100436

发表评论

电子邮件地址不会被公开。 必填项已用*标注

侵权联系
分享本页
返回顶部