Hello Masters,
I have one big question about the SQL*Plus parameter LINESIZE and the display of datas.
I read in documentation Oracle 19 SQL*Plus :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqpug/sqlplus-users-guide-and-reference.pdf "8.5.6 SET LINESIZE
SET LINESIZE sets the total number of characters that SQL*Plus displays on one line before beginning a new line.
Keep LINESIZE as small as possible to avoid extra memory allocations and memory copying.However, if LINESIZE is too small, columns that cannot fit next to each other are put on separate lines. This may reduce performance significantly."So if this parameter is too small it may reduce performance. I want to see that!
My screen is 32 inches and can display 246 characters on one line.
I fill my screen with "i" and calculate their number.
SQL> SELECT length('iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii') from dual;
LENGTH('IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII
----------------------------------------------------------------------------------------------------
246
My table for this test.
SQL> CREATE TABLE zztest AS SELECT * FROM dba_objects WHERE rownum < 20001;
Table created.
SQL> ALTER TABLE zztest ADD id number;
Table altered.
SQL> UPDATE zztest SET id = rownum;
20000 rows updated.
SQL> commit;
Commit complete.
SQL> ALTER TABLE zztest ADD CONSTRAINT pk_zztest PRIMARY KEY (id);
Table altered.
Total length of characters to display all columns' name of the zztest test table.
SQL> select sum(length(column_name)) from dba_tab_cols where table_name = 'ZZTEST' and owner = 'HR' order by column_name;
SUM(LENGTH(COLUMN_NAME))
------------------------
283
I flush the buffer cache every time, because I want to read data from disk (the "most" expensive operation as "everyone" says).
I execute my SELECT twice with a FLUSH between them but I only put the display of the first execution to save space (the time is the same for twice).
SQL> set lines 100
SQL> set timing on
SQL> ALTER SYSTEM FLUSH buffer_cache;
What I see is that : columns on nine lines (on my computer).
Time : almost one minute.
SQL> select * from zztest;
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_
---------- -------------- ----------------------- --------- ---------
TIMESTAMP STATUS T G S NAMESPACE
------------------- ------- - - - ----------
EDITION_NAME
--------------------------------------------------------------------------------
SHARING E O A
------------------ - - -
DEFAULT_COLLATION
--------------------------------------------------------------------------------
D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID ID
- - ------------- ------------- -------------- -------------- ----------
...
20000 rows selected.
Elapsed: 00:00:56.68
Now I change LINESIZE, I set it at 150.
The output is different : only seven lines for the name of the columns (on my computer) but the time is the same.
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> SET LINESIZE 150;
SQL> select * from zztest;
OWNER
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
-------------- ----------------------- --------- --------- ------------------- ------- - - - ----------
EDITION_NAME SHARING E
-------------------------------------------------------------------------------------------------------------------------------- ------------------ -
O A DEFAULT_COLLATION D S CREATED_APPID CREATED_VSNID
- - ---------------------------------------------------------------------------------------------------- - - ------------- -------------
MODIFIED_APPID MODIFIED_VSNID ID
-------------- -------------- ----------
...
20000 rows selected.
Elapsed: 00:00:59.49
I change again LINESIZE, I set it at 300.
The output is different : only 4 lines for the name of the columns (on my computer) AND the time is incredibly low, only ten seconds...
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> SET LINESIZE 300;
SQL> select * from zztest;
OWNER OBJECT_NAME
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
-------------------------------------------------------------------------------------------------------------------------------- ---------- -------------- ----------------------- --------- --------- ------------------- ------- - - - ----------
EDITION_NAME SHARING E O A DEFAULT_COLLATION D S CREATED_APPID CREATED_VSNID
-------------------------------------------------------------------------------------------------------------------------------- ------------------ - - - ---------------------------------------------------------------------------------------------------- - - ------------- -------------
MODIFIED_APPID MODIFIED_VSNID ID
-------------- -------------- ----------
REMOTE_SCHEDULER_AGENT
...
20000 rows selected.
Elapsed: 00:00:09.97
OK, let's continue! I change again LINESIZE, I set it at 500, more than the size of my screen.
The output is different : only 3 lines for the name of the columns (on my computer) AND the time is again better, only five seconds...
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> SET LINESIZE 500;
SQL> select * from zztest;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- -------------- ----------------------- --------- --------- ------------------- ------- - - -
NAMESPACE EDITION_NAME SHARING E O A DEFAULT_COLLATION D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID ID
---------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ - - - ---------------------------------------------------------------------------------------------------- - - ------------- ------------- -------------- -------------- ----------
REMOTE_SCHEDULER_AGENT SUBMIT_JOB_RESULTS 20044 PROCEDURE 26-JAN-17 26-JAN-17 2017-01-26:14:43:37 VALID N N N
1 METADATA LINK N Y N USING_NLS_COMP N N 19999
20000 rows selected.
Elapsed: 00:00:05.37
Last test : I set LINESIZE to 5000.
The output is different : only 1 lines for the name of the columns (on my computer) AND the time is again better, only four seconds...
However if I am want to copy / paste the result, all the name of the columns are present in Notepad so even if they are not displayed on the screen, they are present on my PC.
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> SET LINESIZE 5000;
SQL> select * from zztest;
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME SHARING E O A DEFAULT_COLLATION D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID ID
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------- -------------- ----------------------- --------- --------- ------------------- ------- - - - ---------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ - - - ---------------------------------------------------------------------------------------------------- - - ------------- ------------- -------------- -------------- ----------
REMOTE_SCHEDULER_AGENT SUBMIT_JOB_RESULTS 20044 PROCEDURE 26-JAN-17 26-JAN-17 2017-01-26:14:43:37 VALID N N N 1 METADATA LINK N Y N USING_NLS_COMP N N 19999
...
20000 rows selected.
Elapsed: 00:00:03.80
If I use Autotrace, I see that if I increase LINESIZE, "Physical Reads" and "Consistent gets" decrease but there is a limit to that benefit.
SQL> show linesize
linesize 80
SQL> show pagesize
pagesize 14
SQL> set timing on
SQL> set autotrace on
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> select * from zztest;
...
20000 rows selected.
Elapsed: 00:00:57.94
Execution Plan
----------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
135 recursive calls
4 db block gets
1956 consistent gets
380 physical reads
220 redo size
2958855 bytes sent via SQL*Net to client
15270 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
20000 rows processed
I change LINESIZE.
SQL> SET LINESIZE 150;
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> select * from zztest;
...
20000 rows selected.
Elapsed: 00:00:58.10
Execution Plan
----------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
85 recursive calls
4 db block gets
1806 consistent gets
379 physical reads
0 redo size
2958855 bytes sent via SQL*Net to client
15270 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
20000 rows processed
I change LINESIZE.
SQL> SET LINESIZE 300;
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> select * from zztest;
...
20000 rows selected.
Elapsed: 00:00:10.12
Execution Plan
----------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1668 consistent gets
355 physical reads
0 redo size
2958855 bytes sent via SQL*Net to client
15270 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000 rows processed
I change LINESIZE.
SQL> SET LINESIZE 500;
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> select * from zztest;
...
20000 rows selected.
Elapsed: 00:00:05.46
Execution Plan
----------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1668 consistent gets
355 physical reads
0 redo size
2958855 bytes sent via SQL*Net to client
15270 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000 rows processed
I set LINESIZE to 5000.
SQL> SET LINESIZE 5000;
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> select * from zztest;
20000 rows selected.
Elapsed: 00:00:03.73
Execution Plan
----------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1668 consistent gets
355 physical reads
0 redo size
2958855 bytes sent via SQL*Net to client
15270 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20000 rows processed
Last last test : I cancel the output of datas on my screen.
Here it is incredible : not even one second!
SQL> show linesize
linesize 80
SQL> show pagesize
pagesize 14
SQL> SET AUTOTRACE TRACEONLY
SQL> ALTER SYSTEM FLUSH buffer_cache;
SQL> select * from zztest;
...
20000 rows selected.
Elapsed: 00:00:00.32
Execution Plan
----------------------------------------------------------
Plan hash value: 3582063246
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20000 | 2441K| 101 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| ZZTEST | 20000 | 2441K| 101 (0)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
125 recursive calls
6 db block gets
1943 consistent gets
391 physical reads
0 redo size
2958855 bytes sent via SQL*Net to client
15270 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
20000 rows processed
To fetch the SAME amount of data from the database to the client, time varies from 57 seconds to 0.3 second...
So the biggest part of the job is done by displaying data on my screen, not by physical reads? Do you agree with that?
In the doc I read
"However, if LINESIZE is too small, columns that cannot fit next to each other are put on separate lines. This may reduce performance significantly."" : can you detail that? And, last thing, can you explain me why the cost is so big for displaying data on screen : if I do not display them, my SELECT is executed in only 0.3 seconds?
Thank you very very much for the response,
Best regards,
D. DUBOIS
I'm not sure what you're trying to show here. The LINESIZE has no impact on the time it takes to execute your SQL. But it does affect how long it takes SQL*Plus to render the results.
If you measure the SQL execution time in your database (e.g. with the monitor hint), you should find that the database time for all tests is about the same and fairly trivial - in the order of milliseconds.
Most of the time is spent sending and receiving results from the database and SQL*Plus displaying them.
And as the docs say and you've found choosing a small value for linesize causes it to take longer to show the data.
PS - I'm not sure why you want "expensive" physical reads; this just adds another variable (disk I/O) which is irrelevant to testing linesize. Ideally you want NO phsyical reads, as this avoids any extra variance in disk response to influence the results.