 |
|
Oracle Tips by Burleson |
Observe Poorly-Defined Objects
In this assignment we will deliberately
miss-set the storage parameters for a table in your Oracle database.
In this exercise, you will create an Oracle table with improper
settings and load the table with sample data. The student will run
DML timings against the table, and also interrogate row chaining
within the table. Submit results to instructor by due date.
WE can easily create a “bad” table within
Oracle by deliberately mis-setting some important storage parameters.
Your tasks are as follows:
1 - Copy this code snippet onto your Oracle
database and run it to create a table with inappropriate storage
values and populate it with rows from the DBA_OBJECTS view.
connect
pubs/pubs
drop table
test_table;
create table
test_table
storage
(
freelists 20
freelist groups 20
initial 2000
next 2000
minextents 20
maxextents 25
)
as
select *
from
dba_objects
;
analyze table test_table compute statistics;
2 – Write a query against the DBA_TABLES and
DBA_SEGMENTS views to display the following columns about your table.
* table_name,
* num_rows,
* extents,
* chain_cnt,
* freelists,
* freelist_groups
3. Turn-in the resulting query to your
instructor and comment on those parameters that are inappropriate and
the effect they had on the table. Write a short sentence describing
why each storage parameters was inappropriate, and suggest a correct
setting.
ANSWER
set heading
on
select
t.table_name,
num_rows,
extents,
chain_cnt,
s.freelists,
s.freelist_groups
from
dba_tables t,
dba_segments s
where
t.table_name = s.segment_name
and
t.table_name = 'TEST_TABLE';
* The table has extended because of the low
value of INITIAL
* There are no chained rows because no UPDATES
were issued
* The freelist groups parameter is only used
with RAC (Real Application clusters)
* There is no need for MINEXTENTS because we
are not striping the table
* We should never set MAXEXTENTS because the
table may lock-up
The main points of this tutorial include:
* Most relational databases provide a wealth
of options for tables. Tables can be defined as index-organized
tables, single-table clusters, or standard tables.
* Oracle provides complete control over the
internal storage of tables with numerous storage parameters.
* You can use the dbms_metadata package to
quickly punch DDL from the Oracle data dictionary.
* Storage parameters affect how the table is
stored inside the tablespace.
* Inappropriate settings for PCTFREE and
PCTUSED can cause DML to run for long periods of time.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
