Hello The Masters of Oracle,
Last time I was using AUTOTRACE in order to collect statistics about a SELECT.
I used SQLcl to change from SQL*Plus and... oh, it was a schock! Statistics were very very differents from SQL*Plus.
So, I decided to test with SQL*Plus, SQLcl, SQL Developer and Toad.
Can you tell me why, for three Oracle officials products, we have a such big difference in the number of statistics?
Which ones are, according to you, the bests to use with SQLcl (the richest product)?
============================================================================================
Autotrace with SQL*Plus
============================================================================================
First, I create the table.
SQL> create table zz01(id number primary key, name varchar2(50 CHAR), date_deb date);
Table creee.
SQL> insert into zz01 select rownum, 'PARKER' ||to_char(rownum), sysdate from dual connect by level < 10001;
10000 lignes creees.
SQL> commit;
Validation effectuee. I activate AUTOTRACE and execute my SELECT : there are 11 statistics.
SQL> set autotrace on
SQL> select * from zz01 where rownum < 6;
ID NAME DATE_DEB
---------- -------------------------------------------------- --------
1 PARKER1 04/01/19
2 PARKER2 04/01/19
3 PARKER3 04/01/19
4 PARKER4 04/01/19
5 PARKER5 04/01/19
Plan d'execution
----------------------------------------------------------
Plan hash value: 2489330668
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 245 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| ZZ01 | 10000 | 478K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<6)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistiques
----------------------------------------------------------
8 recursive calls
0 db block gets
93 consistent gets
0 physical reads
0 redo size
846 bytes sent via SQL*Net to client
552 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
============================================================================================
Autotrace with SQLcl
============================================================================================
With SQLcl, we have :
- 87 differents statistics :-)
- these ones, from SQL*Plus, are missing with SQLcl :
- sorts (disk)
- rows processed
Some are incomprehensibles to me :
- CCursor + sql area evicted
- HSC Heap Segment Block Changes
- shared hash latch upgrades - no wait
- ...
Statistics
-----------------------------------------------------------
1 ASSM cbk:blocks examined
1 ASSM gsp:L1 bitmaps examined
1 ASSM gsp:L2 bitmaps examined
1 ASSM gsp:get free block
11 CCursor + sql area evicted
3 CPU used by this session
8 CPU used when call started
24 DB time
3 HSC Heap Segment Block Changes
188 Requests to/from client
188 SQL*Net roundtrips to/from client
5 blocks cleaned out using minact
627 buffer is not pinned count
2 buffer is pinned count
3600 bytes received via SQL*Net from client
105942 bytes sent via SQL*Net to client
318 calls to get snapshot scn: kcmgss
3 calls to kcmgas
190 calls to kcmgcs
81920 cell physical IO interconnect bytes
87 cluster key scan block gets
76 cluster key scans
6 commit cleanouts
6 commit cleanouts successfully completed
1054 consistent gets
338 consistent gets examination
336 consistent gets examination (fastpath)
1054 consistent gets from cache
716 consistent gets pin
709 consistent gets pin (fastpath)
3 cursor authentications
21 db block changes
17 db block gets
17 db block gets from cache
13 db block gets from cache (fastpath)
3 deferred (CURRENT) block cleanout applications
25 enqueue releases
25 enqueue requests
303 execute count
20 file io service time
39978 file io wait time
10 free buffer requested
3 immediate (CURRENT) block cleanout applications
101 index fetch by key
116 index scans kdiixs1
8773632 logical read bytes from cache
1 messages sent
529 no work - consistent read gets
207 non-idle wait count
4 non-idle wait time
305 opened cursors cumulative
1 opened cursors current
23 parse count (hard)
35 parse count (total)
2 parse time cpu
9 parse time elapsed
10 physical read IO requests
81920 physical read bytes
10 physical read total IO requests
81920 physical read total bytes
10 physical reads
10 physical reads cache
1 process last non-idle time
699 recursive calls
3 recursive cpu usage
12 redo entries
3172 redo size
1154 redo synch time (usec)
24 redo synch time overhead (usec)
1 redo synch time overhead count ( 2ms)
1 redo synch writes
1 redo write info find
12 rows fetched via callback
293 session cursor cache hits
1071 session logical reads
1 shared hash latch upgrades - no wait
77 sorts (memory)
2306 sorts (rows)
31 sql area evicted
109 table fetch by rowid
222 table scan blocks gotten
18525 table scan disk non-IMC rows gotten
18530 table scan rows gotten
89 table scans (short tables)
708 undo change vector size
4 user I/O wait time
189 user calls ============================================================================================
Autotrace with SQL Developer
============================================================================================
With SQL Developer, we have :
- 45 differents statistics
- these ones, from SQL*Plus, are missing with SQL Developer :
- db block gets
- physical reads
- redo size
- rows processed
Statistics
-----------------------------------------------------------
1 CPU used by this session
3 CPU used when call started
9 DB time
38 Requests to/from client
38 SQL*Net roundtrips to/from client
76 buffer is not pinned count
547 bytes received via SQL*Net from client
70062 bytes sent via SQL*Net to client
38 calls to get snapshot scn: kcmgss
13 calls to kcmgcs
7 cluster key scan block gets
7 cluster key scans
111 consistent gets
47 consistent gets examination
47 consistent gets examination (fastpath)
111 consistent gets from cache
64 consistent gets pin
64 consistent gets pin (fastpath)
1 cursor authentications
4 enqueue releases
4 enqueue requests
33 execute count
13 index fetch by key
17 index scans kdiixs1
909312 logical read bytes from cache
53 no work - consistent read gets
46 non-idle wait count
33 opened cursors cumulative
1 opened cursors current
5 parse count (hard)
6 parse count (total)
1 parse time elapsed
1 process last non-idle time
52 recursive calls
3 rows fetched via callback
28 session cursor cache hits
111 session logical reads
5 sorts (memory)
1813 sorts (rows)
16 table fetch by rowid
15 table scan blocks gotten
1044 table scan disk non-IMC rows gotten
1044 table scan rows gotten
3 table scans (short tables)
39 user calls ============================================================================================
Autotrace with Toad 12.10
============================================================================================
And now, the well known product Toad.
Honestly, I do not really see what they can do compared to those of SQL * Plus, they are for me unusable because it misses all the part on the number of blocks read in memory or on hard disk.
With Toad, we have :
- 11 differents statistics
- these ones, from SQL*Plus, are missing with Toad :
- db block gets
- consistent gets
- physical reads
- redo size
- bytes sent via SQL*Net to client
- bytes received via SQL*Net from client
- SQL*Net roundtrips to/from client
- sorts (memory)
- sorts (disk)
Statistics
----------------------------------------------------------
0 recursive calls
0 spare statistic 11
0 spare statistic 15
0 spare statistic 24
0 commit cleanout failures: buffer being written
0 securefile direct read ops
0 securefile direct write ops
0 securefile inode read time
0 securefile uncompressed bytes
0 securefile bytes deduplicated
5 rows pr Thank you very much for your help.
David D. from Paris