咨询微信： dbservice1234 7 x 24 在线支持！

简体中文
English
日本語

你在这里

You are here:

首页

博客

PDSERVICE的博客

Oracle Demantra数据库优化总结

Posted by PDSERVICE on 4月 06, 2017 In

如果自己搞不定可以找诗檀软件专业ORACLE 优化团队成员帮您调优!

诗檀软件专业数据库优化团队

服务热线： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

Oracle Demantra

· 版本号：v7.3.1.3

1.2 具体场景描述

Demantra Workflow在项目实施多年后，数据量庞大，运行时间超30小时，极大影响了工作效率。

需要对程序运行进行调优，历史数据清理、数据库分析重整。以达到极大降低运行时间，提升效率的目的。

2. Oracle Demantra数据库分析

2.1 索引建议收集

通过以下页面链接访问并使用Index Advisor:

http://localhost:xx/demantra/admin/indexAdvisor.jsp

2.2 查看等待事件

AWR报告Top 5 waits

2.3 查看硬件情况

数据库、系统及硬件检查

1）增加RAID阵列中的磁盘并条带化，这能极大提升I/O性能。

2）如果数据库是32位的，那么使用64位系统及数据库并加倍分配buffer pool内存。

2.4 碎片及链式行检查

The sql will allow us to assess the fragmentation, chain_cnt column as a percentage of the num_rows column and Statistics freshness which is a product of the last_analyzed column as well as the sample_size column versus total row count in the num_rows column.

SELECT * FROM user_tables ORDER BY num_rows DESC;

在执行了TABLE_REORG和REBUILD_SCHEMA后，请立即执行以下语句，以查看表整理效果

SELECT * FROM user_tables ORDER BY chain_cnt DESC;

If you have rows, especially for key tables like Sales_Data or Mdp_Matrix, whose chain_cnt column has a count greater than 0 then your block size, for that tablespace, may not be large enough. Corrective measures should be taken.

对于TABLE_REORG及REBUILD_SCHEMA具体操作说明，可以

参考：

· Reordering Columns and Rows in Oracle For Demantra Performance Improvement Prior to 7.3.1.3 (文档 ID 1085012.1)

· Oracle Demantra Shrink vs. Rebuild Schema Fragmentation/Chaining Elimination (文档 ID 1109438.1)

2.5 TABLE_REORG

v7.3.1.3 or higher

· 执行TABLE_REORG.CHECK_REORG('T');

· 查看表LOG_TABLE_REORG中的建议

before v7.3.1.3

手工运行以下查询以判断是否需要Table Reorg：

查看Cluster Factor

· 值高于0.75 – 不需要Reorg

· 值介于0.5和0.75 – 推荐Reorg

· 值低于0.5 – 强烈推荐进行Table Reorg

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';

SELECT ui.index_name,

