gather_plan_statistics提示的使用,获取详细的执行统计信息

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'));

或 select * from table(dbms_xplan.display_cursor(sql_id => 'd9ayadktcs0g2',format => '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,可以为我们提供更为详尽的执行统计信息 */

`torch gather` 和 MXNet的`F.gather_nd` 都是用来从张量中选取特定索引元素的功能,但在使用上有一些细微差别。 1. **PyTorch (torch)**: `gather`函数主要用于沿着给定维度`dim`获取指定索引处的元素。它接受两个参数,第一个参数是源张量(`input`),第二个参数是一个长度匹配源张量该维度的整数切片(`index`)。例如,如果你有一个三维张量和一维索引,你可以选择每个索引对应的列。输出的张量将与输入的形状相同,除了指定的维度会变为1。 ```python input = torch.tensor([[[1, 2], [3, 4]], [[5, 6], [7, 8]]]) index = torch.tensor([0, 1]) output = torch.gather(input, dim=1, index=index) # 输出: tensor([[1, 4], # [5, 8]]) ``` 2. **MXNet (F.gather_nd)**: `F.gather_nd`与`torch`的`gather`类似,但它可以处理更高维度的索引,允许你通过多个索引来取出多维张量的元素。这个函数需要一个数据张量`(data)`、一个形状匹配的数据张量`(indices)`作为索引以及一个轴`(axis)`。这个函数返回的是一个由索引指定元素组成的张量,形状是 `(indices.shape[:-1] + data.shape[axis+1:])`。 ```python import mxnet as mx data = mx.nd.array([[[1, 2], [3, 4]], [[5, 6], [7, 8]]]) indices = mx.nd.array([[0, 1], [1, 0]]) # 二维索引 output = mx.nd.F.gather_nd(data, indices) # 输出: [[1, 3], [6, 5]] ``` **区别总结**: - PyTorch的`gather`更适用于单个或低维度索引的情况。 - MXNet的`F.gather_nd`支持高维度或多维索引,适合提取多位置的元素。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值