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
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.
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?
Thank you so much for your write-up concerning long parse times and the AccessRights table. I've been spinning with Teradata Tech Support for months to no avail, but your comments actually provide a basis for investigation that doesn't feel like a dead end before we start.