trunc((ut.num_rows / ui.clustering_factor) /

(ut.num_rows / ut.blocks),

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');

查看SALES_DATA表Out of Ratio

· OOR值超过30%，需要考虑表Reorg

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 BYITEM_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;

2.6 REBUILD_SCHEMA

The procedure should optimally be run like this:

exec rebuild_schema('1');

The ('1') is the parameter that forces a rebuild on all objects.

· 不管是否执行成功，请使用以下语句查看执行是否有报错：

SELECT * FROM db_exception_log ORDER BY err_date DESC;

· rebuild_schema procedure在最后会调用analyze_schema procedure。但是注意调用语句为：

dynamic_ddl('BEGIN analyze_schema(100000);END;');

This means that it will only analyze tables where the stats are older than 100000 days.

为了尽快对表进行分析，需要单独跑analyze_schema procedure，默认值为7。

2.7 数据库参数检查

Optimize Worksheet Threads

· Appserver Properties Parameters, (via the appserver.properties file, in old releases or aps_params)

· query_run.per_user = 12- threadpool.query_run.per_user

· query_run.size = 60- Threadpool.query_run.size

· threadpool.query_run.per_user=16 (seems optimal to some environments and yields the fastest response time for paged worksheets)

· worksheet.full.load=1 (used when using cross tab worksheets)

· client.worksheet.calcSummaryExpressions=0

· Remove if needed the custom time levels - it may highly impact performance

· worksheet.data.comb.block_size

· PGA_Aggregate_Target 4GB

· SGA_TARGET 1/2 Sizing Estimate for RAM

· SGA_MAX_SIZE – Same as SGA_TARGET

· BUFFER_CACHE (at least 16k Buffer Cache) to support 16k block data. Large rows in larger block size reduce row chaining and improve I/O; Larger block sizes can maximize compression, minimize waste; Small rows in large block size cause contention. Choose minimum of MEMORY_TARGET, MEMORY_MAX_SIZE for 11g

· ApprovalProcessScope set to 1

Check Parallel Support

· Max number of Parallel Update Threads, - MaxUpdateThreads=5

· Default Threads = 5 (Number of DB Server CPU + 1)

DB_BLOCK_SIZE

表空间或Schema下的表DB_BLOCK_SIZE大小推荐应为16K以上。

· 检查sales_data和mdp_matrix表，若不是块大小小于16K，则考虑将表迁移到块更大的表空间中。

迁移方法有两种可选：

1） Alter table move
（以表SYSADM.RECV_LN_DISTRIB为例）

-- 1. Modify the initialization paramters to include:

ALTER SYSTEM SET db_32k_cache_size = 1024M SCOPE = BOTH;

-- 2. Create a 32K block size tablespace to accommodate the tables in question.

CREATE TABLESPACE PS32KTSPACE

DATAFILE '<PATH/TO/YOUR/FILE>/ps32ktspace01.dbf>' SIZE 500M

BLOCK SIZE 32K

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

-- 3. Move the table(s) to this newly create tablespace:

ALTER TABLE SYSADM.RECV_LN_DISTRIB MOVE TABLESPACE PS32KTSPACE;

-- 4. Rebuild all the indexes of this table

ALTER INDEX SYSADM.RECV_LN_DISTRIB.<INDEX_NAME> REBUILD;

2） Use datapump export/import

expdp username/password dumpfile=example schema=exampletables=table1,table2

impdp username/password dumpfile=example schema=exampletables=table1,tabl2

--- if you want to do it in a new tablespace

impdp username/password dumpfile=example schema=exampletables=table1,tabl2 remap_tablespace=tablespace_source:tablespace_target

16k BUFFER CACHE

db_cache_size should be set to 16k

Demantra system runs better when it has more memory in the Oracle Server Buffer Cache. Please allocate a sufficient amount of memory to the 16k buffer.

This change should decrease the number of physical I/O s that are performed and hence results in better performance.

Check Memory – Buffer Cache

We have found that the Demantra system runs better when it has more memory in the Oracle Server Buffer Cache.

Our Buffer Cache is 1,128M and our Shared Pool is 200M. Making this change should decrease the number of physical I/O that are performed.

Optimizer_index_caching

should be set between 75 to 100.

Optimizer_index_cost_adj

Setting this parameter between 25 and 50 reduces the cost to access the indexes and should reduce the database response time.

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

Should be set false.

OPTIMIZER_USE_SQL_PLAN_BASELINES

Should be set false.

db_file_multiblock_read_count

· If not use Oracle SYSTEM_STATS, then value < 8.

· If use Oracle SYSTEM_STATS, then value should be:

(Number of Physical disk blocks * SAN read-ahead) / Oracle block size

INITRANS

This Controls the initial transaction slots. A transaction slot is required for a session that needs to modify a block in an object. Tables default = 1, Indexes default = 2. Set INITRANS to 100 for the large tables and associated indexes.

_b_tree_bitmap_plans hidden parameter should be FALSE.

CURSOR_SHARING

For version before 12.2.6

· For a stand alone install, it should be FORCE.

· For not a stand alone install (EBS/ASCP is on the same server), then it should be EXACT.

For version 12.2.6 and after, it should be EXACT.

Connection Pool

Can control the DB connection pool size via AppServer.properties/ aps_params table in Demantra schema.

MaxDBConnections is set to support all the user and batch activities that are executed in parallel.

· select * from aps_params where lower(PNAME) in ('maxdbconnections'); set the MaxDBConnections to 10 x concurrent users

· MaxDBConnections, = the number of (concurrent users * threadpool.query_run.per_user) + 10 (if threadpool.query_run.per_user > 4).

For the threadpool.query_run.per_user as per Document: 470852.1 you should experiment by increasing it in chunks of 4 to determine an optimal value

· threadpool.query_run.size = 4*no.of concurrent users;

也不可随意设置过大，超出可打开连接最大数，会导致一些等待状态的会话被关闭。

SQLNET.EXPIRE_TIME

Set 5 or bigger if simultaneously too many commit and cluster waits.

Check Tablespaces, DBHInts, BATCH Parameters

select * from aps_params where pname like'%DB%';

select * from db_params where pname like'%DB%';

SELECT tablespace_name,

SUM(bytes_used / 1024 / 1024),

SUM(bytes_free / 1024 / 1024)

FROM V$temp_space_header

GROUP BY tablespace_name;

SELECT * FROM DBA_TEMP_FILES;

-- truncate the temporary tablespaces

SELECT * FROM db_params WHERE lower(pname) LIKE '%hint%';

SELECT PNAME, VALUE_NUMBER

FROM APS_PARAMS

WHERE LOWER(PNAME) LIKE '%batch%';

For DBHint:

INSERT INTO worksheet_hints

VALUES

(13979,

'parallel(BRANCH_DATA) parallel(MDP_MATRIX) parallel(T_DATES_LIST) parallel(T_COMB_LIST) parallel(T_EP_LS5) parallel(T_EP_P4) parallel(T_EP_P2)',

'parallel(BRANCH_DATA) parallel(MDP_MATRIX)

parallel(T_DATES_LIST) parallel(T_COMB_LIST) parallel(T_EP_LS5) parallel(T_EP_P4) parallel(T_EP_P2)');

-- Verify by reviewing the collaborator log or the AWR logs.

Redo Log Size

Because Demantra generates a lot of redo logging it is possible for Oracle to get bogged down switching redo log members. To prevent this problem ensure that the redo log members are at least 300 MB in size (total). It is ok to have 3 files of 100 MB each in a redo log member. 500 MB might be even better.

2.8 检查运维工作

SCHEDULED_CLEANUP_TASKS

· Runs the Analyze Schema, Drop Temp Tables, Clean Log Tables, and Rebuild Tables workflows.

· By default this procedure runs once a week, on Saturdays.

CLEAN_LOG_TABLES

· his procedure runs once a week as part of the Scheduled Cleanup Tasks workflow

· Remove old data from db_exception_log and audit_trail tables

· Parameter audit_history_length control System 12 Number of months of audit data to keep

DROP_TEMP_TABLES

· Deletes temporary database tables that are created

· By default, this procedure runs once a week as part of the Scheduled Cleanup Tasks workflow.

ANALYZE_SCHEMA

· run after the first import, and then once per week as part of the Scheduled Cleanup Tasks workflow

· run after running REBUILD_INDEXES and REBUILD_TABLES

INVALID OBJECTS

Invalid objects listed in the “invalid.txt” can be dropped like this:

-- 1. check_and_drop

EXEC check_and_drop('DYN_PROP_429%');

EXEC check_and_drop('PROCESSTEMPSALESTABLE0');

EXEC check_and_drop('V_SIMULATION_387_13292');

-- 2. build and compile

EXEC BUILD_ORDER_COMPILE

-- 3. compile all

EXEC COMPILE_ALL

RECOMPILE

对所有对象进行重新编译：

EXEC CALL_DM_BUILD_PROCEDURES;

EXEC BUILD_ORDER_COMPILE;

EXEC COMPILE_ALL;

AUDIT

· System parameter audit_history_length 12 Number of months of audit data to keep. Used by the CLEAN_LOG_TABLES procedure.

In the Update_Mode column, in the Audit_Trail table, there are three values:

o Update_Mode = 1 represents User Changes made to the worksheet by typing in new values

o Update_Mode = 2 represents changes that come in through Integration Import Interfaces

o Update_Mode = 3 represents updates made through the Business Logic Engine (BLE)

o Update_Mode = 4 represents User Changes made to the worksheet through Copy/Paste

To slow down the future growth in the number of rows in this table you might consider turning off one or both of these parameters in Business Modeler --> Parameters --> System Parameters --> System (tab) and turn off the following parameters:

o AuditBLE (represents Update_Mode = 2 updates)

o AuditIntegration represents Update_Mode = 3 updates)

· should be done during off-hours maintenance.

· Standard Procedure Clean_log_tables Is Not Cleaning Up The Audit_values Table

Check the bug and fix the issue by adding one index:

ALTER TABLE audit_values ADD(CONSTRAINT audit_values_audit_id_fk FOREIGN

KEY(audit_id) REFERENCESaudit_trail(audit_id) ON DELETE CASCADE);

3. Oracle Demantra数据库优化

3.1 TABLE_REORG Action Plan

Note:

· Oracle database scripts location has been moved from the root directory of the installation to the software installation under the following location:
\Demand Planner\Database Objects\Oracle Server\admin

· 确保使用了最新补丁包
7314232 ARU #18544718 17640575 GENERIC TABLE_REORG (7.3.1.x , 12.2.x)

The new admin directory has the following scripts:

GRANT_HTTP_TO_DEMANTRA.sql

grant_table_reorg.log

grant_table_reorg.sql

revoke_table_reorg.log

revoke_table_reorg.sql

run_table_reorg.sql

sys_grants.sql

system_revokes.sql

UPDATE_PASSWORDS.sql

MDP_MATRIX_DATA

· This is a non partitioned table.

-- Step 1:- Create tablespace for MDP_MATRIX

-- =================================================================

CREATE TABLESPACE MDP_MATRIX_DATA

DATAFILE '/my_instance/oradata/data01/MDP_MATRIX_DATA_01.dbf' SIZE5000m

LOGGING ONLINE PERMANENT BLOCKSIZE 16384

EXTENT MANAGEMENT LOCAL

AUTOALLOCATE DEFAULT NOCOMPRESS

SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLESPACE MDP_MATRIX_DATA ADD DATAFILE'/my_instance/oradata/data01/MDP_MATRIX_DATA_02.dbf' SIZE 5000m;

ALTER TABLESPACE MDP_MATRIX_DATA ADD DATAFILE'/my_instance/oradata/data01/MDP_MATRIX_DATA_03.dbf' SIZE 5000m;

-- Step 2:- Create tablespace for MDP_MATRIX INDEX's

-- =================================================================

CREATE TABLESPACE MDP_MATRIX_IDX

DATAFILE '/my_instance/oradata/data01/MDP_MATRIX_IDX_01.dbf' SIZE5000m

LOGGING ONLINE PERMANENT BLOCKSIZE 16384

EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLESPACE MDP_MATRIX_DATA ADD DATAFILE'/my_instance/oradata/data01/MDP_MATRIX_IDX_02.dbf' SIZE 5000m;

ALTER TABLESPACE MDP_MATRIX_DATA ADD DATAFILE'/my_instance/oradata/data01/MDP_MATRIX_IDX_03.dbf' SIZE 5000m;

-- Step 3:- Move the table MDP_MATRIX to MDP_MATRIX_DATA

-- =================================================================

conn demantra/<Passwd>

ALTER SESSION FORCE PARALLEL DML;

ALTER TABLE MDP_MATRIX MOVE MDP_MATRIX_DATA PARALLEL 7;

ALTER TABLE MDP_MATRIX PARALLEL 3;

-- Step 4:- Do the Row order reorg for MDP_MATRIX Table

-- as per the Primary Key

-- NOTE:- Monitor the Tablespaces Closely MDP_MATRIX_DATA/ And MDP_MATRIX_IDX

-- for this below activity.

-- =================================================================

SELECT /*+ PARALLEL(SALES_DATA,4) */

(ROUND(((SELECT /*+ PARALLEL(SALES_DATA,4) */

COUNT(*) AS CNT

FROM (SELECT /*+ PARALLEL(SALES_DATA,4) */

SALES_DATE,

ITEM_ID,

LOCATION_ID,

RELATIVE_FNO,

BLOCK_NUMBER,

ROW_NUMBER,

DATA_ROW,

(LAG(DATA_ROW)

OVER(PARTITION BY RELATIVE_FNO,

BLOCK_NUMBER ORDER BY ROW_NUMBER)) ASPREV_DATA_ROW

FROM (SELECT /*+ PARALLEL(SALES_DATA,4) */

SALES_DATE,

ITEM_ID,

LOCATION_ID,

RELATIVE_FNO,

BLOCK_NUMBER,

ROW_NUMBER,

(DENSE_RANK() OVER(PARTITION BY RELATIVE_FNO,

BLOCK_NUMBER ORDER BYSALES_DATE,

ITEM_ID,

LOCATION_ID)) AS DATA_ROW

FROM (SELECT /*+ PARALLEL(SALES_DATA,8) */

SALES_DATE,

ITEM_ID,

LOCATION_ID,

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) RELATIVE_FNO,

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)AS BLOCK_NUMBER,

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ASROW_NUMBER

FROM SALES_DATA) C) B) A

WHERE DATA_ROW != PREV_DATA_ROW

AND DATA_ROW != PREV_DATA_ROW + 1) /

(SELECT /*+ PARALLEL(SALES_DATA,4) */

COUNT(*)

FROM SALES_DATA)),

3) * 100) AS "Out Of Order Ratio %"

