Explain: 2 minutes, Execute: > 80 minutes!! 1

[Lysen]

What's going on with our database, if explain says the total estimated time is 1 minute and 38 seconds, but the execution took more than an hour before I cancelled the query???! We're running V2R5 with Teradata Sql assistant 6.1...

The query is supposed to come back with about 30.000 records, has 5 joins to 2 moderatly big tables (500.000 rows) and 3 small (between 5 and 200 rows).

When I add a WHERE clause to come back with 1 row/unique number, it takes about 5 seconds... The weird thing is, when I fired the total query before the weekend, it would complete within a few minutes!! With the where clause added it took about 5 seconds, just like now...

If anyone could enlighten me on how to solve this, or how to check if the db isn't unstable or something, I'ld be very pleased!

 

[tdatgod]

Check to make sure all the AMPS are ONLINE. Maybe one of them is offline or fatal causing Teradata to have to run in degraded mode.

get status ( from the DBW SUPRV window )

/tpasw/bin/vprocmanager -g | more
( which is all the above command does )

Its possible The where clause narrows the query down to eliminate any degaraded amp and that is why it completes properly.



Check to make sure STATS are collected properly and up to date. Maybe some how they have been lost or no longer reflect the under lying data. ( someone added data when you weren't looking ).

 

[Lysen]

Hi tdatgod, thanks for your quick reply!
Is it possible that AMPS get back ONLINE themselves without interference of a user/admin?

I tried to run the query today, and it worked like it's supposed to! It's finished within a few minutes... I ran the vprocmanager like you asked me to, and it shows that all amps are ONLINE. Hence my question above...

I have another question: I have Teradata PMon (performance monitor) installed on my win xp machine. We used to have teradata V2R4, and all the tools worked. WinDDI, PMon and such all could connect to the db. Since we have V2R5 and I try to connect PMon to the DB, it says: this machine is not in your hostfile.

Where is this file located and how can I add machines to this file? Or am I missing some sort of DLL file? I thought I would just try to get an answer here, before going through that HUGE pile of manuals we got with the new release...

 

[ZemogSC]

No, the amps will not come back online by themselves. There is user intervention involved.

What other type of activity was on the machine when you tried to run your query? Is it posible that someone/something else had locked the table(s) you were working with?

I can't think of a way to see a history of locks on the system, but if you have access logging turned on, you may be able to see if someone else was running something at the same time.

It's a shot in the dark, but the best I can do with a hang-over ;-)