sql语句优化.sql

oracle sql语句优化 hawk 收集

d.full_name as employee_name,
f.name as department_name,
a.user_id as user_id,
a.responsibility_id as responsibility_id,
b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME
from FND_USER_RESP_GROUPS a,
FND_RESPONSIBILITY_VL b,
fnd_user c,
hr_employees d,
per_assignments_f e,
hr_all_organization_units_tl f
where a.user_id = c.user_id
and c.employee_id = d.employee_id
and c.employee_id = e.PERSON_ID
and e.ORGANIZATION_ID = f.organization_id
and a.responsibility_id = b.RESPONSIBILITY_ID
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < e.EFFECTIVE_END_DATE
order by c.description, c.user_name, a.responsibility_id

;
关联一下fnd_application 表就可以了
select c.user_name as login_name,
d.full_name as employee_name,
f.name as department_name,
a.user_id as user_id,
a.responsibility_id as responsibility_id,
b.RESPONSIBILITY_NAME as RESPONSIBILITY_NAME,
g.application_short_name
from FND_USER_RESP_GROUPS a,
FND_RESPONSIBILITY_VL b,
fnd_user c,
hr_employees d,
per_assignments_f e,
hr_all_organization_units_tl f,
fnd_application g
where a.user_id = c.user_id
and c.employee_id = d.employee_id
and c.employee_id = e.PERSON_ID
and e.ORGANIZATION_ID = f.organization_id
and a.responsibility_id = b.RESPONSIBILITY_ID
and sysdate > e.EFFECTIVE_START_DATE
and sysdate < e.EFFECTIVE_END_DATE
and b.APPLICATION_ID = g.application_id
order by c.description, c.user_name, a.responsibility_id;

-- oracle-ebs 常用命令
1、查看ebs的patch状况的命令和方法
./patchsets.sh connect=apps/apps applptch=/applptch_11i.txt htmlout=Report.html silent=y

2、查看invalid对象： SELECT OWNER, OBJECT_TYPE, COUNT(*)
                   FROM DBA_OBJECTS
                   WHERE STATUS = 'INVALID'
                   GROUP BY OWNER, OBJECT_TYPE;
3、编译invalid对象： @utlrp.sql
4、通过form访问系统： java oracle.apps.fnd.security.AdminAppServer apps/apps                    AUTHENTICATION ON DBC=v880d_wgnprod.dbc
5、查看xml publisher 版本：$ adident Header $OA_JAVA/oracle/apps/xdo/common/MetaInfo.class$Header MetaInfo.java 115.19 2004/11/30 02:45:52 tsuzuki


--Oracle 常用技巧和脚本
如何查看ORACLE的隐含参数？  
ORACLE的显式参数，除了在INIT.ORA文件中定义的外，在svrmgrl中用"show parameter *"，可以显示。但ORACLE还有一些参数是以“_”，开头的。如我们非常熟悉的“_offline_rollback_segments”等。  
这些参数可在sys.x$ksppi表中查出。  
语句：“select ksppinm from x$ksppi where substr(ksppinm,,)=’_’; ”  
. 如何查看安装了哪些ORACLE组件？  
进入${ORACLE_HOME}/orainst/，运行./inspdver，显示安装组件和版本号。  
. 如何查看ORACLE所占用共享内存的大小？  
可用UNIX命令“ipcs”查看共享内存的起始地址、信号量、消息队列。  
在svrmgrl下，用“oradebug ipc”，可看出ORACLE占用共享内存的分段和大小。  
example:  
SVRMGR> oradebug ipc  
-------------- Shared memory --------------  
Seg Id Address Size  
 7fe000 78  
 800000 9000  
 9800000 670886  
