Home » SQL & PL/SQL » SQL & PL/SQL » Understanding AUTOTRACE output (Oracle 10g, XP)
Understanding AUTOTRACE output [message #380672] Mon, 12 January 2009 23:48 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Expert Gurus,

I have set AUTOTRACE option on in SQL *Plus environment to get some detailed output with statestics.
And then when I display the user name using SELECT, It gives too much complex output as shown below.
Hence, I am unable to understand it.
can any buddy help me to understand the output?

for convinence I have set LINESIZE accordingly.

Raja>select username from user_users;

USERNAME
------------------------------
DBO

1 row selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 704625391

----------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                         |     1 |    82 |    10  (10)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN            |                         |     1 |    82 |    10  (10)| 00:00:01 |
|   2 |   NESTED LOOPS                   |                         |     1 |    73 |     9  (12)| 00:00:01 |
|   3 |    NESTED LOOPS                  |                         |     1 |    70 |     8  (13)| 00:00:01 |
|*  4 |     HASH JOIN                    |                         |     1 |    67 |     7  (15)| 00:00:01 |
|*  5 |      HASH JOIN OUTER             |                         |     1 |    64 |     5  (20)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID| PROFILE$                |     1 |     9 |     1   (0)| 00:00:01 |
|   7 |        NESTED LOOPS              |                         |     1 |    39 |     2   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS CLUSTER     | USER$                   |     1 |    30 |     1   (0)| 00:00:01 |
|*  9 |          INDEX UNIQUE SCAN       | I_USER#                 |     1 |       |     0   (0)| 00:00:01 |
|* 10 |         INDEX RANGE SCAN         | I_PROFILE               |    17 |       |     0   (0)| 00:00:01 |
|* 11 |       TABLE ACCESS FULL          | RESOURCE_GROUP_MAPPING$ |     1 |    25 |     2   (0)| 00:00:01 |
|  12 |      TABLE ACCESS FULL           | USER_ASTATUS_MAP        |     9 |    27 |     2   (0)| 00:00:01 |
|  13 |     TABLE ACCESS CLUSTER         | TS$                     |     1 |     3 |     1   (0)| 00:00:01 |
|* 14 |      INDEX UNIQUE SCAN           | I_TS#                   |     1 |       |     0   (0)| 00:00:01 |
|  15 |    TABLE ACCESS CLUSTER          | TS$                     |     1 |     3 |     1   (0)| 00:00:01 |
|* 16 |     INDEX UNIQUE SCAN            | I_TS#                   |     1 |       |     0   (0)| 00:00:01 |
|  17 |   BUFFER SORT                    |                         |     1 |     9 |     9  (12)| 00:00:01 |
|* 18 |    TABLE ACCESS BY INDEX ROWID   | PROFILE$                |     1 |     9 |     1   (0)| 00:00:01 |
|* 19 |     INDEX RANGE SCAN             | I_PROFILE               |    17 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("U"."ASTATUS"="M"."STATUS#")
   5 - access("CGM"."VALUE"(+)="U"."NAME")
   6 - filter("P"."RESOURCE#"=1 AND "P"."TYPE#"=1)
   8 - filter("U"."TYPE#"=1)
   9 - access("U"."USER#"=USERENV('SCHEMAID'))
  10 - access("U"."RESOURCE$"="P"."PROFILE#")
  11 - filter("CGM"."STATUS"(+)='ACTIVE' AND "CGM"."ATTRIBUTE"(+)='ORACLE_USER')
  14 - access("U"."DATATS#"="DTS"."TS#")
  16 - access("U"."TEMPTS#"="TTS"."TS#")
  18 - filter("DP"."RESOURCE#"=1 AND "DP"."TYPE#"=1)
  19 - access("DP"."PROFILE#"=0)


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


thanks in advance

regards,
Delna
Re: Understanding AUTOTRACE output [message #380674 is a reply to message #380672] Mon, 12 January 2009 23:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at user_users view definition in ALL_VIEWS.

Regards
Michel

[Updated on: Mon, 12 January 2009 23:51]

Report message to a moderator

Re: Understanding AUTOTRACE output [message #380679 is a reply to message #380674] Tue, 13 January 2009 00:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Michel Sir,

I think you didn't get my point.
I have problem in understanding the output given for TRACEed output.
Not for the USER_USERS view.
Help me in that regard.

regards,
Delna
Re: Understanding AUTOTRACE output [message #380684 is a reply to message #380679] Tue, 13 January 2009 00:13 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
delna.sexy wrote on Tue, 13 January 2009 07:01
I have problem in understanding the output given for TRACEed output.
Not for the USER_USERS view.
Help me in that regard.

What do you not understand? As you stated, USER_USERS is a view over Oracle internal tables. As TRACE shows access to real tables (not views), they are displayed there. What is the problem? Do you really want to know their meaning and relationship? As they are internal, I doubt they are documented (at least) the same as USER_* views.
Re: Understanding AUTOTRACE output [message #380686 is a reply to message #380684] Tue, 13 January 2009 00:19 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes sir,

Which are the tables and/or views?
What they are used for?
Relationship between them...
and other related details

regards,
Delna
Re: Understanding AUTOTRACE output [message #380714 is a reply to message #380686] Tue, 13 January 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Which are the tables and/or views?

Quote:
Have a look at user_users view definition in ALL_VIEWS.

The other details are Oracle stuff.

Regards
Michel
Re: Understanding AUTOTRACE output [message #380716 is a reply to message #380686] Tue, 13 January 2009 01:46 Go to previous message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Hi,
If I am not misunderstood your problem then i think
you want to know - "how to interpret the Autotrace Output ?".
If that is the case then following documents might be helpful.

Using Autotrace in SQL*Plus

Understanding Execution Plans

Using EXPLAIN PLAN

Gathering Optimizer Statistics

Regards,
Ram.
Previous Topic: Where is wrong?
Next Topic: Single SQL query to join 3 tables
Goto Forum:
  


Current Time: Sat Dec 03 20:36:39 CST 2016

Total time taken to generate the page: 0.07670 seconds