Parsing Engine is taking long time to parse a query !!!

Database
Fan

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

6 REPLIES
Enthusiast

Re: Parsing Engine is taking long time to parse a query !!!

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

Re: Parsing Engine is taking long time to parse a query !!!

Hi!

I have the same problem... any solution?

Enthusiast

Re: Parsing Engine is taking long time to parse a query !!!

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

Enthusiast

Re: Parsing Engine is taking long time to parse a query !!!

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

Roland Wenzlofsky
Enthusiast

Re: Parsing Engine is taking long time to parse a query !!!

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

Teradata Employee

Re: Parsing Engine is taking long time to parse a query !!!

How are you managing the rights for users/roles?