Long Parsing Issue

Database
Enthusiast

Long Parsing Issue

Hi,

We are facing long parsing issues and observed most of the sessions which are reporting the high delays, are also reporting the high parser time due to the workload chosen for the session classification. I can think of is to introduce a separate high priority workload for PARSING phase of all the sessions (which are critical and suffering from high delays).  I am seeing the parsing time and as well Parsing CPU Time is greater than 30 seconds and even seiing three and four digits seconds for the some of the sessions. As per my understanding, ideal parsing time (both ParsingCPUTime and ParsingTime) is abnormal if it greater than two digit seconds. please correct me if i am wrong.

7 REPLIES
Teradata Employee

Re: Long Parsing Issue

Large CPU Time for parsing versus elongated elapsed Parsing Time (for relatively modest Parsing CPU) are two different things.

When the elapsed time is long relative to the CPU time, and particularly if the issue correlates with a specific SessionWDID, then a parsing-only workload may help. See Carrie's blog for details (which depend on the release of TD you are running): http://developer.teradata.com/blog/carrie

On the other hand, large Parsing CPU time typically indicates a complex query. If the request itself looks simple, perhaps there are multiple layers of complex views involved, or many joins, etc. A separate workload won't reduce the Parsing CPU. Sometimes large parsing CPU is necessary. If there are specific queries that seem to consume far more parsing CPU than other similar queries, try to determine what makes the problem queries different.

Supporter

Re: Long Parsing Issue

Same point as Fred mention. In case of large CPU times - check the complexity of the SQL by running "show select...".

Depending on the DB release there had been issues with the existance of JI in complex queries in the past.

Enthusiast

Re: Long Parsing Issue

Hi Fred & Ulrich,

Thanks for prompt repsonse.

Yes, we are seeing elapsed time is very long relative to ParserCPUTime (specific to session WDID) but not all the time as shown below:

Row Labels

Sum of Sum(ParserCPUTime)

Sum of QueryPrepSec

Sum of AMPSec

Sum of TotDelaytime

8

                                         18,372.36

                    2,445,888.68

           180,361.62

                        95,826.36

12

                                            9,903.17

                    2,022,695.26

             66,527.88

                              602.53

13

                                            5,394.01

                    1,946,405.29

             41,457.32

                              259.95

3

                                            8,040.07

                    1,899,677.38

           125,235.63

                        74,250.37

15

                                            5,747.61

                    1,353,337.37

             47,052.16

                              380.46

7

                                         12,750.79

                    1,176,524.13

           207,700.98

                      128,698.83

9

                                         12,324.01

                       638,505.84

           132,456.17

                        39,696.64

11

                                            6,889.21

                       411,856.95

             61,908.05

                              664.29

10

                                            8,806.21

                       390,505.97

           102,365.60

                        13,492.49

14

                                            6,341.00

                          92,785.41

             45,897.98

                                 61.51

It is not specic to one complex query, it is across whole environment during mulitiple periods.

we are having two issues here, one is long parsing issue and another is session delay issue (ParserCPUTime vs QryPrepSecs are relatively low):

As part of solution, We are planning to follow the below steps to nail down the issue:

1) Clearing skew in accessrights table (currently we are having 76) by running checktable

2) Seeing the options to implement seperate parser workload

3) Cache effectiveness.

4) ETL window and reporting window confined to their defined window or not.

Am i missing any other factors to consider in digging more.

Thanks,

Harsha.

Teradata Employee

Re: Long Parsing Issue

Checktable has no effect on skew. Skew in AccessRights is usually addressed by implementing Roles and database-level GRANTs and then revoking redundant user / table level access rights; in particular you may want to revoke "automatic" rights granted to the creator of an object.

Review DBQL (or use Viewpoint Rewind) to understand concurrency and throttle delays during the periods in question.

I would also look at ResUsage (particularly ResSawtView) to see if Flow Control is a factor.

Teradata Employee

Re: Long Parsing Issue

There is no ideal parsing time. A very complex query accessing a complex data model can take a while to parse in favor of having a shorter run-time by finding the best plan. Eg if spending a minute on parsing results in a plan that runs quickly, then the parsing time is a good thing.

Would it be possible for you to describe the type and complexity of the quereies and describe the data model that is being accessed? How many tables? views? nested views? views with joins/unions/...? indexes/JIs on tables? complexity in queries - complex expressions, case expressions, subqueries,...?

Note that parsing times can be affected by workload priority of the session/query, especially if the system is highly CPU busy. The overall CPU busy of the system should be examined during the periods when the queries are exhibiting the long parse times. If the workload has a critical response time, then the workload should be given a high priority which will also apply to the parsing of that workload.

Parsing time can be affected by dictionary locks. Review the query log to see if lots of DDL is being done at the times long parsing times are being experienced. DDL locks on the dictionary can make the parser wait for dictionary information to be retrieved.

Delays are mentioned. Make sure to segregate the delay time from the parsing time. The delay time is from workload throttling rules and is completely independent of parse ti,e A critical response time query should be in a workload which is not throttled.

Anything you can provide in the way of details or findings will be appreciated. Pare time is an area we are actively looking at but there are many contributors and more data would be valuable to understand each variation of the issue.

Enthusiast

Re: Long Parsing Issue

Hi ToddAWalter,

 

I have a followup qiestion on your answer. We are facing long parsing issue for one the IDs on our platform. That ID is running lot of create and drop tables in  Fload jobs and the tables are in one and same db. When we go to the jobs in parsing state, we dont see any sql in the sql tab, but we have seen same IDs sessios blocking each other due to write lock on dbc.accesstightsm, so we assume it is the same. We also saw that dbc.accessights table is skewed for the ID,databasename combination. So, what could be a resolution here ?

1. Could using more than one ID/database name help here ?

2. Is this issue because they are dropping/creating temp tables ? And will this go away if they delete instead of drop ?

3. Do we need to clean up dbc.accessrights for this ID & DB combination and how do we do it ?

3. Do we need to see something else here ?

Your comment :

 

Parsing time can be affected by dictionary locks. Review the query log to see if lots of DDL is being done at the times long parsing times are being experienced. DDL locks on the dictionary can make the parser wait for dictionary information to be retrieved.

--Samir 

 

 

Tags (1)
Enthusiast

Re: Long Parsing Issue

Hi ToddAWalter,

Could you/anyone provide input on this. This is somewhat critical to us.

 

Thanks !

Samir Singh