Performance - Steps in die Execution Plan differs massiv from the real execution

Database

Performance - Steps in die Execution Plan differs massiv from the real execution

Hello,

i have  performance Problems with my parameterized sql statemens which are commited to the database.

The Problem is, that die Execution Plan predict only 9 steps, but  the DBC.DBQLOGTBL shows for a couple of querys up to 296 steps.

The bulk of the querys have only 9 steps and a good performance, but the worst querys smashed up our performance.

How can i analyse why some querys for other values in the parameterized sql takes up to 296 steps ?

What would be the best way ?

I have seen, that the Querys which perform bad are Cached Queries.

Regards

Lukas

2 REPLIES

Re: Performance - Steps in die Execution Plan differs massiv from the real execution

hey experts

no ideas ?

Teradata Employee

Re: Performance - Steps in die Execution Plan differs massiv from the real execution

For queries with the same ProcID, are you seeing the CacheFlag sequence S-G-T (Specific, Generic, Cached generic) where the G and T perform badly? Check Carrie's article on "peek using".

Perhaps you could enable DBQL Explain / XMLExplain logging (for a brief period) to capture the EXPLAIN for the bad Generic plan, then use that to try to identify stats, constraints, etc. that will improve that plan.