Thread: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题

最新推荐文章于 2024-06-06 21:15:00 发布
最新推荐文章于 2024-06-06 21:15:00 发布
阅读量533 收藏
点赞数
分类专栏： oracle sql 语句调优及相关技术
关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-25 上午12:35 		 
Click to report abuse... Click to reply to this threadReply
事发问题，测试环境程序测试完后第二天，发布于生产环境，结果出现了大量用户堵塞问题，查看数据库为某一条语句效率很低，紧急调整后事故解决，事后开总结会，开发的提出在测试环境语句效率很高，生产环境却很低，询问本人
本人经拙劣测试把语句摘到最小化，发现确实效率差距巨大，以下为语句
SELECT count(*) FROM PAY_DTL T WHERE
(T.MER_ID = '0000043686' OR T.PAY_CUST_ID = '0000043686' OR T.RECV_CUST_ID= '0000043686' OR T.ORDER_ID IN (SELECT I.ORDER_ID FROM AUDIT_GOPAY_ORDER_ITEM I WHERE I.RECV_EMAIL = 'yanshi_gfb@126.com'))
AND T.EXT_TXN_DT >= '20120923' AND T.EXT_TXN_DT <= '20121023';

当时生产库PAY表有480W行，测试库有350W行，差距很小，但执行计划结果如下：
生产库：
Execution Plan
Plan hash value: 4060007937

IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT 1491608 (1)00:00:20
1SORT AGGREGATE 149  
* 2FILTER     
3TABLE ACCESS BY INDEX ROWIDPAY_DTL3255155K1608 (1)00:00:20
* 4INDEX RANGE SCANIDX_PAY_DTL23255 69 (2)00:00:01
* 5TABLE ACCESS FULLAUDIT_GOPAY_ORDER_ITEM1355 (0)00:00:01


Predicate Information (identified by operation id):

2 - filter("T"."PAY_CUST_ID"='0000043686' OR "T"."MER_ID"='0000043686' OR
"T"."RECV_CUST_ID"='0000043686' OR EXISTS (SELECT 0 FROM "AUDIT_GOPAY_ORDER_ITEM" "I" WHERE
"I"."RECV_EMAIL"='tukevin003@163.com' AND "I"."ORDER_ID"=:B1))
4 - access("T"."EXT_TXN_DT">='20120923' AND "T"."EXT_TXN_DT"<='20121023')
filter((("T"."TXN_TYPE"='00' OR "T"."TXN_TYPE"='01' OR "T"."TXN_TYPE"='02' OR
"T"."TXN_TYPE"='03' OR "T"."TXN_TYPE"='06' OR "T"."TXN_TYPE"='25' OR "T"."TXN_TYPE"='27' OR
"T"."TXN_TYPE"='45' OR "T"."TXN_TYPE"='49' OR "T"."TXN_TYPE"='64' OR "T"."TXN_TYPE"='71' OR
"T"."TXN_TYPE"='83' OR "T"."TXN_TYPE"='90' OR "T"."TXN_TYPE"='91' OR "T"."TXN_TYPE"='93' OR
"T"."TXN_TYPE"='96') OR "T"."TXN_TYPE"='24' AND ("T"."TXN_STA_CD"='2' OR "T"."TXN_STA_CD"='6'))
AND "T"."TXN_STA_CD"<>'0' AND "T"."TXN_STA_CD"'x' AND "T"."TXN_STA_CD"'y' AND
"T"."TXN_STA_CD"'z')
5 - filter("I"."RECV_EMAIL"='tukevin003@163.com' AND "I"."ORDER_ID"=:B1)

Statistics

1 recursive calls
0 db block gets
9257155 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

测试库：
Execution Plan
Plan hash value: 4060007937

IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT 155787 (0)00:00:10
1SORT AGGREGATE 155  
* 2FILTER     
3TABLE ACCESS BY INDEX ROWIDPAY_DTL1914102K787 (0)00:00:10
* 4INDEX RANGE SCANIDX_PAY_DTL21914 18 (0)00:00:01
* 5TABLE ACCESS FULLAUDIT_GOPAY_ORDER_ITEM1373 (0)00:00:01


