For some distributions today, there are already three dynamic performance views available to DBAs (database Administrator, DBA) and developers, respectively, V$sql, V$sqlarea, and V$sqltext.
These views can be used to collect statistical information about the execution of SQL commands. The fourth dynamic performance view, V$sqlstats, is added to Oracle 10g, Release 2, which makes it easier to access such data.
Unlike static data dictionary views (static dictionary view, which is prefixed by user_, All_, or Dba_ views), dynamic performance views are constantly updated as the system runs. This makes it possible to monitor the performance of SQL statements as they are executed.
As with static views, you have to get permission to use them first. For non-database administrators, such as in a typical development environment, you can give Select_catalog_role permissions so that they can choose from them.
The following are some brief descriptions of the features provided by each view. For more information, refer to Oracle Database 10g Guide, Chapter 7th.
- V$sql: This view uses a CLOB (character large object, character giant object) column (column, which is the property field in the view) to provide the full text of the SQL statement. There is also a list of objects that hold up to 1000 VARCHAR2 characters (the first 1000 characters of the SQL statement), which is convenient to use. The available statistics are broad: the number of parsing statements (parse) and illegal statements, the read and write times of the disk, the elapsed time, the latency, and the optimizer (optimizer) data. You can also learn about the user and schema that created the statement (the deployment object), and how many users are currently executing it.
- V$sqlarea: This view contains a lot of the same statistics as v$sql. However, V$sql contains one row of statistics for each initial SQL statement and its children (child), and this view only produces a statistic for the actual input of the SQL string.
- V$sqlstats: This is the newly added view in the 10GR2 version, which is faster and more convenient than V$sql and V$sqlarea, and contains only a subset of other view column. In general, it does not connect user information. Each of its rows corresponds to an SQL statement and its execution plan's hash value (hash values). Its added advantage is that the record entry in this view is more durable than other views. Therefore, although a statement has expired in the shared pool (which causes it to disappear from the V$sqlarea), you can still find it through the view.
- V$sqltext: Sometimes you want to separate every line of SQL, not a giant object in V$sql, so v$sql can make you wish. You can select a specific type of statement (for example, select an INSERT statement, or a SELECT statement) by using the code name in the Command_type column. The Sql_text column holds a single statement, and piece column numbers the statement so that it is sorted by an order by. Another variable called V$sqltext_with_nelines preserves the original line break to improve readability (V$sqltext replaces the newline character with a space). Neither V$sqltext nor V$sqltext_with_newlines will tell you who executed the statement, or who is giving you these statistics. If you want to get that kind of information, you'll also need to implement the address and Hash_value column in V$sql and V$sqlarea views.
Together, these four view tools together provide you with raw material to help you detect how your SQL database is performing.
As a computer expert, Bob Watkins (OCP,MCDBA,MCSE,MCT) has 25 years experience as a technical trainer, consultant, and database administrator.
