[20241213]18c sqlplus rowlimit设置.txt
--//18c开始sqlplus支持rowlimit，看信息就知道限制结果集的输出行数，这样明显看执行计划统计信息自然不准。
--//不过我发现oracle 在处理上非常特别，做一个记录以及简单分析。
1.环境：
SCOTT@book01p> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
create table t1 as
select
to_char(trunc(dbms_random.value(10000000000, 20000000000 ))) phone_no,
trunc(dbms_random.value(0, 30 )) ext,
lpad(rownum,10) v1,
rpad('x',100) padding
from
dual
connect by level <= 1000;
--//记录有1000条。
SCOTT@book01p> show arraysize
arraysize 100
SCOTT@book01p> show rowprefetch
rowprefetch 1
2.测试1：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------ -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@book01p> set rowlimit 3
SCOTT@book01p> select * from t1;
PHONE_NO EXT V1 PADDING
---------------------------------------- ---------- -------------------- --------
19735021584 26 1 x
16847874564 4 2 x
13812085521 5 3 x
3 rows selected. (rowlimit reached)
--//显示3行。
SCOTT@book01p> set rowlimit off
--//必须关闭rowlimit特性，不然下面的内容仅仅显示3行。
SCOTT@book01p> @ dpc '' '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3154rqzb8xudy, child number 1
-------------------------------------
select * from dept
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "DEPT"@"SEL$1"
18 rows selected.
--//可以发现最执行计划是设置set rowlimit 3前的执行计划。
3.测试2：
--//退出重新登录：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------ -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@book01p> set rowlimit 3
SCOTT@book01p> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
3 rows selected. (rowlimit reached)
SCOTT@book01p> set rowlimit off
SCOTT@book01p> @ dpc '' '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID a2dk8bdn0ujx7, child number 1
-------------------------------------
select * from emp
Plan hash value: 3956160932
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 14 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 12 | 468 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "EMP"@"SEL$1"
--//这次显示正确!! 因为rowlimit=3，arraysize=100，fetch2次数量是1，13.虽然显示3条，但是完成了执行计划.
--//总之注意这个细节，通过这样方式再查询执行计划要小心。
4.测试3：
--//退出重新登录：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------ -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@book01p> set rowlimit 3
SCOTT@book01p> select * from t1;
PHONE_NO EXT V1 PADDING
---------------------------------------- ---------- -------------------- ----------
19735021584 26 1 x
16847874564 4 2 x
13812085521 5 3
3 rows selected. (rowlimit reached)
SCOTT@book01p> set rowlimit off
SCOTT@book01p> @ hash
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
3599690174 3154rqzb8xudy 1 59838 3383998547 d68ee9be 2024-12-13 18:17:28 16777227
SCOTT@book01p> @ sql_id 3154rqzb8xudy
-- SQL_ID = 3154rqzb8xudy come from shared pool
select * from dept;
--//你可以使用tpt hash脚本查询，发现记录的sql_id=3154rqzb8xudy 对应select * from dept;
$ cat tpt/hash.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
--------------------------------------------------------------------------------
--
-- File name: hash.sql
-- Purpose: Show the hash value, SQL_ID and child number of previously
-- executed SQL in session
--
-- Author: Tanel Poder
-- Copyright: (c) http://www.tanelpoder.com
--
-- Usage: @hash
--
--
-- Other: Doesn't work on 9i for 2 reasons. There appears to be a bug
-- with v$session.prev_hash_value in 9.2.x and also there's no
-- SQL_ID nor CHILD_NUMBER column in V$SESSION in 9i.
--
--------------------------------------------------------------------------------
select
ses.prev_hash_value hash_value
, ses.prev_sql_id sql_id
, ses.prev_child_number child_number
, MOD(ses.prev_hash_value, 131072) kgl_bucket
, (select sql.plan_hash_value
from v$sql sql
where
sql.sql_id = ses.prev_sql_id
and sql.child_number = ses.prev_child_number
and sql.address = ses.prev_sql_addr) plan_hash_value
, lower(to_char(ses.prev_hash_value, 'XXXXXXXX')) hash_hex
, ses.prev_exec_start sql_exec_start
, ses.prev_exec_id sql_exec_id
from
v$session ses
where
ses.sid = userenv('sid')
/
--//换一句话，在set rowlimit的情况下。v$session的prev_sql_id会出现没有更新的情况。
4.测试3：
--//退出重新登录：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
SCOTT@book01p> set arraysize 4
--//设置arraysize=4.
SCOTT@book01p> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------ -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@book01p> set rowlimit 3
SCOTT@book01p> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
3 rows selected. (rowlimit reached)
SCOTT@book01p> set rowlimit off
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3154rqzb8xudy, child number 1
-------------------------------------
select * from dept
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 7 |
| 1 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "DEPT"@"SEL$1"
18 rows selected.
--//这次看到前面的执行语句的执行计划，这是因为设置arraysize=4，这样执行select * from emp仅仅fetch2次数量是1,4.emp表并没
--//有完成结束执行。
5.测试4：
--//退出重新登录：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> set rowprefetch 0
SP2-0267: rowprefetch option 0 out of range (1 through 32767)
--//最大32767。缺省不设置是1.
SCOTT@book01p> set rowprefetch 2000
SCOTT@book01p> select * from dept;
DEPTNO DNAME LOC
---------- ------------------------------ -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@book01p> set rowlimit 3
SCOTT@book01p> select * from t1;
PHONE_NO EXT V1 PADDING
---------------------------------------- ---------- -------------------- -------
19735021584 26 1 x
16847874564 4 2 x
13812085521 5 3 x
3 rows selected. (rowlimit reached)
SCOTT@book01p> set rowlimit off
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 27uhu2q2xuu7r, child number 1
-------------------------------------
select * from t1
Plan hash value: 3617692013
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 1000 |00:00:00.01 | 21 |
| 1 | TABLE ACCESS FULL| T1 | 1 | 1000 | 124K| 8 (0)| 00:00:01 | 1000 |00:00:00.01 | 21 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / "T1"@"SEL$1"
18 rows selected.
--//这次看到执行计划是正确的。因为当设置rowprefetch 2000时，扫描t1时fetch按照2000抽取，第1次执行就抽取完成。
5.附上dpc.sql的脚本：
$ cat dpc.sql
select * from table(dbms_xplan.display_cursor(nvl('&1',null),nvl('&3',null),'all allstats last peeked_binds cost partition note -projection -outline &2'));