 |
|
Monitoring Partitioned
Tables
Oracle Tips by Burleson Consulting
|
Partitioned tables and indexes were new in
Oracle8. In Oracle8i, their functionality has been expanded to include
subpartitions and the ability to hash partitions. The Remote DBA will be
tasked with monitoring these new types of tables. Essentially, the Remote DBA
will want to know which tables are partitioned, the ranges for each
partition, and the table fraction locations for each partition. Let’s
examine a couple of reports that provide this level of information.
The first report script provides information on partition names,
partitioning value, partition tablespace location, and whether the
partition is logging or not. The script is shown in Source 10.13.
SOURCE 10.13 Script to report on partitioned
table structure.
rem
rem Name: tab_part.sql
rem Function : Report on partitioned table structure
rem History: MRA 6/13/97 Created
rem
COLUMN table_owner FORMAT a10 HEADING 'Owner'
COLUMN table_name FORMAT a15 HEADING
'Table'
COLUMN partition_name FORMAT a15 HEADING 'Partition'
COLUMN tablespace_name FORMAT a15 HEADING 'Tablespace'
COLUMN high_value FORMAT a10 HEADING 'Partition|Value'
COLUMN subpartition_count FORMAT 9,999 HEADING 'Sub-Partitions'
SET LINES 130
START title132 'Table Partition Files'
BREAK ON table_owner ON table_name
SPOOL rep_out/&&db/tab_part.lis
SELECT
table_owner,
table_name,
partition_name,
sub_partition_count,
high_value,
tablespace_name,
logging
FROM sys.Remote DBA_tab_partitions
ORDER BY table_owner,table_name
/
SPOOL OFF
The output from the script in Source 10.13 is
shown in Listing 10.12. When looking at the report in this listing,
keep in mind that the Partition Value column contains the value that
the partition values will be less than but won’t include.
LISTING 10.12 Example of output of the
partitioned table structures report.
Date:
05/09/99
Page: 1
Time: 03:42 PM Table Partition
Files
SYSTEM
ORTEST1
database
Partition
Sub
Owner Table
Partition Value
Tablespace LOGGING
Partitions
----------------------------------------------
------------------------------------------------------------------
SYSTEM TEST5 Q1_1997 TO_DATE(' USER_DATA
NONE 5
1997-04-01
00:00:00'
, 'SYYYY-M
M-DD HH24:
MI:SS', 'N
LS_CALENDA
R=GREGORIA
Q2_1997 TO_DATE('
USER_DATA NONE 4
1997-07-01
00:00:00'
, 'SYYYY-M
M-DD HH24:
MI:SS', 'N
LS_CALENDA
R=GREGORIA
Q3_1997 TO_DATE(' USER_DATA
NONE 2
1997-10-01
00:00:00'
, 'SYYYY-M
M-DD HH24:
MI:SS', 'N
LS_CALENDA
R=GREGORIA
Q4_1997 TO_DATE(' USER_DATA NONE
8
1998-01-01
00:00:00'
, 'SYYYY-M
M-DD HH24:
MI:SS', 'N
LS_CALENDA
R=GREGORIA
Q1_1998 TO_DATE('
USER_DATA NONE 4
1998-04-01
00:00:00'
, 'SYYYY-M
M-DD HH24:
MI:SS', 'N
LS_CALENDA
R=GREGORIA
The second set of data a Remote DBA will want to know
about a partition structure is its storage characteristics. The report
in Source 10.14 shows an example of a report with this type of
information. An example of output from the script in Source 10.14 is
shown in Listing 10.13.
SOURCE 10.14 Example of script to report on
partition storage characteristics.
rem
rem NAME: Tab_pstor.sql
rem FUNCTION: Provide data on part. table stor. charcacteristics
rem HISTORY: MRA 6/13/97 Created
rem
COLUMN table_owner FORMAT a6
HEADING 'Owner'
COLUMN table_name
FORMAT a14 HEADING 'Table'
COLUMN partition_name FORMAT a9
HEADING 'Partition'
COLUMN tablespace_name FORMAT a11
HEADING 'Tablespace'
COLUMN pct_free
FORMAT 9999 HEADING '%|Free'
COLUMN pct_used
FORMAT 999 HEADING '%|Use'
COLUMN ini_trans FORMAT 9999
HEADING 'Init|Tran'
COLUMN max_trans
FORMAT 9999 HEADING 'Max|Tran'
COLUMN initial_extent FORMAT 9999999
HEADING 'Init|Extent'
COLUMN next_extent FORMAT
9999999 HEADING 'Next|Extent'
COLUMN max_extent
HEADING 'Max|Extents'
COLUMN pct_increase FORMAT 999
HEADING '%|Inc'
COLUMN partition_position FORMAT 9999
HEADING 'Part|Nmbr'
SET LINES 130
START title132 'Table Partition File Storage'
BREAK ON table_owner on table_name
SPOOL rep_out/&&db/tab_pstor.lis
SELECT
table_owner,
table_name,
tablespace_name,
partition_name,
partition_position,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent,
max_extent,
pct_increase
FROM sys.Remote DBA_tab_partitions
ORDER BY table_owner,table_name
/
SPOOL OFF
LISTING 10.13 Example of partition storage
report output.
Date: 06/14/97
Page: 1
Time: 01:16 PM
Table Partition File Storage
SYSTEM
ORTEST1 database
Part % % Init Max
Init Next Max
%
Owner Table Tablespace
Partition Nmbr Free Use Tran Tran Extent
Extent Extents Inc
------ ----------- ---------- ----------- ----- ----- --- ----- ----
------- -------- ------- ---
SYSTEM PART_TEST RAW_DATA TEST_P1
1 10 90 1
255 1048576 1048576 249 0
RAW_DATA TEST_P2 2
10 90 1 255
1048576 1048576 249 0
RAW_DATA TEST_P3 3
10 90 1 255
1048576 1048576 249 0
Generally speaking, the storage
characteristics for your partitions should be similar, if not
identical, for a given table. Having said that, let me add that only
you know your data, and if, say, you are partitioning a sales table by
month, and your particular industry always has a slump in the summer
(for example, you sell skis), then your summer months’ partitions
would be different from those for the peak months.
Monitoring Partition Statistics
Oracle8, Oracle8i. and Oracle9i partitions
store their analysis results in the Remote DBA_TAB_PARTITIONS view. The Remote DBA
needs to monitor partitions (and subpartitions) much as he or she
would tables for chained rows, extents, and the like. The script in
Source 10.15 shows an example of how to retrieve the statistics data
in Remote DBA_TAB_PARTITIONS. The report generated by the table partition
statistic script is shown in Listing 10.14.
SOURCE 10.15 Example of partitions statistic
report.
rem
rem Name: tab_part_stat.sql
rem Function : Report on partitioned table statistics
rem History: MRA 6/13/97 Created
rem
COLUMN table_name FORMAT a15
HEADING 'Table'
COLUMN partition_name FORMAT a15 HEADING
'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 'Which Owner to report on?:'
SET LINES 130
START title132 'Table Partition Statistics For &owner1'
BREAK ON table_owner ON table_name ON partition_name
SPOOL rep_out/&&db/tab_part_stat.lis
SELECT
table_name,
partition_name,
num_rows,
blocks,
avg_space,
chain_cnt,
avg_row_len,
to_char(last_analyzed,'dd-mon-yyyy
hh24:mi') last_analyzed
FROM
sys.Remote DBA_tab_partitions
WHERE
table_owner LIKE UPPER('%&&owner1%')
ORDER BY
table_owner,table_name
/
SPOOL OFF
CLEAR BREAKS
CLEAR COLUMNS
TTITLE OFF
UNDEF owner1
LISTING 10.14 Example of partition statistics
report.
Date:
06/14/97
Page: 1
Time: 01:16
PM Table Partition File
Storage SYSTEM
ORTEST1
database
%
Part % % Init Max Init
Next Max
Owner Table
Tablespace Partition Nmbr
Free Use Tran Tran Extent Extent
Extents
Inc
---------------------------------------------------------------------------------------------------------------------------
SYSTEM PART_TEST RAW_DATA TEST_P1 1 10 90
1 255 1048576 1048576 249
0
RAW_DATA TEST_P2 2 10 90 1
255 1048576 1048576 249
0
RAW_DATA TEST_P3 3 10 90 1
255 1048576 1048576 249
0
The Remote DBA has to pay attention to the chain
count. If this column starts showing about a 5 to 10 percent ratio
against the Num Rows column, the partition needs to be rebuilt. If any
partition shows that it is out of balance (excessive rows when filled
in comparison to other partitions), then perhaps that partition needs
to be split.
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. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
