Home > Oracle > example to gather runtime statistics in Oracle 10g

example to gather runtime statistics in Oracle 10g


SQL> CREATE TABLE t1 AS SELECT * FROM all_objects;

Table created.

SQL> CREATE TABLE t2 AS SELECT * FROM all_objects;

Table created.

SQL> CREATE TABLE t3 AS SELECT * FROM all_objects WHERE ROWNUM <= 100;

Table created.

SQL> CREATE INDEX it3 ON t3 (object_id);

Index created.

SQL> BEGIN
  2     DBMS_STATS.GATHER_TABLE_STATS(user,'T1');
  3     DBMS_STATS.GATHER_TABLE_STATS(user,'T2');
  4     DBMS_STATS.GATHER_TABLE_STATS(user,'T3',cascade=>TRUE);
  5  END;
  6  /

PL/SQL procedure successfully completed.
Now we'll set STATISTICS_LEVEL to ALL and run a SQL statement against our three tables.
SQL> ALTER SESSION SET STATISTICS_LEVEL = ALL;

Session altered.

SQL> SELECT /*+ ORDERED */
  2         t1.object_type
  3  ,      COUNT(*) AS object_count
  4  FROM   t1
  5  ,      t2
  6  ,      t3
  7  WHERE  t1.object_id = t2.object_id
  8  AND    t2.object_id = t3.object_id
  9  GROUP  BY
 10         t1.object_type;

OBJECT_TYPE         OBJECT_COUNT
------------------- ------------
CLUSTER                        5
INDEX                         35
TABLE                         60

3 rows selected.
Now we'll examine the runtime statistics of this cursor.
We'll use the 'RUNSTATS_LAST' parameter value to indicate that we wish to view the statistics for this execution of the cursor only (a cumulative 'RUNSTATS_TOT' is also available).

SQL> SELECT plan_table_output
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'RUNSTATS_LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1h5g6627pb79z, child number 1
-------------------------------------
 SELECT  t1.object_type, COUNT(*) AS object_count from t1,t2,t3 WHERE
t1.object_id = t2.object_id AND    t2.object_id = t3.object_id GROUP
BY t1.object_type

Plan hash value: 1353981761

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      5 |00:00:00.83 |    2051 |
|   1 |  HASH GROUP BY       |      |      1 |      5 |      5 |00:00:00.83 |    2051 |
|*  2 |   HASH JOIN          |      |      1 |    100 |    100 |00:00:00.83 |    2051 |
|*  3 |    HASH JOIN         |      |      1 |    100 |    100 |00:00:00.41 |    1026 |
|   4 |     INDEX FULL SCAN  | IT3  |      1 |    100 |    100 |00:00:00.01 |       1 |
|   5 |     TABLE ACCESS FULL| T2   |      1 |  71688 |  71688 |00:00:00.10 |    1025 |
|   6 |    TABLE ACCESS FULL | T1   |      1 |  71684 |  71684 |00:00:00.11 |    1025 |
---------------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

Note
-----
   - cardinality feedback used for this statement
30 rows selected.

Advertisements
Categories: Oracle
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: