[20241213]18c sqlplus rowlimit设置.txt

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

posted @ 2024-12-14 20:25  lfree  阅读(17)  评论(0)    收藏  举报