对SQL语句进行分析和优化

安装和查看ORACLE执行计划
ORACLE在执行SQL语句时使用的步骤的集合叫做执行计划

前起条件：
在目录：$ORACLE_HOME/RDBMS/ADMIN目录下的执行utlxplan.sql

查看执行计划：
EXPLAN PLAN FOR

CREDIT @ORCL>explain plan for select * from creditcard;

Explained.

看SQL执行计划的信息
CREDIT @ORCL>select a.operation,options,object_name,object_type,id,parent_id from plan_table a order by id;

更直观：
CREDIT @ORCL>select lpad(‘ ‘,2*(level-1)) || operation || ‘ ‘ || options || ‘ ‘ || object_name || ‘ ‘ || decode(id,0,’cost=’||position) “Query Plan” from plan_table connect by prior id=parent_id;

Query Plan
————————————————————————————————————————
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
SELECT STATEMENTcost=3
TABLE ACCESSFULLCREDITCARD
TABLE ACCESSFULLCREDITCARD
这个也可以查询：
CREDIT @ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
—-免费云主机域名——————————————————————————————————————–
Plan hash value: 2658862924

——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 9 | 1332 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| CREDITCARD | 9 | 1332 | 3 (0)| 00:00:01 |
——————————————————————————–

Note
—–
– dynamic sampling used for this statement (level=2)

打开自动跟踪功能：
set autotrace on

通过ROWID访问表的执行计划：
SYS AS SYSDBA@ORCL>explain plan for
2 select * from hr.departments where rowid=’AAAR5QAAFAAAACvAAa’;

Explained.

Elapsed: 00:00:00.05
SYS AS SYSDBA@ORCL>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
Plan hash value: 313428322

——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |
——————————————————————————————

8 rows selected.

连接查询的执行计划：

优化案例分析:
提高GROUP BY 语句的效率：
select cardno,sum(amount) from consume group by cardno having cardno=’9555xxxx3′ or cardno=’9555xxxx8′;
——————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————-
| 0 | SELECT STATEMENT | | 114K| 4475K| 175 (3)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 114K| 4475K| 175 (3)| 00:00:03 |
| 3 | TABLE ACCESS FULL| CONSUME | 114K| 4475K| 171 (1)| 00:00:03 |
——————————————————————————-
1. 进行全表扫描TABLE ACCESS FULL
2.执行分组统计HASH GROUP BY
3.执行过滤操作FILTER
分析：过滤操作在分组统计之后，所有分组统计处理的数据量比较大
优化后语句：
select cardno,sum(amount) from consume where “CARDNO”=’9555xxxx3′ OR “CARDNO”=’9555xxxx8′ group by cardno;

使用EXISTS代替IN关键字

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
方法1：运行以下脚本，生成plan_table表

SQL> @/u01/app/oracle/product/10.2/db_1/rdbms/admin/utlxplan.sql

Table created.

SQL> explain plan for
2 select deptno from scott.dept group by deptno;

Explained.

SQL> select id,operation,options,object_name,position from plan_table;

ID OPERATION OPTIONS OBJECT_NAME POSITION
—- ——————– ————— ————————- ———-
0 SELECT STATEMENT 1
1 SORT GROUP BY NOSORT 1
2 INDEX FULL SCAN PK_DEPT 1

方法2：oracle提供v$sql_plan来

SQL> select id,options,operation,object_name,cost
2 from v$sql_plan
3 where object_owner=’SCOTT’;

no rows selected－－没有数据的原因是：刚刚的explain plan for命令只产生执行计划，而不是真正执行语句

SQL> select deptno from scott.dept group by deptno;

DEPTNO
———-
10
20
30
40

SQL> select id,operation,options,object_name,position from plan_table;

ID OPERATION OPTIONS OBJECT_NAME POSITION
—- ——————– ——————– ——————– ———-
0 SELECT STATEMENT 1
1 SORT GROUP BY NOSORT 1
2 INDEX FULL SCAN PK_DEPT 1

相关推荐: 解决redis缓存击穿的方法

这篇文章主要介绍了解决redis缓存击穿的方法，具有一定借鉴价值，需要的朋友可以参考下。希望大家阅读完这篇文章后大有收获。下面让小编带着大家一起了解一下。分布式缓存是网站服务端经常用到的一种技术，在读多写少的业务场景中，通过使用缓存可以有效地支撑高并发的访问量…