All Forums Database
Kishore_1 208 posts Joined 03/10
21 Apr 2015
Stats Collection

I am running a few stats collection statement ,sequentially , by reading and executing through a cursor called from within a stored proc.At any point, if a stats collection statement is failing ,due to "no more spool space" I have to manually monitor and restart the process after eliminating such statements.Is there any approach, that if the query is spooling out, it should capture the error in an user-defined exception table and cursor should proceed to the next statement ,without aborting the session.

 

Any help in this is highly appreciated.

 

Thanks,

Kishore

 

Kishore_1 208 posts Joined 03/10
21 Apr 2015

I am reading the stats statements from a metadata table

VandeBergB 182 posts Joined 09/06
21 Apr 2015

Kishore,
What version of TD are you running?

Some drink from the fountain of knowledge, others just gargle.

Kishore_1 208 posts Joined 03/10
21 Apr 2015

We are using Teradata 14.10 version

VandeBergB 182 posts Joined 09/06
21 Apr 2015

you should be able to set up automated stats collection via viewpoint.  there's no need to run anything else, especially a cursor.  row by row cursors force TD into single threaded processing, laying waste to the idea of an MPP

Some drink from the fountain of knowledge, others just gargle.

dnoeth 4628 posts Joined 11/04
21 Apr 2015

Hi Kishore,
as VandeBergB mention you might switch to TD14.10's AutoStats feature.
If you still want to collect using your own logic, I would suggest switching to the apporach I described over here: How to simplify your statistics collection with a queue table
This works well and you can easily collect stats in parallel. You can simply use your existing cursor and insert the rows into the queue in that order. 

Dieter

Kishore_1 208 posts Joined 03/10
22 Apr 2015

Thanks VandeBergB ,Dieter for the suggestions.While I will explore the auto stats collection portlet in view point, as an immediate resolution I am planning to adopt  SP (queue table approach)as suggested .
Thank you guys for the help!!
Much Appreciated!

Jmeher 6 posts Joined 11/14
26 Apr 2015

If suppose one of my table is loading a million data on daily basis by truncate load method, then how will the stats collect play its role over here for performance improvent while loading?

You must sign in to leave a comment.