Predicate Information (identified by operation id):

2 - filter("T"."RECV_CUST_ID"='0000043686' OR "T"."PAY_CUST_ID"='0000043686' OR
"T"."MER_ID"='0000043686' OR EXISTS (SELECT 0 FROM "AUDIT_GOPAY_ORDER_ITEM" "I" WHERE
"I"."RECV_EMAIL"='tukevin003@163.com' AND "I"."ORDER_ID"=:B1))
4 - access("T"."EXT_TXN_DT">='20120923' AND "T"."EXT_TXN_DT"<='20121023')
filter("T"."TXN_STA_CD"<>'0' AND "T"."TXN_STA_CD"'z' AND "T"."TXN_STA_CD"'x' AND
"T"."TXN_STA_CD"'y' AND (("T"."TXN_TYPE"='00' OR "T"."TXN_TYPE"='01' OR "T"."TXN_TYPE"='02'
OR "T"."TXN_TYPE"='03' OR "T"."TXN_TYPE"='06' OR "T"."TXN_TYPE"='25' OR "T"."TXN_TYPE"='27' OR
"T"."TXN_TYPE"='45' OR "T"."TXN_TYPE"='49' OR "T"."TXN_TYPE"='64' OR "T"."TXN_TYPE"='71' OR
"T"."TXN_TYPE"='83' OR "T"."TXN_TYPE"='90' OR "T"."TXN_TYPE"='91' OR "T"."TXN_TYPE"='93' OR
"T"."TXN_TYPE"='96') OR "T"."TXN_TYPE"='24' AND ("T"."TXN_STA_CD"='2' OR "T"."TXN_STA_CD"='6')))
5 - filter("I"."RECV_EMAIL"='tukevin003@163.com' AND "I"."ORDER_ID"=:B1)

Statistics

1 recursive calls
0 db block gets
3581 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

事后我怀疑是因为语句嵌套的问题，导致100W的数据差异导致逻辑读和效率差距很大，事后我将生产库的数据完全拷贝到测试库，2个库数据完全一样的时候，执行同样的语句，测试库逻辑读只上升到170W，效率还是很快，生产库为1000多W
生产库为RAC，测试库为单机，怀疑结构问题，将语句在生产库的物理DG端（单机，本地磁盘和测试库配置一样的服务器）执行，仍为1000W逻辑读，效率很低
数据库版本都为11.2.0.3

xhr8334

Posts: 1
Registered: 10/25/12
Re: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-25 上午12:40   in response to: Ming in response to: Ming 		 
Click to report abuse... Click to reply to this threadReply
从你描述看，考虑几方面问题。
1 你在主库和DG侧做，结果一样。这就证明了我之前的怀疑，是不是表的计划有问题。因为DG是抽日志的，两侧可以简单认为是镜像，起码在你这个问题上可以这么理解。
2 因为你硬件环境不同，Oracle的引擎会自动优化执行过程，是叫ADDB还是叫什么的，是一个来自10g的新特性。

3 关于你总监发飙的事，我觉得，如果可以的话，你生产库上做个move和gather，再试试看。

开个trace和10046吧。
Robinson

Posts: 1
Registered: 07/29/11
Re: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-25 上午1:24   in response to: Ming in response to: Ming
Helpful
Click to report abuse... Click to reply to this threadReply
其实这个问题很简单，但是可能对这种热------>对ORACLE CBO，对执行计划认识不深的人，可能无法看出此类问题。
出现这种情况，肯定是SQL语句执行计划引起的。大家注意看执行计划里面ID=2是FILTER
FILTER是什么意思呢？它表示说ID=3 返回多少数据，那么ID=5 就会执行 ID=3-ID=2 这么多次。

