Tuesday, June 28, 2011

How to get list of Top Performing SQLs in Oracle?

The V$SQLAREA is used for identifying the top most resource-consuming SQL statements with a variety of criteria. It is widely useful to Database Administrators, Application Developers, Oracle Support Engineers and generally anyone involved in an Oracle Database Performance Tuning activity.
The thresholds used are the same as used by default in Statspack:
Buffer Gets : 10,000
Physical Reads : 1,000
Executions : 100
Parse Calls : 1,000
Sharable Memory : 1,048576
Version Count : 20
For Top Buffer Gets >>>
SELECT * FROM(
SELECT substr(sql_text,1,40) sql,
buffer_gets, executions, buffer_gets/executions "Gets/Exec",hash_value,address
FROM V$SQLAREA
WHERE buffer_gets > 10000
ORDER BY buffer_gets DESC
)WHERE rownum <= 10;
For Top Physical Reads >>>
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
disk_reads, executions, disk_reads/executions "Reads/Exec",
hash_value,address
FROM V$SQLAREA
WHERE disk_reads > 1000
ORDER BY disk_reads DESC)
WHERE rownum <= 10;
For Top Executions >>>
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
executions, rows_processed, rows_processed/executions "Rows/Exec",
hash_value,address
FROM V$SQLAREA
WHERE executions > 100
ORDER BY executions DESC)
WHERE rownum <= 10;
For Top Parse Calls >>>
SELECT * FROM
(SELECT substr(sql_text,1,40) sql,
parse_calls, executions, hash_value,address
FROM V$SQLAREA
WHERE parse_calls > 1000
ORDER BY parse_calls DESC)
WHERE rownum <= 10;

What are the Collections or Composite Datatypes in PL/SQL?

A Composite Datatypes are internal components that can be manipulated individually in PL/SQL are also called as PL/SQL Collections.

A PL/SQL collection is an ordered group of elements of the same type. It is a general concept that encompasses lists, arrays, and other familiar data types. Each element has a unique subscript that determines its position in the collection.

Three types of PL/SQL collections are in Oracle.

Index-by tables :

It is also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. They are similar to hash tables in other programming languages.Index-by tables is a non -persistent collection and it can’t store in the database.

Nested tables:

It can hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays :

Variable-Size Arrays (Varrays) can hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Nested tables and Varrays are persistent collections and it is stored in database.