Document Type | Technical Information
Category | Tuning
Applicable Product Versions | 6FS07PS, 7FS02PS
Document Number | TTUTI010
Overview
In Tibero, DBMS_XPLAN.DISPLAY_CURSOR is a useful tool that analyzes and outputs the execution plan of executed SQL.
This tool changes the output format depending on the FORMAT option, and in particular, the precise option is used to enhance the accuracy of execution statistics.
This article analyzes through experiments the differences in output values of the CARDS and ROWS columns depending on whether the precise option is used, and how the rounding criteria are applied.
Method
Test Environment Setup
DROP TABLE TEST;
CREATE TABLE TEST (
c1_340557 NUMBER,
c2_340557 VARCHAR(200),
c3_340557 VARCHAR2(200),
c4_340557 NCHAR(20),
c5_340557 NVARCHAR2(200),
c6_340557 NUMBER,
c7_340557 CHAR(20),
c8_340557 INTEGER,
c9_340557 FLOAT,
c10_340557 BINARY_FLOAT,
c11_340557 NUMBER,
c12_340557 DATE,
c13_340557 TIMESTAMP );
INSERT INTO TEST
SELECT
LEVEL,
NVL(CHR(96 + MOD(LEVEL,32)) || CHR(97 + MOD(LEVEL,32)),'za'),
DECODE(MOD(LEVEL,4), 0, '10', 1, '20', 2, '30', '40'),
TO_CHAR(LEVEL),
TO_CHAR(LEVEL || CHR(96 + MOD(LEVEL,32))),
MOD(LEVEL,10),
'TESTCOL',
LEVEL,
ROUND(LN(LEVEL), 5),
ROUND(LOG(2, LEVEL), 3),
LEVEL,
TO_DATE('2025-' || LPAD(MOD(LEVEL,12)+1, 2, '0') || '-' || LPAD(MOD(LEVEL,28)+1, 2, '0'), 'YYYY-MM-DD'),
TO_TIMESTAMP('2025-' || LPAD(MOD(LEVEL,12)+1, 2, '0') || '-' || LPAD(MOD(LEVEL,28)+1, 2, '0') || ' 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual CONNECT BY LEVEL < 10000000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','TEST',ESTIMATE_PERCENT => 100, GRANULARITY => 'ALL');
Execution and Result Comparison
Case 1 - Autotrace Based Test
SET AUTOT TRACEONLY EXP PLANS STAT SELECT * FROM TEST WHERE C1_340557 < 5000000;
- Rows processed: 4,999,999
- Predicate selectivity: 0.500
Case 2 - Without Using PRECISE
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('49c6h0w31ujw5', 254, FORMAT => 'CARDS +ROWS'));
ID | Operation | Name | Cards | Rows |
|---|---|---|---|---|
1 | TABLE ACCESS (FULL) | TEST | 4999K | 0 |
- Cards: 4,999,998 โ Displayed as 4999K (truncated)
- Unit standard applied (K)
Case 3 - Using PRECISE
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('49c6h0w31ujw5', 254, FORMAT => 'PRECISE +CARDS +ROWS'));
ID | Operation | Name | Cards | Rows |
|---|---|---|---|---|
1 | TABLE ACCESS (FULL) | TEST | 4999998 | 0 |
- Cards: Displays the exact number
Output Format Rules
If the precise option is not used, the CARDS and ROWS values are output by truncation, not rounding.
- Example: 100500 โ 100K (truncated)
Also, if the CARDS or ROWS value is less than 100,000, the actual number is output as is. From 100,000 and above, K and M units are used for summarized notation, and automatic conversion between units also occurs.
- Example: 12,000K โ 12M (automatically converted to higher unit)
Conclusion
When using DBMS_XPLAN.DISPLAY_CURSOR with the precise option specified, the CARDS and ROWS values are output as the actual numbers, enabling more accurate execution plan analysis.
Especially for queries processing large amounts of data, accurate numbers instead of summarized K/M unit output allow precise understanding of the information needed for tuning.