Skip to Main Content
  • Questions

  • Performance degradation after database restoration

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: February 05, 2019 - 2:05 pm UTC

Last updated: February 06, 2019 - 5:15 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

HI ALL, I HAVE A PROBLEM HERE


WE are running an application(FOR REPORTING ONLY) with Oracle database 11.2,
Data for this oracle database is getting from SQL server ( users are inserting data into SQL server first
and finally with the help of a batch file those data is transferring to oracle database,
and with some procedures data getting updated in to corresponding tables.
Batch file is set to run 24/7 . )

Recently we had an issue with oracle database server and had to restore the database to a new server
now the problem is while the process of updating the transferred data to oracle database, if someone accessing the same table,
like
'select * from <tab_name>'
'select count(*) from <tab_name>'
it is taking too long to respond and overall performance is degraded

i have checked for 'locking' in the database and i found some tables are getting locked
INCLUDING AUD$ TABLE (ROW_X (SX): Row Exclusive Table Lock)
(lock occur only when executing the update procedures)

What i can i do for this situation??

Do this table locking affect EXPDP?

Why aud$ table is getting locked??

Finally i have a doubt which is not related to the above question

is there any performance gain by moving aud$ table from SYSTEM tablespace to another one??


and Connor said...

"restore the database to a new server"

Did you use data pump or RMAN? If it was data pump, you have effectively unloaded and recreated every table and its data in your schemas. So that has totally changed:

- the table block density
- the index density
- the optimizer statistics

Its like a brand new system. A RMAN restore would copy the binary files and thus the data characteristics would remain unchanged.

A reader query will not perform any locks, but having said that, if a table is undergoing heavy changes, then queries against the table will work harder to 'undo' those changes so that query is consistent in terms of the data it displays.

It is very strange to see anything locking the AUD$ table. Depending on your audit options you could *add* rows very aggressively, but whilst they will be row locks they should never be *blocking* anything, unless you have some purge jobs setup.

You wont see a performance change with moving AUD$ but it probably can help with managing the SYSTEM tablespace.

Use the DBMS_AUDIT_MGMT package to relocate it.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 06, 2019 - 9:11 am UTC

Yeah we were using datapump utility to restore the database.
audit parameter is set to "DB" for our environment and there is no extra configuration for audit records..

Thank you for your valuable response over my situation :)


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.