- What is the formula to calculate space for the tables in a database?
- For example, if I want to create a datafile in a database what should be the minimum size that I define for the datafile (is there any formula)?
- If I run a query that displays the number of physical read and writes per second for each datafile and they are high numbers, then what should I do to tune the database?
- If I run a query that displays users with high CPU processing since instance startup, then what area do I need to focus on?
- If I run a query that displays waiting sessions blocked through other sessions, then what do I need to do?
- If I run a query that displays the user that has performed the most physical disk reads then what do I need to do?
- If I run a query that displays the most resource-intensive SQL statements that have been recently executed, what should I do to tune the database?
- DBAs used to have complex spreadsheets to correctly size tables and indexes in their database. These are no longer used as the cost of disk storage has dropped dramatically. Unless you are running a VLDB, you probably have more storage than you need. So do not worry about how much space a table requires. If you are running a VLDB, then disk may be a factor for you. In either case, use Locally Managed Tablespaces to let Oracle size your tables for you.
- The minimum for your tablespace's datafiles is the minimum size to hold your data while accomodating some period of growth. But you can make your datafiles small initially and then let them grow (autoextend) until they are as large as they need to be.
- If the sum of your physical read and writes for a datafile is high, then it is likely that you have a very active segment (table or index) in that tablespace. You might want to consider moving this highly active segment to a tablespace of its own, residing on a disk device of its own. That way, the activity against this segment will not impact activity against other segments, and vice versa. To figure out which segments are experiencing the highest read and write activity in your database, query V$SEGMENT_STATISTICS similar to the following:
SELECT owner,object_name,tablespace_name,SUM(value) AS IO_COUNT FROM v$segment_statistics WHERE statistic_name IN ('physical reads','physical writes', 'physical reads direct','physical writes direct') GROUP BY owner,object_name,tablespace_name ORDER BY 4;
- Just because a session has accumulated lots of CPU does not necessarily mean that a problem exists. Maybe this is intended. Or maybe they wrote inefficient PL/SQL code. You'll have to look at this on a case-by-case basis and examine what that session is running against the database.
- Again, just because a session is waiting on another session does not necessarily mean that a problem exists. The Oracle database is a multi-user transactional environment. Oracle handles all of these transactions from many users via locking. Once a transaction commits or rolls back, the lock is released. If a session is holding onto a lock for a long time, then there are a few things you can focus on. One, have that session commit more often if possible. Two, tune the session's transactions so that they run faster and do not hold the lock as long.
- If a user has performed a large amount of physical reads, then the first place I would look is at the SQL statements they are running. A large amount of physical reads might mean that they are running suboptimal SQL statements, which can mean unnecessary full table scans. Tune their SQL statements. Another place I would look is at my buffer cache. It might be that the buffer cache is too small, leading to physical reads.
- If you know the most resource-intensive SQL statements, then your next course of action is to tune those SQL statements. If you can get these SQL statements to run and consume fewer resources, then the user will get results faster and other users will be impacted less.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.