Oracle Query Tuning Tips

Query Tuning
-------------------

CO-Relations (vs) Joins (vs) Sets
++++++++++++++++++++++++++++++++++

Co-Relations/Joins/Sets are actually using for joint or disjoint collections.

1. Co-Relations sub queries are high cost expensive queries when compare to Joins.
2. However, Co-Relations are using less memory when compared to Joins.
3. Joins are always using some builting algorithms like (Hash join (Small set of rows), Merge Join (Huge set of row + Sort)) which makes the things very faster.

Find the below examples

-> CREATE TABLE TEST(T INT);
-> CREATE TABLE TEST1(T1 INT);
-> INSERT INTO TEST VALUES(GENERATE_SERIES(1,1000));
-> INSERT INTO TEST VALUES(GENERATE_SERIES(500,1499));


CO-Relation
-----------
postgres=# EXPLAIN SELECT * FROM TEST AS OUT WHERE OUT.T IN (SELECT INN.T1 FROM TEST1 AS INN WHERE INN.T1=OUT.T);
QUERY PLAN
------------------------------------------------------------------
Seq Scan on test "out" (cost=0.00..48043.00 rows=1200 width=4)
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on test1 inn (cost=0.00..40.00 rows=1 width=4)
Filter: (t1 = "out".t)
(5 rows)


If you want to tune this Co-Relation then use "EXISTS" instead of "IN" Operation.(Note:- Replacing EXISTS/NOT EXISTS with IN/NOT IN is depends on the subquery logic).

postgres=# EXPLAIN SELECT * FROM TEST AS OUT WHERE EXISTS (SELECT INN.T1 FROM TEST1 AS INN WHERE INN.T1=OUT.T);
QUERY PLAN
-------------------------------------------------------------------------
Hash Semi Join (cost=26.50..54.25 rows=1000 width=4)
Hash Cond: ("out".t = inn.t1)
-> Seq Scan on test "out" (cost=0.00..14.00 rows=1000 width=4)
-> Hash (cost=14.00..14.00 rows=1000 width=4)
-> Seq Scan on test1 inn (cost=0.00..14.00 rows=1000 width=4)
(5 rows)

Observer the cost of both queries.

Joins
-----
postgres=# EXPLAIN SELECT * FROM TEST AS OUT INNER JOIN TEST1 AS INN ON INN.T1=OUT.T;
QUERY PLAN
-------------------------------------------------------------------------
Hash Join (cost=26.50..54.25 rows=1000 width=8)
Hash Cond: ("out".t = inn.t1)
-> Seq Scan on test "out" (cost=0.00..14.00 rows=1000 width=4)
-> Hash (cost=14.00..14.00 rows=1000 width=4)
-> Seq Scan on test1 inn (cost=0.00..14.00 rows=1000 width=4)
(5 rows)


Set Operators(Intersect)
------------------------
postgres=# EXPLAIN SELECT * FROM TEST INTERSECT SELECT * FROM TEST1;
QUERY PLAN
---------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..53.00 rows=1000 width=4)
-> Append (cost=0.00..48.00 rows=2000 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..24.00 rows=1000 width=4)
-> Seq Scan on test (cost=0.00..14.00 rows=1000 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..24.00 rows=1000 width=4)
-> Seq Scan on test1 (cost=0.00..14.00 rows=1000 width=4)


In the above three plans Set Operator's plan showing optimal result. We mayn't the same for all the cases. The execution plan always depends on the number of conditions what we are implying and the no.of rows and the row length.

In some of the cases Join Operations gives us very less cost when compares to Intersect operation.Because, Set operators always uses some internal SORT algorithms.


Inserted more records.

postgres=# SELECT COUNT(*) FROM TEST;
count
-------
32000
(1 row)

postgres=# SELECT COUNT(*) FROM TEST1;
count
-------
32000
(1 row)


Co-Relations
------------
postgres=# EXPLAIN SELECT * FROM TEST AS OUT WHERE OUT.T IN (SELECT INN.T1 FROM TEST1 AS INN WHERE INN.T1=OUT.T);
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on test "out" (cost=0.00..8417806.00 rows=16000 width=4)
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on test1 inn (cost=0.00..526.00 rows=32 width=4)
Filter: (t1 = "out".t)
(5 rows)

