This is in continuation to the previous blogs published on SQL analysis namely :
1. Native SQL usage
2. Run schedule of Queries and their analysis.
How do you see the actual query that the user is executing ..? is there a way to actually see how the query looks like when it is being executed ..? Can we get more information on whether a table is being read directly or an index is being used..?
How can I help out my DBA in terms of troubleshooting bad query perormance..? who is to do what here..? Why do I have to understand SQL..?
Why do I have to understand SQL ..?
Interesting question … the responsibility ultimately lies on the BW delevloper since we are responsible for the data model. The query execution gives us insights into how the query actually runs and if we can do something about it. Also when you use the run schedules for a query along with the SQL query analysis – it helps us understand how a query actually behaves and helps design better data models over time.
Display SQL Query
Here the catch phrase is “Query Statement in OPEN-SQL” this means that the query is being shown to you in Open SQL but then translated to normal SQL that the database can understand later. Very nice blog on Open SQL Versus Native SQL..
I have use a simple query on a DSO for the screenshots but then ideally execute a query on a cube with aggregates and then you should be able to find if the aggregate is helping since the aggregate E table will be hit in the SQL curey and not the Fat Tables of the cube if the aggregate is built correctly.
Points of Interest:
The syntax is pretty much the same as normal SQL and you can identify the where clauses and group by conditions to be able to identify the query execution and therefore performance bottlenecks better.
Why do we need to do this ?
You can identify the aggregation of the Key figures and also make sure that only necessary tables are being hit – in some cases with queries having too many RKFs , an unnecessary table / Infoobject may be included which is not meant to be queried upon and this can be removed.
The other advantage is that you can take this query to your DBA and have it analyzed for performance which they will not be able to do so if it was a screenshot of Query Designer.
The only difference between OPEN-SQL and the Do not use OPEN-SQL is that you can take this output and paste it into a database tool like TOAD and then analyze the same . Also you can see that a temporary view is being generated to hold the output of the query here.
What does DB Optimizer functions do.
There are a lot of Query hints that can be used to speed up queries. For example there is a hint called Star transformation – this is used to explicitly tell the Query engine that the target table is a cube and necessary optimizations can be done. This might or might not speed up the query execution but are considered good to have. The effectiveness of the same however depends on the currency of your statistics information. There are many hints some of which are relevant. A more detailed list can be found at :
Similar hints are available for most databases other than Oracle too.. I have mostly worked on Oracle Instances and hence could not find anything other than Oracle – would love to hear about other databases too.