by Wade Lockhart
February 14, 2017
This will be a two part blog which will discuss how to measure your Informatica repository performance (pt.1) and then provide some strategies for increasing performance (pt.2). Since this is pt.1, I will dive into the topic of ways to judge the performance of your repository.
When I speak about performance of the repository, I mean a way to judge how responsive queries are against the repository. For example, if a query to fetch information from the repository takes x seconds through Informatica – that same query run from a client tool takes y seconds. You can assume that these two values (x and y) should be relatively close. If x is much higher than y then it is time to consider some strategies to bring that number down.
There are lots of ways to perform this type of test. However, for this blog I am going to demonstrate a strategy where you can configure the repository service to provide you with some raw SQL and its responsiveness. You can then run that same SQL in a client tool and compare query times. I also assume some foundational Informatica Administration knowledge.
This blog is just providing some general advice and a direction to pursue, but you should always contact Informatica Support or your account representative before implementing.
Also, if you believe you have a performance issue, please check out the Informatica Support KB pages as well.
Step 1: Configure the Repository Service
Note: This is a disruptive change and will require a stop/start of the repository service.
1. Log into the Administration Console as a user with admin privileges on the repository services.
2. Select the Repository Service.
3. Make the following two changes under the Properties tab:
A. Custom Properties -> Add EnableODLTracing and set the value to Yes.
4. Advanced Properties – > Change the Log Level to a value of Trace
5. Stop and start the repository service.
6. Now check the logs for that repository service and you should be able to see a new Severity of Trace in the logs.
7. You should also now be able to see ODL Trace entries in the repository service logs. These are the entries that will provide you with some sample SQL queries to run in your client.
Step 2: Grab a sample SQL query and run it in a client tool
8. Copy one of the ODL Trace SQL statements from the logs, preferably one that comes from a session and takes a reasonably long time (like greater than 5 seconds to execute in Informatica).
9. Copy one of the SQL statements directly from the session logs, if you know a session that runs a good SQL statement and you can manually run that SQL statement without doing any damage.
10. Login to the repository database using any client tool (eg Toad or AQT for Oracle or sqlplus for MS SQL Server.
11. Execute the SQL statement in the client tool.
Step 3: Compare the Results and Reset your Configs
Now you can compare the values (in seconds) to figure out how much faster the query is outside of Informatica. Your goal for an optimal repository should be to make that difference as small as possible. If your Informatica SQL query time is 3+ times longer than the manual SQL query, then you will want to read pt.2 of this blog and go over some strategies to clean up the repository.
Important: Now go back into the repository service and set the log level back to INFO and delete the custom property you added. Then stop/start the repository service. If you do not do this, it will create a ton of logs that you don’t need!
Please check back next Tuesday February 21 for Part 2.