Question:
What can cause an Oracle database to run slower (in Queries or when inserting and deleting data), and yet not presenting any other apparent errors?
Solution:
This could be happening for several reasons, and it’s impossible to determine which is the underlying cause without a deeper analysis of the Database. But there are some measures that can be preemptively taken in order to help reduce most errors and running issues:
- Check if all tables have primary keys and indexes.
- Check if the SQL queries have an effective filter.
- Check if the Query displays only the fields it will actually use (to do so, use SQL’s Fields clause).
- Set up the Query’s CursorLocation as Server.
You should also check for the presence of TRC files in the server. These files are generated by Oracle and help trace and debug queries. They can be found at adump, bdump, cdump, and dpdump directories. These files can be rather large, and they take up too much space in the hard disk. Their presence usually signals there are problems in the Database. To know more about TRC files, see this article from Microsoft’s documentation.
