 |
|
|
| > |
QUESTION POSED ON: 17 November 2005
- 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.
|
|
|
|
|
|
|
|
Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and
answer pairs from more than 250 TechTarget industry experts.
|
|
|
|
|
|
|
|
|
|
|
|
