[20230726]使用dbms_xplan.format_size格式化数字输出.txt

--//dbms_xplan包多数情况下使用来显示执行计划.看了Franck Pachot的博客,作者通过dbms_xplan.format_size函数可以格式化数字输
--//出.通过几个简单的例子说明:
--//另外参考链接:http://dbaparadise.com/2019/10/how-can-dbms_xplan-format_size-be-userful-for-you/

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0


SCOTT@test01p> @desc_proc sys dbms_xplan format_%
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER PACKAGE_NAME OBJECT_NAME    SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT    DEFAULTED
----- ------------ -------------- -------- ------------- --------- --------- ----------
SYS   DBMS_XPLAN   FORMAT_SIZE           1               VARCHAR2  OUT       N
                                         2 NUM           NUMBER    IN        N

                   FORMAT_NUMBER         1               VARCHAR2  OUT       N
                                         2 NUM           NUMBER    IN        N

                   FORMAT_SIZE2          1               VARCHAR2  OUT       N
                                         2 NUM           NUMBER    IN        N

                   FORMAT_NUMBER2        1               VARCHAR2  OUT       N
                                         2 NUM           NUMBER    IN        N

                   FORMAT_TIME_S         1               VARCHAR2  OUT       N
                                         2 NUM           NUMBER    IN        N

--//实际上是一系列函数,注意返回类型是字符类型,注意设定显示宽度.

2.例子:
SCOTT@test01p> column nn format a16
SCOTT@test01p> column mm format a16
SCOTT@test01p> select dbms_xplan.format_size(1024) nn,dbms_xplan.format_size2(1024) mm from dual;
NN                   MM
-------------------- ----------------
1024                    1k

SCOTT@test01p> select dbms_xplan.format_size(1024*1024) nn,dbms_xplan.format_size2(1024*1024) mm from dual;
NN                   MM
-------------------- ----------------
1024K                   1m

SCOTT@test01p> select dbms_xplan.format_size(1024*1024*1024) nn,dbms_xplan.format_size2(1024*1024*1024) mm from dual;
NN                   MM
-------------------- ----------------
1024M                   1g

SCOTT@test01p> select dbms_xplan.format_size(1024*1024*1024*1024) nn,dbms_xplan.format_size2(1024*1024*1024*1024) mm from dual;
NN                   MM
-------------------- ----------------
1024G                   1t
--//dbms_xplan.format_size2 显示的字母是小写,而dbms_xplan.format_size显示的字母是大写,不知道oracle为什么这样设定.

--//你可以使用它格式化显示对应数字.例子:
column SEG_Size format a30
SELECT *
  FROM ( SELECT segment_name,dbms_xplan.format_size(bytes) as SEG_Size FROM dba_segments WHERE owner =  'XXX' AND segment_type = 'TABLE' ORDER BY bytes)
 WHERE rownum  <= 10;

SELECT FILE_NAME
     , DBMS_XPLAN.FORMAT_SIZE(BYTES) NN
  FROM DBA_DATA_FILES
 WHERE TABLESPACE_NAME LIKE '%SYS%'
 ORDER BY BYTES;

FILE_NAME                                       NN
----------------------------------------------- -----
D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF 235M
D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF 270M
 
3.继续:
--//DBMS_XPLAN.FORMAT_NUMBER可以猜测除以1000.例子:

SCOTT@test01p> select dbms_xplan.format_number(1024) nn,dbms_xplan.format_number2(1024) mm from dual;
NN                   MM
-------------------- ----------------
1024                    1K

SCOTT@test01p> select dbms_xplan.format_number(1024*1000) nn,dbms_xplan.format_number2(1024*1000) mm from dual;
NN                   MM
-------------------- ----------------
1024K                   1M

SCOTT@test01p> select dbms_xplan.format_number(2000*1000*1000) nn,dbms_xplan.format_number2(2000*1000*1000) mm from dual;
NN                   MM
-------------------- ----------------
2000M                   2G

SCOTT@test01p> select dbms_xplan.format_number(1024*1024*1024*20) nn,dbms_xplan.format_number2(1024*1024*1024*20) mm from dual;
NN                   MM
-------------------- ----------------
21G                    21G
--//dbms_xplan.format_number2显示的是大写,似乎做了四舍五入处理.

4.DBMS_XPLAN.FORMAT_TIME_S用于格式化时间输出,将秒转换为HH24:MI:SS格式,例子:

SCOTT@test01p> select dbms_xplan.format_time_s(901) nn from dual;
NN
--------------------
00:15:01

SCOTT@test01p> select dbms_xplan.format_time_s(901.45) nn from dual;
NN
--------------------
00:15:01

SCOTT@test01p> select dbms_xplan.format_time_s(86410) nn from dual;
NN
--------------------
24:00:10

SCOTT@test01p> select dbms_xplan.format_time_s(86410*9) nn from dual;
NN
--------------------
216:01:30

4.附上
$ cat desc_proc.sql
column owner format a10
column package_name format a30
column object_name format a30
column data_type format a30
column ARGUMENT_NAME format a30
column defaulted format a10
set verify off
break on owner on package_name on object_name  skip 1
PROMPT INPUT OWNER PACKAGE_NAME OBJECT_NAME
PROMPT sample : @desc_proc sys dbms_stats gather_%_stats
Prompt
SELECT owner
        ,package_name
        ,object_name
        ,sequence
        ,argument_name
        ,data_type
        ,in_out
        ,defaulted
    FROM dba_arguments
   WHERE     owner = NVL (UPPER ('&1'), 'SYS')
         AND package_name = NVL (UPPER ('&2'), 'DBMS_STATS')
         AND object_name LIKE NVL (UPPER ('&3'), object_name)
         AND data_level = 0
ORDER BY overload, subprogram_id,sequence;
--ORDER BY owner,package_name,object_name,sequence;