Typical problem that you can encounter during tuning is how to change explain plan inside complex view or inside a subquery for your sql statements. QB_NAME hints helps to do it. It can define a name for a query block. This name can then be used in another query block to hint tables appearing in the named query block.

Continue reading →

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 →

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 →

WITH clause is commonly used to simplify SQL statements. This article shows impact of dedicated hints MATERIALIZE and INLINE for processing data with WITH clause.

Continue reading →