Oracle PGA monitoring using ASH and AWR

Article presents how to create simple report to monitor PGA in Oracle database using ASH or AWR as source.

PGA is dedicated memory structure in SGA stores stores following areas:

Cursors and private sql areas

  •  opened cursors, links to shared SQL areas in shared pool
  • bind variable for cursors
  •  query execution state information

SQL work areas

  • sort operations (order by, group-by, rollup, window function)
  • hash-join
  • bitmap merge
  • bitmap create

Continue reading

Transform SQL to XML in Oracle

This article presents methods to transform an SQL to XML format with minimum effort possible.

Let’s take simple SQL

SELECT owner, table_name 
  FROM dba_tables
 WHERE table_name LIKE 'USER%' and owner='SYS';

OWNER                          TABLE_NAME                   
------------------------------ ------------------------------
SYS                            USER_HISTORY$                 
SYS                            USER_ASTATUS_MAP              
SYS                            USER$

and try to transform it into XML format

Continue reading

Oracle GANTT report in SQL

This article presents how to generate GANTT reports using sql.

A GANTT charts are commonly used in project management or batch processing, as one of the most popular and useful ways of showing activities (tasks or events) displayed against time. On the left of the chart is a list of the activities and along the top is a suitable time scale. Each activity is represented by a bar; the position and length of the bar reflects the start date, duration and end date of the activity. This allows you to see at a glance:

  • What the various activities are
  • When each activity begins and ends
  • How long each activity is scheduled to last
  • Where activities overlap with other activities, and by how much
  • The start and end date of the whole project

Continue reading