asked on

sqlplus autotrace

Hi,
I need to insert into Oracle table the execution plan as well as statistics of the statements with autotrace for sqlplus session.

for example:
SQL> set autotrace on;
SQL> select count(*)
2 from dual;

count(*)
----------
1

Exrcution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Can I insert this output into Oracle table?
I need to trace many statements for the current session and save them in a table.

Thanks in advance!

David VanZandt

Simplest: SPOOL the output and store on your file system. It is heavily documented how to date-stamp files, add instance names, etc; and they are appendable.

Otherwise, you're looking at clob storage if you require the store-as-table thing.

Have you evaluated the usefulness of the .trc output in your trace logs?

ralph_rea

ASKER

I need to find the following values ¿¿for the sqlplus session set autotrace on;

Id
Operation
Name
Rows
Cost (%CPU)
Time
Statistics

I'd like to create a table with these columns and insert these values or or I'd like to find some system table that gives me the data.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial