ITPUB论坛-中国最专业的IT技术社区

 手机号登录  找回密码
 注册
查看: 5255|回复: 21

[讨论] SQL优化问题,这个有点好玩啊。

[复制链接]
认证徽章
论坛徽章:
0
发表于 2017-12-20 13:47 | 显示全部楼层 |阅读模式
TIM图片20171220133558.png

可以看到历史没有其他执行计划,两个不同id,哈希值一样;这个也能理解,毕竟SQL文本一样
SQL> select t.sql_id,t.plan_hash_value from dba_hist_sql_plan t where t.sql_id in('ad5rxw3scxygc','81j8nmtvuzham');

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
81j8nmtvuzham       139501551
81j8nmtvuzham       139501551
81j8nmtvuzham       139501551
81j8nmtvuzham       139501551
81j8nmtvuzham       139501551
81j8nmtvuzham       139501551
81j8nmtvuzham       139501551
81j8nmtvuzham       139501551
ad5rxw3scxygc       139501551
ad5rxw3scxygc       139501551
ad5rxw3scxygc       139501551

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
ad5rxw3scxygc       139501551
ad5rxw3scxygc       139501551
ad5rxw3scxygc       139501551
ad5rxw3scxygc       139501551
ad5rxw3scxygc       139501551

16 rows selected.

SQL>


SQL> select *
  from table(dbms_xplan.display_awr('81j8nmtvuzham',
                                    139501551,
                                    4064957742,
                                    format => 'ALL'));  2    3    4    5  

SQL_ID 81j8nmtvuzham
--------------------
SELECT EFCTLV.JOB_ID,EFCTLV.CREATOR,EFCTLV.CREATE_TIME,EFCTLV.MODIFIER,E
FCTLV.MODIFY_TIME,EFCTLV.REC_STATUS,EFCTLV.ORG_ID,EFCTLV.TIME_ZONE,EFCTL
V.PM_CODE,EFCTLV.MCLC_PM_CODE,EFCTLV.LINE_NO,EFCTLV.RULE_PM_CODE,EFCTLV.
LIST_NAME,EFCTLV.COLUMN_NAME,EFCTLV.COLUMN_VALUE,EFCTLV.COST_PM_CODE,EFC
TLV.REC_VER,EFCTLV.ENABLED_FLAG  FROM EF_CR_TEMPLATE_LIST_VALUE EFCTLV
where 1=1 and EFCTLV.STATUS = 30 AND EXISTS(select line_no from (select
line_no, max(decode(column_name,'发站编码', column_value)) "发站编码",
max(decode(column_name,'发站', column_value)) "发站",
max(decode(column_name,'到站编码', column_value)) "到站编码",
max(decode(column_name,'到站', column_value)) "到站",
max(decode(column_name,'委托客户', column_value)) "委托客户",
max(decode(column_name,'结算属性', column_value)) "结算属性",
max(decode(column_name,'方向', column_value)) "方向",
max(decode(column_name,'A', column_value)) "A",
max(decode(column_name,'B', column_value)) "B",
max(decode(column_name,'C', column_value)) "C",
max(decode(column_name,'D', column_value)) "D",
max(decode(column_name,'E1', column_value)) "E1",
max(decode(column_name,'E2', column_value)) "E2",
max(decode(column_name,'E3', column_value)) "E3",
max(decode(column_name,'E4', column_value)) "E4",
max(decode(column_name,'E5', column_value)) "E5",
max(decode(column_name,'E6', column_value)) "E6",
max(decode(column_name,'开始时间', column_value)) "开始时间",
max(decode(column_name,'结束时间', column_value)) "结束时间" from
EF_CR_TEMPLATE_LIST_VALUE  where  rule_pm_code=:1 AND CHARGE_ITEM_NO =
:2 AND STATUS = :3 group by line_no,rule_pm_code order by
to_number(line_no) asc) where 1=1 AND EFCTLV.LINE_NO=LINE_NO   AND 发站
like :4  AND 到站 like :5  AND 结算属性 like :6  AND 方向 like :7) AND
EFCTLV.CHARGE_ITEM_NO =:8 AND EFCTLV.RULE_PM_CODE = :9