这里ID=3返回3255行，其实是不可信的，具体你可以自己算一下它返回多少行。通常来说CBO会把返回基数算少。
那么这里我假设它返回3W行记录， 然后呢 你的 测试库 与 生产库 虽然数据量 相同。但是 他们的BLOCK数目肯定不同。
生产库 肯定是在运行中，那么它BLOCK肯定是要经常变化，也就是说生产库的 BLOCK 数目 肯定比 测试库大------着个理解吗？

好， 那么我假设 生产库比 测试库的 BLOCK数目大 500个 ，我假设FILTER 过滤之后返回数据 1W行
那么FILTER 循环就会循环2W次， 那么逻辑读的差异 就是 2W*500=1KW

着个就是根本原因。

具体的解决方案你也知道了，就是把SQL进行改写，用UNION 代替OR 。
我来举个例子，方便你 和你的总监解释，你一可以自己模拟一下：

create table test1 as select * from dba_objects;

create index idx1 on test1(owner);

create table test2 as select * from test1;

SQL> select count(*) from test2;

COUNT(*)
72577

create table test3 as select * from test1 where rownum<62577; ----减去1000行的记录

create index idx2 on test2(owner);
create index idx3 on test3(owner);

select count(*) from test1 a where owner='SCOTT' or
object_id in(select object_id from test2 where owner='SCOTT');

select count(*) from test1 a where owner='SCOTT' or
object_id in(select object_id from test3 where owner='SCOTT');

SQL> select count(*) from test1 a where owner='SCOTT' or
2 object_id in(select object_id from test2 where owner='SCOTT');

已用时间: 00: 00: 03.60

执行计划

Plan hash value: 3372948367

IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT 1301063 (1)00:00:13
1SORT AGGREGATE 130  
* 2FILTER     
3TABLE ACCESS FULLTEST1931792729K1063 (1)00:00:13
* 4TABLE ACCESS BY INDEX ROWIDTEST21302 (0)00:00:01
* 5INDEX RANGE SCANIDX220 1 (0)00:00:01


Predicate Information (identified by operation id):

2 - filter("OWNER"='SCOTT' OR EXISTS (SELECT 0 FROM "TEST2" "TEST2" WHERE
"OWNER"='SCOTT' AND "OBJECT_ID"=:B1))
4 - filter("OBJECT_ID"=:B1)
5 - access("OWNER"='SCOTT')

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

统计信息

0 recursive calls
0 db block gets
436386 consistent gets -----------逻辑读43w
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


查询 test3

SQL> select count(*) from test1 a where owner='SCOTT' or
2 object_id in(select object_id from test3 where owner='SCOTT');

已用时间: 00: 00: 00.54

执行计划

Plan hash value: 748224891

IdOperationNameRowsBytesCost (%CPU)Time

0SELECT STATEMENT 1301063 (1)00:00:13
1SORT AGGREGATE 130  
* 2FILTER     
3TABLE ACCESS FULLTEST1931792729K1063 (1)00:00:13
* 4TABLE ACCESS BY INDEX ROWIDTEST31301 (0)00:00:01
* 5INDEX RANGE SCANIDX31 1 (0)00:00:01


Predicate Information (identified by operation id):

2 - filter("OWNER"='SCOTT' OR EXISTS (SELECT 0 FROM "TEST3" "TEST3" WHERE
"OWNER"='SCOTT' AND "OBJECT_ID"=:B1))
4 - filter("OBJECT_ID"=:B1)
5 - access("OWNER"='SCOTT')

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

统计信息

0 recursive calls
0 db block gets
73601 consistent gets -----逻辑读是7W
0 physical reads
0 redo size
430 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

两个表数据只少了1K行，为什么逻辑读 差别这么大啊？？？？ 着个就是 根本原因。

顺便打个广告，有想学SQL优化的，可以找我 QQ 692162374 。保证教会你一眼看出执行计划哪里有问题的能力，保证教会你5分钟内解决超大型SQL的能力。

帖子经 Robinson编辑过

960105

