 |
|
Monitoring Table
Subpartitions
Oracle Tips by Burleson
|
In Oracle8i, the concept of partitions having
subpartitions was introduced. Many of the commands, such as ALTER,
require the name of the subpartition if you wish to execute commands
against a subpartition. Usually, Oracle8i will automatically name the
partitions created as subpartitions unless you specifically add a
subpartition to an existing set of partitions and subpartitions. The
report in Source 10.16 demonstrates how to extricate the information
about subpartitions from the database data dictionary. An example of a
report generated by the script in Source 10.16 is shown in Listing
10.15.
SOURCE 10.16 Example of subpartition report
script.
rem
rem Name: tab_subpart.sql
rem Function : Report on partitioned table structure
rem History: MRA 6/13/97 Created
rem
COLUMN table_owner NEW_VALUE owner1 NOPRINT
COLUMN table_name FORMAT a15
HEADING 'Table'
COLUMN partition_name FORMAT a15 HEADING
'Partition'
COLUMN tablespace_name FORMAT a15 HEADING
'Tablespace'
COLUMN initial_extent FORMAT 9,999 HEADING 'Initial|Extent
(K)'
COLUMN next_extent FORMAT 9,999 HEADING
'Next|Extent (K)'
COLUMN pct_increase FORMAT 999
HEADING 'PCT|Increase'
SET LINES 130
START title132 'Table Sub-Partition Files For &owner1'
BREAK ON table_owner ON table_name ON partition_name
SPOOL rep_out/&&db/tab_subpart.lis
SELECT
table_owner,
table_name,
partition_name,
subpartition_name,
tablespace_name,
logging,
initial_extent/1024 initial_extent,
next_extent/1024 next_extent,
pct_increase
FROM sys.dba_tab_subpartitions
ORDER BY table_owner,table_name,partition_name
/
SPOOL OFF
LISTING 10.15 Example of output from
subpartition report.
Date:
05/09/99
Page: 1
Time: 03:59 PM Table Sub-Partition Files For
SYSTEM
SYSTEM
ORTEST1 database
Initial Next
PCT
Table Partition SUBPARTITION_NAME Tablespace
LOG Extent (K) Extent (K)
Increase
--------------- --------------- ------------------ -----------
--------------------------------------------------TEST5
Q1_1997 SYS_SUBP1
DATA_TBS1 YES 40 40
0
SYS_SUBP2
DATA_TBS2 YES 40 40
0
SYS_SUBP4
DATA_TBS4 YES 40 40
0
SYS_SUBP3
DATA_TBS3 YES 40 40
0
WEEK1
DATA_TBS1 YES 40 40
0
Q1_1998 SYS_SUBP17
DATA_TBS1 YES 40 40
0
SYS_SUBP18
DATA_TBS2 YES 40 40
0
SYS_SUBP19
DATA_TBS3 YES 40 40
0
SYS_SUBP20
DATA_TBS4 YES 40 40
0
Q2_1997 SYS_SUBP5
DATA_TBS1 YES 40 40
0
SYS_SUBP8
DATA_TBS4 YES 40 40
0
SYS_SUBP7
DATA_TBS3 YES 40 40
0
SYS_SUBP6
DATA_TBS2 YES 40 40
0
Q3_1997 Q3_1997_S1
DATA_TBS1 YES 40 40
0
Q3_1997_S2
DATA_TBS2 YES 40 40
0
Q4_1997 SYS_SUBP9
Q4_TBS1 YES 40
40
0
SYS_SUBP10
Q4_TBS2 YES 40
40
0
SYS_SUBP11
Q4_TBS3 YES 40
40
0
SYS_SUBP12
Q4_TBS4 YES 40
40
0
SYS_SUBP13
Q4_TBS5 YES 40 40
0
SYS_SUBP14
Q4_TBS6 YES 40 40
0
SYS_SUBP15
Q4_TBS7 YES 40
40
0
SYS_SUBP16
Q4_TBS8 YES 40
40
0
Monitoring Subpartition Statistics
Like tables and partitions, statistics are all
gathered at the subpartition level. The view DBA_TAB_SUBPARTITIONS
contains the statistics for all analyzed subpartitions. Source 10.17
shows a sample report on subpartition statistics in Oracle8i and
Oracle.
SOURCE 10.17
Example of subpartition statistics report.
rem
rem Name: tab_subpart_stat.sql
rem Function : Report on partitioned table structure
rem History: MRA 6/13/97 Created
rem
COLUMN table_name
FORMAT a15 HEADING 'Table'
COLUMN partition_name FORMAT a15
HEADING 'Partition'
COLUMN subpartition_name FORMAT a15 HEADING 'Sub|Partition'
COLUMN num_rows
HEADING 'Num|Rows'
COLUMN blocks
HEADING 'Blocks'
COLUMN avg_space
HEADING 'Avg|Space'
COLUMN chain_cnt
HEADING 'Chain|Count'
COLUMN avg_row_len
HEADING 'Avg|Row|Length'
COLUMN last_analyzed
HEADING 'Analyzed'
ACCEPT owner1 PROMPT 'Owner to Report On?: '
SET LINES 130
START title132 'Table Sub-Partition Statistics For &owner1'
BREAK ON table_owner ON table_name ON partition_name
SPOOL rep_out/&&db/tab_subpart_stat.lis
SELECT
table_owner,
table_name,
partition_name,
subpartition_name,
num_rows,
blocks,
avg_space,
chain_cnt,
avg_row_len,
to_char(last_analyzed,'dd-mon-yyyy hh24:mi')
last_analyzed
FROM
sys.dba_tab_subpartitions
WHERE
Table_owner LIKE UPPER('%&&owner1%')
ORDER BY
table_owner,table_name,partition_name
/
SPOOL OFF
CLEAR COLUMNS
TTITLE OFF
UNDEF owner1
The output from the report given in Source 10.17 is shown in
Listing 10.16. Note that this example isn’t highly complex, as I
didn’t load the subpartitions with data; nevertheless, you should see
the value in having a report of this type available.
LISTING 10.16 Example of output from
subpartition statistics report.
Date:
05/09/99
Page: 1
Time: 04:55 PM Table
Sub-Partition Statistics For system
SYSTEM
ORTEST1 database
Avg
Sub
Num Avg
Chain Row
Table Partition Partition
Rows Blocks Space
Count Length Analyzed
----------- ---------- ---------------- ----- -------- ----------
------ ------------TEST5 Q1_1997
SYS_SUBP1
0 0
0 0
0 09-may-1999 16:40
SYS_SUBP2
0 0
0 0
0 09-may-1999 16:40
SYS_SUBP3
0 0
0 0
0 09-may-1999 16:40
SYS_SUBP4
0 0
0 0
0 09-may-1999 16:40
WEEK1
0 0
0 0 0 09-may-1999 16:40
Q1_1998
SYS_SUBP17 0
0 0
0 0 09-may-1999 16:40
SYS_SUBP18 0
0 0
0 0 09-may-1999 16:40
SYS_SUBP20 0
0 0
0 0 09-may-1999 16:40
SYS_SUBP19 0
0 0
0 0 09-may-1999 16:40
Q2_1997
SYS_SUBP5 0 0
0 0
0 09-may-1999 16:40
SYS_SUBP6
0 0
0 0
0 09-may-1999 16:40
SYS_SUBP7
0 0
0 0
0 09-may-1999 16:40
SYS_SUBP8
0 0
0 0
0 09-may-1999 16:40
Q3_1997
Q3_1997_S1 0
0 0
0 0 09-may-1999 16:40
Q3_1997_S2
0 0
0 0
0 09-may-1999 16:40
Q4_1997
SYS_SUBP9
0 0
0 0
0 09-may-1999 16:40
SYS_SUBP10 0
0 0
0 0 09-may-1999 16:40
SYS_SUBP11 0
0 0
0 0 09-may-1999 16:40
SYS_SUBP12 0
0 0
0 0 09-may-1999 16:40
SYS_SUBP13 0
0 0
0 0 09-may-1999 16:40
SYS_SUBP14 0
0 0
0 0 09-may-1999 16:40
SYS_SUBP15
0 0
0 0
0 09-may-1999 16:40
SYS_SUBP16 0
0 0
0 0 09-may-1999 16:40
The DBA must pay attention to the chain count,
for if this column starts showing a 5 to 10 percent ratio against the
rows column, the subpartition needs to be rebuilt. If any subpartition
shows that it is out of balance (excessive rows when filled in
comparison to other subpartitions), then perhaps the main partition
needs to be re-split using more subpartitions.
Monitoring Nested Tables
Another table type new to Oracle8 was the
nested table, defined as a table called by reference in another table
and whose reference value appears as a column. Refer back to the table
columns report, shown in Listing 10.7_, and note the columns named “xxxx_list”(my
self-imposed naming convention): each is an example of a nested table
reference column. I suggest that for ease in recognizing a nested
table column, you impose a similar convention. Each of these
“xxxx_list” columns contains a pointer value that points to a nested
table. The DBA_NESTED_TABLES view provides a convenient place to
monitor nested tables. An example of a report run against the DBA_
NESTED_TABLES view is shown in Source 10.18.
SOURCE 10.18
Example of script to monitor nested tables.
rem
rem NAME: tab_nest.sql
rem PURPOSE: Report on Nested Tables
rem HISTORY: MRA 6/14/97 Created
rem Updated 5//8/99 to Oracle8i
rem
COLUMN owner
FORMAT a10 HEADING 'Owner'
COLUMN table_name
FORMAT a15 HEADING 'Store Table'
COLUMN table_type_owner FORMAT a10 HEADING 'Type|Owner'
COLUMN table_type_name FORMAT a15 HEADING 'Type|Name'
COLUMN parent_table_name FORMAT a25 HEADING 'Parent|Table'
COLUMN parent_table_column FORMAT a12 HEADING 'Parent|Column'
COLUMN storage_spec FORMAT
a15 HEADING 'Storage|Spec'
COLUMN return_type
FORMAT a7 HEADING 'Return|Type'
SET PAGES 58 LINES 132 VERIFY OFF FEEDBACK OFF
START title132 'Nested Tables'
BREAK ON owner
SPOOL rep_out\&db\tab_nest.lis
SELECT
owner,
table_name,
table_type_owner,
table_type_name,
parent_table_name,
parent_table_column,
LTRIM(storage_spec) storage_spec,
LTRIM(return_type) return_type
FROM
sys.dba_nested_tables
ORDER BY
owner;
SPOOL OFF
Notice in this source the use of LTRIM on the
storage_spec and return_type fields. For some reason, Oracle8i,
version 8.1.3, stores these columns left-padded. If you don’t
institute a proper naming discipline with developers and with yourself
under Oracle8, you will quickly become lost when trying to track down
various components. The output from the script in Source 10.18 is
shown in Listing 10.17.
Note: I can’t stress enough how
important a good naming convention is for using the new table types,
object types, and various new structures in Oracle8, 9i and 9i.
I have included several other table-monitoring
scripts in the script zip file on the Wiley Web site, specifically for
monitoring bound tables and tables that can’t get their next extent.
LISTING 10.17 Example of output from nested
table script.
Date:
05/09/99
Page: 1
Time: 05:47 PM
Nested Tables
SYSTEM
ORTEST1 database
Type Type
Parent Parent
Storage Return
Owner Store Table Owner
Name Table
Column Spec
Type
-------- -------------- -------- ------------- -----------------------
---------SYSTEM GALLERIES_NTAB SYSTEM
PICTURE_NT GALLERIES
CONTENTS USER_SPECIFIED LOCATOR
TELE_DBA ADDRESSESV8i TELE_DBA ADDRESS_LIST
CLIENTSV8i
ADDRESSES DEFAULT VALUE
CONTRACTSV8i
TELE_DBA CONTRACT_LIST CLIENTS_INFO_NUMBERSV8i CONTRACTS DEFAULT
VALUE
CIRCUITSV8i
TELE_DBA CIRCUIT_LIST CLIENTS_INFO_NUMBERSV8i CIRCUITS
DEFAULT VALUE
In Oracle8i, the concept of partitions having
subpartitions was introduced. Many of the commands, such as ALTER,
require the name of the subpartition if you wish to execute commands
against a subpartition. Usually, Oracle8i will automatically name the
partitions created as subpartitions unless you specifically add a
subpartition to an existing set of partitions and subpartitions. The
report in Source 10.16 demonstrates how to extricate the information
about subpartitions from the database data dictionary. An example of a
report generated by the script in Source 10.16 is shown in Listing
10.15.
SOURCE 10.16
Example of subpartition report script.
rem
rem Name: tab_subpart.sql
rem Function : Report on partitioned table structure
rem History: MRA 6/13/97 Created
rem
COLUMN table_owner NEW_VALUE owner1 NOPRINT
COLUMN table_name FORMAT a15
HEADING 'Table'
COLUMN partition_name FORMAT a15 HEADING
'Partition'
COLUMN tablespace_name FORMAT a15 HEADING
'Tablespace'
COLUMN initial_extent FORMAT 9,999 HEADING 'Initial|Extent
(K)'
COLUMN next_extent FORMAT 9,999 HEADING
'Next|Extent (K)'
COLUMN pct_increase FORMAT 999
HEADING 'PCT|Increase'
SET LINES 130
START title132 'Table Sub-Partition Files For &owner1'
BREAK ON table_owner ON table_name ON partition_name
SPOOL rep_out/&&db/tab_subpart.lis
SELECT
table_owner,
table_name,
partition_name,
subpartition_name,
tablespace_name,
logging,
initial_extent/1024 initial_extent,
next_extent/1024 next_extent,
pct_increase
FROM sys.dba_tab_subpartitions
ORDER BY table_owner,table_name,partition_name
/
SPOOL OFF
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