postgres=# EXPLAIN SELECT * FROM TEST AS OUT WHERE EXISTS (SELECT INN.T1 FROM TEST1 AS INN WHERE INN.T1=OUT.T);
QUERY PLAN
---------------------------------------------------------------------------
Hash Semi Join (cost=846.00..1772.00 rows=32000 width=4)
Hash Cond: ("out".t = inn.t1)
-> Seq Scan on test "out" (cost=0.00..446.00 rows=32000 width=4)
-> Hash (cost=446.00..446.00 rows=32000 width=4)
-> Seq Scan on test1 inn (cost=0.00..446.00 rows=32000 width=4)
(5 rows)


Joins
------
postgres=# EXPLAIN SELECT * FROM TEST AS OUT INNER JOIN TEST1 AS INN ON INN.T1=OUT.T;
QUERY PLAN
---------------------------------------------------------------------------
Hash Join (cost=846.00..12892.00 rows=1024000 width=8)
Hash Cond: ("out".t = inn.t1)
-> Seq Scan on test "out" (cost=0.00..446.00 rows=32000 width=4)
-> Hash (cost=446.00..446.00 rows=32000 width=4)
-> Seq Scan on test1 inn (cost=0.00..446.00 rows=32000 width=4)
(5 rows)

Set Operators
-------------
postgres=# EXPLAIN SELECT * FROM TEST INTERSECT SELECT * FROM TEST1;
QUERY PLAN
-----------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..1692.00 rows=1000 width=4)
-> Append (cost=0.00..1532.00 rows=64000 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..766.00 rows=32000 width=4)
-> Seq Scan on test (cost=0.00..446.00 rows=32000 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..766.00 rows=32000 width=4)
-> Seq Scan on test1 (cost=0.00..446.00 rows=32000 width=4)
(6 rows)


We need to check all the cases for fine tuning the query. Sometimes, we might get a better plan with JOINS when compares to INTERSECT.

We may get the co-related queries with DELETE/UPDATE Statements also. So, please re-write them with USING/FROM always. If possible we can go with INTERSECT also.

UPDATE
------

Actual
------

UPDATE Test AS OUT
SET T = ( SELECT T1 FROM Test1 INN WHERE INN.T=OUT.T);

Recommended
-----------

UPDATE Test AS OUT
SET T = T1
FROM Test1 AS INN Where INN.T=OUT.T;


DELETE
------
Actual
------
DELETE From Test As OUT Where NOT Exists (Select INN.T1 FROM TEST1 Where OUT.T!=INN.T1);

Recommended
-----------
DELETE From Test As Out USING Test1 as Inn where Out.T!=Inn.T1


Equating Single Values (vs) In Single Value
-------------------------------------------

There could be slight cheper cost when compares to "=" and "IN". Find the below test case with "=" and using "IN".

postgres=# BEGIN WORK;
postgres=# DELETE FROM TEST WHERE T =1;
DELETE 64
Time: 65.426 ms
postgres=# ROLLBACK;

postgres=# BEGIN WORK;
postgres=# DELETE FROM TEST WHERE T IN(1);
DELETE 64
Time: 34.683 ms
postgres=# ROLLBACK;


UNION (vs) FULL OUTER JOINS
---------------------------

Actual Query with Unions.
-------------------------
SELECT * FROM test WHERE true AND t IN (2,3,4,5) AND mp > 0
UNION
SELECT * FROM test WHERE true AND t IN (2,3,4,5) AND sp > 0
UNION
SELECT * FROM TEST WHERE TRUE AND T IN (2,3,4,5) AND TP>0;

Most of the Set operators are using Sort operations. If the sort operation is taking much more time, then following one is recommended with the indexes.


And with FullOuter Joins.
-------------------------
SELECT coalesce(a.*,b.*,c.*) FROM
(SELECT * FROM test WHERE true AND t IN (2,3,4,5)AND mp > 0) AS A
FULL OUTER JOIN
(SELECT * FROM test WHERE true AND t IN (2,3,4,5)AND sp> 0) AS B
ON A.t=B.t and a.mp=b.mp and a.sp=b.sp and a.tp=b.tp
FULL OUTER JOIN
(SELECT * FROM test WHERE true AND t IN (2,3,4,5)AND tp> 0) AS C
ON B.t=C.t and B.mp=C.mp and C.sp=b.sp and C.tp=b.tp;

