Oracle Table Analyze

I have one big table containing 10,00,000 records.
When i analyze this table the system hangs.
Is there any problem with SGA Size.

The same table if i analyze in other database with double SGA size it takes onle 3-4 minutes but it does not hang.
How i can speed up this tables's analyzing process.
vishalgoyal123Asked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
It shouldnot hang. If the SGA is small then it will take longer time to analyze. You should be patient and wait. Try to analyze through OEM - it will show you if it works.
0
 
anand_2000vCommented:
10,00,000 records will take some time if your SGA is not big enough. See if you can increase the SGA. Better still use the stats pack to analyze the table.
0
 
seazodiacConnect With a Mentor Commented:
I think the correct way to get around the SGA problem is that

Use "ANALYZE TABLE <table_name> ESTIMATE STATISTICS SAMPLE n PERCENT"
OR
"ANALYZE TABLE <table_name> ESTIMATE STATISTICS SAMPLE n ROWS"

By doing this, you avoid the analysis of the whole 1 million records, CBO also get an overall estimate of statistics as well.

give it a try , see if this command will speed up your analysis
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
anand_2000vCommented:
Estimate, IMHO, will cause more problems than solve. I have seen situations wherein a *compute* results in avoidance of usage on index and resultant *speeding of access* and
a *estimate* still going for the index access.

Of course every situation is unique.
0
 
seazodiacCommented:
anand_2000v, what you said about "estimate" is NOT true:

If no sample size is provided when estimating statistics with the ANALYZE command, Oracle will take a default sample size of the first 1064 rows. This may not be effective and most often will result in bad queries. But If the ESTIMATE sample size is greater than 50%, it is as good as the COMPUTE option, but you get shorter analyze time by skipping the rest of records.

0
 
anand_2000vCommented:
oh...I thought that estimate by default uses 20% !!!! 1064 rows....thanx for the info
0
All Courses

From novice to tech pro — start learning today.