复制下面的语句，用SYSDB或者有DBA权限的用户执行即可。

执行完后，就具备如下功能：

1. 任何用户执行gs，可以获取数据库上的活动会话情况
2. 任何用户执行gm1，可以获取数据库上的内存使用情况1
3. 任何用户执行gm2，可以获取数据库上的内存使用情况2
4. 任何用户执行gmmc，可以获取数据库上会话的内存使用情况
5. 任何用户执行gbuf，可以获取数据库上的BUFFER使用情况
6. 任何用户执行glock0，可以获取数据库上会话的上锁情况
7. 任何用户执行glock1，可以获取数据库上活动会话的阻塞情况
8. 任何用户执行gfuni，可以获取数据库上所有的函数索引和定义
9. 任何用户执行proc_logmnr，可以根据传入时间到目前的归档，进行日志挖掘分析
10. 任何用户执行ghint，可以获取数据库上后台配置的hint
11. 任何用户执行gsql_stat，可以获取数据中记录的执行过的SQL的资源消耗信息

CREATE OR REPLACE
        FUNCTION sysdba.base_fun_001
                (
                        F NUMBER)
                RETURN VARCHAR
        IS
        BEGIN
                IF F>60 AND F<3600 THEN
                        RETURN CAST(F/60 AS INT)||'分钟'||mod(f, 60)||'秒';
                elseif f>3600 THEN
                        return cast(f/3600 as int)||'小时'||cast(mod(f, 3600)/60 as int)||'分钟'||mod(f, 60)||'秒';
                else
                        RETURN f||'秒';
                end if;
        END;
        
create or replace
procedure sysdba.gs
as -- create or replace public synonym gs for sysdba.gs;
begin
        select
                thrd_id                                     ,
                datediff(ss, last_recv_time, sysdate) ss    ,
                *                                           ,
                cast(sf_get_session_sql(sess_id) as varchar),
                'sf_close_session('
                ||sess_id
                ||');'
        from
                v$sessions
        where
                state='ACTIVE'
        order by
                last_recv_time;
end;
/


create or replace
procedure sysdba.gm1
as -- create or replace public synonym gm1 for sysdba.gm1;
begin
        select
                COUNT(              *) over(partition by name order by 1) 类型总数            ,
                trunc(avg(total_size/1024.0/1024) over(partition by name order by 1)) 平均总 ,
                trunc(avg(data_size /1024.0/1024) over(partition by name order by 1)) 平均在用,
                trunc(org_size      /1024.0/1024) 初始                                      ,
                trunc(data_size     /1024.0/1024) 在用                                      ,
                trunc(total_size    /1024.0/1024) 总的                                      ,
                trunc(target_size   /1024.0/1024) 水位                                      ,
                                    *
        from
                v$mem_pool
        where
                regexp_like(name, 'VIRTUAL|SESS');
end;
/

create or replace
procedure sysdba.gm2
as -- create or replace public synonym gm2 for sysdba.gm2;
begin
        select
                regexp_replace(name,'[0-9]'),count(*),
                trunc(sum((org_size    /1024.0/1024))) 初始,
                trunc(sum((data_size   /1024.0/1024))) 在用,
                trunc(sum((total_size  /1024.0/1024))) 总的,
                trunc(sum((target_size /1024.0/1024))) 水位
        from
                v$mem_pool
        group by
                regexp_replace(name,'[0-9]') order by 总的 desc;
end;
/

create or replace
procedure sysdba.gmmc
as -- create or replace public synonym gmmc for sysdba.gmmc;
-- 如果运行报错的话，把 listagg(distinct name,' $ ')within  中的distinct删除即可；最近的dmserver是支持这个语法了，但是以前的dmserver不支持
begin
        select
                creator 会话的线程号  ,count(*)                      ,
                trunc(sum((org_size    /1024.0/1024))) 初始,
                trunc(sum((data_size   /1024.0/1024))) 在用,
                trunc(sum((total_size  /1024.0/1024))) 总的,
                trunc(sum((target_size /1024.0/1024))) 水位,
                (select sql_text from v$sessions where thrd_id=CREATOR) 最近执行,
                listagg(distinct name,' $ ')within group(order by name) 涉及的内存池类型
        from
                v$mem_pool
        group by
                "V$MEM_POOL".CREATOR
        having
                count(*)<=4 order by 3 desc;
end;
/

create or replace
procedure sysdba.gbuf
as -- create or replace public synonym gbuf for sysdba.gbuf;
begin
        select
                name                                      ,
                count(*)                                  ,
                avg(rat_hit) 命中率75                        ,
                trunc(sum((n_pages /1024.0/1024*page))) 大小,
                trunc(sum((free    /1024.0/1024*page))) 空闲,
                trunc(sum((n_dirty /1024.0/1024*page))) 脏页,
                trunc(sum((n_clear /1024.0/1024*page))) 零页
                -- select *
        from
                v$bufferpool
        group by
                name;
end;
/


create or replace
procedure sysdba.gLOCK0
as -- create or replace public synonym gLOCK0 for sysdba.gLOCK0;
begin
with
        temp1 as
        (
                select
                        (
                                select
                                        listagg(sch.name
                                        ||'.'
                                        ||tab.name, char(10)) within group(
                                order by
                                        tab.id)
                                from
                                        sysobjects sch,
                                        sysobjects tab
                                where
                                        tab.schid=sch.id
                                    and tab.id in
                                        (
                                                select table_id from v$lock where trx_id=f.trx_id and trx_id!=0
                                        )
                        )
                        tabs,
                        *
                from
                        v$sessions f
                where
                        trx_id in
                        (
                                select id from v$trx where status='ACTIVE'
                        )
                ORDER BY
                        LAST_RECV_TIME
        )
select
        tabs 涉及到那些表上的锁,
        sess_id 会话id  ,
        'sp_close_session('
        ||sess_id
        ||');' close会话                                   ,
        last_send_time 操作时间                              ,
        cast(sf_get_session_sql(sess_id) as varchar) 最近语句,
        state 当前是否执行                                     ,
        curr_sch 用户名                                     ,
        clnt_host                                        ,
        appname                                          ,
        clnt_ip                                          ,
        osname                                           ,
        *
from
        temp1
where
        tabs is not null
order by
        操作时间;
end;
/


create or replace
procedure sysdba.gLOCK1
as -- create or replace public synonym gLOCK0 for sysdba.gLOCK1;
begin
        select
                (
                        select blocked from v$lock where trx_id=a.trx_id and blocked=1 limit 1
                )
                是否阻塞                                ,
                (sf_get_session_sql(sess_id)) 完整的sQL,
                (
                        select
                                ins_cnt
                                ||' '
                                ||del_cnt
                                ||' '
                                ||upd_cnt idu
                        from
                                v$trx
                        where
                                id    =a.trx_id
                            and trx_id>0
                )
                更新的数据idu                                                         ,
                sysdba.base_fun_001(datediff(ss, last_recv_time, sysdate))大概执行的时间,
                *
        from
                v$sessions a
        where
                state='ACTIVE'
        ORDER BY
                DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) DESC;
end;
/


create or replace
procedure sysdba.gfuni
as -- create or replace public synonym gfuni for sysdba.gfuni;
begin
        select
        cast(dbms_metadata.get_ddl('INDEX',OBJ.NAME,sch.name) AS VARCHAR) DDL,
                DM_BIT_TEST(XTYPE, 2),
                sch.name             ,
                obj.name             ,
                *
        from
                SYSINDEXES idx,
                SYSOBJECTS sch,
                SYSOBJECTS obj
        where
                sch.id               =obj.schid
            and obj.id               =idx.id
            and DM_BIT_TEST(XTYPE, 2)=1;
end;
/

create or replace procedure sysdba.proc_logmnr(v1_  datetime) as
flag varchar default to_char(sysdate,'yyyymmddhhmiss');
begin
execute immediate 'drop table if exists '||'logmnr_'||flag||'';
execute immediate
'CREATE TABLE "SYSDBA".logmnr_'||flag||'
(
"SCN" BIGINT,
"START_SCN" BIGINT,
"COMMIT_SCN" BIGINT,
"TIMESTAMP" DATETIME(6),
"START_TIMESTAMP" DATETIME(6),
"COMMIT_TIMESTAMP" DATETIME(6),
"XIDUSN" BIGINT,
"XIDSLT" BIGINT,
"XIDSQN" BIGINT,
"XID" BINARY(8),
"PXIDUSN" BIGINT,
"PXIDSLT" BIGINT,
"PXIDSQN" BIGINT,
"PXID" BINARY(8),
"TX_NAME" VARCHAR(256),
"OPERATION" VARCHAR(32),
"OPERATION_CODE" INT,
"ROLL_BACK" INT,
"SEG_OWNER" VARCHAR(128),
"SEG_NAME" VARCHAR(256),
"TABLE_NAME" VARCHAR(128),
"SEG_TYPE" INT,
"SEG_TYPE_NAME" VARCHAR(32),
"TABLE_SPACE" VARCHAR(32),
"ROW_ID" VARCHAR(20),
"USERNAME" VARCHAR(128),
"OS_USERNAME" VARCHAR(4000),
"MACHINE_NAME" VARCHAR(4000),
"AUDIT_SESSIONID" BIGINT,
"SESSION#" BIGINT,
"SERIAL#" BIGINT,
"SESSION_INFO" VARCHAR(4000),
"THREAD#" BIGINT,
"SEQUENCE#" INT,
"RBASQN" INT,
"RBABLK" INT,
"RBABYTE" INT,
"UBAFIL" BIGINT,
"UBABLK" BIGINT,
"UBAREC" BIGINT,
"UBASQN" BIGINT,
"ABS_FILE#" INT,
"REL_FILE#" INT,
"DATA_BLK#" INT,
"DATA_OBJ#" INT,
"DATA_OBJV#" INT,
"DATA_OBJD#" INT,
"SQL_REDO" VARCHAR(4000),
"SQL_UNDO" VARCHAR(4000),
"RS_ID" VARCHAR(32),
"SSN" INT,
"CSF" INT,
"INFO" VARCHAR(32),
"STATUS" INT,
"REDO_VALUE" BIGINT,
"UNDO_VALUE" BIGINT,
"SAFE_RESUME_SCN" BIGINT,
"CSCN" BIGINT,
"OBJECT_ID" BINARY(16),
"EDITION_NAME" VARCHAR2(30),
"CLIENT_ID" VARCHAR2(64)) ;';
declare begin
dbms_logmnr.end_logmnr();
exception when others then
null;
end;

