Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sqlplus autotrace

Posted on 2012-09-03
3
Medium Priority
?
725 Views
Last Modified: 2012-09-06
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!
0
Comment
Question by:ralph_rea
3 Comments
 
LVL 23

Expert Comment

by:David
ID: 38360936
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?
0
 

Author Comment

by:ralph_rea
ID: 38360990
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.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38363350
Don't do this with autotrace.  Do this with explain plan.  You can set statement id's and trace as many select's as you wish.  The plans are stored and you can extract whatever you want.

http://docs.oracle.com/cd/E11882_01/server.112/e16638/ex_plan.htm#PFGRF009
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this article, we’ll look at how to deploy ProxySQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question