[20241213]18c sqlplus rowlimit设置.txt

来源：这里教程网时间：2026-03-03 21:01:55 作者：

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

编辑推荐：

返回列表

[20241213]18c sqlplus rowlimit设置.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推