Applied Analytics Part 2

The analytic Pivot feature can be used to present useful information in an easy to read format. This is the case when you have information in multiple rows that you would rather display in multiple columns.

For example, recently I wanted to view information about the busiest database objects. When doing performance tuning, this information can be valuable. Oracle provides the v$segment_statistics view to contain performance metrics for each object.

Since I only wanted to review certain metrics and wanted to see all metrics from each object, the following pivot query was created. This query could be ordered by any column to show the top objects for that column's metric. Being able to see the other metrics for each object on the same line made the output more useful.

Example query:
With pivot_stats as (
select owner,object_name,statistic_name,value from v$segment_statistics
)
select * from pivot_stats
PIVOT
(sum(value) for statistic_name in ('logical reads', 'physical writes' ,'row lock waits' ))

Applied Analytics Part 1

The Top x query can be applied to administrative queries. This common analytic query will come in handy if you are looking for top resource consumers

For example to see the top 10 largest tables in the database, the following query can be used:
Select owner, segment_name, segment_type, bytes
from (
   Select owner, segment_name, segment_type, bytes,
   rank () over
     (order by bytes desc) as rank
   from dba_segments)
where rank <= 10

This query can also be used against system tables such as v$sqlstats and v$segment_statistics to obtain performance information about the database. Using rank you can easily select the data you want.