DISTINCT (vs) Group By
----------------------
DISTINCT operation also using "SORT" when it's in action. So, please use Group by instead of DISTINCT.

Actual
------
SELECT DISTINCT T FROM TEST;

Recommended
-----------
SELECT T FROM TEST GROUP BY T;

Teradata tutorial link

http://tunweb.teradata.ws/tunstudent/

Go through this Teradata Database User Manuals Then down load

::CSV files in Oracle ::

How to make a csv/flat file in oracle through UTL_FILE
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

CSV(comma separated values) are mostly useful in data transformation. I believe these files are mostly useful in DWH.

We can do the same through sqlplus features.

SQL> set heading off
SQL> set feedback off
SQL> spool /home/oracle/test/csv.txt
SQL> select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from emp;

7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20

7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

SQL> spool off;

SQL> host more /home/oracle/test/csv.txt
SQL> select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from emp;

7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20

7934,MILLER,CLERK,7782,23-JAN-82,1300,,10


Here you can find the other way with UTL_FILE. This concept is similar to FILES in C language.

Step 1:
--------
Need to create a directory at os level

Ex:-
-----
/home/oracle/test

Step 2:
---------
Need to connect to the database as a sysdba grant below privileges to the required user.

CONNECT / AS SYSDBA

GRANT EXECUTE ON UTL_FILE TO scott;

CREATE OR REPLACE DIRECTORY test AS '/home/oracle/test'; in windows('c:\test\');

GRANT read, write ON DIRECTORY test TO scott;


Step 3:
---------
CONNECT scott/tiger

CREATE OR REPLACE Procedure make_csv_emp(csv_symbol char)
IS

fp UTL_FILE.FILE_TYPE;
BEGIN
fp:=UTL_FILE.FOPEN('TEST','emp_csv.txt','w');
FOR REC IN (SELECT * FROM EMP)
LOOP
UTL_FILE.PUTF(fp,REC.EMPNO||csv_symbol||REC.ENAME||csv_symbol||REC.JOB||csv_symbol||REC.MGR||csv_symbol||REC.HIREDATE||csv_symbol||REC.SAL||csv_symbol||REC.COMM||csv_symbol||REC.DEPTNO||'\n');
END LOOP;
UTL_FILE.FCLOSE(fp);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000,'ORA-ERROR::'||SQLCODE||'::'||SQLERRM);
END;

Step 4:
--------

Just execute the function and open the file.

SQL> exec make_csv_emp(',');

PL/SQL procedure successfully completed.

[oracle@localhost test]$ more emp_csv.txt
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

ORACLE ARCHITECTURE

UNIT TESTING IN INFORMATICA

Unit testing can be broadly classified into 2 categories.
Quantitative Testing
Validate your Source and Target
a) Ensure that your connectors are configured properly.
b) If you are using flat file make sure have enough read/write permission on the file share.
c) You need to document all the connector information.
Analyze the Load Time
a) Execute the session and review the session statistics.
b) Check the Read and Write counters. How long it takes to perform the load.
c) Use the session and workflow logs to capture the load statistics.
d) You need to document all the load timing information.
Analyze the success rows and rejections.
a) Have customized SQL queries to check the source/targets and here we will perform the Record Count Verification.
b) Analyze the rejections and build a process to handle those rejections. This requires a clear business requirement from the business on how to handle the data rejections. Do we need to reload or reject and inform etc? Discussions are required and appropriate process must be developed.
Performance Improvement
a) Network Performance
b) Session Performance
c) Database Performance
d) Analyze and if required define the Informatica and DB partitioning requirements.
Qualitative Testing
Analyze & validate your transformation business rules. More of functional testing.
e) You need review field by field from source to target and ensure that the required transformation logic is applied.
f) If you are making changes to existing mappings make use of the data lineage feature Available with Informatica Power Center. This will help you to find the consequences of Altering or deleting a port from existing mapping.
g) Ensure that appropriate dimension lookup’s have been used and your development is in Sync with your business requirements.

