SQL 优化准备工作

问题：当接到一个远程调优SQL的任务时，在去现场前应当做那些准备工作呢？

如果能提前得到下述信息，可能有些时候就不需要我们去现场了。

就算不能解决问题，相信对问题的分析与解决效率也是很有帮助的。

 

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

--1、SQL内容

SQL> select * from dual;

 

D

-

X

 

--2.获取SQL的执行计划

SQL> select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------

SQL_ID  a5ks9fhw2v9s1, child number 0

-------------------------------------

select * from dual

 

Plan hash value: 3543395131

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

 

 

--3.统计SQL中每个表的实际数据量

SELECT COUNT(1) FROM &table_name;

 

--4.查看SQL中每个表占用的存储空间

SQL> SELECT SUM(BYTES) / 1024 / 1024 MB

  2    FROM USER_SEGMENTS S

  3   WHERE S.SEGMENT_NAME = UPPER('&TABLE_NAME');

 

--5查看表或视图定义

--5.1 表定义 desc &table_name

SQL> SELECT U.TABLE_NAME, U.COLUMN_NAME, U.NUM_DISTINCT,U.DATA_TYPE,u.DATA_LENGT

  2    FROM USER_TAB_COLUMNS U

  3   WHERE U.TABLE_NAME = UPPER('&TABLE_NAME');

 

--5.2 视图定义

SQL> SELECT VIEW_NAME, TEXT FROM USER_VIEWS WHERE VIEW_NAME = '&view_name';

 

 

--6.统计信息，数据量，并行度

SQL> SELECT T.TABLE_NAME, T.NUM_ROWS, T.LAST_ANALYZED, T.TABLESPACE_NAME,t.degree

  2    FROM USER_TABLES T

  3   WHERE T.TABLE_NAME = UPPER('&TABLE_NAME');

 

 

--7.相关表的索引信息

SQL> SELECT I.TABLE_NAME, I.INDEX_NAME, C.COLUMN_NAME,UC.DATA_TYPE,UC.DATA_LENGTH,

  2         C.COLUMN_POSITION, UC.NUM_DISTINCT,       I.NUM_ROWS,

  3         I.BLEVEL,I.LAST_ANALYZED,I.LEAF_BLOCKS

  4    FROM USER_INDEXES I, USER_IND_COLUMNS C, USER_TAB_COLUMNS UC

  5   WHERE I.TABLE_NAME = upper( '&TABLE_NAME')

  6     AND I.TABLE_NAME = C.TABLE_NAME

  7     AND I.TABLE_NAME = UC.TABLE_NAME

  8     AND C.COLUMN_NAME = UC.COLUMN_NAME

  9     AND I.INDEX_NAME = C.INDEX_NAME

 10     ORDER BY C.INDEX_NAME, C.COLUMN_POSITION;

 

 

--8.用10046事件获取该SQL的trc文件

 

alter session set tracefile_identifier='10046';

 alter session set timed_statistics = true;

 alter session set statistics_level=all;

 alter session set max_dump_file_size = unlimited;

 alter session set events '10046 trace name context forever,level 12';

 -- Execute the queries or operations to be traced here

 select * from dual;

 alter session set events '10046 trace name context off';

 exit;

 

 

--获取10046事件的trc文件

8.1.运行SQL查询

SQL> SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||

  2         p.spid || '.trc' trace_file_name

  3    FROM (SELECT p.spid

  4            FROM SYS.v$mystat m, SYS.v$session s, SYS.v$process p

  5           WHERE m.statistic# = 1

  6             AND s.SID = m.SID

  7             AND p.addr = s.paddr) p,

  8         (SELECT t.INSTANCE

  9            FROM SYS.v$thread t, SYS.v$parameter v

 10           WHERE v.NAME = 'thread'

 11             AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,

 12         (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d

 13  ;

 

--8.2.另一种办法直接到udump目录下，按时间排序找到最新生成的trc文件  

/oracle$ cd $ORACLE_BASE/admin/<sid>/udump

/oracle/app/admin/<sid>/udump$ls -lrt

 

 

--9.查看数据库初始化参数

SQL> SELECT NAME, VALUE

  2    FROM V$PARAMETER

  3   WHERE NAME LIKE 'optimizer%'

  4      OR NAME LIKE 'parallel%'

  5      OR NAME IN ('cursor_sharing', 'db_file_multiblock_read_count',

  6          'hash_area_size', 'hash_join_enabled',

  7          'query_rewrite_enabled', 'query_rewrite_integrity',

  8          'sort_area_size', 'star_transformation_enabled',

  9          'bitmap_merge_area_size', 'partition_view_enabled')

 10   ORDER BY NAME;

 

--10.描述清楚这段SQL的业务逻辑

如果是查询的SQL最好有相关的查询界面的截图。如果能分析清楚这段SQL的业务逻辑，对SQL的调优将会有很大的帮助。

说不定一个小小的改动，增加一个查询时间限制条件，设置某个查询条件为必录等等，将会大大提高SQL的执行效率。我

觉得优化SQL时了解业务逻辑是非常必要的，如果你在做某个行业的SQL优化，你就会明白懂得业务逻辑有多么重要。