Oracle常用sql

Oracle常用sql

---

1. 查询执行最慢的50行sql

SELECT
	* 
FROM
	(
SELECT
	AREA.SQL_TEXT,
	AREA.SQL_FULLTEXT,
	AREA.EXECUTIONS "count",--执行次数
	ROUND( AREA.ELAPSED_TIME / 1000000, 2 ) "time", --总执行时间
	ROUND( AREA.ELAPSED_TIME / 1000000 / AREA.EXECUTIONS, 2 ) "avgTime", --平均执行时间
	AREA.COMMAND_TYPE,
	AREA.PARSING_USER_ID "userId",
	U.USERNAME "userName",
	AREA.HASH_VALUE 
FROM
	V$SQLAREA AREA
	LEFT JOIN ALL_USERS U ON AREA.PARSING_USER_ID = U.USER_ID 
WHERE
	AREA.EXECUTIONS > 0 
ORDER BY
	( AREA.ELAPSED_TIME / AREA.EXECUTIONS ) DESC ) 
WHERE
	ROWNUM <= 50;

2.用户连接情况

SELECT
	S.MACHINE,
	S.PROGRAM,
	S.USERNAME,
	COUNT( * ) 
FROM
	V$PROCESS P,
	V$SESSION S 
WHERE
	P.ADDR = S.PADDR 
	AND S.USERNAME IS NOT NULL 
GROUP BY
	S.MACHINE,
	S.PROGRAM,
	S.USERNAME 
ORDER BY
	COUNT( * ) DESC

3.连表更新或插入

-- 将源表employees_source中匹配的数据更新到目标表employees_target中，同时将源表employees_source中不匹配的数据插入到目标表employees_target中。 
MERGE INTO employees_target t USING employees_source s ON ( t.employee_id = s.employee_id ) 
WHEN MATCHED THEN
UPDATE 
	SET t.first_name = s.first_name,
	t.last_name = s.last_name,
	t.salary = s.salary 
	WHEN NOT MATCHED THEN
	INSERT ( t.employee_id, t.first_name, t.last_name, t.salary )
VALUES
	( s.employee_id, s.first_name, s.last_name, s.salary );