Database upgraded to 11.2.0.4

[satish]

Dear erman,

We have upgraded database from 11.1.0.7 to 11.2.0.4
We have ran adstats.sql after upgrade which is a post step.No issues reported since a week from app teams.We found below query which takes lot of time to provide output in new database which is not the case in 11.1.0.7.Any suggestions please.

SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
round(df.bytes / (1024 * 1024), 2) curr_ts_size,
round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
from V$temp_space_header
group by tablespace_name, bytes_free, bytes_used) fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_temp_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;


Thank you

[ErmanArslansOracleBlog]

You can get the old (performing well) execution plan from your 11.1.0.7 database and fix it in the newly database. (or you can use AWR for getting that old execution plan and fix that) Example for getting it for AWR ->  select * from table(dbms_xplan.display_awr('<sql_id>'));

[satish]

Hi erman,

It is a query which used to check tablespace usage.Do you think we have run gather stats on fixed object's?

Thank you

[ErmanArslansOracleBlog]

Yes, gathering fixed object stats and dictionary stats may help, but you already should have done it during the upgrade, or at a later stage in the upgrade.
In my previous update, I have sent you can do about this sql.
If you are considering those stats, do it in your TEST env first.. Then take a db backup before considering doing it on PROD.