Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Database
Highlighted
Enthusiast

Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Hi,

We are facing one strange Behaviour for SQL which is generating two different plan for same SQL. When we use SQL ASSIT. then same query take few seconds to execute but same query going into product join and taking more resource on MSTR tool.

We also performed below steps to find out the root cause:

  1. We checked the caheFlag if there is any generic plan issue but found only specific plan exist for problem SQL on both side (SQL Assit & MSTR)
  2. Collected Stats with no threshold on tables
  3. COST Profile are same for both user ( SQL Assit. & MSTR user)

Did anyone faced such issue, If yes, please suggest how to fix this issue ?

 

Thanks 

Tags (1)
11 REPLIES
Teradata Employee

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Are both using the same transaction mode (ANSI vs Teradata)?

Senior Apprentice

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

As well as checking transaction mode (as suggested by @Fred) you should also check:

- default database for both queries

- check API being used (this is less likely to be the problem). MSTR is almost certainly going through ODBC whereas SQLA might be ODBC or .NET. Certainly ODBC can 'convert' some sql before sending it to the dbms.

 

I'm assuming here that the two sql requests were run within a fairly short time span of each other?

 

Can you post the SQL and the explain plans for both? Preferably retrieved from DBQL tables as those will contain 'reality' - i.e. what SQL was actually run and what plan was used.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Both Are Teradata Mode

Enthusiast

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Is the same DSN being used for both queries?  Many BI tools use 64 bit drivers, SQLA uses 32bit drivers or .Net Providers.

If they are using different DSNs what are the differences? i.e. disable parsing being enabled on one but not the other.

From SQLA are you using Teradata.net or ODBC for the connection?

William Miteff
Enthusiast

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Hi William,

 

We are using same DSN name and ODBC for SQL Assist & MSTR tool. 

Enthusiast

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Depending on how your system is setup, you can have 32 and 64 bit dsns named the same.  you can try to change the name of the DSN via the control panel and see it still shows up in both tools just to verify.  are you using the same user id from both tools? different cost profiles can be assoicated with different ids, you DBA team would need to help verify that in your environment.

William Miteff
Teradata Employee

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Is the actual SQL statement text being submitted to the database the same?

You mentioned Specific plan. Does the statement include parameters?

Enthusiast

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

We are using different user but cost profiles (TD 15)  are same. Also DSN are same

Enthusiast

Re: Strange !! SQL Genrating Different EXplain Plan for SQL Assist & BI Tool (MSTR)

Yes, We are submitting same query and Query has parameters. We also checked the cacheFlag which is NULL in both cases