Plan hash value: 139501551
------------------------------------------------------------------------------------------------------------
| Id  | Operation                                             | Name                                                      | Rows  | Bytes    | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                                                            |            |              |  1171 (100) |          |
|   1 |  FILTER                                                    |                                                           |              |             |                     |          |
|   2 |   TABLE ACCESS BY INDEX ROWID          | EF_CR_TEMPLATE_LIST_VALUE         |    90      | 20070 |   677   (1)     | 00:00:09 |
|   3 |    INDEX RANGE SCAN                              | IDX_ECTLV_CIN_NEW                        | 14011  |            |    59   (0)      | 00:00:01 |
|   4 |   FILTER                                                   |                                                             |            |            |                      |          |
|   5 |    SORT GROUP BY NOSORT                    |                                                             |     1      |    72  |    11   (0)        | 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID         | EF_CR_TEMPLATE_LIST_VALUE            |     1    |    72  |    11   (0)        | 00:00:01 |
|   7 |      INDEX RANGE SCAN                            | IDX_ECTLV_LC_NEW                             |    18 |            |     3   (0)        | 00:00:01 |
------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1        / EFCTLV@SEL$1
   3 - SEL$1        / EFCTLV@SEL$1
   4 - SEL$9F15F3D6

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
6 - SEL$9F15F3D6 / EF_CR_TEMPLATE_LIST_VALUE@SEL$3
   7 - SEL$9F15F3D6 / EF_CR_TEMPLATE_LIST_VALUE@SEL$3


58 rows selected.

SQL>


第二个SQL_ID:

SQL>  select *
  from table(dbms_xplan.display_awr('ad5rxw3scxygc',
                                    139501551,
                                    4064957742,
                                    format => 'ALL'));     2    3    4    5  

SQL文本略。。。。

这个执行计划与上面一样,我就不排版了_(:з」∠)_。。
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 139501551

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                           |       |       |  1171 (100)|          |
|   1 |  FILTER                        |                           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EF_CR_TEMPLATE_LIST_VALUE |    90 | 20070 |   677   (1)| 00:00:09 |
|   3 |    INDEX RANGE SCAN            | IDX_ECTLV_CIN_NEW         | 14011 |       |    59   (0)| 00:00:01 |
|   4 |   FILTER                       |                           |       |       |            |          |
|   5 |    SORT GROUP BY NOSORT        |                           |     1 |    72 |    11   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
|   6 |     TABLE ACCESS BY INDEX ROWID| EF_CR_TEMPLATE_LIST_VALUE |     1 |    72 |    11   (0)| 00:00:01 |
|   7 |      INDEX RANGE SCAN          | IDX_ECTLV_LC_NEW          |    18 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1        / EFCTLV@SEL$1
   3 - SEL$1        / EFCTLV@SEL$1
   4 - SEL$9F15F3D6

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
6 - SEL$9F15F3D6 / EF_CR_TEMPLATE_LIST_VALUE@SEL$3
   7 - SEL$9F15F3D6 / EF_CR_TEMPLATE_LIST_VALUE@SEL$3


58 rows selected.

SQL>


那么我的疑问是:一样的SQL,一样的执行计划,都执行了上千次,为啥执行时间不一样呢??(实际上这个是我宕机前收集的AWR报告,是的,我RAC宕了。。_(:з」∠)_)

v$sqlarea和v$sql里面目前看不到这些SQL_ID。



认证徽章
论坛徽章:
0
发表于 2017-12-20 14:14 | 显示全部楼层
SQL文本一样, 会有两个不同的SQL_ID ?  请确认SQL文本是否100%一样,注意空格和绑定变量名字

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2017-12-20 14:18 | 显示全部楼层
Johnelse 发表于 2017-12-20 14:14
SQL文本一样, 会有两个不同的SQL_ID ?  请确认SQL文本是否100%一样,注意空格和绑定变量名字

这个是应用程序写好的,一个功能SQL不会写两遍的_(:з」∠)_;然后我找出来比对了。的确一模一样;

使用道具 举报

回复
认证徽章
论坛徽章:
18
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29ITPUB15周年纪念
日期:2018-03-20 15:25:18托尼托尼·乔巴
日期:2018-02-05 08:12:49蒙奇·D·路飞
日期:2018-01-31 16:03:23秀才
日期:2018-01-02 15:35:05技术图书徽章
日期:2018-01-02 15:30:09技术图书徽章
日期:2017-12-21 14:05:31妮可·罗宾
日期:2017-11-17 10:43:23技术图书徽章
日期:2017-11-17 09:13:02娜美
日期:2017-12-15 13:56:33
发表于 2017-12-20 14:49 | 显示全部楼层
影舞狼 发表于 2017-12-20 14:18
这个是应用程序写好的,一个功能SQL不会写两遍的_(:з」∠)_;然后我找出来比对了。的确一模一样;

