Oracle : Using WITH Clause for improving performance

Many times using inline views destroy the execution plans. This is mainly because the execution plan of inline view interfere with the overall execution plan of the query.

To counter this, inline view can be taken to the top inside WITH clause. But this alone will not help. We need to incorporate hint /*+ materialize */ , as only then database treats the view as a physical table. Database creates a temporary table out of the view which is then used in the query. Execution plan is found far improved than before.


Query with following structure showed nested loop join between tables tab1 and tab2. This mainly was influenced by the inline view. Specifying USE_HASH hint also does not work.

SELECT col1,
col2,
Sum(col4)
FROM tab1,
tab2,
(SELECT DISTINCT Last_day(To_date(dates,'DD-MM-YYYY')) end_dt,
Add_months(Last_day(To_date(dates,'DD-MM-YYYY')) + 1,
-1) strt_dt,
To_char(To_date(dates,'DD-MON-YYYY'),'MMYYYY') mon_dt
FROM (SELECT To_char(Add_months(SYSDATE,-10) + (LEVEL - 1),'DD-MON-YYYY') AS dates
FROM dual
CONNECT BY To_number(To_char((Add_months(SYSDATE,-10) + (LEVEL - 1)),'YYYYMMDD')) <= To_number(To_char(SYSDATE,'YYYYMMDD')))) tim
WHERE tab1.assign_dt <= tim.end_dt
AND tab1.assign_dt >= tim.strt_dt
AND tab1.col3 = tab2.col3
GROUP BY col1,
col2

Changing the query to the following using WITH caluse leads to perfect execution plan using HASH join .


WITH TIM as (
(SELECT /*+ materialize */ DISTINCT Last_day(To_date(dates,'DD-MM-YYYY')) end_dt,
Add_months(Last_day(To_date(dates,'DD-MM-YYYY')) + 1,
-1) strt_dt,
To_char(To_date(dates,'DD-MON-YYYY'),'MMYYYY') mon_dt
FROM (SELECT To_char(Add_months(SYSDATE,-10) + (LEVEL - 1),'DD-MON-YYYY') AS dates
FROM dual
CONNECT BY To_number(To_char((Add_months(SYSDATE,-10) + (LEVEL - 1)),'YYYYMMDD')) <= To_number(To_char(SYSDATE,'YYYYMMDD'))))
)
SELECT col1,
col2,
Sum(col4)
FROM tab1,
tab2,
tim
WHERE tab1.assign_dt <= tim.end_dt
AND tab1.assign_dt >= tim.strt_dt
AND tab1.col3 = tab2.col3
GROUP BY col1,
col2

DB2 : collecting detailed statistics

to get the best optimizer execution plan, collecting detailed table statisitics might help:

db2 -x -r "runstats.db2" "select rtrim(concat('runstats on table ',concat(rtrim(tabSchema),concat('.',concat(rtrim(tabname),' on all columns with distribution on all columns and sampled detailed indexes all allow write access'))))) from syscat.tables where type='T'"
db2 -v -f "runstats.db2"

DB2 : Generating SQL Explain plan

This is how explain plan can be generated in DB2 database.

Create a file statements.db2:
select deptno from dept@

db2expln -database U001DB -stmtfile statements.db2 -terminator @ -terminal -user siebel siebel

Section Code Page = 1252

Estimated Cost = 30087412.000000
Estimated Cardinality = 19275950.000000

Access Table Name = SIEBEL.S_ORG_EXT ID = 7,201
| #Columns = 2
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Left Outer Hash Join
| Early Out: Single Match Per Outer Row
| Estimated Build Size: 7776000
| Estimated Probe Size: 9948879872
| Access Table Name = SIEBEL.S_ORG_EXT ID = 7,201
| | #Columns = 2
| | Relation Scan
| | | Prefetch: Eligible
...
...

INFORMATICA : Truncate Logs

There was an unusual behavior when i proceeded with repository manager to truncate the existing session logs. Option "Truncate Log" under "Edit" menu was disabled. Selecting/expanding the required folder also did not help.

Since my informatica server was on AIX with client on windows, i did the following to truncate the logs:

$ pmrep
pmrep>connect -r Powercenter -d Domain_server1 -n Administrator -x Administrator

pmrep>truncatelog -t all
truncatelog completed successfully.

DB2 : Establish remote connection on Unix/AIX

On server hosting DB2 Database:
db2set DB2COMM=tcpip
update database manager configuration using svcename 50000
db2stop
db2start

On client server (with DB2 client installed)
db2 UNCATALOG DATABASE U001DB
db2 UNCATALOG DCS DATABASE U001DB
db2 UNCATALOG NODE SERVER1

db2 CATALOG TCPIP NODE SERVER1 REMOTE SERVER1 SERVER 50000 REMOTE_INSTANCE DB2 SYSTEM SERVER1 OSTYPE AIX
db2 CATALOG DCS DATABASE U001DB AS U001DB
db2 CATALOG DATABASE U001DB AS U001DB AT NODE SERVER1 AUTHENTICATION DCS

Where, SERVER1 is the server where db2 server resides. U001DB is the db2 database name.

Test connection:

On client server:
db2 connect to U001DB user siebel using siebel

Database Connection Information

Database server = DB2/AIX64 9.1.6
SQL authorization ID = SIEBEL
Local database alias = U001DB