FROM dual;

-- Step 5:- Reorder the MDP_MATRIX Table

-- Reorder the MDP_MATRIX Table as per Primary Key

-- Script to grant_table_reorg (locate the sql and adjust your path)

-- =================================================================

SQL> @your_instance_name/oracle/sales_data/grant_table_reorg.sql

-- use both of the below in subsequent order.

-- To place the null columns ar the end of the row, use the following:

SQL> table_reorg.reorg ('DEMANTRA','MDP_MATRIX','C');

-- To reorder the rows in Primary Key (PK) order, use the following:

SQL> table_reorg.reorg ('DEMANTRA','MDP_MATRIX','R');

-- Script to revoke_table_reorg (locate the sql and adjust your path)

SQL> @your_instance_name/oracle/sales_data/revoke_table_reorg.sql

-- Step 6:- Rebuild the index MDP_MATRIX matrix and move them to

-- MDP_MATRIX_IDX Tablespace

-- =================================================================

spool INDEX_REBUILD.lst

Alter Index WS_MDPMATRIX_1_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index WS_MDPMATRIX_2_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index WS_MDPMATRIX_3_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index DIVIDER_I_X_1 rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index WS_MDPMATRIX_4_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index MATRIX_IND rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_E1_ITEM_CAT_1_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_ITEM_CAT_2_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_ITEM_CAT_3_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_ITEM_CAT_4_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_ITEM_CAT_5_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_ITEM_CAT_6_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_IT_BR_CAT_1_EP_ID_IDX rebuild Online parallel(degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_IT_BR_CAT_2_EP_ID_IDX rebuild Online parallel(degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_IT_BR_CAT_3_EP_ID_IDX rebuild Online parallel(degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_IT_BR_CAT_4_EP_ID_IDX rebuild Online parallel(degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_IT_BR_CAT_5_EP_ID_IDX rebuild Online parallel(degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_PARENT_AD_NUM_I_X rebuild Online parallel (degree7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_ACCOUNT_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_BUS_GROUP_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_CUSTOMER_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_CUST_CLASS_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_DEMAND_CLASS_I_X rebuild Online parallel (degree7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_LEGAL_ENTITY_I_X rebuild Online parallel (degree7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_OPER_UNIT_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_PROD_CAT_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_PROD_FAMILY_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_SALES_CH_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_SUPPLIER_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_TP_ZONE_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_EBS_ZONE_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_ITEM_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_10_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_1_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_2_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_3_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_4_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_5_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_6_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_7_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_8_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_I_ATT_9_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_LR1_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_LR2A_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_LR2_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_LS1_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_LS2_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_LS3_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_LS4_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_LS5_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_LS6_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_10_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_1_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index MDP_PK rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_6_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_7_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_8_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_9_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_ORGANIZATION_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_ORG_TYPE_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_P1_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_P2A1_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_P2A2_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_P2A_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_P2B_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_P3_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_P4_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index T_EP_SITE_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index MDP_MATRIX_1465_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_ITEM_CAT_7_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_2_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_3_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_4_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_L_ATT_5_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index DO_FORE_IND rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index LOC_NO rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index MDP_BRANCH_ID_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index MDP_IN rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index MDP_LOC rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index MDP_MATRIX_481_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index MDP_MATRIX_537_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index MDP_MATRIX_706_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index MDP_MATRIX_ITEM_ID_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index MM_COMP_LEAD_DATE_IDX rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index PREDICTION_STATUS_IND rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index PROP_CHANGES_IND rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_CORP_CODE_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_BR_CAT_1_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_BR_CAT_2_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_BR_CAT_3_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_BR_CAT_4_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_BR_CAT_5_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_BR_CITY_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_BR_COUNTRY_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_BR_STATE_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CAT_1_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CAT_2_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CAT_3_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CAT_4_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CAT_5_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CAT_6_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CAT_7_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CITY_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_CTRY_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_E1_CUST_STATE_I_X rebuild Online parallel (degree 7) tablespace MDP_MATRIX_IDX;

Alter Index T_EP_P2_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

Alter Index DIVIDER_I_X rebuild Online parallel (degree 7) tablespaceMDP_MATRIX_IDX;

spool off;

spool Change_parallel.lst

Alter Index WS_MDPMATRIX_1_IDX parallel 1;

Alter Index WS_MDPMATRIX_2_IDX parallel 1;

Alter Index WS_MDPMATRIX_3_IDX parallel 1;

Alter Index DIVIDER_I_X_1 parallel 1;

Alter Index WS_MDPMATRIX_4_IDX parallel 1;

Alter Index MATRIX_IND parallel 1;

Alter Index T_EP_E1_ITEM_CAT_1_I_X parallel 1;

Alter Index T_EP_E1_ITEM_CAT_2_I_X parallel 1;

Alter Index T_EP_E1_ITEM_CAT_3_I_X parallel 1;

Alter Index T_EP_E1_ITEM_CAT_4_I_X parallel 1;

Alter Index T_EP_E1_ITEM_CAT_5_I_X parallel 1;

Alter Index T_EP_E1_ITEM_CAT_6_I_X parallel 1;

Alter Index T_EP_E1_IT_BR_CAT_1_EP_ID_IDX parallel 1;

Alter Index T_EP_E1_IT_BR_CAT_2_EP_ID_IDX parallel 1;

Alter Index T_EP_E1_IT_BR_CAT_3_EP_ID_IDX parallel 1;

Alter Index T_EP_E1_IT_BR_CAT_4_EP_ID_IDX parallel 1;

Alter Index T_EP_E1_IT_BR_CAT_5_EP_ID_IDX parallel 1;

Alter Index T_EP_E1_PARENT_AD_NUM_I_X parallel 1;

Alter Index T_EP_EBS_ACCOUNT_I_X parallel 1;

Alter Index T_EP_EBS_BUS_GROUP_I_X parallel 1;

Alter Index T_EP_EBS_CUSTOMER_I_X parallel 1;

Alter Index T_EP_EBS_CUST_CLASS_I_X parallel 1;

Alter Index T_EP_EBS_DEMAND_CLASS_I_X parallel 1;

Alter Index T_EP_EBS_LEGAL_ENTITY_I_X parallel 1;

Alter Index T_EP_EBS_OPER_UNIT_I_X parallel 1;

Alter Index T_EP_EBS_PROD_CAT_I_X parallel 1;

Alter Index T_EP_EBS_PROD_FAMILY_I_X parallel 1;

Alter Index T_EP_EBS_SALES_CH_I_X parallel 1;

Alter Index T_EP_EBS_SUPPLIER_I_X parallel 1;

Alter Index T_EP_EBS_TP_ZONE_I_X parallel 1;

Alter Index T_EP_EBS_ZONE_I_X parallel 1;

Alter Index T_EP_ITEM_I_X parallel 1;

Alter Index T_EP_I_ATT_10_I_X parallel 1;

Alter Index T_EP_I_ATT_1_I_X parallel 1;

Alter Index T_EP_I_ATT_2_I_X parallel 1;

Alter Index T_EP_I_ATT_3_I_X parallel 1;

Alter Index T_EP_I_ATT_4_I_X parallel 1;

Alter Index T_EP_I_ATT_5_I_X parallel 1;

Alter Index T_EP_I_ATT_6_I_X parallel 1;

Alter Index T_EP_I_ATT_7_I_X parallel 1;

Alter Index T_EP_I_ATT_8_I_X parallel 1;

Alter Index T_EP_I_ATT_9_I_X parallel 1;

Alter Index T_EP_LR1_I_X parallel 1;

Alter Index T_EP_LR2A_I_X parallel 1;

Alter Index T_EP_LR2_I_X parallel 1;

Alter Index T_EP_LS1_I_X parallel 1;

Alter Index T_EP_LS2_I_X parallel 1;

Alter Index T_EP_LS3_I_X parallel 1;

Alter Index T_EP_LS4_I_X parallel 1;

Alter Index T_EP_LS5_I_X parallel 1;

Alter Index T_EP_LS6_I_X parallel 1;

Alter Index T_EP_L_ATT_10_I_X parallel 1;

Alter Index T_EP_L_ATT_1_I_X parallel 1;

Alter Index MDP_PK parallel 1;

Alter Index T_EP_L_ATT_6_I_X parallel 1;

Alter Index T_EP_L_ATT_7_I_X parallel 1;

Alter Index T_EP_L_ATT_8_I_X parallel 1;

Alter Index T_EP_L_ATT_9_I_X parallel 1;

Alter Index T_EP_ORGANIZATION_I_X parallel 1;

Alter Index T_EP_ORG_TYPE_I_X parallel 1;

Alter Index T_EP_P1_I_X parallel 1;

Alter Index T_EP_P2A1_I_X parallel 1;

Alter Index T_EP_P2A2_I_X parallel 1;

Alter Index T_EP_P2A_I_X parallel 1;

Alter Index T_EP_P2B_I_X parallel 1;

Alter Index T_EP_P3_I_X parallel 1;

Alter Index T_EP_P4_I_X parallel 1;

Alter Index T_EP_SITE_I_X parallel 1;

Alter Index MDP_MATRIX_1465_IDX parallel 1;

Alter Index T_EP_E1_ITEM_CAT_7_I_X parallel 1;

Alter Index T_EP_L_ATT_2_I_X parallel 1;

Alter Index T_EP_L_ATT_3_I_X parallel 1;

Alter Index T_EP_L_ATT_4_I_X parallel 1;

Alter Index T_EP_L_ATT_5_I_X parallel 1;

Alter Index DO_FORE_IND parallel 1;

Alter Index LOC_NO parallel 1;

Alter Index MDP_BRANCH_ID_IDX parallel 1;

Alter Index MDP_IN parallel 1;

Alter Index MDP_LOC parallel 1;

Alter Index MDP_MATRIX_481_IDX parallel 1;

Alter Index MDP_MATRIX_537_IDX parallel 1;

Alter Index MDP_MATRIX_706_IDX parallel 1;

Alter Index MDP_MATRIX_ITEM_ID_IDX parallel 1;

Alter Index MM_COMP_LEAD_DATE_IDX parallel 1;

Alter Index PREDICTION_STATUS_IND parallel 1;

Alter Index PROP_CHANGES_IND parallel 1;

Alter Index T_EP_CORP_CODE_I_X parallel 1;

Alter Index T_EP_E1_BR_CAT_1_I_X parallel 1;

Alter Index T_EP_E1_BR_CAT_2_I_X parallel 1;

Alter Index T_EP_E1_BR_CAT_3_I_X parallel 1;

Alter Index T_EP_E1_BR_CAT_4_I_X parallel 1;

Alter Index T_EP_E1_BR_CAT_5_I_X parallel 1;

Alter Index T_EP_E1_BR_CITY_I_X parallel 1;

Alter Index T_EP_E1_BR_COUNTRY_I_X parallel 1;

Alter Index T_EP_E1_BR_STATE_I_X parallel 1;

Alter Index T_EP_E1_CUST_CAT_1_I_X parallel 1;

Alter Index T_EP_E1_CUST_CAT_2_I_X parallel 1;

Alter Index T_EP_E1_CUST_CAT_3_I_X parallel 1;

Alter Index T_EP_E1_CUST_CAT_4_I_X parallel 1;

Alter Index T_EP_E1_CUST_CAT_5_I_X parallel 1;

Alter Index T_EP_E1_CUST_CAT_6_I_X parallel 1;

Alter Index T_EP_E1_CUST_CAT_7_I_X parallel 1;

Alter Index T_EP_E1_CUST_CITY_I_X parallel 1;

Alter Index T_EP_E1_CUST_CTRY_I_X parallel 1;

Alter Index T_EP_E1_CUST_STATE_I_X parallel 1;

Alter Index T_EP_P2_I_X parallel 1;

Alter Index DIVIDER_I_X parallel 1;

spool off;

-- Step 7:- Recompile the Invalid Objects

-- =================================================================

Run utlrp.sql / dbms_recomp -- Recompile all the objects.

Spool Data_collection_after_MDP_Tablereorg.lst

select object_type,count(1) from dba_objects where owner='DEMANTRA'group by object_type order by 2;

select object_name,object_type,owner,status from dba_objects wherestatus='INVALID';

select index_name,table_name,status from dba_indexes wheretable_name='MDP_MATRIX';

select constraint_name,table_name,status from dba_constraints wheretable_name='MDP_MATRIX';

Spool Off

-- Step 8:- Gather the Schema Stats Again

-- =================================================================

-- DROP TEMPS

execute DBMS_STATS.DELETE_TABLE_STATS(ownname => 'DEMANTRA', tabname =>'SALES_DATA');

execute DBMS_STATS.DELETE_TABLE_STATS(ownname => 'DEMANTRA', tabname =>'MDP_MATRIX');

exec dbms_stats.GATHER_SCHEMA_STATS(OWNNAME=>'DEMANTRA', estimate_percent=>30 ,DEGREE=> 10);

-- Step 9:- Check the Reorder out of ratio

-- =================================================================

SELECT /*+ PARALLEL(SALES_DATA,4) */

(ROUND(((SELECT /*+ PARALLEL(SALES_DATA,4) */

COUNT(*) AS CNT

FROM (SELECT /*+ PARALLEL(SALES_DATA,4) */

SALES_DATE,

ITEM_ID,

LOCATION_ID,

RELATIVE_FNO,

BLOCK_NUMBER,

ROW_NUMBER,

DATA_ROW,

(LAG(DATA_ROW)

OVER(PARTITION BY RELATIVE_FNO,

BLOCK_NUMBER ORDER BY ROW_NUMBER)) ASPREV_DATA_ROW

FROM (SELECT /*+ PARALLEL(SALES_DATA,4) */

SALES_DATE,

ITEM_ID,

LOCATION_ID,

RELATIVE_FNO,

BLOCK_NUMBER,

ROW_NUMBER,

(DENSE_RANK() OVER(PARTITION BY RELATIVE_FNO,

BLOCK_NUMBER ORDER BYSALES_DATE,

ITEM_ID,

LOCATION_ID)) AS DATA_ROW

FROM (SELECT /*+ PARALLEL(SALES_DATA,8) */

SALES_DATE,

ITEM_ID,

LOCATION_ID,

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) RELATIVE_FNO,

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)AS BLOCK_NUMBER,

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ASROW_NUMBER

FROM SALES_DATA) C) B) A

WHERE DATA_ROW != PREV_DATA_ROW

AND DATA_ROW != PREV_DATA_ROW + 1) /

(SELECT /*+ PARALLEL(SALES_DATA,4) */

COUNT(*)

FROM SALES_DATA)),

3) * 100) AS "Out Of Order Ratio %"

Spool Data_collection_after_MDP_column_reorg.lst

SELECT object_type, COUNT(1)

FROM dba_objects

WHERE owner = 'DEMANTRA'

GROUP BY object_type

ORDER BY 2;

SELECT object_name, object_type, owner, status

FROM dba_objects

WHERE status = 'INVALID';

SELECT index_name, table_name, status

FROM dba_indexes

WHERE table_name = 'MDP_MATRIX';

SELECT constraint_name, table_name, status

FROM dba_constraints

WHERE table_name = 'MDP_MATRIX';

Spool Off

Recommended is to perform gathering stats on the schema at 30%. Verify that the indexes have fresh stats. The PK index should be global, the remaining indexes should be local. The out of order SQL will have skewed results because of the partitions. However you should still see some performance improvement.

3.2 REBUILD_SCHEMA Action Plan

当前版本ReOrg Procedure已经整合有Rebuild 功能。

等待之后文档整理后添加。

3.2 POST REORDERING

run gather stats (, analyze table, rebuild schema, update_synonyms, make a clone; optimization..)

TABLESPACE

your block size, for that tablespace, may not be large enough. Corrective measures should be taken.

SELECT tablespace_name,

SUM(bytes_used / 1024 / 1024),

SUM(bytes_free / 1024 / 1024)

FROM V$temp_space_header

GROUP BY tablespace_name;

SELECT * FROM DBA_TEMP_FILES;

you may truncate the temporary tablespaces

gather new statistics several times from different db-users, re-organize tables

4. Oracle Demantra查询参考

4.1 CHAINNING

The SQL below can be used to determine the % fragmentation of tables in the Demantra schema.Please note: you should have run analyze schema before running this SQL to get an accurate result.

SELECT table_name,

chain_cnt,

num_rows,

(chain_cnt / num_rows) * 100 percent

FROM user_tables

WHERE chain_cnt > 0

AND num_rows > 0

ORDER BY chain_cnt DESC;

4.2 FRAGMENTATION

Performance can be compromised if rows are not in consecutive order according to the natural primary key order.

SQL below can be used to get this ratio for a given table and key columns in the sequence of the Primary Key columns.

Execute the SQL on three of your largest tables; Replace <TABLE> with the table name in question, <KEY COLUMNS> with a list of the primary key column names, in the order that they appear in the PK.

SELECT (round(((SELECT COUNT(*) AS cnt

FROM (SELECT < key columns >,

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 < key columns >,

relative_fno,

block_number,

row_number,

(dense_rank()

over(PARTITION BY relative_fno,

block_number ORDER BY < keycolumns >)) AS data_row

FROM (SELECT < key columns >,

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 < TABLE >) c) b) a

WHERE data_row != prev_data_row

AND data_row != prev_data_row + 1) /

(SELECT COUNT(*) FROM < TABLE >)),

3) * 100) AS "out of order ratio %"

FROM dual;

SQL Examples for sales_data, mdp_matrix and promotion_data:

-- for table mdp_matrix

SELECT (round(((SELECT COUNT(*) AS cnt

FROM (SELECT item_id,

location_id,

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,

relative_fno,

block_number,

row_number,

(dense_rank()

over(PARTITION BY relative_fno,

block_number ORDER BYitem_id,

location_id)) AS data_row

FROM (SELECT ITEM_ID,

location_id,

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 mdp_matrix) c) b) a

WHERE data_row != prev_data_row

AND data_row != prev_data_row + 1) /

(SELECT COUNT(*) FROM mdp_matrix)),

3) * 100) AS "out of order ratio %"

FROM dual;

-- for table sales_data

SELECT (round(((SELECT COUNT(*) AS cnt

FROM (SELECT item_id,

location_id,

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,

relative_fno,

block_number,

row_number,

(dense_rank()

over(PARTITION BY relative_fno,

block_number ORDER BYitem_id,

location_id)) AS data_row

FROM (SELECT item_id,

location_id,

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;

-- for table PROMOTION_DATA

SELECT (ROUND(((SELECT COUNT(*) AS CNT

FROM (SELECT item_id,

location_id,

sales_date,

promotion_id,

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,

promotion_id,

RELATIVE_FNO,

BLOCK_NUMBER,

ROW_NUMBER,

(DENSE_RANK()

OVER(PARTITION BY RELATIVE_FNO,

BLOCK_NUMBER ORDER BYitem_id,

location_id,

sales_date,

promotion_id)) AS DATA_ROW

FROM (SELECT item_id,

location_id,

sales_date,

promotion_id,

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 PROMOTION_DATA) C) B) A

WHERE DATA_ROW != PREV_DATA_ROW

AND DATA_ROW != PREV_DATA_ROW + 1) /

(SELECT COUNT(*) FROM PROMOTION_DATA)),

3) * 100) AS "Out Of Order Ratio %"

FROM DUAL;

你在这里

Oracle Demantra数据库优化总结