Posts: 3
Registered: 09/19/12
Re: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-25 上午6:22   in response to: Robinson in response to: Robinson 		 
Click to report abuse... Click to reply to this threadReply
大家注意看执行计划里面ID=2是FILTER
FILTER是什么意思呢？它表示说ID=3 返回多少数据，那么ID=5 就会执行 ID=3-ID=2 这么多次。大家注意看执行计划里面ID=2是FILTER

这个可以再解释一下么，没看明白

Liu Maclean(刘相兵)

Posts: 684
Registered: 08/21/12
Re: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-25 上午7:37   in response to: Ming in response to: Ming
ask_maclean , oracle数据库官方中文技术论坛 , 甲骨文中国
 
Click to report abuse... Click to reply to this threadReply
在 测试库和 产品库 分别加入 gather_plan_statistics HINT 执行SQL， 并贴出 A-Rows 的执行计划

怀疑和 基数 cardnality 有关

具体参考 http://www.askmaclean.com/archives/gather-more-plan-statistics-by-gather_plan_statistics-hint.html

Gather more plan statistics by gather_plan_statistics hint

March 22, 2011 By Leave a Comment

在10g以后我们可以通过利用gather_plan_statistics提示来了解更多的SQL执行统计信息，具体使用方法如下：

SQL> set linesize 150
SQL> set pagesize 2000
SQL> set autotrace traceonly exp

SQL> select avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;

Execution Plan
----------------------------------------------------------
Plan hash value: 3294250112

---------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   1 |  HASH GROUP BY		      | 	    |	 27 |	621 |	  5  (20)| 00:00:01 |
|   2 |   NESTED LOOPS		      | 	    |	106 |  2438 |	  4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	107 |	749 |	  3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	  1 |	 16 |	  1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	  1 |	    |	  0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

SQL> show parameter cursor_sharing

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing			     string	 EXACT

SQL>  show parameter statistics_level

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
statistics_level		     string	 TYPICAL

SQL> set autotrace off;

SQL> select /*+ gather_plan_statistics */   avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME;

SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */  avg(SALARY),DEPARTMENT_NAME from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME

Plan hash value: 3294250112
-------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |    106 |00:00:00.01 |	  106 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

/* 可以从starts列看到某种操作执行了多少次，例如这里的INDEX UNIQUE SCAN为107次 */

/*也可以通过SQL_ID来定位计划信息 */


SQL> select t.* 
from v$sql s 
   , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID' ;
  

Enter value for sql_id: bctzu9xuxay18 

old   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = '&SQL_ID'
new   3:    , table(dbms_xplan.display_cursor(s.sql_id,s.child_number,'ALL IOSTATS LAST')) t where s.sql_id = 'bctzu9xuxay18'

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	bctzu9xuxay18, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ avg(SALARY),DEPARTMENT_NAME from 
employees e,departments d where
e.DEPARTMENT_ID=d.DEPARTMENT_ID group by DEPARTMENT_NAME

Plan hash value: 3294250112

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		      | Name	    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY		      | 	    |	   1 |	   27 |   621 |     5  (20)| 00:00:01 |     11 |00:00:00.01 |	  219 |
|   2 |   NESTED LOOPS		      | 	    |	   1 |	  106 |  2438 |     4	(0)| 00:00:01 |    106 |00:00:00.01 |	  219 |
|   3 |    TABLE ACCESS FULL	      | EMPLOYEES   |	   1 |	  107 |   749 |     3	(0)| 00:00:01 |    107 |00:00:00.01 |	    7 |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |	 107 |	    1 |    16 |     1	(0)| 00:00:01 |    106 |00:00:00.01 |	  212 |
|*  5 |     INDEX UNIQUE SCAN	      | DEPT_ID_PK  |	 107 |	    1 |       |     0	(0)|	      |    106 |00:00:00.01 |	  106 |
---------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / D@SEL$1
   5 - SEL$1 / D@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "DEPARTMENT_NAME"[VARCHAR2,30], AVG("SALARY")[22]
   2 - "SALARY"[NUMBER,22], "DEPARTMENT_NAME"[VARCHAR2,30]
   3 - "SALARY"[NUMBER,22], "E"."DEPARTMENT_ID"[NUMBER,22]
   4 - "DEPARTMENT_NAME"[VARCHAR2,30]
   5 - "D".ROWID[ROWID,10]

SQL> alter session set statistics_level=ALL;
Session altered.


/* 在session级别设置statistics_level为ALL,可以为我们提供更为详尽的执行统计信息 */
Ming

Posts: 11
Registered: 04/14/12
Re: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-25 下午6:26   in response to: Liu Maclean(刘相兵) in response to: Liu Maclean(刘相兵) 		 
Click to report abuse... Click to reply to this threadReply
您好，加完HINT执行计划如下：

生产库：

PLAN_TABLE_OUTPUT
SQL_ID aphv5cu3fckpy, child number 0
SELECT /*+ gather_plan_statistics */ count(*) FROM CIECCPAY.PAY_DTL T
WHERE (T.MER_ID = '0000043686' OR T.PAY_CUST_ID = '0000043686' OR
T.RECV_CUST_ID= '0000043686' OR T.ORDER_ID IN (SELECT I.ORDER_ID FROM
CIECCPAY.AUDIT_GOPAY_ORDER_ITEM I WHERE I.RECV_EMAIL =
'yanshi_gfb@126.com')) AND T.EXT_TXN_DT >= '20120923' AND T.EXT_TXN_DT
<= '20121023'

Plan hash value: 4060007937

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

0SELECT STATEMENT 1 100:00:11.4310M
1SORT AGGREGATE 11100:00:11.4310M
* 2FILTER 1 200:00:11.4310M
3TABLE ACCESS BY INDEX ROWIDPAY_DTL17900645K00:00:01.69427K
* 4INDEX RANGE SCANIDX_PAY_DTL217900645K00:00:00.356253
* 5TABLE ACCESS FULLAUDIT_GOPAY_ORDER_ITEM645K1100:00:09.289682K


Predicate Information (identified by operation id):

2 - filter(("T"."MER_ID"='0000043686' OR "T"."RECV_CUST_ID"='0000043686' OR
"T"."PAY_CUST_ID"='0000043686' OR IS NOT NULL))
4 - access("T"."EXT_TXN_DT">='20120923' AND "T"."EXT_TXN_DT"<='20121023')
5 - filter(("I"."RECV_EMAIL"='yanshi_gfb@126.com' AND "I"."ORDER_ID"=:B1))

测试库：
PLAN_TABLE_OUTPUT

SQL_ID aphv5cu3fckpy, child number 0
SELECT /*+ gather_plan_statistics */ count(*) FROM CIECCPAY.PAY_DTL T
WHERE (T.MER_ID = '0000043686' OR T.PAY_CUST_ID = '0000043686' OR
T.RECV_CUST_ID= '0000043686' OR T.ORDER_ID IN (SELECT I.ORDER_ID FROM
CIECCPAY.AUDIT_GOPAY_ORDER_ITEM I WHERE I.RECV_EMAIL =
'yanshi_gfb@126.com')) AND T.EXT_TXN_DT >= '20120923' AND T.EXT_TXN_DT
<= '20121023'

Plan hash value: 4060007937

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

0SELECT STATEMENT 1 100:00:06.124321K
1SORT AGGREGATE 11100:00:06.124321K
* 2FILTER 1 200:00:06.124321K
3TABLE ACCESS BY INDEX ROWIDPAY_DTL17942645K00:00:01.33448K
* 4INDEX RANGE SCANIDX_PAY_DTL217942645K00:00:00.284343
* 5TABLE ACCESS FULLAUDIT_GOPAY_ORDER_ITEM645K1100:00:04.433872K


Predicate Information (identified by operation id):

2 - filter(("T"."MER_ID"='0000043686' OR "T"."RECV_CUST_ID"='0000043686' OR
"T"."PAY_CUST_ID"='0000043686' OR IS NOT NULL))
4 - access("T"."EXT_TXN_DT">='20120923' AND "T"."EXT_TXN_DT"<='20121023')
5 - filter(("I"."RECV_EMAIL"='yanshi_gfb@126.com' AND "I"."ORDER_ID"=:B1))

