關於一個網友最佳化問題的解決

http://www.itpub.net/thread-1074410-1-1.html
急求解答:兩個相同的資料庫的執行計劃差別怎麼那麼大，附帶執行計劃!!!
­
最近工作中遇到一個很鬱悶的問題:
­
前提:
我一個培訓環境，一個測試環境，均是AIX 5.3的作業系統，2個CPU ,6G記憶體,測試環境是1個月前透過rman方式將資料從培訓環境中匯入的
檢查確認引數檔案保持一致,表結構一致,資料量差別非常小
問題現象:
一個sql語句在兩個配置和表結構相同的資料庫中的執行計劃不同，而且差別太大,查詢結果出來後一個是5秒,一個幾分鐘
sql語句:
select *
from (
select distinct b.user_name,
a.full_name,
'N' selected,
b.department_description,
b.organization_id
from cux_user_name_v a, cux_department_user_v b
where a.user_name = b.user_name(+)
)
where
user_name in
('EAM_SETUP', 'HAN.XIANGXU', 'XU.JIANZHENG', 'HE.ZEMING', 'GAO.JIEFANG',
'MA.RENJIE', 'YOU.ZHONGQIAN', 'DONG.XUEJUN', 'QU.SHIMIN', 'SONG.YUEHAI',
'ZHANG.JINGYA', 'SHAN.QI', 'CHEN.BAOHAI', 'MU.YONG', 'LIU.XIAOYU',
'LI.ZHIFENG', 'QIAN.MAOYUE')
and organization_id = 3013[@more@]

http://www.itpub.net/thread-1074410-1-1.html
急求解答:兩個相同的資料庫的執行計劃差別怎麼那麼大，附帶執行計劃!!!
­
最近工作中遇到一個很鬱悶的問題:
­
前提:
我一個培訓環境，一個測試環境，均是AIX 5.3的作業系統，2個CPU ,6G記憶體,測試環境是1個月前透過rman方式將資料從培訓環境中匯入的
檢查確認引數檔案保持一致,表結構一致,資料量差別非常小
問題現象:
一個sql語句在兩個配置和表結構相同的資料庫中的執行計劃不同，而且差別太大,查詢結果出來後一個是5秒,一個幾分鐘
sql語句:
select *
from (
select distinct b.user_name,
a.full_name,
'N' selected,
b.department_description,
b.organization_id
from cux_user_name_v a, cux_department_user_v b
where a.user_name = b.user_name(+)
)
where
user_name in
('EAM_SETUP', 'HAN.XIANGXU', 'XU.JIANZHENG', 'HE.ZEMING', 'GAO.JIEFANG',
'MA.RENJIE', 'YOU.ZHONGQIAN', 'DONG.XUEJUN', 'QU.SHIMIN', 'SONG.YUEHAI',
'ZHANG.JINGYA', 'SHAN.QI', 'CHEN.BAOHAI', 'MU.YONG', 'LIU.XIAOYU',
'LI.ZHIFENG', 'QIAN.MAOYUE')
and organization_id = 3013
­
其中表和檢視的關係如下:
apps.cux_user_name_v --檢視
applsys.fnd_user--(表6815條記錄）
APPS.hr_employees--檢視
APPS.PER_PEOPLE_F--檢視
HR.Per_All_People_f --（表43324條記錄）
APPS.PER_ASSIGNMENTS_F --檢視
HR.PER_ALL_ASSIGNMENTS_F --（表68825條記錄）
HR.PER_ASSIGNMENT_STATUS_TYPES --（表44條記錄）
apps.cux_department_user_v --檢視
APPS.BOM_DEPT_RES_INSTANCES_EMP_V --檢視
PER_ALL_PEOPLE_F --（表43324條記錄）
BOM.BOM_RESOURCE_EMPLOYEES --（表5236條記錄）
BOM.BOM_DEPT_RES_INSTANCES --（表5400條記錄）
APPS.hr_employees --檢視
applsys.fnd_user --(表6815條記錄）
APPS.bom_departments_all_v
BOM.BOM_DEPARTMENTS --（表623條記錄）
BOM.BOM_RESOURCES --（表526條記錄）
說明:左邊的為右邊物件的父物件,舉例:apps.cux_user_name_v 由表applsys.fnd_user和檢視APPS.hr_employees構成
而APPS.hr_employees由檢視APPS.PER_PEOPLE_F,檢視APPS.PER_ASSIGNMENTS_F和表HR.PER_ASSIGNMENT_STATUS_TYPES 來構成
­
另外的說明:所有的表和索引都相等並有效
已經做過表和索引的分析
問題相當於本來這個語句在培訓環境上是正常的，過了幾天,突然發現查詢語句變的很慢了，而一個月前的同步到測試環境的資料，執行依舊很快
請各位兄弟姐妹們趕快救命撒!
附件為f5的執行計劃和 alter session set events '10046 trace name context forever,level 8' tkp後的日誌,
只是從跟蹤日誌看到培訓環境的
239668702 TABLE ACCESS FULL FND_USER 有問題，
而且查詢記錄總共是1200萬多條記錄,但是fnd_user上相關的索引確實存在,
­
地址:http://www.itpub.net/thread-1074408-1-1.html
­
­
­
dancingfire1979 上傳了這個附件：
2008-10-22 02:59
­
測試庫.JPG (107.2 KB)


2008-10-22 02:59
­
培訓庫.JPG (175.73 KB)


2008-10-22 02:59
­

其實這個問題已經處理過了， 由於這個語句最終的分析是巢狀了8個檢視,其實只用了9個表，而且檢視是經過了4層的巢狀,所以直接聯絡開發人員和模組設計部門,減少檢視的巢狀,直接採用原表的查詢方法,速度變成了2秒.
只是為什麼會出現這種情況，我今天克隆了一個資料庫，然後透過rman的方式重新建立了一個模擬環境
再次執行表分析後
BEGIN
DBMS_STATS.gather_table_stats(ownname => 'applsys',tabname => 'FND_USER');
END;
查詢速度就恢復到了5秒左右，
但是以前我曾經做過分析:
analyze table applsys.fnd_user compute statistics for all indexes
卻沒有將時間縮短，
請問這兩個分析方法有區別麼?
__________________
天行者~~~~~~~~~~~~~
­
lhdz_bj：
哦，原來問題就在這裡：
DBMS_STATS.gather_table_stats(ownname => 'applsys',tabname => 'FND_USER');
會產生表FND_USER相關的所有統計資訊，包括表和索引的。
而analyze table applsys.fnd_user compute statistics for all indexes
這個語句似乎只產生了表上所有索引的統計資訊，而沒有分析表等統計資訊。
所以，收集統計資訊最好用DBMS_STATS包，這也是ORACLE官方推薦的方法。