BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter