2013年2月28日 星期四
Gather Schema Statistics 遇到 ORA-20005: object statistics are locked (stattype = ALL) 的錯誤
Error message：
+---------------------------------------------------------------------------+
In GATHER_SCHEMA_STATS , schema_name= HR percent= 100 degree = 16 internal_flag= NOBACKUP
Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=HR.HR_ALL_ORGANIZATION_UNITS
***ORA-20005: object statistics are locked (stattype = ALL)***
+---------------------------------------------------------------------------+
Solution：
確認 log 出現的 table 使用以下SQL找的出來：
select owner,table_name
from dba_tab_statistics a
where a.STATTYPE_LOCKED = 'ALL'
and owner <> 'SYS';
執行：
exec dbms_stats.unlock_table_stats('HR', 'HR_ALL_ORGANIZATION_UNITS');
2013年2月21日 星期四
User無法在workflow notification找到單子簽核
### 現象 ###
user收到信之後，會點開信件做簽核，但是user將mail kill了，所以要到workflow notification 畫面上去做簽核，但是user找不到單子
### debug 過程 ###
-- 跟user借帳號，確認真的找不到單子，非user誤動作
-- 查詢user id是否跟epmall相同 => 相同
-- 查詢workflow是否正確 => 正確，停留在發信給主管簽核的workflow
-- 直接查詢wf_notifications table找到此張notification，狀態是open and send，表示尚未簽核，並且有發信給user，但是recipient_role欄位是寄給另一位使用者，非該位主管帳號
-- 查詢帳號，發現這位主管的employee assign給2位ERP User
-- 請該主管使用這2個帳號，將未結單全部簽完，再將他不要使用的那個ERP帳號，將employee連結拿掉，保持employee and fnd_user是一對一關係
### 原因 ###
employee 和erp user是一對多的關係，改成一對一即可
### 解決方式 ###
employee 和erp user是一對多的關係，改成一對一即可
delete item information 效能不彰
### 現象 ###
執行delete item information 發現刪除一顆料需要2~3分鐘，所以做trace，發現瓶頸點是這段 SQL：
SELECT 1
FROM DUAL
WHERE :ORGANIZATION_ID =
(SELECT TO_NUMBER(PROFILE_OPTION_VALUE)
FROM FND_PROFILE_OPTIONS FPO,
FND_PROFILE_OPTION_VALUES FPOV
WHERE FPO.PROFILE_OPTION_NAME ='SP_ORGANIZATION_ID'
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.APPLICATION_ID = FPOV.APPLICATION_ID
AND FPOV. LEVEL_ID = 10001)
AND EXISTS
(SELECT NULL
FROM QP_PRICING_ATTRIBUTES
WHERE PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND PRODUCT_ATTR_VALUE IS NOT NULL
AND PRODUCT_ATTR_VALUE <> 'ALL'
AND PRODUCT_ATTR_VALUE = :ITEM_ID
UNION
SELECT NULL
FROM QP_PRICING_ATTRIBUTES
WHERE PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND PRODUCT_ATTR_VALUE IS NOT NULL
AND COMPARISON_OPERATOR_CODE = '='
AND PRICING_ATTR_VALUE_FROM = :ITEM_ID)
前往metalink尋找到Note: 1063320.1 Performance Problem When Delete Items (BMCDEL)
發現到有相同的SQL語句，建議apply patch 9273708
### 原因 ###
The issue is caused by the Advance Pricing Delete constraint query. The problem is identified in the bug Bug:9273708 . This regression bug was due to the change done as per Bug:7503062
### 解決方式 ###
1. Download and review the readme and pre-requisites for Patch:9273708
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. Confirm the following file versions:
$BOM_TOP/patch/115/import/US/bomdelc.ldt should have version 115.14.115100.44
5. Retest the issue.
6. Migrate the solution as appropriate to other environments.
### 防範措施 ###
N/A
執行delete item information 發現刪除一顆料需要2~3分鐘，所以做trace，發現瓶頸點是這段 SQL：
SELECT 1
FROM DUAL
WHERE :ORGANIZATION_ID =
(SELECT TO_NUMBER(PROFILE_OPTION_VALUE)
FROM FND_PROFILE_OPTIONS FPO,
FND_PROFILE_OPTION_VALUES FPOV
WHERE FPO.PROFILE_OPTION_NAME ='SP_ORGANIZATION_ID'
AND FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.APPLICATION_ID = FPOV.APPLICATION_ID
AND FPOV. LEVEL_ID = 10001)
AND EXISTS
(SELECT NULL
FROM QP_PRICING_ATTRIBUTES
WHERE PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND PRODUCT_ATTR_VALUE IS NOT NULL
AND PRODUCT_ATTR_VALUE <> 'ALL'
AND PRODUCT_ATTR_VALUE = :ITEM_ID
UNION
SELECT NULL
FROM QP_PRICING_ATTRIBUTES
WHERE PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
AND PRODUCT_ATTRIBUTE = 'PRICING_ATTRIBUTE1'
AND PRODUCT_ATTR_VALUE IS NOT NULL
AND COMPARISON_OPERATOR_CODE = '='
AND PRICING_ATTR_VALUE_FROM = :ITEM_ID)
前往metalink尋找到Note: 1063320.1 Performance Problem When Delete Items (BMCDEL)
發現到有相同的SQL語句，建議apply patch 9273708
### 原因 ###
The issue is caused by the Advance Pricing Delete constraint query. The problem is identified in the bug Bug:9273708 . This regression bug was due to the change done as per Bug:7503062
### 解決方式 ###
1. Download and review the readme and pre-requisites for Patch:9273708
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. Confirm the following file versions:
$BOM_TOP/patch/115/import/US/bomdelc.ldt should have version 115.14.115100.44
You can use the commands like the following:
strings -a $BOM_TOP/patch/115/import/US/bomdelc.ldt |grep '$Header'.5. Retest the issue.
6. Migrate the solution as appropriate to other environments.
### 防範措施 ###
N/A
2013年2月19日 星期二
Oracle ERP收集統計值 (效能調教相關)
OM-PERF: Performance Tuning - FAQ [ID 113573.1]
1. What is CBO?
CBO stands for Cost Based Optimization. The R11i application was written to use the CBO. It determines the most efficient way to execute a SQL statement based on statistical data stored in the data dictionaries. These stored statistics are used to compute the cost of executing the SQL. The execution plan with the lowest cost is the one chosen by the optimizer.
2. Why the switch from RBO to CBO in 11i?
The Rule Based Optimizer (RBO) uses a fixed ranking system to determine the most efficient access method when executing a SQL statement. This method is not as flexible as CBO; which takes into consideration the data distribution characteristics of the database. CBO also offers a number of enhancements which are not available with RBO.
3. Is RBO obsolete in R11i?
No, RBO still exists. By default the 11i apps will use CBO. However, by the use of hints, RBO can still be invoked.
4. How do you gather the statistics necessary for CBO?
In R11i the FND_STATS package is used to generate statistics on the database. FND_STATS is a PL/SQL wrapper around the DBMS_STATS package, which issues the ANALYZE command. However, you should not use the ANALYZE command or DBMS_STATS package directly. Doing so may result in incomplete statistics being generated.
Use the following command to gather schema statistics:
SQL> exec fnd_stats.gather_schema_statistics('ONT') --- For a specific schema
SQL> exec fnd_stats.gather_schema_statistics('ALL') --- For all schemas
You can also run the following concurrent programs from System Administrator
to generate statistics:
Analyze All Index Column Statistics
Backup Table Statistics
Gather All Column Statistics
Gather Column Statistics
Gather Schema Statistics
Gather Table Statistics
Restore Table Statistics
When you gather statistics for a table or a whole schema, it cascades down and gathers also the statistics for all indexes on each table and all columns for that particular table or schema.
5. How can you tell whether the database objects were analyzed?
Use the Verify Stats report to determine whether the current statistics are accurate. This report is a utility provided with FND_STATS, and can be run as follows:
SQL> set server output on
SQL> set long 10000
SQL> exec fnd_stats.verify_stats('ONT', 'ONT.OE_ORDER_HOLDS_ALL');
This procedure takes a schema name and/or a list of comma separated table_names and checks the stats on all these objects. It then spools out a report. Some important columns to look at are LAST_ANALYZED and NUM_ROWS.
6. When slow performance is encountered check that the profile options OM: Debug is set to 'No' and OM: Debug Level is 'null'. If these profile options are set to a value of 'yes' for OM: Debug or other than NULL for OM: Debug Level, then system performance maybe adversely affected.
--------------------------------------------------------------------------------
Reference：http://space.itpub.net/35489/viewspace-748538
Oracle10g或以上版本DB：
exec dbms_stats.gather_schema_stats(ownname => 'DFMS', ptions => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
-- -- 11g取样值比较大，可以使用samplesize,10g好像是5~20%，可以根据需要调整
method_opt => 'for all columns size auto',
-- 一般第一次收集的时候使用 'for all columns size 1' 删除所有列上的直方图，之
后可以使用 size auto , 逐渐调整，稳定后推荐使用 'for all columns size repeat'
cascade=>TRUE,
degree => 8 ) ;
EBS使用的统计信息方式(区别：规定好了哪些列需要直方图)：
Gather All Column Statistics--FND_STATS.GATHER_ALL_COLUMN_STATS()
Gather Column Statistics--FND_STATS.GATHER_COLUMN_STATS()
Gather Schema Statistics--FND_STATS.GATHER_SCHEMA_STATS()
Gather Table Statistics--FND_STATS.GATHER_TABLE_STATS()
Sample：
分析一个SCEHMA (EBS系统) :
exec fnd_stats.GATHER_TABLE_STATS('PA');
分析一个SCEHMA (一般系统) :
exec dbms_stats.gather_schema_stats(
ownname => 'HRM',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat', -- 稳定后推荐repeat
cascade =>TRUE,
degree => 12
)
1. What is CBO?
CBO stands for Cost Based Optimization. The R11i application was written to use the CBO. It determines the most efficient way to execute a SQL statement based on statistical data stored in the data dictionaries. These stored statistics are used to compute the cost of executing the SQL. The execution plan with the lowest cost is the one chosen by the optimizer.
2. Why the switch from RBO to CBO in 11i?
The Rule Based Optimizer (RBO) uses a fixed ranking system to determine the most efficient access method when executing a SQL statement. This method is not as flexible as CBO; which takes into consideration the data distribution characteristics of the database. CBO also offers a number of enhancements which are not available with RBO.
3. Is RBO obsolete in R11i?
No, RBO still exists. By default the 11i apps will use CBO. However, by the use of hints, RBO can still be invoked.
4. How do you gather the statistics necessary for CBO?
In R11i the FND_STATS package is used to generate statistics on the database. FND_STATS is a PL/SQL wrapper around the DBMS_STATS package, which issues the ANALYZE command. However, you should not use the ANALYZE command or DBMS_STATS package directly. Doing so may result in incomplete statistics being generated.
Use the following command to gather schema statistics:
SQL> exec fnd_stats.gather_schema_statistics('ONT') --- For a specific schema
SQL> exec fnd_stats.gather_schema_statistics('ALL') --- For all schemas
You can also run the following concurrent programs from System Administrator
to generate statistics:
Analyze All Index Column Statistics
Backup Table Statistics
Gather All Column Statistics
Gather Column Statistics
Gather Schema Statistics
Gather Table Statistics
Restore Table Statistics
When you gather statistics for a table or a whole schema, it cascades down and gathers also the statistics for all indexes on each table and all columns for that particular table or schema.
5. How can you tell whether the database objects were analyzed?
Use the Verify Stats report to determine whether the current statistics are accurate. This report is a utility provided with FND_STATS, and can be run as follows:
SQL> set server output on
SQL> set long 10000
SQL> exec fnd_stats.verify_stats('ONT', 'ONT.OE_ORDER_HOLDS_ALL');
This procedure takes a schema name and/or a list of comma separated table_names and checks the stats on all these objects. It then spools out a report. Some important columns to look at are LAST_ANALYZED and NUM_ROWS.
6. When slow performance is encountered check that the profile options OM: Debug is set to 'No' and OM: Debug Level is 'null'. If these profile options are set to a value of 'yes' for OM: Debug or other than NULL for OM: Debug Level, then system performance maybe adversely affected.
--------------------------------------------------------------------------------
Reference：http://space.itpub.net/35489/viewspace-748538
Oracle10g或以上版本DB：
exec dbms_stats.gather_schema_stats(ownname => 'DFMS', ptions => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
-- -- 11g取样值比较大，可以使用samplesize,10g好像是5~20%，可以根据需要调整
method_opt => 'for all columns size auto',
-- 一般第一次收集的时候使用 'for all columns size 1' 删除所有列上的直方图，之
后可以使用 size auto , 逐渐调整，稳定后推荐使用 'for all columns size repeat'
cascade=>TRUE,
degree => 8 ) ;
EBS使用的统计信息方式(区别：规定好了哪些列需要直方图)：
Gather All Column Statistics--FND_STATS.GATHER_ALL_COLUMN_STATS()
Gather Column Statistics--FND_STATS.GATHER_COLUMN_STATS()
Gather Schema Statistics--FND_STATS.GATHER_SCHEMA_STATS()
Gather Table Statistics--FND_STATS.GATHER_TABLE_STATS()
Sample：
分析一个SCEHMA (EBS系统) :
exec fnd_stats.GATHER_TABLE_STATS('PA');
分析一个SCEHMA (一般系统) :
exec dbms_stats.gather_schema_stats(
ownname => 'HRM',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat', -- 稳定后推荐repeat
cascade =>TRUE,
degree => 12
)
訂閱：
文章 (Atom)