question on optimisers, gather statistics etc on Oracle

Post #139,348 by boxley 2/2/04 3:26:41 PM Reply	question on optimisers, gather statistics etc on Oracle Oracle 8.1.7 we have a system that creates a new partition with oracle at the beginning of every month. We use a command called gather statistics on the current oracle partition. What we are being told is that if this is not done periodically oracle will thrash the disk IO because the optimiser isnt working properly. Today the are telling me that it needs to be run against an empty partition (march) or the optimiser wont work causing our application to be unusable (we are totally query based). Ran the gather statistics command on the empty partition and now the application runs fine. Can anyone tell me why this works? thanx, bill same old crap, con artists ripping off fools. Ah, hell, Catholic Church it start off that way. They All do. Jesus probably had three walnut shells one pea, then he's dead and can't be questioned, Gabriel Dupre questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org]
Post #139,360 by Simon_Jester 2/2/04 4:26:18 PM Reply	Gather statistics pulls in data regarding the layout of the database table and the block structure underneath. This information tells the optimizer whether or not it's worth it to use an index or just to hit the entire table to find a row (or a set of rows). Example: if you have a table with 4 rows in it (believe it or not, we do) (ahem, provided that they are small rows) it's probably not worth the extra expense of going through the index. Just grab the entire database and look for the row that meets the criteria. However, tables change over time (well, some do) getting more data....losing data, etc. If your table started off with 4 rows and has since had 100,000 rows added, it may approach the data wrong. Before, it was worthwhile to pull the whole table (table scan) and now it'll be an expensive (time-wise) operation. Gathering statistics tells the database to go update itself. I would've thought Oracle had evolved to the point where gather statistics was a background process now. I'm surprised they had you run gather statistics against an empty partition. If something in the statistics database was corrupted, this would have the possible effect of resetting it back to 0. But I would've thought they'd have you run gather statistics again against the real data.
Post #139,418 by boxley 2/2/04 7:37:50 PM Reply	we did both running it against populated tables still crapped the application cpu usage 99% disk IO 100% as soon as we did gather statistics on the partition with no data cpu utilization 2% disk IO 25% thanx, bill same old crap, con artists ripping off fools. Ah, hell, Catholic Church it start off that way. They All do. Jesus probably had three walnut shells one pea, then he's dead and can't be questioned, Gabriel Dupre questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org]

Welcome to IWETHEY!