嘗試比較一下?
select dbms_lob.compare((select sql_fulltext from v$sql where SQL_ID='81j8nmtvuzham'),(select sql_fulltext from v$sql where SQL_ID='ad5rxw3scxygc')) from dual

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2017-12-20 15:38 | 显示全部楼层
guocun09 发表于 2017-12-20 14:49
嘗試比較一下?
select dbms_lob.compare((select sql_fulltext from v$sql where SQL_ID='81j8nmtvuzha ...

SQL> select dbms_lob.compare((select sql_fulltext from v$sql where SQL_ID='81j8nmtvuzham'),(select sql_fulltext from
v$sql where SQL_ID='ad5rxw3scxygc')) from dual;
DBMS_LOB.COMPARE((SELECTSQL_FULLTEXTFROMV$SQLWHERESQL_ID='81J8NMTVUZHAM'),(SELEC
--------------------------------------------------------------------------------


SQL>

兽人族永不畏惧!!!!┗|`O′|┛ 嗷~~

使用道具 举报

回复
论坛徽章:
186
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39马上有钱
日期:2014-10-27 09:26:57马上有房
日期:2014-11-07 08:46:05马上有钱
日期:2014-11-12 09:33:24马上有钱
日期:2014-11-24 15:17:08马上有对象
日期:2015-01-14 17:33:15沸羊羊
日期:2015-02-11 09:07:41懒羊羊
日期:2015-03-04 09:03:43暖羊羊
日期:2015-03-04 14:50:37
发表于 2017-12-20 15:41 | 显示全部楼层
文本一定不同.除非http://blog.itpub.net/267265/viewspace-2147197/

也许结尾有空格之类的呢?

使用ora_hash(sql_text)看看是否一样.

使用道具 举报

回复
认证徽章
论坛徽章:
18
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29ITPUB15周年纪念
日期:2018-03-20 15:25:18托尼托尼·乔巴
日期:2018-02-05 08:12:49蒙奇·D·路飞
日期:2018-01-31 16:03:23秀才
日期:2018-01-02 15:35:05技术图书徽章
日期:2018-01-02 15:30:09技术图书徽章
日期:2017-12-21 14:05:31妮可·罗宾
日期:2017-11-17 10:43:23技术图书徽章
日期:2017-11-17 09:13:02娜美
日期:2017-12-15 13:56:33
发表于 2017-12-20 15:47 | 显示全部楼层
影舞狼 发表于 2017-12-20 15:38
SQL> select dbms_lob.compare((select sql_fulltext from v$sql where SQL_ID='81j8nmtvuzham'),(select ...

查詢結果為空可能是 sql_id不在 V$SQL 中,改DBA_HIST_SQLTEXT看有沒有,試試:

select dbms_lob.compare(
(select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='81j8nmtvuzham'),
(select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID='ad5rxw3scxygc'))
from dual

使用道具 举报

回复
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
发表于 2017-12-20 16:12 | 显示全部楼层
这个很正常啊,谁告诉的执行计划一样,执行时间就必须一样?举个最简单的例子:
SQL中有绑定变量,在选择性好的列值上,可能就扫描很少的数据行,执行时间可能就很短,而在选择性不好的列值上,可能需要扫描很多行,执行时间可能就很长。
楼主的SQL语句里有绑定变量,可能就会存在上述情况。
此外,SQL_ID不仅仅和SQL语句的文本有关,记得在之前的帖子里讨论过这个问题。

使用道具 举报

回复
认证徽章
论坛徽章:
0
发表于 2017-12-20 16:13 | 显示全部楼层
guocun09 发表于 2017-12-20 15:47
查詢結果為空可能是 sql_id不在 V$SQL 中,改DBA_HIST_SQLTEXT看有沒有,試試:

select dbms_lob.comp ...

结果为-1;这个是什么意思呢,这个包没用过

使用道具 举报

回复
论坛徽章:
186
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39马上有钱
日期:2014-10-27 09:26:57马上有房
日期:2014-11-07 08:46:05马上有钱
日期:2014-11-12 09:33:24马上有钱
日期:2014-11-24 15:17:08马上有对象
日期:2015-01-14 17:33:15沸羊羊
日期:2015-02-11 09:07:41懒羊羊
日期:2015-03-04 09:03:43暖羊羊
日期:2015-03-04 14:50:37
发表于 2017-12-20 16:15 | 显示全部楼层
影舞狼 发表于 2017-12-20 16:13
结果为-1;这个是什么意思呢,这个包没用过

ora_hash(sql_text) 看看一样吗?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表