All Forums Database
King W 4 posts Joined 02/09
06 Dec 2011
Parsing Engine is taking long time to parse a query !!!

Hi Guys,

Can any one help me with the Below ?

 

We are currently runnig on Teradata 13.10

and when we are submitting a query which can be rated on the complexity scale(1-10) as 5

but when we are trying to monitor it using teradata monitor it is taking a round 20 minutes just before it start execution and the PE state is Parsing during all this period.

 

Thanks in advance

AbeK 24 posts Joined 08/09
06 Dec 2011

check the TASM configuration, if you have throttles subjected to the session then that might be a place for further investigation.

toccig 2 posts Joined 06/11
20 Dec 2011

Hi!

I have the same problem... any solution?

TSchmolzi 30 posts Joined 07/07
01 Feb 2012

I'm seeing some of this as well especially as the number of sessions increase

- Tim S

oshun 43 posts Joined 07/10
10 Aug 2012

Hi. I had the same problem with parsing times up to several minutes. Additionally we experienced flowcontrol state at one AMP during the nightly batch loads and especially with increasing number of sessions.

 

As the only change we have done before this problem showed up was replacing DROP/CREATE Table statements with DELETE statements in our historization process (called several thousand times for many many tables) I was taking a detailed look into the DBC.AccessRights table as i assumed this could be related to permission lookup by the parsing engine and actually this was the problem:

 

-> As we now did not drop anymore temporary tables from the stage database hundreds of thousands of new permissions stayed permanently in the dbc.accessrights table. This alone was not the problem but as we have such a huge amount of tables for one batch user and the primary index of the dbc.accessrights table is username and databasename the table was completly skewed i.e. one amp had much more work checking permissions during parsing than the others. 

-> This caused not only very long parsing times but as well forced the AMP into flow control state as it ran out of AWT's...

-> As the temporary tables were created by the batch user and as owner holds anyway implizit rights on the created objects we cleaned up the DBC.AccessRights tables from the explicitely inserted permission records (i think 12 per table creation).

 

-> As a result we have now a well performing system again.

 

Anybody nows if this "explicit permission record creation" can be avoided in such a case? 

 

Hope this helps you as well.

 

Roland

 

 

 

 

visit my private blog at http://www.dwhpro.com

Roopalini 13 posts Joined 05/08
31 Jan 2014

Hi Roland,
Can you tell me, how you removed the explicit rights from the DBC.AccessRights table? Do you have any query that gives you that data?
 
Thanks
 
 
 

Adeel Chaudhry 773 posts Joined 04/08
01 Feb 2014

How are you managing the rights for users/roles?

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.