NOTE:
for more information Please refer INFORMATICA REALTIME UNIT TESTING in the same BLOG(how to integrate more than once source)

 

1. UNIT TEST CASE FOR LOAN_MASRER

FUNCTIONALITY_ID	FIELD_NAME	DETAIL	VALUE PASSED	EXPECTED RESULT	ACTUAL RESULT	PASS/FAIL RESULT	REMARK
STG_SCHM_DTLS_001	LOAN _ID	_TYPE_ID SHOULD BE NOT NULL ,FIRST CHARACHER ALPHABET(INSCH) AND LAST 10 CHARACTER NUMERIC VALUES AND ALSO ITS LENGTH IS 16	INSCH00000000002	ACCEPT RECORD	RECORD ACCEPTED	PASS
STG_SCHM_DTLS_002	LOAN_TYPE_ID	REJECT WHEN , NOT NULL ,FIRST 5 CHARACHER NOT (INSCH) OR LAST 10 CHARACTER NON NUMERIC VALUES AND ALSO ITS LENGTH <>16	INSCP001000000002	REJECT RECORDRECORD REJECTED	PASS	RECORD INSERTED INTO REJECTED FILE WITH AN ERROR_ID &ERROR_DETAILS INTO ERROR_TABLE
STG_SCHM_DTLS_003	LOAN_COMPANY_ID	LOAN_COMPANY_ID  MUST BE NOT NULL,FIRST 4 CHRACTER ALPHABET(INCO) AND LAST 11 CHRACTER NUMERIC VALUES AND ALSO LENGTH IS 15	INCO00000000003	ACCEPT RECORD	RECORD ACCEPTED	PASS
STG_SCHM_DTLS_004	LOAN_COMPANY_ID	REJECT WHEN , NOT NULL ,FIRST 4 CHARACHER NOT (INCO) OR LAST 11 CHARACTER NON NUMERIC VALUES AND ALSO ITS LENGTH <>15	INSO00000060003	REJECT RECORD	RECORD REJECTED	PASS	RECORD INSERTED INTO REJECTED FILE WITH AN ERROR_ID &ERROR_DETAILS INTO ERROR_TABLE
STG_SCHM_DTLS_005	START_DATE	START DATE SHOULD  BE  A VALID DATE	12/9/1988	ACCEPT RECORD	RECORD ACCEPTED	PASS
STG_SCHM_DTLS_006	START_DATE	START DATE SHOULD NOT BE LOADED WHEN IT IS NOT A VALID DATE	33FeB/88	REJECT RECORD	RECORD REJECTED	PASS	RECORD INSERTED INTO REJECTED FILE WITH AN ERROR_ID &ERROR_DETAILS INTO ERROR_TABLE
STG_SCHM_DTLS_007	SCHEME_DESC	SCHEME-DESC SHOULD BE ALPHABETIC TYPE	AUTOMOBILE	ACCEPT RECORD	RECORD ACCEPTED	PASS
STG_SCHM_DTLS_008	SCHEME_DESC	REJECT WHEN SCHEME DISCOUNT IS NOT ALPHABETIC TYPE	MOTO124	REJECT RECORD	RECORD REJECTED	PASS	RECORD INSERTED INTO REJECTED FILE WITH AN ERROR_ID &ERROR_DETAILS INTO ERROR_TABLE
STG_SCHM_DTLS_009	PREMIUM_PER_LACS	PREMIUM_PER_LACSSHOULD BE NUMERIC	5000	ACCEPT RECORD	RECORD ACCEPTED	PASS

Differene Between ETL AND ELT

https://sendnow.acrobat.com/?i=beWn91fYd8GGaumpRF5q1g

INFORMATICA 9.1 NEW FEATURES

Session log file rollover: You can limit the size of session logs for real-time sessions. You can limit the size by time or by file size. You can also limit the number of log files for a session.
Licensing

Enforcement of licensing restrictions. PowerCenter will enforce the licensing restrictions on the number of CPUs and repositories.
Lookup Transformation :
Cache updates. You can update the lookup cache based on the results of an expression. When an expression is true, you can add to or update the lookup cache. You can update the dynamic lookup cache with the results of an expression.