. 如何查看当前SQL*PLUS用户的sid和serial#?  
在SQL*PLUS下，运行：  
“select sid, serial#, status from v$session  
where audsid=userenv(’sessionid’);”  
. 如何查看当前数据库的字符集？  
在SQL*PLUS下，运行：  
“select userenv(’language’) from dual;”  
或：  
“select userenv(’lang’) from dual;”  
6. 如何查看数据库中某用户，正在运行什么SQL语句？  
根据MACHINE、USERNAME或SID、SERIAL#，连接表V$SESSION和V$SQLTEXT，可查出。  
SQL*PLUS语句：  
“SELECT SQL_TEXT FROM V$SQL_TEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESS  
AND T.HASH_VALUE=S.SQL_HASH_VALUE  
AND S.MACHINE=’XXXXX’ OR USERNAME=’XXXXX’ -- 查看某主机名，或用户名  
/”  
7. 如何删除表中的重复记录？  
例句：  
DELETE  
FROM table_name a  
WHERE rowid > ( SELECT min(rowid)  
FROM table_name b  
WHERE b.pk_column_ = a.pk_column_  
and b.pk_column_ = a.pk_column_ );  
8. 手工临时强制改变服务器字符集  
以sys或system登录系统，sql*plus运行：“create database character set us7ascii;".  
有以下错误提示：  
* create database character set US7ASCII  
ERROR at line :  
ORA-00: insufficient privileges  
实际上，看v$nls_parameters，字符集已更改成功。但重启数据库后，数据库字符集又变回原来的了。  
该命令可用于临时的不同字符集服务器之间数据倒换之用。  
9. 怎样查询每个instance分配的PCM锁的数目  
用以下命令：  
select count(*) "Number of hashed PCM locks" from v$lock_element where bitand(flags,)<>0  
/  
select count(*) "Number of fine grain PCM locks" from v$lock_element  
where bitand(flags,)=0  
/  
0. 怎么判断当前正在使用何种SQL优化方式？  
用explain plan产生EXPLAIN PLAN，检查PLAN_TABLE中ID=0的POSITION列的值。  
e.g.  
select decode(nvl(position,-),-,’RBO’,,’CBO’) from plan_table where id=0  
/  
. 做EXPORT时，能否将DUMP文件分成多个？  
ORACLE8I中EXP增加了一个参数FILESIZE，可将一个文件分成多个：  
EXP SCOTT/TIGER FILE=（ORDER_.DMP,ORDER_.DMP,ORDER_.DMP) FILESIZE=G TABLES=ORDER；  
其他版本的ORACLE在UNIX下可利用管道和split分割：  
mknod pipe p  
split -b 08m pipe order & #将文件分割成，每个GB大小的，以order为前缀的文件：  
#orderaa,orderab,orderac,... 并将该进程放在后台。  
EXP SCOTT/TIGER FILE=pipe tables=order

.睁无用索引: 
　　DML 性能低下，其中最严重的原因之一是无用索引的存在。所有SQL的插入，更新和删除操作在它们需要在每一行数据被改变时修改大量索引的时候会变得更慢。许多Oracle 管理人员只要看见在一个SQL 查询的WHERE语句出现了一列的话就会为它分配索引。虽然这个方法能够让SQL运行得更快速，但是基于功能的Oracle 索引使得数据库管理人员有可能在数据表的行上过度分配索引。过度分配索引会严重影响关键Oracle 数据表的性能。 
　　在Oracle9i出现以前，没有办法确定SQL查询没有使用的索引。Oracle9i有一个工具能够让你使用ALTER INDEX命令监视索引的使用。然后你可以查找这些没有使用的索引并从数据库里删除它们。 
　　下面是一段脚本，它能够打开一个系统中所有索引的监视功能: 
　　spool run_monitor.sql 
　　select ’alter index ’||owner||’.’||index_name||’ monitoring usage;’ 
　　from dba_indexes 
　　where owner not in (’SYS’,’SYSTEM’); 
　　spool off; 
　　@run_monitor 
　　你需要等待一段时间直到在数据库上运行了足够多的SQL语句以后，然后你就可以查询新的V$OBJECT_USAGE视图。 
　　select index_name,table_name,mon,used 
　　from v$object_usage; 
　　在下面，我们可以看见V$OBJECT_USAGE有一列被称作USED，它的值是YES或者NO。它不会告诉你Oracle使用了这个索引多少次，但是这个工具对于睁没有使用的索引还是很有用的。 
　　SQL> select * from v$object_usage where rownum < 0; 
　　INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING 
　　------------------------------ ------------------------------ ---------- ---- ------------------- ------------------- 
　　ASD DIM_ACCT_ITEM_TYPE_TEMP YES NO 0//00 :0:9 
　　IDX_ACCOUNT_ACCESSORY_TARIFF ACCOUNT_ACCESSORY_TARIFF YES NO 0//00 :0:9 
　　IDX_ACCOUNT_QUOTA_LOG ACCOUNT_QUOTA_LOG YES NO 0//00 :0:9 
　　IDX_ACCOUNT_SYSTEM_PARAMETERS ACCOUNT_SYSTEM_PARAMETERS YES NO 0//00 :0:9 
　　IDX_ACCT ACCT YES NO 0//00 :0:9 
　　IDX_ACCT ACCT YES NO 0//00 ::00 
　　IDX_ACCT ACCT YES NO 0//00 ::00 
　　IDX_ACCT_BIND_DISCT ACCT_BIND_DISCT YES NO 0//00 ::00 
　　IDX_ACCT_BIND_DISCT ACCT_BIND_DISCT YES NO 0//00 ::00 
　　.查看一个很长的操作已经做了多少: 
　　v$session_longops视图可以使Oracle专家减少运行时间很长的DDL和DML语句的运行时间。例如在数据仓库环境中，即使使用并行索引创建技术，构建一个很多G字节大的索引需要耗费很多个小时。这里你就可以查询v$session_longops视图快速睁一个特定的DDL语句已经完成了多少。其实v$session_longops视图也可以用于任何运行时间很长的操作，包括运行时间很长的更新操作。 
　　下面的脚本将显示一个状态信息，说明了运行时间很长的DDL操作已经使用的时间。注意你必须从v$session中取得SID并将其插入到下面的SQL语句中: 
　　select sid,start_time,elapsed_seconds,message 
　　from v$session_longops 
　　where sid =  
　　order by start_time; 
　　这里是一个输出的例子，显示了运行时间很长的CREATE INDEX语句的运行过程。 
　　SID MESSAGE 
　　--- --------------------------------------------------------------- 
　　 Table Scan: CUST.PK_IDX: 7 out of 60 Blocks done 
　　.用set transaction 命令解决ORA-0错误 
　　在执行大事务时,有时oracle会报出如下的错误: 
　　ORA-0:snapshot too old (rollback segment too small) 
　　这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如 
　　set transaction use rollback segment roll_abc; 
　　delete from table_name where ... ; 
　　commit; 
　　提交结束后ORACLE会自动释放对 roll_abc 的指定。 
　　.删除表中重复记录 
　　方法原理: 
　　、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，　 rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 
　　、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中那些具有最大rowid的就可以了，其余全部删除。 
　　实现方法: 
　　SQL> create table a(bm char(),mc varchar(0)); 
　　Table created 
　　SQL> insert into a values(’’,’aaaa’); 
　　SQL> insert into a values(’’,’aaaa’); 
　　SQL> insert into a values(’’,’aaaa’); 
　　SQL> insert into a values(’’,’aaaa’); 
　　SQL> insert into a select * from a; 
　　 rows inserted 
　　SQL> commit; 
　　Commit complete 
　　SQL> select rowid,bm,mc from a; 
　　ROWID BM MC

