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' ))