Monthly archives for October, 2008

Introduce TVD$XTAT

Oct24
2008 11 Comments Written by Christian Antognini

Trivadis Extended Tracefile Analysis Tool (TVD$XTAT) is a command-line tool. Like TKPROF, its main purpose is to take a raw SQL trace file as input and generate a formatted file as output.

Why Is TKPROF Not Enough?

In late 1999, I had my first encounter with extended SQL trace, through MetaLink note Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output (39817.1). From the beginning, it was clear that the information it provided was essential for understanding what an application is doing when it is connected to an Oracle database engine. At the same time, I was very disappointed that no tool was available for analyzing extended SQL trace files for the purpose of leveraging their content. I should note that TKPROF at that time did not provide information about wait events. After spending too much time manually extracting information from the raw trace files, I decided to write my own analysis tool: TVD$XTAT.

Currently, TKPROF provides information about wait events, but it still has three major problems that are addressed in TVD$XTAT:

  • As soon as the argument sort is specified, the relationship between SQL statements is lost.
  • Data is provided only in aggregated form. Consequently, useful information is lost.
  • No information about bind variables is provided.

TVD$XTAT is freeware. You can download it (presently, version 4.0 beta 7) from this page. TOP fully describes how to use it to identify performance problems. The installation is documented in the README file.

It goes without saying that all feedbacks about TVD$XTAT are highly welcome.

Posted in SQL Trace, TKPROF, TOP, TVD$XTAT

Invisible Indexes and Locks

Oct17
2008 5 Comments Written by Christian Antognini

Invisible indexes are useful to temporarily hide an index from the query optimizer. In this post, instead of explaining what invisible indexes are, I would like to show whether the database engine uses invisible indexes to avoid false contention caused by unindexed foreign key.

  • Let’s start by creating two test tables with a relation (foreign key) between them:
SQL> CREATE TABLE t1 AS SELECT rownum AS n FROM dual CONNECT BY level <= 10;

Table created.

SQL> CREATE TABLE t2 AS SELECT rownum AS n FROM dual CONNECT BY level <= 10;

Table created.

SQL> ALTER TABLE t1 ADD CONSTRAINT t1_uk UNIQUE (n);

Table altered.

SQL> ALTER TABLE t2 ADD CONSTRAINT t1_t2_fk FOREIGN KEY (n) REFERENCES t1 (n);

Table altered.
  • Note that the foreign key is unindexed. Therefore, as shown by the following example, false contention due to locks is possible:
Session 1 Session 2 
SQL> SELECT sys_context('userenv','sid') AS sid
  2  FROM dual;

SID
----
86

SQL> INSERT INTO t2 VALUES (1);

1 row created.
 
SQL> SELECT sys_context('userenv','sid') AS sid
  2  FROM dual;

SID
----
85

SQL> DELETE FROM t2 WHERE n = 2;

1 row deleted.

SQL> DELETE FROM t1 WHERE n = 2;
 
SQL> SELECT blocking_session
  2  FROM v$session
  3  WHERE sid = 85;

BLOCKING_SESSION
----------------
              86

SQL> ROLLBACK;

Rollback complete.
 
1 row deleted.

SQL> ROLLBACK;

Rollback complete.
  • Now, let’s execute the same SQL statements when the the foreign key is indexed by an invisible index:
Session 1 Session 2 
SQL> CREATE INDEX i ON t2 (n) INVISIBLE;

Index created.

SQL> INSERT INTO t2 VALUES (1);

1 row created.
 
SQL> DELETE FROM t2 WHERE n = 2;

1 row deleted.

SQL> DELETE FROM t1 WHERE n = 2;

1 row deleted.
  • As you can see there is no false contention due to locks.

Conclusion: invisible indexes can only be used to assess how the query optimizer would behave when a specific index is (not) available. But, be careful, with them you cannot assess how an application would behave when a specific index is (not) available. In fact, except for access paths, invisible indexes are regularly updated and used for setting locks when DML statements are executed.

Posted in 11gR1, Indexes

TOP on Google Book Search

Oct10
2008 2 Comments Written by Christian Antognini

This is a short note to point out that my book, Troubleshooting Oracle Performance, is available on Google Book Search. To browse through it or to use the search capabilities provided by Google, click here. Even if the preview is limited to certain number of pages, in my opinion this service is a great help to decide whether to buy a book or not.

Posted in TOP
← Older Entries