|
"I have never
let my schooling interfere with my education." - Mark Twain
(1835-1910) |
Read
first then play the video:
DBA-VIDEO -Managing
Storage Structures
Managing Storage Structures
Introduction
As a DBA, you are
responsible to manage a table storage structure due to performance
problems or a spacing issue. If you have a table that has lots of
update transactions you would want to be sure that you have enough
space in the PCT space. Your job"s responsibilities dictate that
you should at least be informed of the following basic fundamental
subjects:
Oracle Segments
Extent Allocations
PCT
PCTUSED
Using the DBA_SEGMENTS view
INDEX segment
TABLE segment
Creating an INDEX_ORGANIZED
table
Using the ORGANIZATION
INDEX parameter
Using the TABLESPACE option
Using the PCTTHRESHOLD
parameter
Using the OVERFLOW
TABLESPACE parameter
Understanding Different
Segment Types
Using the DBA_SEGMENTS view
Using the DBA_TABLES view
Using the DBA_EXTENTS view
Using the MAXEXTENTS option
Space allocation in the
Oracle Block unit
Setting the PCT
parameter
Setting the PCUSED
parameter
Commands:
DROP TABLE my_
CREATE TABLE STORAGE
ANALYZE TABLE COMPUTE
STATISTICS
ALTER TABLE STORAGE
Hands-on
In this exercise you will
learn how Oracle segments, extents allocations, pct and pctused
setting works, and more. Note that PCT means that an Oracle user
can add records to a block until the unused space block reaches to the
PCT value. When a block uses all space up to the "1-PCT"
percentage, it stops adding records to the block. Oracle takes that
block out of the list. It means that records can not be added to
the block any more unless you delete records from the block till it
reaches to the PCTUSED value. Then Oracle will add the block in the
list again and records can be added to the block. And this process
continues to determine when to add records in or stop adding records
from the block.
Now, connect to SQL*Plus as
the system/manager user.
SQL> CONNECT system/manager AS SYSDBA
View Segment types
Query the number of different segment types you have in the
database.
SQL> SELECT segment_type,
count(segment_type)
FROM dba_segments
GROUP BY segment_type
/
Notice that you may have more or less than this list. It
all depends on the segments that were created in the database. Take
notes about the counts on the INDEX and TABLE segments.
Create an object
Let's create an Index-Organized Table.
SQL> CREATE TABLE my_iot (
partno NUMBER,
name VARCHAR2(20),
CONSTRAINT pk_my_iot PRIMARY KEY (partno))
ORGANIZATION INDEX
TABLESPACE users
PCTTHRESHOLD 20
OVERFLOW TABLESPACE users
/
Query to see how many different segment types you have in the database
this time.
SQL> SELECT segment_type,
count(segment_type)
FROM dba_segments
GROUP BY segment_type
/
Notice that the count in the INDEX and TABLE segments have
been incremented by one. The reason for that is when you created the
IOT table, it creates the INDEX and TABLE segments. Remember that
although the SYSTEM tablespace can store any database objects, it is
not recommended that you put objects in it other than the dictionary
objects and the system UNDO segment.
Now, drop the table.
SQL> DROP TABLE my_iot
/
Extent Allocation in
tablespace
Let's see how the extent allocation works in tablespace. Create a
table with all of the following options. The initial size of 50k, next
extent size of 50k, a percent increase of zero, a minimum extent of 1,
and a maximum extent of 5.
SQL> CREATE TABLE my_default_table
(col1 CHAR(2000))
STORAGE (INITIAL 50k
NEXT 50k
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 5)
/
Now, let's check or view what we have in the Oracle Dictionary. Query
the DBA_TABLES view and display statistical information about the
table. Be sure to first analyze the table with the compute statistics
option.
SQL> ANALYZE TABLE my_default_table
COMPUTE STATISTICS
/
SQL> SELECT table_name, initial_extent, next_extent,
max_extents, num_rows
FROM dba_tables
WHERE table_name = 'MY_DEFAULT_TABLE'
/
Note that the number you see is the default number for an
initial and next extension. Take notes about it.
Query the distribution of the segments.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
/
Take notes about that.
Lets now insert 20 records into the table.
SQL> BEGIN
SQL>
FOR i IN 1..20 LOOP
SQL>
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL>
END LOOP;
SQL>
COMMIT;
SQL> END;
SQL> /
Query the distribution of segments.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/
Notice that an extent was
added to the list.
Alter your table with a 50 percent extension increase.
SQL> ALTER TABLE my_default_table
STORAGE (PCTINCREASE 50)
/
Lets insert 50 more records into the table.
SQL> BEGIN
SQL>
FOR i IN 1..50 LOOP
SQL>
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL>
END LOOP;
SQL>
COMMIT;
SQL> END;
SQL> /
Query the distribution of the segments again.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/
Notice that 3 more extents were added to the list and each
new extent was incremented by 50 percent.
This time let's insert 50 more records into the table, keeping in
mind, we are not allowed to allocate more than 5 extents, because the
MAXEXTENTS option was set to 5.
SQL> BEGIN
SQL>
FOR i IN 1..50 LOOP
SQL>
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL>
END LOOP;
SQL>
COMMIT;
SQL> END;
SQL> /
Query the distribution of the segments again.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/
Remember that the table can grow up to 5 extents. You can
alter the table to expand the extents.
PCT and PCTUSED space
allocation
Now, let's exercise how to set the pct and the pctused space
allocation in the Oracle block unit. Drop MY_DEFAULT_TABLE and
recreate it with an initial extent size of 100k, a next extent size of
200K, and a percent increase of 0 in the USERS tablespace.
SQL> DROP TABLE my_default_table
/
SQL> CREATE TABLE my_default_table
(col1 CHAR(1000))
STORAGE (INITIAL 100k
NEXT 200k
PCTINCREASE 0)
TABLESPACE users
/
Once again, query the distribution of segments.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/
Let's now insert 100 records into the table.
SQL> BEGIN
SQL>
FOR i IN 1..100 LOOP
SQL>
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL>
END LOOP;
SQL>
COMMIT;
SQL> END;
SQL> /
Once again, query the distribution of the segments.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/
Take notes on the number of created extents.
Check the block space allocation in MY_DEFAULT_TABLE.
SQL> SELECT pct_, pct_used
FROM user_tables
WHERE table_name = 'MY_DEFAULT_TABLE'
/
Notice that the default percent is set to 10 and the
percent used is set to 40.
Now, change the percent to 50 percent. Then, truncate the table
and check the segments allocation in the table.
SQL> ALTER TABLE my_default_table
PCT 50
/
SQL> TRUNCATE TABLE my_default_table
/
SQL> -- Check the segments allocation"
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/
Let's insert another 100
records into the table again.
SQL> BEGIN
SQL>
FOR i IN 1..100 LOOP
SQL>
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL>
END LOOP;
SQL>
COMMIT;
SQL> END;
SQL> /
Then, query the distribution of segments one more time and drop the
table after that.
SQL> SELECT segment_name, extent_id, bytes
FROM dba_extents
WHERE segment_name = 'MY_DEFAULT_TABLE'
ORDER BY 2
/
Write down the number of created extents. Do you see the difference?
This is what is going to happen if you don't properly assign your
block size with the right pct and pctused allocation. Remember
that if you are only inserting records with no changes, the pct
allocation should be as low as 5. If you have a lot of updates you may
want to have the pct allocation set to 20 percent or more.
Drop the table.
SQL> DROP TABLE
my_default_table
/
|
"The longer I
live the more I see that I am never wrong about anything, and
that all the pains that I have so humbly taken to verify my
notions have only wasted my time." - George Bernard Shaw
(1856-1950) |
Questions:
Q: Describe a Segment in
the Oracle database?
Q: Describe the
DBA_SEGMENTS view?
Q: What are the PCT and
PCTUSED space allocations in the CREATE TABLE statement?
Q: How many types of
segment do you have in the Oracle database?
Q: How do you create an
INDEX_ORGANIZED table?
Q: Describe the
ORGANIZATION INDEX parameter.
Q: Describe the
PCTTHRESHOLD and OVERFLOW TABLESPACE parameters.
Q: Describe the DBA_EXTENTS
view.
Q: What do the following
SQL statements do?
SQL> SELECT segment_type,
count(segment_type)
FROM dba_segments
GROUP BY segment_type
/
SQL> CREATE TABLE my_iot (
partno NUMBER,
name VARCHAR2(20),
CONSTRAINT pk_my_iot PRIMARY KEY (partno))
ORGANIZATION INDEX
TABLESPACE users
PCTTHRESHOLD 20
OVERFLOW TABLESPACE users
/
SQL> CREATE TABLE my_default_table
(col1 CHAR(2000))
STORAGE (INITIAL 50k
NEXT 50k
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS 5)
/
Q: How do you query the distribution of the segments in the database?
Q: How do you the following
PL/SQL statement?
SQL> BEGIN
SQL>
FOR i IN 1..100 LOOP
SQL>
INSERT INTO my_default_table VALUES ('Numbers: ' || i);
SQL>
END LOOP;
SQL>
COMMIT;
SQL> END;
SQL> /
|