Oracle执行优化

目录

1.想查询相关的v$视图，报错ORA-00942: 表或视图不存在

2.查看执行计划

3.查看实际SQL的资源消耗

---

1.想查询相关的v$视图，报错ORA-00942: 表或视图不存在

解决办法：

grant select  any dictionary to 用户;    --这个权限比较大 

这个权限是最低的要求，但是可以访问到v$相关视图

grant select_catalog_role to 用户;

收回权限  

revoke select_catalog_role from 用户;
 

2.查看执行计划

方式1： 

SELECT /* 666*/ *FROM EMP
WHERE EMPNO=7499

执行查询语句，然后在v$sql中查询SQL_ID

SELECT *FROM v$sql 
WHERE sql_text LIKE '%SELECT /* 666*/ *FROM EMP WHERE EMPNO=749%';

SELECT *FROM TABLE(dbms_xplan.display_cursor('5gr6mpv3rabzd'));

方式2：

set autoo traceonly;

set autotrace on ----------------- 包含执行计划和统计信息 

set autotrace off ---------------- 不生成autotrace 报告，这是缺省模式

set autotrace on statistics -- 只显示执行统计信息（资源消耗）
set autotrace traceonly ------ 同set autotrace on，但是不显示查询输出结果 

set autot trace explain   ----只包含执行计划

3.查看实际SQL的资源消耗

查看实例参数statistics_level的值：show parameter statistics_le;

步骤:一：执行SQL

方法1：alter session set statistics_level=all

然后执行SQL，再查看资源消耗

方法2：使用/*+ gather_plan_statistics */ hint

执行SQL：

SELECT /* gather_plan_statistics*/ REQUEST_AT,ROUND(COUNT(temp.cnt1)/COUNT(temp.cnt2),2) ratio FROM (
SELECT t1.REQUEST_AT,
	CASE when (t1.STATUS<>'completed' AND u1.banned='No') 
	THEN t1.CLIENT_ID ELSE NULL
	END cnt1,
	CASE WHEN (u1.banned='No')
	THEN u1.banned ELSE NULL
	END cnt2 FROM TRIP t1,users u1
WHERE  t1.CLIENT_ID=u1.users_id
)temp 
GROUP BY temp.REQUEST_AT
ORDER BY temp.REQUEST_AT;

步骤二：查看资源消耗

方法1：查询sql_id，使用dbms_xplan.display_cursor查询资源消耗

SELECT SQL_id,child_number,last_active_time,SQL_TEXT FROM v$sql 
WHERE sql_text LIKE 'SELECT /* gather_plan_statistics*/%'
ORDER BY last_active_time

 结果：

 然后和查看执行计划类似，还是用dbms这个包

SELECT *FROM TABLE(dbms_xplan.display_cursor('392vv1uk08b8n',0,'allstats last'));

方法2：如果是上一个执行SQL刚运行完，那么不查询sqp_id，直接用null代替也可以，因为null默认查询上一个刚执行完的SQL的资源使用情况。

select * from table(dbms_xplan.display_cursor(NULL,null,'allstats last'));

说明：

alter system set statistics_level=basic; 
alter system set statistics_level=typical; 
alter system set statistics_level=all; 
or 
alter session set statistics_level=basic; 
alter session set statistics_level=typical; 
alter session set statistics_level=all; 
（session表示只在当前会话窗口有效，system是系统中有效）

所以想要看看SQL的逻辑度需要设置statistics_level=all才可以的。

statistics_level=basic： 

 statistics_level=all：

查看monitor hint

SELECT  /*+ monitor */ ID,time,people FROM (
	SELECT id ,time,people,rownum,diffday,COUNT(*) OVER(PARTITION BY temp.DIFFDAY) cntday  
	FROM (
		SELECT id ,time,people,rownum,
		TO_DATE(time,'yyyy-mm-dd') -TO_DATE('2000-01-19','yyyy-mm-dd')-rownum diffday  
		FROM stadium
		WHERE people>100
	)temp 
)temp2 
WHERE cntday>=3;

select dbms_sqltune.report_sql_monitor(sql_id=>'3hc93wz48wcy8',TYPE=>'TEXT') from dual;

 

plsql的优化参考

pl/sql性能优化技巧 - 简书

参考链接：

oracle各种执行计划优缺点_春秋小记_51CTO博客