Wednesday Apr 01, 2015

New SQL to Determine Out of Order Ratio and Cluster Factor

Hello!  Please run the following for both mdp_matrix and sales_data.     Questions?  Email at Jeffery.goulette@oracle.com

1. The first SQL will need to be adjusted to accommodate your PK for sales_data and your PK for mdp_matrix.  Adjust the select and from clause to match your PK.   In the case of this sample, the PK was:

ITEM_ID
LOCATION_ID
SALES_DATE
IS_T_EP_SPF


-- ShowOOR.sql

SELECT (ROUND((
 (SELECT COUNT(*) AS CNT
 FROM
   (SELECT ITEM_ID,
     LOCATION_ID,
     SALES_DATE,
     IS_T_EP_SPF ,
     RELATIVE_FNO ,
     BLOCK_NUMBER ,
     ROW_NUMBER ,
     DATA_ROW ,
     (LAG(DATA_ROW) OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER BY ROW_NUMBER)) AS PREV_DATA_ROW
   FROM
     (SELECT ITEM_ID,
       LOCATION_ID,
       SALES_DATE,
       IS_T_EP_SPF ,
       RELATIVE_FNO ,
       BLOCK_NUMBER ,
       ROW_NUMBER ,
       (DENSE_RANK() OVER(PARTITION BY RELATIVE_FNO, BLOCK_NUMBER ORDER BY ITEM_ID,LOCATION_ID,SALES_DATE,IS_T_EP_SPF)) AS DATA_ROW
     FROM
       (SELECT ITEM_ID,
         LOCATION_ID,
         SALES_DATE,
         IS_T_EP_SPF ,
         DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) RELATIVE_FNO ,
         DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_NUMBER ,
         DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)   AS ROW_NUMBER
       FROM SALES_DATA
       ) C
     ) B
   ) A
 WHERE DATA_ROW != PREV_DATA_ROW
 AND DATA_ROW   != PREV_DATA_ROW + 1
 )                               /
 (SELECT COUNT(*) FROM SALES_DATA
 )),3)*100) AS "Out Of Order Ratio %"
FROM DUAL;


-- ShowCF.sql
undefine table_name
SELECT ui.index_name, us.blocks as "Table Blocks", ui.clustering_factor as "Index clustering Factor", ut.num_rows as "Table Rows"
FROM   user_indexes     ui,
       user_tables      ut,
       user_constraints uc,
       user_segments us
WHERE  ui.table_name = ut.table_name
AND    ut.table_name = uc.table_name
AND    ui.index_name = uc.index_name
AND    ut.table_name = us.segment_name
AND    us.segment_type = 'TABLE'
AND    uc.constraint_type = 'P'
AND    ut.table_name      = '&table_name';

Tuesday Feb 17, 2015

Demantra Table_Reorg. A new version has been released!

Demantra Customers, there is a new release of the table_reorg Tool.  This patch will install updates to the TABLE_REORG tool.  Demantra Version: 7.3.1.x and 12.2.x.  The minimum 7.3.1.x version is 7.3.1.3.   You can download this patch using bug 17640575 in My Oracle Support.  For additional information see MOS note:
Demantra TABLE_REORG Tool New Release with Multiple Updates! Partitions, Drop_temps and More! 7.3.1.x to 12.2.x. (Doc ID 1980408.1)

Wednesday Oct 23, 2013

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)

Hello!   There is a new document available:

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG (Doc ID 1594372.1)

Demantra Performance Clustering Factor Out of Order Ratio TABLE_REORG CHECK_REORG

The table reorganization can be setup to automatically run in version 7.3.1.5. 

In version 12.2.2 we run the TABLE_REORG.CHECK_REORG function at every appserver restart.
If the function recommends a reorg then we strongly encourage to reorg the database object. 
This is documented in the official docs.

In versions 7.3.1.3 and 7.3.1.4, the TABLE_REORG module exists and can be used.
It has two main functions that are documented in the Implementation Guide Supplement,
Release 7.3, Part No. E26760-03, chapter 4.

In short, if you are using version 7.3.1.3 or higher, you can check for the need to run a reorg by doing the following 2 steps:

1. Run TABLE_REORG.CHECK_REORG('T');
2. Check the table LOG_TABLE_REORG for recommendations

If you are on a version before 7.3.1.3, you will need to follow the instructions below to determine if you need to do a manual reorg.

How to determine if a table reorg is needed

1. It is strongly encouraged by DEV that You gather statistics on the required table.  The prefered percentage for the gather is 100%.

2. Run the following SQL to evaluate how table reorg might affect Primary Key (PK) based access:

 
SELECT ui.index_name,trunc((ut.num_rows/ui.clustering_factor)/(ut.num_rows/ut.blocks),2)
FROM user_indexes ui, user_tables ut, user_constraints uc
WHERE ui.table_name=ut.table_name
AND ut.table_name=uc.table_name
AND ui.index_name=uc.index_name
AND UC.CONSTRAINT_TYPE='P'
AND ut.table_name=upper('&enter_table_name');
 

3. Based on the result:

VALUE ABOVE 0.75 - DOES NOT REQUIRE REORG

VALUE BETWEEN 0.5 AND 0.75 - REORG IS RECOMMENDED

VALUE LOWER THAN 0.5 - IT IS HIGHLY RECOMMENDED TO REORG

About

This blog delivers the latest information regarding performance and install/upgrade. Comments welcome

Search

Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
6
7
8
9
10
11
12
14
15
16
17
18
19
20
21
23
24
25
26
27
28
29
30
31
      
Today