--####################################################################################
-- Oracle高级技巧
--####################################################################################
1. 删除表的注意事项

　　在删除一个表中的全部数据时，须使用TRUNCATE TABLE 表名;因为用DROP TABLE，DELETE * FROM 表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次DROP，DELETE操作后，该TABLESPACE上百兆的空间就被耗光了。

　　2.having 子句的用法

　　having 子句对 group by 子句所确定的行组进行控制,having 子句条件中只允许涉及常量,聚组函数或group by 子句中的列.

　　3.外部联接"+"的用法

　　外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢

　　用外联接提高表连接的查询速度

　　在作表连接(常用于视图)时，常使用以下方法来查询数据:

　　SELECT PAY_NO, PROJECT_NAME

　　FROM A

　　WHERE A.PAY_NO NOT IN (SELECT PAY_

　　NO FROM B WHERE VALUE >=120000);

　　---- 但是若表A有10000条记录，表B有10000条记录，则要用掉30分钟才能查完，主要因为NOT IN要进行一条一条的比较，共需要10000*10000次比较后，才能得到结果。该用外联接后，可以缩短到1分左右的时间:

　　SELECT PAY_NO,PROJECT_NAME

　　FROM A,B

　　WHERE A.PAY_NO=B.PAY_NO(+)

　　AND B.PAY_NO IS NULL

　　AND B.VALUE >=12000;

　　4.set transaction 命令的用法

　　在执行大事务时,有时oracle会报出如下的错误:

　　ORA-01555:snapshot too old (rollback segment too small)

　　这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如

　　set transaction use rollback segment roll_abc;

　　delete from table_name where ...

　　commit;

　　回滚段roll_abc被指定给这个delete事务,commit命令则在事务结束之后取消了回滚段的指定.

　　5.数据库重建应注意的问题

　　在利用import进行数据库重建过程中,有些视图可能会带来问题,因为结构输入的顺序可能造成视图的输入先于它低层次表的输入,这样建立视图就会失败.要解决这一问题,可采取分两步走的方法:首先输入结构,然后输入数据.命令举例如下 (uesrname:jfcl,password:hfjf,host sting:ora1,数据文件:expdata.dmp):

　　imp jfcl/hfjf@ora1 file=empdata.dmp rows=N

　　imp jfcl/hfjf@ora1 file=empdata.dmp full=Y buffer=64000

　　commit=Y ignore=Y

　　第一条命令输入所有数据库结构,但无记录.第二次输入结构和数据,64000字节提交一次.ignore=Y选项保证第二次输入既使对象存在的情况下也能成功.

　　select a.empno from emp a where a.empno not in

　　(select empno from emp1 where job=’SALE’);