anbob

Posts: 23
Registered: 07/15/11
Re: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-25 下午8:19   in response to: Ming in response to: Ming 		 
Click to report abuse... Click to reply to this threadReply
考虑PROD 的 STATISTICS reads 有构建CR block 因素
Liu Maclean(刘相兵)

Posts: 684
Registered: 08/21/12
Re: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-26 上午1:54   in response to: Ming in response to: Ming
ask_maclean , oracle数据库官方中文技术论坛 , 甲骨文中国
 
Click to report abuse... Click to reply to this threadReply
Plan hash value: 4060007937 执行计划都一样

下一步检测段的状况，请参考一下脚本:

http://www.askmaclean.com/archives/%E8%84%9A%E6%9C%ACsegment-space-usage-explorer.html

脚本:Segment Space Usage Explorer

October 24, 2011 By Leave a Comment

Script:以下脚本可以用于诊断segement space usage问题：

set serveroutput on;
 
      declare
        v_unformatted_blocks number;
        v_unformatted_bytes number;
        v_fs1_blocks number;
        v_fs1_bytes number;
        v_fs2_blocks number;
        v_fs2_bytes number;
        v_fs3_blocks number;
        v_fs3_bytes number;
       v_fs4_blocks number;
       v_fs4_bytes number;
       v_full_blocks number;
       v_full_bytes number;
     begin
     dbms_space.space_usage ('&OWNER', '&TABNAME', 'TABLE', v_unformatted_blocks,
     v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
     v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
     dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
     dbms_output.put_line('FS1 Blocks              = '||v_fs1_blocks);
     dbms_output.put_line('FS2 Blocks              = '||v_fs2_blocks);
     dbms_output.put_line('FS3 Blocks              = '||v_fs3_blocks);
     dbms_output.put_line('FS4 Blocks              = '||v_fs4_blocks);
     dbms_output.put_line('Full Blocks              = '||v_full_blocks);
     end;
     / 
         
         
SELECT TABLE_NAME ,  (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UPPER('&OWNER') order by 2 desc;
Ming

Posts: 11
Registered: 04/14/12
Re: 关于同样的语句和执行计划，同样的数据量效率差距巨大的问题
Posted: 2012-10-29 上午12:56   in response to: Liu Maclean(刘相兵) in response to: Liu Maclean(刘相兵) 		 
Click to report abuse... Click to reply to this threadReply
您好，测试已经完成，数据如下（表只取了前8）：

生产库：
Unformatted Blocks = 472
FS1 Blocks = 32
FS2 Blocks = 39
FS3 Blocks = 109
FS4 Blocks = 2391
Full Blocks = 283035

TABLE_NAME Data lower than HWM in MB
-------------------------
MERCHANT_ORDER_NOTIFY_INFO 447.694523
VC_ACCT_DTL 392.556433
PAY_DTL 365.024868
PAY_DTL_EXPAND_INFO 135.767008
BANK_ORDER_CHECK_LOG 122.174011
USER4AMOUNT 79.888752
BANK_FEE_DTL 57.0051517
ANTI_PHISHING_RECORD 15.6126385
CON_TXN_LOG 10.9062214
BANK_NUMBER 9.50930023
ALLOT_DTL 3.31555939


测试库：
Unformatted Blocks = 0
FS1 Blocks = 1
FS2 Blocks = 3
FS3 Blocks = 7
FS4 Blocks = 62
Full Blocks = 280107

TABLE_NAME Data lower than HWM in MB

-------------------------
MERCHANT_ORDER_NOTIFY_INFO 396.420166
VC_ACCT_DTL 392.556433
PAY_DTL 343.970374
PAY_DTL_EXPAND_INFO 139.431835
BANK_ORDER_CHECK_LOG 134.158684
USER4AMOUNT 79.888752
BANK_FEE_DTL 57.0051517
CON_TXN_LOG 10.9062214
ANTI_PHISHING_RECORD 10.7282228
BANK_NUMBER 9.50930023
RISK_TRANS_LINE 4.43682384

转载： https://forums.oracle.com/forums/thread.jspa?threadID=2456493&tstart=0
yrg5101
关注
专栏目录
MySQL数据库SQL语句执行顺序及优化
Damon_zqt的博客
05-11 1034
一、sql执行顺序 (1)from (3) join (2) on (4) where (5)group by(开始使用select中的别名，后面的语句中都可以使用) (6) avg,sum… (7)having (8) select (9) distinct (10) order by (11) limit 从这个顺序中我们不难发现，所有的 查询语句都是从from开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。 第一步：首先对from子句中的前两个表
简单sql改写
weixin_34060299的博客
06-01 137
select * from test1 where owner='SCOTT' or object_id in(select object_id from test2 where owner='SCOTT');select * from test1 where owner='SCOTT' unionselect * from test1 where object_id in...
oracle执行计划完全一样,急求解答:两个相同的数据库的执行计划差别怎么那么大，附带执行计划!!!...
weixin_35009700的博客
04-05 318
最近工作中遇到一个很郁闷的问题:前提:我一个培训环境，一个测试环境，均是AIX 5.3的操作系统，2个CPU ,6G内存,测试环境是1个月前通过rman方式将数据从培训环境中导入的检查确认参数文件保持一致,表结构一致,数据量差别非常小问题现象:一个sql语句在两个配置和表结构相同的数据库中的执行计划不同，而且差别太大,查询结果出来后一个是5秒,一个几分钟sql语句:select *from (se...
长文图解：单张表数据量太大问题怎么解决？请记住这六个字
JAVA前线技术专栏
12-19 6647
欢迎大家关注公众号「JAVA前线」查看更多精彩分享文章，主要包括源码分析、实际应用、架构思维、职场分享、产品思考等等，同时欢迎大家加我微信「java_front」一起交流学习 1 文章概述 在业务发展初期单表完全可以满足业务需求，在阿里巴巴开发手册也建议：单表行数超过500万行或者单表容量超过2GB才推荐进行分库分表，如果预计三年后数据量根本达不到这个级别，请不要在创建表时就分库分表。 但是随着业务的发展和深入，单表数据量不断增加，逐渐成为业务系统的瓶颈。这是为什么呢？ 从宏观层面分析任何物体都必然有其.
面试官问单表数据量大一定要分库分表吗？我们用六个字和十张图回答
HollisChuang's Blog
06-29 1308
1 文章概述在业务发展初期单表完全可以满足业务需求，在阿里巴巴开发手册也建议：单表行数超过500万行或者单表容量超过2GB才推荐进行分库分表，如果预计三年后数据量根本达不到这个级别，请不要...
mysql快速导入数据_Mysql --学习：大量数据快速导入导出
weixin_29109261的博客
01-18 240
声明：此文供学习使用，原文：https://blog.csdn.net/xiaobaismiley/article/details/41015783【实验背景】项目中需要对数据库中一张表进行重新设计，主要是之前未分区，考虑到数据量大了以后要设计成分区表，同时要对数据库中其他表做好备份恢复的工作。【实验环境】Mysql版本：mysql-5.6.19操作系统：Ubuntu 12.04内存：32GCPU...
数据库查询优化：主从读写分离及常见问题
BoringError的博客
06-25 908
大部分系统的访问模型是读多写少，读写请求量的差距可能达到几个数量级。当单机MySQL无法承受过高的QPS时，可以组建MySQL主从读写分离集群来将读请求分摊到多个从节点中，以实现水平拓展。
【生产问题记录】一次 MQ 堆积的解决 (并发地从数据库中查询出大量数据导致数据库繁忙最终宕机)
LCN29的博客
11-07 428
查看堆内存设置为 2G (-Xmx2G, 小堆多实例的配置), 先尝试将堆内存设置为 4G (-Xmx4G), 解决上面的异常, 让应用能够继续消费。同样通过 jstack 查看, 消费线程都是 Runnable 状态, 默认是解决了, 等消息消费完就行了, 回到旧版本, 定位原因。这种 MQ 堆积的情况, 个人习惯都会先确定一下对应的消费线程的状态, 确定是消费慢, 还是消费阻塞等原因。所有的消费线程都是进入 WAITING 状态, 失去了消费能力, 连续抽查了几台生产实例, 都是相同的情况。
【MySQL调优】如何进行MySQL调优？从参数、数据建模、索引、SQL语句等方向，三万字详细解读MySQL的性能优化方案（2024版）
vincewm的博客
06-06 4478
MySQL调优主要分为三个步骤：监控报警、排查慢SQL、MySQL调优。 排查慢SQL：开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优： 基础优化：缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表； 表设计优化：数据类型优化、冷热数据分表等。 索引优化：考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
Java-MaC：保证Java程序正确执行的轻量级形式化方法
网址：http://www.elsevie r. n l/l oc ate/en tcs/vol um e55. ht ml 18页Java-MaC：一个Java程序运行时保证工具M....监控和检查是根据系统要求的正式规范执行的MaC弥合了形式验证和测试之间的差距，
面试官：单表数据量大一定要分库分表吗？我用6个字和10张图回答
程序员干货站
05-17 1922
一、 文章概述 在业务发展初期单表完全可以满足业务需求，在阿里巴巴开发手册也建议：单表行数超过500万行或者单表容量超过2GB才推荐进行分库分表，如果预计三年后数据量根本达不到这个级别，请不要在创建表时就分库分表。 但是随着业务的发展和深入，单表数据量不断增加，逐渐成为业务系统的瓶颈。这是为什么呢？ 从宏观层面分析任何物体都必然有其物理极限。1965年英特尔创始人摩尔预测：集成电路上可容纳的元器件的数目，约每隔24个月增加一倍，性能提升一倍，即计算机性能每两年翻一番。 但是摩尔定律会有终点吗？有些科学家认为
mysql性能监控与执行计划
许诗宇的博客
08-08 456
MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。特点如下：1、提供了一种在数据库运行时实时检查server的内部执行情况的方法。performance_schema 数据库中的表使用performance_schema存储引擎。该数据库主要关注数据库运行过程中的性能相关的数据，与information_schema不同，information_schema主要关注server运行过程中的元数据信息。...
数据库国产化探究及升级改造过程指导
企业实战系列集 ●●● https://ximenjianxue.blog.csdn.net
03-18 2023
当前官网最新版本为DM8，相较于目前主流产品架构：不同特性依靠不同内核去实现，DM8独特采用了双存储引擎架构，行存储引擎和列存储引擎可相互配合、协同工作。同时实现了计算层和存储层的分离，同一内核既支持共享存储式集群，也支持分布式事务集群。
基于Java开发的国际象棋棋盘游戏设计源码
最新发布
10-05
该项目是一款采用Java语言开发的国际象棋棋盘游戏，包含40个文件，其中包括15个Java源文件、13个PNG图像文件、4个中文文件、3个WAV音频文件、1个Git忽略文件、1个LICENSE许可文件和1个Markdown文件。
基于Java核心的志愿汇系统开源设计源码实现
10-05
该项目是一款开源的志愿汇系统，采用Java核心技术实现，并融合了JavaScript, HTML, CSS等前端技术。系统源码包含3648个文件，其中SVG文件2164个，PNG文件640个，JavaScript文件333个，HTML文件140个，CSS文件136个，Java文件56个，SCSS文件42个，LESS文件26个，JPG文件17个，XML文件11个。此系统提供了一个简单的开源实现，便于用户学习和定制。
Java数据库访问技术：执行SQL语句详解
该资源是一份关于软件工程的PPT，重点讲述了如何执行SQL语句，特别是使用Java进行数据库查询的细节。同时，它还涵盖了Java语言的基础、数据库访问技术以及WEB开发技术。 在Java中执行SQL语句是数据库操作的关键部分...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值