Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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       

                    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.
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter