|
There are two scripts in this document. The report generated by the second script provides the ratios of various sleeps for the latches. ttitle - col name form A25 select name,gets,misses*100/decode(gets,0,1,gets) misses, NAME GETS MISSES SPINS IGETS IMISSES 这几列： ======== SET ECHO off col name form A18 trunc select a.name spin sl01 sl02 sl03 sl04 sl05 |
-- ----------------------------------------------------------------------------------------------
--
-- Script: xplan.sql
--
-- Author: Adrian Billington
--
--
-- Description: Creates a package named XPLAN as a wrapper over DBMS_XPLAN. Provides access to
-- the following DBMS_XPLAN pipelined functions:
--
-- 1. DISPLAY;
-- 2. DISPLAY_CURSOR;
-- 3. DISPLAY_AWR (optional - see Notes section for licence implications).
--
-- The XPLAN wrapper package has one purpose: to include an "order" column in the
-- plan output to show the order in which plan operations are performed. See the
-- following example for details.
--
-- Example: DBMS_XPLAN output (format BASIC):
-- ------------------------------------------------
-- | Id | Operation | Name |
-- ------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | MERGE JOIN | |
-- | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | SORT JOIN | |
-- | 5 | TABLE ACCESS FULL | EMP |
-- ------------------------------------------------
--
-- Equivalent XPLAN output (format BASIC):
-- --------------------------------------------------------
-- | Id | Order | Operation | Name |
-- --------------------------------------------------------
-- | 0 | 6 | SELECT STATEMENT | |
-- | 1 | 5 | MERGE JOIN | |
-- | 2 | 2 | TABLE ACCESS BY INDEX ROWID| DEPT |
-- | 3 | 1 | INDEX FULL SCAN | PK_DEPT |
-- | 4 | 4 | SORT JOIN | |
-- | 5 | 3 | TABLE ACCESS FULL | EMP |
-- --------------------------------------------------------
--
-- Usage: SELECT * FROM TABLE(XPLAN.DISPLAY(...));
-- SELECT * FROM TABLE(XPLAN.DISPLAY_CURSOR(...));
-- SELECT * FROM TABLE(XPLAN.DISPLAY_AWR(...));
--
-- Usage for XPLAN is exactly the same as for DBMS_XPLAN. See the DBMS_XPLAN
-- documentation for all options.
--
-- Note that the only exception to this is that XPLAN.DISPLAY does not contain
-- the FILTER_PREDS parameter available in 10.2+ versions of DBMS_XPLAN.DISPLAY
-- (this parameter enables us to limit the data being returned from an Explain
-- Plan but is of quite limited use).
--
-- See the Notes section for details on the licensing implications of using
-- XPLAN.DISPLAY_AWR.
--
-- Versions: This utility will work for all versions of 10g and upwards.
--
-- Required: 1) PLAN_TABLE of at least 10.1 format
--
-- 2) Either:
-- SELECT ANY DICTIONARY
-- Or:
-- SELECT on V$DATABASE
-- SELECT on V$SQL_PLAN
-- SELECT on V$SESSION
-- SELECT on V$MYSTAT
-- SELECT on DBA_HIST_SQL_PLAN
--
-- 3) CREATE TYPE, CREATE PROCEDURE
--
-- Notes: *** IMPORTANT: PLEASE READ ***
--
-- 1) Oracle license implications
-- ---------------------------
-- The AWR functionality of XPLAN accesses a DBA_HIST% view which means
-- that it requires an Oracle Diagnostic Pack license. The XPLAN.DISPLAY_AWR
-- pipelined function is therefore disabled by default. It can be included
-- by modifying two substitution variables at the start of the script. Please
-- ensure that you are licensed to use this feature: the author accepts
-- no responsibility for any use of this functionality in an unlicensed database.
--
-- Installation: Installation requires SQL*Plus or any IDE that supports substitution
-- variables and SQL*Plus SET commands. To install, simply run the script in
-- the target schema.
--
-- Creates: 1) XPLAN_OT object type
-- 2) XPLAN_NTT collection type
-- 3) XPLAN package
--
-- Removal: 1) DROP PACKAGE xplan;
-- 3) DROP TYPE xplan_ntt;
-- 4) DROP TYPE xplan_ot;
--
--
-- ----------------------------------------------------------------------------------------------
--
-- Define the "commenting-out" substitution variables for the AWR elements of this utility. The
-- default is commented out. To include the AWR functionality, change the variables to " " (i.e.
-- a single space).
--
SET DEFINE ON
DEFINE _awr_start = "/*"
DEFINE _awr_end = "*/"
--
-- Supporting types for the pipelined functions...
--
CREATE OR REPLACE TYPE xplan_ot AS OBJECT( plan_table_output VARCHAR2(300) );
/
CREATE OR REPLACE TYPE xplan_ntt AS TABLE OF xplan_ot;
/
--
-- Xplan package...
--
CREATE OR REPLACE PACKAGE xplan AS
FUNCTION display( p_table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
p_statement_id IN VARCHAR2 DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
FUNCTION display_cursor( p_sql_id IN VARCHAR2 DEFAULT NULL,
p_cursor_child_no IN INTEGER DEFAULT 0,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
&&_awr_start
FUNCTION display_awr( p_sql_id IN VARCHAR2,
p_plan_hash_value IN INTEGER DEFAULT NULL,
p_db_id IN INTEGER DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED;
&&_awr_end
END xplan;
/
CREATE OR REPLACE PACKAGE BODY xplan AS
TYPE ntt_order_map_binds IS TABLE OF VARCHAR2(100);
TYPE aat_order_map IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
g_map aat_order_map;
g_hdrs PLS_INTEGER;
g_len PLS_INTEGER;
g_pad VARCHAR2(300);
----------------------------------------------------------------------------
PROCEDURE reset_state IS
BEGIN
g_hdrs := 0;
g_len := 0;
g_pad := NULL;
g_map.DELETE;
END reset_state;
----------------------------------------------------------------------------
PROCEDURE build_order_map( p_sql IN VARCHAR2,
p_binds IN ntt_order_map_binds ) IS
TYPE rt_id_data IS RECORD
( id PLS_INTEGER
, ord PLS_INTEGER );
TYPE aat_id_data IS TABLE OF rt_id_data
INDEX BY PLS_INTEGER;
aa_ids aat_id_data;
v_cursor SYS_REFCURSOR;
v_sql VARCHAR2(32767);
BEGIN
-- Build SQL template...
-- ---------------------
v_sql := 'WITH sql_plan_data AS ( ' ||
p_sql || '
)
, hierarchical_sql_plan_data AS (
SELECT id
FROM sql_plan_data
START WITH id = 0
CONNECT BY PRIOR id = parent_id
ORDER SIBLINGS BY id DESC
)
SELECT id
, ROW_NUMBER() OVER (ORDER BY ROWNUM DESC) AS ord
FROM hierarchical_sql_plan_data';
-- Binds will differ according to plan type...
-- -------------------------------------------
CASE p_binds.COUNT
WHEN 0
THEN
OPEN v_cursor FOR v_sql;
WHEN 1
THEN
OPEN v_cursor FOR v_sql USING p_binds(1);
WHEN 2
THEN
OPEN v_cursor FOR v_sql USING p_binds(1),
TO_NUMBER(p_binds(2));
WHEN 3
THEN
OPEN v_cursor FOR v_sql USING p_binds(1),
TO_NUMBER(p_binds(2)),
TO_NUMBER(p_binds(3));
END CASE;
-- Fetch the ID and order data...
-- ------------------------------
FETCH v_cursor BULK COLLECT INTO aa_ids;
CLOSE v_cursor;
-- Populate the order map...
-- -------------------------
FOR i IN 1 .. aa_ids.COUNT LOOP
g_map(aa_ids(i).id) := aa_ids(i).ord;
END LOOP;
-- Use the map to determine padding needed to slot in our order column...
-- ----------------------------------------------------------------------
IF g_map.COUNT > 0 THEN
g_len := LEAST(LENGTH(g_map.LAST) + 7, 8);
g_pad := LPAD('-', g_len, '-');
END IF;
END build_order_map;
----------------------------------------------------------------------------
FUNCTION prepare_row( p_curr IN VARCHAR2,
p_next IN VARCHAR2 ) RETURN xplan_ot IS
v_id PLS_INTEGER;
v_row VARCHAR2(4000);
v_hdr VARCHAR2(64) := '%|%Id%|%Operation%|%';
BEGIN
-- Intercept the plan section to include a new column for the
-- the operation order that we mapped earlier. The plan output
-- itself will be bound by the 2nd, 3rd and 4th dashed lines.
-- We need to add in additional dashes, the order column heading
-- and the order value itself...
-- -------------------------------------------------------------
IF p_curr LIKE '---%' THEN
IF p_next LIKE v_hdr THEN
g_hdrs := 1;
v_row := g_pad || p_curr;
ELSIF g_hdrs BETWEEN 1 AND 3 THEN
g_hdrs := g_hdrs + 1;
v_row := g_pad || p_curr;
ELSE
v_row := p_curr;
END IF;
ELSIF p_curr LIKE v_hdr THEN
v_row := REGEXP_REPLACE(
p_curr, '\|',
RPAD('|', GREATEST(g_len-7, 2)) || 'Order |',
1, 2
);
ELSIF REGEXP_LIKE(p_curr, '^\|[\* 0-9]+\|') THEN
v_id := REGEXP_SUBSTR(p_curr, '[0-9]+');
v_row := REGEXP_REPLACE(
p_curr, '\|',
'|' || LPAD(g_map(v_id), GREATEST(g_len-8, 6)) || ' |',
1, 2
);
ELSE
v_row := p_curr;
END IF;
RETURN xplan_ot(v_row);
END prepare_row;
----------------------------------------------------------------------------
FUNCTION display( p_table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
p_statement_id IN VARCHAR2 DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_plan_table VARCHAR2(128) := NVL(p_table_name, 'PLAN_TABLE');
v_sql VARCHAR2(512);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Prepare the inputs for the order map...
-- ---------------------------------------
v_sql := 'SELECT id, parent_id
FROM ' || v_plan_table || '
WHERE plan_id = (SELECT MAX(plan_id)
FROM ' || v_plan_table || '
WHERE id = 0 %bind%)
ORDER BY id';
IF p_statement_id IS NULL THEN
v_sql := REPLACE(v_sql, '%bind%');
ELSE
v_sql := REPLACE(v_sql, '%bind%', 'AND statement_id = :bv_statement_id');
v_binds := ntt_order_map_binds(p_statement_id);
END IF;
-- Build the order map...
-- --------------------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY(
v_plan_table, p_statement_id, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
reset_state();
RETURN;
END display;
----------------------------------------------------------------------------
FUNCTION display_cursor( p_sql_id IN VARCHAR2 DEFAULT NULL,
p_cursor_child_no IN INTEGER DEFAULT 0,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_sql_id v$sql_plan.sql_id%TYPE;
v_child_no v$sql_plan.child_number%TYPE;
v_sql VARCHAR2(256);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Set a SQL_ID if default parameters passed...
-- --------------------------------------------
IF p_sql_id IS NULL THEN
SELECT prev_sql_id, prev_child_number
INTO v_sql_id, v_child_no
FROM v$session
WHERE sid = (SELECT m.sid FROM v$mystat m WHERE ROWNUM = 1)
AND username IS NOT NULL
AND prev_hash_value <> 0;
ELSE
v_sql_id := p_sql_id;
v_child_no := p_cursor_child_no;
END IF;
-- Prepare the inputs for the order mapping...
-- -------------------------------------------
v_sql := 'SELECT id, parent_id
FROM v$sql_plan
WHERE sql_id = :bv_sql_id
AND child_number = :bv_child_no';
v_binds := ntt_order_map_binds(v_sql_id, v_child_no);
-- Build the plan order map from the SQL...
-- ----------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
v_sql_id, v_child_no, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
reset_state();
RETURN;
END display_cursor;
&_awr_start
----------------------------------------------------------------------------
FUNCTION display_awr( p_sql_id IN VARCHAR2,
p_plan_hash_value IN INTEGER DEFAULT NULL,
p_db_id IN INTEGER DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT 'TYPICAL' )
RETURN xplan_ntt PIPELINED IS
v_sql VARCHAR2(256);
v_binds ntt_order_map_binds := ntt_order_map_binds();
BEGIN
reset_state();
-- Prepare the SQL for the order mapping...
-- ----------------------------------------
v_sql := 'SELECT id, parent_id
FROM dba_hist_sql_plan
WHERE sql_id = :bv_sql_id
AND plan_hash_value = :bv_plan_hash_value
AND dbid = :bv_dbid';
-- Determine all plans for the sql_id...
-- -------------------------------------
FOR r_awr IN (SELECT DISTINCT
sql_id
, plan_hash_value
, dbid
FROM dba_hist_sql_plan
WHERE sql_id = p_sql_id
AND plan_hash_value = NVL(p_plan_hash_value, plan_hash_value)
AND dbid = NVL(p_db_id, (SELECT dbid FROM v$database))
ORDER BY
plan_hash_value)
LOOP
-- Prepare the binds and build the order map...
-- --------------------------------------------
v_binds := ntt_order_map_binds(r_awr.sql_id,
r_awr.plan_hash_value,
r_awr.dbid);
-- Build the plan order map from the SQL...
-- ----------------------------------------
build_order_map(v_sql, v_binds);
-- Now we can call DBMS_XPLAN to output the plan...
-- ------------------------------------------------
FOR r_plan IN ( SELECT plan_table_output AS p
, LEAD(plan_table_output) OVER (ORDER BY ROWNUM) AS np
FROM TABLE(
DBMS_XPLAN.DISPLAY_AWR(
r_awr.sql_id, r_awr.plan_hash_value,
r_awr.dbid, p_format
))
ORDER BY
ROWNUM)
LOOP
IF g_map.COUNT > 0 THEN
PIPE ROW (prepare_row(r_plan.p, r_plan.np));
ELSE
PIPE ROW (xplan_ot(r_plan.p));
END IF;
END LOOP;
END LOOP;
reset_state();
RETURN;
END display_awr;
&_awr_end
END xplan;
/
UNDEFINE _awr_start
UNDEFINE _awr_end
1、删除重复记录
delete from wr_recordformtable a
where rowid!=
(select max(rowid)
from wr_recordformtable t
where
t.pid=a.pid and t.tablename=a.tablename
)
2、获得视图执行的时间，精确获得时间
create table test_view (viewname varchar2(32),d integer,cn integer);
create or replace procedure ppp is
cursor c1 is
select object_name
from user_objects o
where o.object_type = ‘VIEW’ and o.status = ‘VALID’;
sqlstr varchar2(1000);
d1 timestamp;
d2 timestamp;
d integer;
num integer;
dstr varchar2(100);
begin
delete from test_view;
commit;
for rr in c1 loop
begin
d1 := systimestamp;
sqlstr := ‘select count(*) from ‘ || rr.object_name;
execute immediate sqlstr
into num;
d2 := systimestamp;
dstr := to_char((d2 – d1), ‘hh24:mi:ssx’);
dstr := substr(dstr, 9, length(dstr) – 9);
d := to_number(substr(dstr, 1, 2)) * 3600 * 1000 +
to_number(substr(dstr, 4, 2)) * 60 * 1000 +
to_number(substr(dstr, 7, 2)) * 1000 +
to_number(substr(dstr, 10, 3));
dbms_output.put_line(dstr || ‘–’ || d);
insert into test_view values (rr.object_name, d, num);
commit;
exception
when others then
dbms_output.put_line(sqlstr || ‘:’ || sqlcode || ‘:’ || sqlerrm);
end;
end loop;
3、返回rownum在4-10之间的数据
select rownum,month,sell from sale where rownum<10
minus
select rownum,month,sell from sale where rownum<5;
4、查询当前用户下所有表的记录数
select ‘select ”’||tname||”’,count(*) from ‘||tname||’;’ from tab where tabtype=’TABLE’;
5、快速编译所有视图
—-当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍，
—-因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。
SQL> SPOOL ON.SQL
SQL> SELECT’ALTER VIEW ‘||TNAME||’ COMPILE;’ FROM TAB;
SQL> SPOOL OFF
然后执行ON.SQL即可。
SQL> @ON.SQL
6、 如何删除表中的列？
alter table 表1 drop column 列1;
7、查询primary key和forgen key的关系表
select
a.owner 外键拥有者,
a.table_name 外键表,
c.column_name 外键列,
b.owner 主键拥有者,
b.table_name 主键表,
d.column_name 主键列
from
user_constraints a,
user_constraints b,
user_cons_columns c,
user_cons_columns d
where
a.r_constraint_name=b.constraint_name
and a.constraint_type=’R’
and b.constraint_type=’P’
and a.r_owner=b.owner
and a.constraint_name=c.constraint_name
and b.constraint_name=d.constraint_name
and a.owner=c.owner
and a.table_name=c.table_name
and b.owner=d.owner
and b.table_name=d.table_name
/
8、查询重复记录:
法一: 用Group by语句 此查找很快的
select count(num), max(name) from student –查找表中num列重复的，列出重复的记录数，并列出他的name属性
group by num
having count(num) >1 –按num分组后找出表中num列重复，即出现次数大于一次
delete from student(上面Select的)
这样的话就把所有重复的都删除了。—–慎重
法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:
—- 执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录
SELECT * FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D –D相当于First,Second
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
9、Oracle中如何实现某一字段自动增加1？
实现方法：
建立一个最小为1,最大为999999999的一个序列号会自动循环的序列
create sequence 序列名
increment by 1
start with 1
maxvalue 999999999
cycle;
当向表中插入数据时，SQL语句写法如下：
SQL> insert into 表名 values(序列名.nextval,列1值,列2值);
10、如何查看什么时间有哪些数据库对象结构被修改过？
SELECT OBJECT_NAME, –对象名
OBJECT_TYPE, –对象类型
TO_CHAR(CREATED, ‘YYYY-Mon-DD HH24:MI’) CREATE_TIME, –创建时间
TO_CHAR(LAST_DDL_TIME, ‘YYYY-Mon-DD HH24:MI’) MOD_TIME, –修改时间
TIMESTAMP, –时间戳
STATUS –状态
FROM USER_OBJECTS
WHERE to_char(LAST_DDL_TIME,’yyyymmdd’)>’20020101′;
11、获得一个表占的空间
select s.*,bytes/1024/1024 from user_segments s
一般blob占的空间比较大,通过如下表，可以获得LOBSEGMENT是哪个表的那个字段
select * from user_lobs
select ‘GRANT ‘||sp||’ to ‘||user from
(select s.privilege sp from USER_SYS_PRIVS s union
select r.granted_role from USER_ROLE_PRIVS r)
我的权限如下:
1 GRANT ALTER ANY INDEX to LANDUSER
2 GRANT ALTER ANY TABLE to LANDUSER
3 GRANT ALTER ANY TRIGGER to LANDUSER
4 GRANT ALTER SYSTEM to LANDUSER
5 GRANT CONNECT to LANDUSER
6 GRANT CREATE ANY SYNONYM to LANDUSER
7 GRANT CREATE ANY TABLE to LANDUSER
8 GRANT CREATE ANY TRIGGER to LANDUSER
9 GRANT CREATE ANY VIEW to LANDUSER
10 GRANT CREATE PUBLIC SYNONYM to LANDUSER
11 GRANT CREATE SEQUENCE to LANDUSER
12 GRANT DBA to LANDUSER
13 GRANT DELETE ANY TABLE to LANDUSER
14 GRANT DROP ANY PROCEDURE to LANDUSER
15 GRANT DROP ANY SYNONYM to LANDUSER
16 GRANT DROP ANY TABLE to LANDUSER
17 GRANT DROP ANY TRIGGER to LANDUSER
18 GRANT DROP ANY VIEW to LANDUSER
19 GRANT DROP PUBLIC SYNONYM to LANDUSER
20 GRANT EXECUTE ANY PROCEDURE to LANDUSER
21 GRANT EXP_FULL_DATABASE to LANDUSER
22 GRANT IMP_FULL_DATABASE to LANDUSER
23 GRANT SELECT ANY TABLE to LANDUSER
24 GRANT UNLIMITED TABLESPACE to LANDUSER
25 GRANT UPDATE ANY TABLE to LANDUSER