Database deadlock resilience: In previous releases, when the Integration Service encountered a database deadlock during a lookup, the session failed. Effective in 9.0, the session will not fail. When a deadlock occurs, the Integration Service attempts to run the last statement in a lookup. You can configure the number of retry attempts and time period between attempts.

Multiple rows return: You can configure the Lookup transformation to return all rows that match a lookup condition. A Lookup transformation is an active transformation when it can return more than one row for any given input row.

SQL overrides for uncached lookups:
In previous versions you could create a SQL override for cached lookups only. You can create an SQL override for uncached lookup. You can include lookup ports in the SQL query.


Mapping Architect for Visio :
New mapping objects: You can include the following objects in a mapping template: Pipeline Normalizer transformation Custom transformation PowerExchange source definition PowerExchange target definition
You can also create a mapping template from a mapping that contains these objects.

Shortcuts. You can configure a transformation to use a shortcut. You can create a mapping template from a mapping that contains shortcuts to reusable transformations.

SQL Transformation :

Auto-commit for connections. You can enable auto-commit for each database connection. Each SQL statement in a query defines a transaction. A commit occurs when the SQL statement completes or the next statement is executed, whichever comes first. Exactly-once processing. The Integration Service provides exactly-once delivery of real-time source messages to the SQL transformation. If there is an interruption in processing, the Integration Service can recover without requiring the message to be sent again. To perform exactly-once processing, the Integration Service stores a set of operations for a checkpoint in the PM_REC_STATE table. Passive transformation. You can configure the SQL transformation to run in passive mode instead of active mode. When the SQL transformation runs in passive mode, the SQL transformation returns one output row for each input row.

XML Transformation :

XML Parser buffer validation. The XML Parser transformation can validate an XML document against a schema. The XML Parser transformation routes invalid XML to an error port. When the XML is not valid, the XML Parser transformation routes the XML and the error messages to a separate output group that you can connect to a target.
Starting from Version 9 , powercenter admin console is called as informatica administrator.

Informatica Administrator (PowerCenter Administration Console) Effective in version 9.0, the PowerCenter Administration Console is renamed to Informatica Administrator. The Informatica Administrator has a new interface. Some of the properties and configuration tasks from the PowerCenter Administration Console have been moved to different locations in Informatica Administrator. The Informatica Administrator is expanded to include new services and objects.

Analyst Service. Application service that runs Informatica Analyst in the Informatica domain. Create and enable an Analyst Service on the Domain tab of Informatica Administrator. When you enable the Analyst Service, the Service Manager starts Informatica Analyst. You can open Informatica Analyst from Informatica Administrator.

Data Integration Service. Application service that processes requests from Informatica Analyst and Informatica Developer to preview or run data profiles and mappings. It also generates data previews for SQL data services and runs SQL queries against the virtual views in an SQL data service. Create and enable a Data Integration Service on the Domain tab of Informatica Administrator. Model Repository Service. Application service that manages the Model repository. The Model repository is a relational database that stores the metadata for projects created in Informatica Analyst and Informatica Designer. The Model repository also stores run-time and configuration information for applications deployed to a Data Integration Service. Create and enable a Model Repository Service on the Domain tab of Informatica Administrator. PowerExchange Listener Service. Manages the PowerExchange Listener for bulk data movement and change data capture. The PowerCenter Integration Service connects to the PowerExchange Listener through the Listener Service. PowerExchange Logger Service. Manages the PowerExchange Logger for Linux, UNIX, and Windows to capture change data and write it to the PowerExchange Logger Log files. Change data can originate from DB2 recovery logs, Oracle redo logs, a Microsoft SQL Server distribution database, or data sources on an i5/OS or z/OS system. Connection management. Database connections are centralized in the domain. You can create and view database connections in Informatica Administrator, Informatica Developer, or Informatica Analyst. Create, view, edit, and grant permissions on database connections in Informatica Administrator. Deployment. You can deploy, enable, and configure deployment units in the Informatica Administrator. Deploy Deployment units to one or more Data Integration Services. Create deployment units in Informatica Developer. Monitoring. You can monitor profile jobs, scorecard jobs, preview jobs, mapping jobs, and SQL Data Services for each Data Integration Service. View the status of each monitored object on the Monitoring tab of Informatica Administrator.