When I try to use AUTOTRACE in Toad for Oracle v15.0 I get the following error:
Unable to retrieve autotrace statistics
Autotrace does work fine in SQLPLUS.
Any suggestions to fix this?
You need the SELECT or READ privilege on V$STATNAME and V$SESSSTAT so we can execute this query:
SELECT pt.VALUE, sn.NAME
FROM v$statname sn, v$sesstat pt
WHERE sn.statistic# = pt.statistic#
AND PT.SID = :CSID
AND pt.VALUE > 0
ORDER BY UPPER (sn.NAME);
Hi John,
The PLUSTRACE role already has SELECT on V_$MYSTAT, V_$SESSTAT, and V_$STATNAME. And I am able to do simple SELECTs on V$STATNAME and V$SESSTAT as well as run the SELECT statement you provided.
Am I missing something?
Thanks,
Alan
Not sure. Turn on spool SQL to see if there is any error message.
Main Menu: Database -> Spool SQL -> Spool to Screen.
Ok - here is a funny error. The query I am trying to run (it is just something I made up quickly to test autotrace) is:
SELECT table_name
FROM sys.dba_tables
WHERE table_name LIKE 'A%'
ORDER BY 1;
The error message seems to be:
[Error] ORA-00972: identifier is too long
(5: 0): >> explain plan set statement_id='AHR_DEV_IRX_DUR_USER:20220502095533' into PLAN_TABLE for SELECT table_name
FROM sys.dba_tables
WHERE table_name LIKE 'A%'
ORDER BY 1
Error at line 5
ORA-00972: identifier is too long
It looks like it is trying to create a statement_id out of the schema name plus some datetime string plus the entire sql statement.
Well, it's just the schema name + date time string.
The entire statement is not part of the statement id.
That is too long. It should not be more than 30 characters. Something is not adding up though.
The code I'm looking at uses OS User Name (not Schema) + a different date format.
In addition, OS User Name is trimmed to 17 characters so this error does not happen.
Also, I don't see that error text ("unable to retrieve autotrace statistics") anywhere in our source.
Where in Toad are you getting this error? Can you post a screen shot?
ah - correct about the statement_id string. I misread it. In any case:
Here is the shortened SQL script:
set autotrace on
select table_name from user_tables order by 1;
Here is the script Output:
TABLE_NAME
--------------------------------------------------------------------------------
DBDEV_LOG
EMP
EMPLOYEES
3 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=894 Card=518 Bytes=100 K)
1 0 SORT ORDER BY (Cost=894 Card=518 Bytes=100 K)
2 1 FILTER
3 2 NESTED LOOPS OUTER (Cost=893 Card=519 Bytes=100 K)
4 3 HASH JOIN RIGHT OUTER (Cost=889 Card=519 Bytes=97 K)
5 4 INDEX FAST FULL SCAN SYS.I_USER2 (Cost=3 Card=554 Bytes=2 K)
6 4 HASH JOIN OUTER (Cost=886 Card=519 Bytes=95 K)
7 6 NESTED LOOPS OUTER (Cost=886 Card=519 Bytes=95 K)
8 7 STATISTICS COLLECTOR
9 8 NESTED LOOPS OUTER (Cost=652 Card=519 Bytes=90 K)
10 9 HASH JOIN (Cost=651 Card=519 Bytes=77 K)
11 10 TABLE ACCESS FULL SYS.TS$ (Cost=4 Card=6 Bytes=18)
12 10 HASH JOIN OUTER (Cost=647 Card=519 Bytes=75 K)
13 12 HASH JOIN (Cost=308 Card=519 Bytes=69 K)
14 13 NESTED LOOPS (Cost=308 Card=519 Bytes=69 K)
15 14 STATISTICS COLLECTOR
16 15 HASH JOIN (Cost=239 Card=519 Bytes=53 K)
17 16 INDEX FAST FULL SCAN SYS.I_USER2 (Cost=3 Card=554 Bytes=12 K)
18 16 MERGE JOIN CARTESIAN (Cost=236 Card=523 Bytes=41 K)
19 18 NESTED LOOPS (Cost=0 Card=1 Bytes=34)
20 19 FIXED TABLE FIXED INDEX SYS.X$KSPPI (ind:1) (Cost=0 Card=1 Bytes=30)
21 19 FIXED TABLE FIXED INDEX SYS.X$KSPPCV (ind:1) (Cost=0 Card=1 Bytes=4)
22 18 BUFFER SORT (Cost=236 Card=523 Bytes=24 K)
23 22 TABLE ACCESS BY INDEX ROWID BATCHED SYS.OBJ$ (Cost=236 Card=523 Bytes=24 K)
24 23 INDEX RANGE SCAN SYS.I_OBJ5 (Cost=8 Card=523)
25 14 TABLE ACCESS CLUSTER SYS.TAB$ (Cost=1 Card=1 Bytes=32)
26 25 INDEX UNIQUE SCAN SYS.I_OBJ# (Cost=0 Card=1)
27 13 TABLE ACCESS FULL SYS.TAB$ (Cost=1 Card=1 Bytes=32)
28 12 TABLE ACCESS FULL SYS.SEG$ (Cost=339 Card=40 K Bytes=476 K)
29 9 INDEX RANGE SCAN SYS.I_IMSVC1 (Cost=0 Card=1 Bytes=26)
30 7 INDEX RANGE SCAN SYS.I_OBJ1 (Cost=233 Card=1 Bytes=10)
31 6 INDEX FAST FULL SCAN SYS.I_OBJ1 (Cost=233 Card=222 K Bytes=2 M)
32 3 INDEX RANGE SCAN SYS.I_OBJ1 (Cost=2 Card=1 Bytes=6)
33 2 TABLE ACCESS BY INDEX ROWID BATCHED SYS.USER_EDITIONING$ (Cost=2 Card=1 Bytes=6)
34 33 INDEX RANGE SCAN SYS.I_USER_EDITIONING (Cost=1 Card=2)
35 2 TABLE ACCESS BY INDEX ROWID BATCHED SYS.USER_EDITIONING$ (Cost=2 Card=1 Bytes=6)
36 35 INDEX RANGE SCAN SYS.I_USER_EDITIONING (Cost=1 Card=2)
37 2 NESTED LOOPS SEMI (Cost=4 Card=1 Bytes=31)
38 37 INDEX RANGE SCAN SYS.I_OBJ4 (Cost=3 Card=1 Bytes=11)
39 37 INDEX RANGE SCAN SYS.I_USER2 (Cost=1 Card=1 Bytes=20)
Unable to retrieve autotrace statistics
Here is the Errors tab:
[Error] ORA-00936: missing expression
(1: 127): >> SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#
*
Error at line 1
ORA-00936: missing expression
[Error] ORA-00936: missing expression
(3: 127): >> SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#
*
Error at line 3
ORA-00936: missing expression
[Error] ORA-00936: missing expression
(3: 127): >> SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#
*
Error at line 3
ORA-00936: missing expression
And here is from the MESSAGES tab:
[Error] ORA-00936: missing expression
(1: 127): >> SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#
*
Error at line 1
ORA-00936: missing expression
[Error] ORA-00936: missing expression
(3: 127): >> SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#
*
Error at line 3
ORA-00936: missing expression
[Error] ORA-00936: missing expression
(3: 127): >> SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#
*
Error at line 3
ORA-00936: missing expression
Here is the SPOOL SQL tab (with IP and hostnames masked out):
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:50.715
select 'Test connection' from dual;
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:50.954
BEGIN SYS.DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:51.224
select PARAMETER,VALUE from nls_session_parameters where PARAMETER in('NLS_NUMERIC_CHARACTERS','NLS_DATE_FORMAT','NLS_CURRENCY');
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:51.440
select to_char(9,'9C') from dual;
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:51.667
select sid from V$SESSION where audsid = (select USERENV('SESSIONID') from dual);
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:52.001
SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#;
-- ORA-00936: missing expression
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:52.472
SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#;
-- ORA-00936: missing expression
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:52.813
select table_name from user_tables order by 1;
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:53.202
SELECT PT.VALUE, sn.Name FROM v$statname sn, SYS.V_$SESSTAT PT WHERE sn.statistic#=pt.statistic# and PT.SID=97 AND sn.name in IN ('recursive calls', '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)', 'rows processed') ORDER BY PT.STATISTIC#;
-- ORA-00936: missing expression
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:53.448
explain plan set statement_id='AROSENTHAL_DEV:20220502102852' into PLAN_TABLE for select table_name from user_tables order by 1;
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:53.964
select level, id, parent_id, operation||decode(options, null,'',' '||options)|| DECODE(ID,0,DECODE(OPTIMIZER,NULL,'', ' Optimizer Mode=' ||OPTIMIZER)), decode(object_name, null, ' ', object_owner || '.' || object_name),decode(cardinality,null,' ',decode(sign(cardinality-1000), -1, cardinality||' ', decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||' K', decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||' M', trunc(cardinality/1000000000)||' G')))) NumRows,decode(bytes,null,' ',decode(sign(bytes-1024), -1, bytes||' ', decode(sign(bytes-1048576), -1, trunc(bytes/1024)||' K', decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||' M', trunc(bytes/1073741824)||'G')))) bytes, decode(cost,null,' ',decode(sign(cost-10000000), -1, cost||' ', decode(sign(cost-1000000000), -1, trunc(cost/1000000)||' M', trunc(cost/1000000000)||' G'))) cost,decode(object_node,null,' ', object_node) tq, lpad(decode(other_tag, null,' ',decode(other_tag,'PARALLEL_TO_SERIAL', ' P->S', decode(other_tag, 'PARALLEL_TO_PARALLEL', ' P->P', decode(other_tag, 'PARALLEL_COMBINED_WITH_PARENT', ' PCWP', decode(other_tag, 'PARALLEL_FROM_SERIAL', ' S->P', decode(other_tag, 'PARALLEL_COMBINED_WITH_CHILD', ' PCWC', decode(other_tag,null,' ',other_tag)))))))||' ', 6, ' ') , rpad(' '||decode(distribution, null,' ',decode(distribution, 'PARTITION (ROWID)', 'PART (RID)', decode(distribution, 'PARTITION (KEY)', 'PART (KEY)', decode(distribution, 'ROUND-ROBIN', 'RND-ROBIN', decode(distribution, 'BROADCAST', 'BROADCAST', distribution))))), 12, ' ') || decode(partition_start, 'ROW LOCATION', 'ROWID', decode(partition_start, 'KEY', 'KEY', decode(partition_start, 'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6), 'NUMBER', substr(substr(partition_start, 8, 10), 1, length(substr(partition_start, 8, 10))-1), decode(partition_start,null,' ',partition_start))))) pstart, decode(partition_stop, 'ROW LOCATION', 'ROW L', decode(partition_stop, 'KEY', 'KEY', decode(partition_stop, 'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6), 'NUMBER', substr(substr(partition_stop, 8, 10), 1, length(substr(partition_stop, 8, 10))-1), decode(partition_stop,null,' ',partition_stop))))) pstop from PLAN_TABLE START WITH ID=0 AND STATEMENT_ID='AROSENTHAL_DEV:20220502102852' CONNECT BY PRIOR ID=PARENT_ID AND STATEMENT_ID='AROSENTHAL_DEV:20220502102852' ORDER BY ID,POSITION;
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:54.105
delete from PLAN_TABLE where STATEMENT_ID='AROSENTHAL_DEV:20220502102852';
----------------------------------
-- Session: AROSENTHAL_DEV@(DESCRIPTION=(ADDRESS=(PORT=1521)(HOST=xx.xx.xx.xx)(PROTOCOL=TCP))(CONNECT_DATA=(SERVICE_NAME=hostname.domain)))
-- Timestamp: 10:28:54.262
DECLARE
v_lines SYS.DBMS_OUTPUT.chararr;
v_line VARCHAR2 (32767);
v_clob CLOB;
v_cnt NUMBER;
v_output VARCHAR2 (32767);
PROCEDURE write_to_clob (p_clob IN OUT CLOB, p_str IN OUT VARCHAR2)
AS
BEGIN
BEGIN
IF p_str IS NOT NULL THEN
DBMS_LOB.writeappend (p_clob, LENGTH (p_str), p_str);
END IF;
EXCEPTION
WHEN VALUE_ERROR
THEN
NULL;
END;
p_str := NULL;
END;
BEGIN
v_cnt := :inOutCnt;
SYS.DBMS_OUTPUT.get_lines (v_lines, v_cnt);
DBMS_LOB.createtemporary (v_clob, FALSE, DBMS_LOB.session);
DBMS_LOB.open (v_clob, DBMS_LOB.lob_readwrite);
v_output := NULL;
FOR line IN 1 .. v_lines.COUNT
LOOP
IF :trim_line = 1 THEN
v_line := TRIM (v_lines (line));
ELSE
v_line := v_lines (line);
END IF;
IF v_line IS NOT NULL
THEN
v_line := REPLACE (v_line, CHR (0));
IF LENGTHB (v_output) + LENGTHB (v_line) > 32767
THEN
write_to_clob (v_clob, v_output);
END IF;
v_output := v_output || v_line;
IF line < v_lines.COUNT
THEN
IF LENGTHB (v_output) + 2 > 32767
THEN
write_to_clob (v_clob, v_output);
END IF;
v_output := v_output || CHR (13) || CHR (10);
END IF;
END IF;
END LOOP;
write_to_clob (v_clob, v_output);
if v_cnt > 0 then
:outLines := v_clob;
end if;
:inOutCnt := v_cnt;
DBMS_LOB.freetemporary (v_clob);
END;
:trim_line(INTEGER,IN)=1
:inOutCnt(INTEGER,IN/OUT)=500
:outLines(CLOB,OUT)=<NULL>
And here is from my ABOUT screen:
Toad for ORacle
Toad for Oracle Expert (64-bit) (Multiple Keys Active)
Add-ons: DB Admin Module
15.0.97.11178
Oh, you are running a script. I can reproduce the problem that way. Looks like we have some bad SQL in there.
I thought you were running with F9.
If you are just running single statements, use F9. It is faster, uses less memory, and you can do more with the results.
You should be able to autotrace with F9 (or the circled button) instead of using F5. But I'll fix it for F5.
Thank you for reporting this.
Ok. I thought it had to be run as a script. But it does seem to work with F9.
Thanks John !!!
You're welcome. And if Explain Plan is really what you're after, just do a CTRL+E. That will run it and switch to that tab.