这两天在对不同环境之间的数据进行迁移，于是用到Fndload这样的工具，发现其实还是有部分童鞋并未真正使用过，于是写下了这个文档，供大家参考。
查出低效的SQL
–查找低效sql
SELECT EXECUTIONS,
DISK_READS,
BUFFER_GETS,
ROUND((BUFFER_GETS – DISK_READS) / BUFFER_GETS, 2) Hit_radio,
ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS > 0
AND BUFFER_GETS > 0
AND (BUFFER_GETS – DISK_READS) / BUFFER_GETS < 0.8
ORDER BY 4 DESC;
–查找bad sql的方法：
select *
from (select buffer_gets, sql_text
from v$sqlarea
where buffer_gets > 500000
order by buffer_gets desc)
where rownum <= 30;
—执行次数多的SQL：
select sql_text, executions
from (select sql_text, executions from v$sqlarea order by executions desc)
where rownum < 81;
–读硬盘多的SQL：
select sql_text, disk_reads
from (select sql_text, disk_reads from v$sqlarea order by disk_reads desc)
where rownum < 21;
–排序多的SQL：
select sql_text, sorts
from (select sql_text, sorts from v$sqlarea order by sorts desc)
where rownum < 21;
–分析的次数太多，执行的次数太少，要用绑变量的方法来写sql：
set pagesize 600;
set linesize 120;
select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs"
from v$sqlarea
where executions < 5
group by substr(sql_text, 1, 80)
having count(*) > 30
order by 2;
–游标的观察：
set pages 300;
select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistiC#
and b.name = 'opened cursors current'
group by b.name;
select count(0) from v$open_cursor;
select user_name, sql_text, count(0)
from v$open_cursor
group by user_name, sql_text
having count(0) > 30;
–查看当前用户&username执行的SQL：
select sql_text
from v$sqltext_with_newlines
where (hash_value, address) in
(select sql_hash_value, sql_address
from v$session
where username = '&username')
order by address, piece;
财务系统个性化信息查询
SELECT FCR.FUNCTION_NAME,
FCR.DESCRIPTION,
FCR.TRIGGER_EVENT,
FCR.TRIGGER_OBJECT,
FCR.CONDITION,
FCR.ENABLED,
FCR.FIRE_IN_ENTER_QUERY,
FCR.RULE_TYPE,
FCR.FORM_NAME,
FCS.LEVEL_ID,
FCS.LEVEL_VALUE
FROM APPLSYS.FND_FORM_CUSTOM_RULES FCR,
APPLSYS.FND_FORM_CUSTOM_SCOPES FCS,
APPLSYS.FND_FORM_CUSTOM_ACTIONS FCA
WHERE FCR.ID = FCS.RULE_ID(+)
AND FCR.ID = FCA.RULE_ID(+);
获取员工主管信息
SELECT PP.LAST_NAME 员工姓名,
PP.PERSON_ID 员工ID,
PP.SEX 性别,
PP.EMAIL_ADDRESS 电子邮件,
PPF.LAST_NAME 主管姓名,
PPF.EMAIL_ADDRESS 主管EMAIL
FROM PER_PEOPLE_F PP, PER_ASSIGNMENTS_F PA, PER_PEOPLE_F PPF
WHERE PP.PERSON_ID = PA.PERSON_ID
AND SYSDATE BETWEEN PA.EFFECTIVE_START_DATE AND PA.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND PA.SUPERVISOR_ID = PPF.PERSON_ID
ORDER BY PP.CREATION_DATE ASC;
获取会计科目段子父级关系
––-相关的参数值请根据自己的实际情况填写
SELECT DATA_SOURCE.FLEX_VALUE_SET_NAME 值集名称,
DATA_SOURCE.FLEX_VALUE_SET_ID 值集ID,
DATA_SOURCE.FLEX_VALUE 值代码,
DATA_SOURCE.DESCRIPTION 值描述,
DATA_SOURCE.SUMMARY_FLAG 是否父级,
DATA_SOURCE.HIERARCHY_NAME 组级别,
DATA_SOURCE.RANGE_FROM 范围从,
DATA_SOURCE.RANGE_TO 范围至
FROM (SELECT FFVS.FLEX_VALUE_SET_NAME FLEX_VALUE_SET_NAME,
FFVS.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID,
FFV.FLEX_VALUE FLEX_VALUE,
FFV.SUMMARY_FLAG SUMMARY_FLAG,
FFV.DESCRIPTION DESCRIPTION,
FFHV.HIERARCHY_NAME HIERARCHY_NAME,
FFVNH.CHILD_FLEX_VALUE_LOW RANGE_FROM,
FFVNH.CHILD_FLEX_VALUE_HIGH RANGE_TO
FROM FND_FLEX_VALUES_VL FFV,
FND_FLEX_VALUE_NORM_HIERARCHY FFVNH,
FND_FLEX_VALUE_SETS FFVS,
FND_FLEX_HIERARCHIES_VL FFHV
WHERE FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE_SET_ID = FFVNH.FLEX_VALUE_SET_ID
AND FFV.FLEX_VALUE = FFVNH.PARENT_FLEX_VALUE
AND FFV.STRUCTURED_HIERARCHY_LEVEL = FFHV.HIERARCHY_ID
AND FFVS.FLEX_VALUE_SET_NAME = 'XXXX_ACCOUNT'
AND FFV.ENABLED_FLAG = 'Y'
AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
AND FFVS.FLEX_VALUE_SET_NAME IN
(SELECT B.SEGMENT_NAME
FROM APPLSYS.FND_ID_FLEX_STRUCTURES A,
APPLSYS.FND_ID_FLEX_SEGMENTS B,
APPLSYS.FND_FLEX_VALUE_SETS C,
(SELECT *
FROM APPLSYS.FND_SEGMENT_ATTRIBUTE_VALUES AB
WHERE AB.ID_FLEX_CODE = 'GL#'
AND AB.ATTRIBUTE_VALUE = 'Y'
AND AB.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL') ABB
WHERE A.ID_FLEX_CODE = B.ID_FLEX_CODE
AND A.ID_FLEX_CODE = 'GL#'
AND A.ID_FLEX_NUM = B.ID_FLEX_NUM
AND B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID
AND B.ID_FLEX_NUM = ABB.ID_FLEX_NUM(+)
AND B.APPLICATION_COLUMN_NAME =
ABB.APPLICATION_COLUMN_NAME(+)
AND A.ID_FLEX_STRUCTURE_CODE = 'XXXX_ACCOUNT_FLEXFIELD')
UNION
SELECT FFVS.FLEX_VALUE_SET_NAME FLEX_VALUE_SET_NAME,
FFVS.FLEX_VALUE_SET_ID FLEX_VALUE_SET_ID,
FFV.FLEX_VALUE FLEX_VALUE,
FFV.SUMMARY_FLAG SUMMARY_FLAG,
FFV.DESCRIPTION DESCRIPTION,
'明细组' HIERARCHY_NAME,
NULL RANGE_FROM,
NULL RANGE_TO
FROM FND_FLEX_VALUES_VL FFV, FND_FLEX_VALUE_SETS FFVS
WHERE FFV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID
AND FFVS.FLEX_VALUE_SET_NAME = 'XXXX_ACCOUNT'
AND FFV.ENABLED_FLAG = 'Y'
AND (FFV.END_DATE_ACTIVE IS NULL OR FFV.END_DATE_ACTIVE > SYSDATE)
AND FFVS.FLEX_VALUE_SET_NAME IN
(SELECT B.SEGMENT_NAME
FROM APPLSYS.FND_ID_FLEX_STRUCTURES A,
APPLSYS.FND_ID_FLEX_SEGMENTS B,
APPLSYS.FND_FLEX_VALUE_SETS C,
(SELECT *
FROM APPLSYS.FND_SEGMENT_ATTRIBUTE_VALUES AB
WHERE AB.ID_FLEX_CODE = 'GL#'
AND AB.ATTRIBUTE_VALUE = 'Y'
AND AB.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL') ABB
WHERE A.ID_FLEX_CODE = B.ID_FLEX_CODE
AND A.ID_FLEX_CODE = 'GL#'
AND A.ID_FLEX_NUM = B.ID_FLEX_NUM
AND B.FLEX_VALUE_SET_ID = C.FLEX_VALUE_SET_ID
AND B.ID_FLEX_NUM = ABB.ID_FLEX_NUM(+)
AND B.APPLICATION_COLUMN_NAME =
ABB.APPLICATION_COLUMN_NAME(+)
AND A.ID_FLEX_STRUCTURE_CODE = 'XXXX_ACCOUNT_FLEXFIELD')
AND FFV.SUMMARY_FLAG = 'N') DATA_SOURCE
ORDER BY DATA_SOURCE.FLEX_VALUE_SET_ID, DATA_SOURCE.FLEX_VALUE;