Tablespaces
designed to end disk conflicts
Author: Alex Caminals Even
though indexes are carefully organized when a database (DB) is initially designed,
after time passes and when new indexes need to be created, DBAs sometimes forget
about index disk location. Take a look at the direct relationship
between an index and where it’s stored: An index is created using
a tablespace (defined in the storage clause). These tablespaces use data files
(i.e. file system files). And finally, these data files are stored on physical
disks. A typical tablespace design is done using big,
medium and small extent sizes. For example, the following are common examples
of tablespaces:
| TABLESPACE_NAME | EXTENT_SIZE |
| TAB_BIG | 100
MB | | TAB_MED |
10 MB | | TAB_SMALL |
1 MB | The above design might be good from
some points of view, but it will eventually lead to disk conflicts. Actually,
any tablespace design might end up with disk conflict issues without careful planning
and design. If a table has numerous indexes, and all of
them have the same approximate size, they are likely to be grouped in the same
tablespace. When this happens, all INSERT, UPDATE and DELETE statements
will try to write into the same data file, leading to a bottleneck in the system. To
avoid this type of conflict, when a system is using the big/medium/small extent
size approach, it’s always good practice to define multiple tablespaces
for the same extent size, thus spreading the data files amongst different disks,
and to also define different tablespaces in the storage clauses of the indexes
(when they belong to the same tables). Of course, a new
type of conflict may arise if the number of available disks is reduced and the
system bottleneck will move there instead of in the indexes tablespace definition.
If, however, you can utilize multiple disks then you will gain efficiencies in
the distribution of this index tablespace (and data file) through the implementation
of a very simple change to the storage clause of the indexes in your system.
Distributing indexes among tablespaces and physical disks: 
Go
to top
|
Create
indexes built for speed and efficiency
Author:
Alex Caminals Avoid duplicating indexes
in Oracle When a database (DB) is first designed,
a lot of time goes into the task of designing the indexes to be as efficient as
possible. However, when the application is in production and new indexes need
to be created, developers and DBAs often forget that they can reuse indexes. Let’s
take a closer look at this in the following example: John,
a senior developer, has to create a new report to be available in the company
intranet for the sales team to log in and get their open opportunities. Table
OPPORTUNITIES has an index on column CREATED_BY. That index is already being
used on reports sent to the sales team (each sales rep receives their own report).
John contacts the DBA team to request help creating the new report. As the
query needs to be very fast, John proposes to Mark, a junior DBA, that he creates
a new index based on the columns CREATED_BY, STATUS. Mark, after checking the
pros and cons of adding a new index, decides to move forward with the proposed
plan. In the weekly DBAs meeting, where all production
changes are evaluated by the entire DBA team, Mark shares his proposal. Jane,
a senior DBA, takes the lead and replies to Mark with this tip: When
you have two indexes and one of them is fully included into the other index from
the first column (i.e. all columns of the first index are in the second one, with
the same order and position, and starting from position number one), the first
index can be removed. Jane also says that, in this
case, creating a new index to speed up the queries on CREATED_BY and STATUS is
a good decision. Keep in mind, however, that when executing the queries only on
CREATED_BY, the optimizer can choose between the original index and the newly
created one. This renders the first index useless and could now be removed. Mary,
another senior DBA, adds that by removing the first index instead of having two
indexes, some space is saved. Furthermore, the cost of DML sentences is reduced
as in INSERT, UPDATE and DELETE sentences, meaning a fewer number of indexes will
have to be updated. She also remarks that dropping one index and creating
another one might affect existing queries using hints on index usage. Her
final proposals is drop the existing index and recreate it on the CREATED_BY,
STATUS, keeping the same index name. After listening to
all of these comments, Mark, the junior DBA, is now clear on how to proceed with
all future requests to create indexes. Go
to top
|