DECLARE BEGIN
FOR RS IN (select 'DBMS_LOGMNR.ADD_LOGFILE('''||NAME||''');' SQL1 from v$archived_log WHERE FIRST_TIME>cast(v1_ as datetime) ) LOOP
DECLARE BEGIN
EXECUTE IMMEDIATE RS.SQL1;
EXCEPTION WHEN OTHERS THEN
null;
END;
END LOOP;
END;

DBMS_LOGMNR.START_LOGMNR(OPTIONS=>(2048+16));

execute immediate '
insert into logmnr_'||flag||'  select * from V$LOGMNR_CONTENTS ';commit;

select 'logmnr_'||flag;
dbms_logmnr.end_logmnr();

end;
/

create or replace procedure sysdba.ghint()as 
begin
execute immediate 'create table sysdba.injecthint_back_'||to_char(sysdate,'yyyymmddHHmiss')||' as 
select ''-- creator：''||creator||'' ''||crtdate||''
'' ctor,''declare begin SF_deINJECT_HINT(''''''||name||''''''); exception when others then null;end;
/
'' re_, 
''SF_INJECT_HINT(''''''||replace(sql_text::varchar,'''''''','''''''''''')||'''''', 
''''''||hint_text::varchar||'''''', ''''''||name||'''''', '''''''',true,true);'' redo
 from sysinjecthint;';

select '-- creator：'||creator||' '||crtdate||'
' ctor,'declare begin SF_deINJECT_HINT('''||name||'''); exception when others then null;end;
/
' re_, 
'SF_INJECT_HINT('''||replace(sql_text::varchar,'''','''''')||''', 
'''||hint_text::varchar||''', '''||name||''', '''',true,true);' redo
 from sysinjecthint;
 
 end;
 /

create or replace procedure sysdba.gsql_stat() as 
begin
select /*+stat(f 10000)*/ f.START_TIME,sess_id,f.sql_id,io_wait_time,EXEC_TIME,EXEC_TIME-io_wait_time Real_time,(select listagg(sql_text)within group(order by sql_nth) from v$sqltext where sql_id=f.sql_id)
,LOGIC_READ_CNT,PHY_READ_CNT,TAB_SCAN_CNT,HASH_JOIN_CNT,BTR_SPLIT_CNT,MAX_MEM_USED,TRX_ID,THREAD_ID
N_LOGIC_READ,N_PHY_READ,AFFECTED_ROWS,HARD_PARSE_FLAG,
* from v$sql_stat_history f,v$sql_history b
where f."SESSID"=b.sess_id and f.SQL_ID=b.SQL_ID order by io_wait_time desc limit 99;
WITH
     /*+ NO_USE_CVT_VAR */
     TMP1 AS
     ( SELECT
              /*MY_SELF_GAD_FX_SQL*/
              SQL_ID SQLID,
              LISTAGG2(SQL_TEXT,'') 
              WITHIN GROUP (ORDER BY SQL_NTH ASC) AS FULLSQL
         FROM SYS.V$SQLTEXT
     GROUP BY SQL_ID
     )
SELECT B.*,
         TMP1.FULLSQL,
         C.SQL_PLAN
    FROM TMP1, --TOP 10
         (SELECT ROUND(EXEC_TIME/1000.0,3) SS,
                  SQL_TXT,
                  SQL_ID,
                  LOGIC_READ_CNT,
                  PHY_READ_CNT,
                  IO_WAIT_TIME IO_WAIT_TIME_MS,
                  MAX_MEM_USED MAX_MEM_USED_KB
             FROM V$SQL_STAT_HISTORY
         ORDER BY SS DESC) B,
         V$PLN_HISTORY C
   WHERE TMP1.SQLID=B.SQL_ID
     AND C.SQL_ID=B.SQL_ID
     AND B.SQL_TXT NOT LIKE'%MY_SELF_GAD_FX_SQL%'
ORDER BY SS DESC limit 99;
end;
/

-- wait g0
-- wait gbuf speed
-- wait gallm
-- wait gkeyini



create or replace public synonym gs for sysdba.gs;
create or replace public synonym gm1 for sysdba.gm1;
create or replace public synonym gm2 for sysdba.gm2;
create or replace public synonym gmmc for sysdba.gmmc;
create or replace public synonym gbuf for sysdba.gbuf;
create or replace public synonym gLOCK0 for sysdba.gLOCK0;
create or replace public synonym gLOCK1 for sysdba.gLOCK1;
create or replace public synonym gfuni for sysdba.gfuni;
create or replace public synonym proc_logmnr for sysdba.proc_logmnr;
create or replace public synonym ghint for sysdba.ghint;
create or replace public synonym gsql_stat for sysdba.gsql_stat;

这里补充一些常用的bash命令合集：

#1. 查看硬解析的耗时TOP情况
./disql -S SYSDBA/SYSDBA -C 'set pagesize 0' -E 'select PARSE_TIME,HARD_PARSE_CNT,PARSE_CNT from v$sql_stat_history order by HARD_PARSE_CNT desc limit 10;'