SQL query slow
Hi,
Problem:
The following SQL stmt caused tremedous io and cpu usage and took a long long time to finish.
SELECT SUM(BYTES) FROM USER_FREE_SPACE F, USER_USERS T WHERE F.TABLESPACE_NAME = T.DEFAULT_TABLESPACE
The original explain plan shows total cost=14089.
After implementing a new plan suggested by the SQL tuning advisor and exectued following suggested by tuning advisor,
BEGIN
Dbms_Stats.gather_table_stats(ownname => 'SYS', tabname => 'FET$', estimate_percent => Dbms_Stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
Dbms_Stats.gather_table_stats(ownname => 'SYS', tabname => 'UET$', estimate_percent => Dbms_Stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
Problem:
The following SQL stmt caused tremedous io and cpu usage and took a long long time to finish.
SELECT SUM(BYTES) FROM USER_FREE_SPACE F, USER_USERS T WHERE F.TABLESPACE_NAME = T.DEFAULT_TABLESPACE
The original explain plan shows total cost=14089.
After implementing a new plan suggested by the SQL tuning advisor and exectued following suggested by tuning advisor,
BEGIN
Dbms_Stats.gather_table_stats(ownname => 'SYS', tabname => 'FET$', estimate_percent => Dbms_Stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
Dbms_Stats.gather_table_stats(ownname => 'SYS', tabname => 'UET$', estimate_percent => Dbms_Stats.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
0