Skip to Content

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

Run Schedule

 

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..
http://www.itcserver.com/blog/2006/06/26/open-sql-vs-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.

SQL Version

Output :

Output

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.

Options

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 :
http://www.oradev.com/hints.jsp

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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Jack Jacinto
    First I find this blog very interesting & I’d love to delve into this with all my free time.
    Second, I’ve been dealing with data manipulation for years now & I’m at a point where I need to easily index & quantify my data using all fields that I have.  Right now I get my work done using a postal program & Excel to get my numbers.  Mind you I work with data lists that contain about 260000+ names a week.  & I want to be able to get counts using the data I’ve stored for the past 3+ years.  So we are talking about data in the millions.  Basically I wanted to know where I should begin.  I’m going to be doing this all from home, so what type of server should I purchase?  What programming language would help me the best?  What OS should I use?  Are there any open source programs I can get online, since I’m going be doing this on my own dime & time.  Hope you can help.

    Peace Always,
    Jack

    (0) 
    1. Arun Varadarajan Post author
      Jack,
      What you could do is try and setup something on mySQL. Only thing is that mySQL does not have too many free clients… I have not looked much at mySQL clients but guess you should have the same functionality as TOAD for these too for a fully featured client
      (0) 

Leave a Reply