数据库

常用语句：

sql/plus

sqlplus 'amdocs/Amdocs.Jx.China.110#@ysdb1'

tnsping BSNYSDB12

并行迁移

insert /*+append nologging parallel(8)*/ into

导表

exp ls65_sid/aaaa@ysdb2 tables=ls65_sid.task_plan_detail_t file=/jftgfs01/ibasbill/v70data/task_plan_detail_t.dmp

建同义词 CREATE  SYNONYM ls6_bill.ACCT_BILL_SEQ FOR ACCT_BILL_SEQ;

show parameter process

dense_rank的用法

select INVOICE_CODE,

                INVOICE_CUR_NBR,

                INVOICE_END_NBR,

                dense_rank() over(ORDER BY INVOICE_SEQ, INVOICE_CODE, INVOICE_END_NBR) rank

          FROM INVOICE_STAFF_t

          WHERE REGION_ID = :v1

            AND STAFF_ID = :v2

            AND STATE in ('USE', 'REC');

建dblink

create  [PUBLIC] database link cashdb

  connect to abm identified by "abm"

  using '(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 134.224.22.75)(PORT = 1521))

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = cashdb)

  )

)';

select * from v$session;

select * from v$process;

select * from v$sqltext_with_newlines

select replace(syntax,'$MON$','200803') from longshine_object_syntax where  object_name

like 'BILL_INVOICE_DETAIL_$MON$_T' and owner='LS6_BILL' order by object_name ,object_seq,object_type ;

create or replace public synonym BILL_ITEM_TIER_T

  for LS6_PARA.BILL_ITEM_TIER_T;

tmadmin

./nmon

psc

类型转换select    Cast(branch_id  as  varchar(40))

时间格式　TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24')

开放语句并行执行的能力，会话级设置sql的跟踪。  一般还需要设置timed_statistics 。 对dump的跟踪文件上sql性能跟踪  alter session enable parallel dml

更改分区　　alter table payment_plan_t enable row movement

刷共享池 alter system flush shared_pool

DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，

字段允许为空

ALTER TABLE amdocs.abm_check_log MODIFY(  O_PAYMENT_STATE    NULL);

alter table amdocs.abm_check_log rename

强制索引

/*+INDEX(SEG IDX_T_RES_ALLOSEG_ALLOID)*/

1、/*+INDEX(SEG IDX_T_RES_ALLOSEG_ALLOID)*/ 这里的/*...*/中间不要有空格

2、表名要用别名，即：以上面的sql语句为例，要使用SEG，而不是T_RES_ALLOSEG

数据库表分析

--分析分区表

SELECT 'exec dbms_stats.gather_table_stats(ownname => ''' || TABLE_OWNER ||

      ''',tabname => ''' || TABLE_NAME || ''',partname => ''' ||

      A.PARTITION_NAME ||

      ''',granularity => ''PARTITION'',cascade => TRUE,degree => 4);'

  FROM DBA_TAB_PARTITIONS A

WHERE A.TABLE_OWNER  LIKE UPPER('ls6_sid%')

  AND TABLE_NAME IN upper('pricing_section_t')

  AND a.partition_name LIKE 'PART_5%'

;

--非分析分区表

SELECT 'exec dbms_stats.gather_table_stats(ownname => ''' || OWNER ||

      ''',tabname => ''' || TABLE_NAME ||''', cascade => TRUE,degree => 4);'

  FROM dba_tables A

WHERE A.OWNER  LIKE  UPPER('ls6_%')

  AND table_name=UPPER('SINGLE_PRICING_SECTION_T');

查看表是否有分析到

select NUM_ROWS,BLOCKS,AVG_SPACE,AVG_ROW_LEN from all_all_tables where owner='LS6_BILL_NC'

AND table_name='EXT_DATA_200805_T';

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('||chr(39)||OWNER||chr(39)||','||chr(39)||TABLE_NAME||chr(39)||',granularity =>'||chr(39)||'all'||chr(39)||', cascade => true,Block_Sample=>true, Method_Opt=>'||chr(39)||'FOR ALL INDEXED COLUMNS SIZE 1'||chr(39)||');' as syntax FROM ALL_TABLES

WHERE OWNER IN ('LS65_BILL_JA') and  TABLE_NAME IN ('EXT_DATA_200908_T')

ORDER BY OWNER,TABLE_NAME;

60、删除重复列的方法

(1) DELETE FROM table_name A WHERE ROWID > (

    SELECT min(rowid) FROM table_name B

    WHERE A.key_values = B.key_values);

(2) create table table2 as select distinct * from table1;

    drop table1;

    rename table2 to table1;

(3) Delete from mytable where rowid not in(

      select max(rowid) from mytable

      group by column_name );

(4) delete from mytable t1

      where  exists (select 'x' from my_table t2

                  where t2.key_value1 = t1.key_value1

                    and t2.key_value2 = t1.key_value2

                    ...

                    and t2.rowid > t1.rowid);

-----------循环操作

declare

  i NUMBER;

BEGIN

    for i in 1010..1020 loop

insert into SYSTEM_PARAMETERS_T (REGION_ID, PARA_CLASS, PARA_TYPE, PARA_NAME, PARA_FLAG, PARA_VALUE, REMARK, PARA_LEVEL)

values (i, 'ACCT_DISCT', 'GROUP_REPAT_FALG', '用户群捆绑优惠结果是否分摊到群代表号码', 'SERV_GROUP_FLAG', '1', '用户群摊分时摊分号码获取方式:1.serv_group_t代表号 0.随机', 1);

commit;

    end loop;

    commit;

end;

回闪表

CREATE TABLE amdocs.llj_bill_sys_1

AS SELECT * FROM ls6_para.bill_system_parameters_t AS OF TIMESTAMP TO_TIMESTAMP('2008-05-06 08:10:00', 'YYYY-MM-DD HH24:MI:SS') ;

--碎片整理  MOVE并且重建索引

select 'ALTER TABLE ' || a.owner || '.' || a.segment_name ||

      ' MOVE PARTITION  ' || a.partition_name || ' ;'

  from dba_segments a

where a.segment_name in

      ('OP_QUERY_ITEM_T', 'TASK_PLAN_DETAIL_T', 'ABM_SEQUENCE_T',

        'CUST_PRICE_PLAN_T', 'ACCT_T', 'ACCT_POST_ADDRESS_T', 'SERV_T',

        'BLOCK_T', 'PAYMENT_PLAN_T')

  and owner like 'LS65%';

select 'ALTER INDEX ' || a.owner || '.' || a.segment_name ||

      ' REBUILD  PARTITION ' || a.partition_name || ' TABLESPACE ' ||

      a.tablespace_name || ';'

  from dba_segments a

where a.segment_name in

      (select b.index_name

          from dba_indexes b

        where b.table_owner like 'LS65%'

          and b.table_name in

              ('OP_QUERY_ITEM_T', 'TASK_PLAN_DETAIL_T', 'ABM_SEQUENCE_T',

                'CUST_PRICE_PLAN_T', 'ACCT_T', 'ACCT_POST_ADDRESS_T',

                'SERV_T', 'BLOCK_T', 'PAYMENT_PLAN_T'))

  and owner like 'LS65%';

查看存储过程使用了哪个表

select * from  sys.source$ a  where obj# in(

select o.obj# from sys.obj$ o, sys.source$ s, sys.user$ u

where o.obj# = s.obj#  and o.owner# = u.user#

and u.name like 'AMDOCS%'

  --and o.type# in (7, 8/*, 9, 11, 12, 13, 14*/)

  )

and upper(a.source) like '%JXJF%'

;

-----替换掉所有存储过程和function里面的一些dblink 关键字 如下方法需要用oracle dba用户执行

--1）首先备份那些需要重建的存储过程和function

/*drop table amdocs.cwf_p_f ;*/

create table amdocs.cwf_p_f as

select distinct u.name user_name, o.name object_name ,decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

              11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',

              'UNDEFINED') type_name

from sys.obj$ o, sys.source$ s, sys.user$ u

where o.obj# = s.obj#  and o.owner# = u.user#

and u.name like 'LS65%' AND (s.source LIKE '%@jxjf%' OR s.source LIKE '%@JXJF%'

or s.source LIKE '%@jxys%' OR s.source LIKE '%@JXYS%'

or s.source LIKE '%@jfdb%' OR s.source LIKE '%@JFDB%'

or s.source LIKE '%@ysdb%' OR s.source LIKE '%@YSDB%')

  and o.type# in (7, 8/*, 9, 11, 12, 13, 14*/) ;

蔡文峰(蔡文峰) 10:13:48

更新的范例 ：

update sys.source$ a set source=replace(source,'@ysdb',' ') where obj# in(

select o.obj# from sys.obj$ o, sys.source$ s, sys.user$ u

where o.obj# = s.obj#  and o.owner# = u.user#

and u.name like 'LSPRE%' AND (s.source LIKE '%@ysdb%')

  and o.type# in (7, 8/*, 9, 11, 12, 13, 14*/))

  and (a.source LIKE '%@ysdb%');

SELECT org_id

            FROM ibas_sid_para.ORGANIZATION_T@to_abmdb3

          CONNECT BY PRIOR ORG_ID = PARENT_ORG_ID

          START WITH ORG_ID = 2109004000

---

latch free检查 步骤如下：

1.找到等待latch free的session的session id(sid)

select sid,event,p1,p1raw from v$session_wait;

2.根据该session id,查看该session正在执行的语句,sql为:

SELECT SQL_TEXT

FROM  V$SQL SQL, V$SESSION S

WHERE SQL.HASH_VALUE = S.SQL_HASH_VALUE

AND    SQL.ADDRESS = S.SQL_ADDRESS

AND    S.SID = &sid ;

3.再根据该session id,查看该session的等待状况.

在会话级，查询视图V$SESSION_WAIT时如果有该事件存在，那么该视图中的

P1－表示Latch地址，也就是进程正在等待的latch地址。

P2－表示Latch编号，对应于视图V$LATCHNAME中的latch#。

P3－表示为了获得该latch而尝试的次数。

sql为:

SELECT SID, EVENT, P1TEXT, P1RAW, P2TEXT, P2, SECONDS_IN_WAIT

FROM V$SESSION_WAIT

WHERE SID = &sid ;

4.如果event为latch free,则根据P2的值,查询具体的latch free类型,sql为:

SELECT LATCH#, NAME

FROM V$LATCH

WHERE LATCH# = :P2 ;

5.查看系统当前最多的LATCH FREE是哪种

SELECT latch#, name, gets, misses, sleeps

FROM  v$latch

WHERE sleeps>0

ORDER BY sleeps;

6.如果较多的LATCH FREE种类,和4中查询的符合,则根据P2,查询这个LATCH存在的子LATCH及其对应的类型

SELECT addr, latch#, gets, misses, sleeps

FROM  v$latch_children

WHERE sleeps>0       

AND    latch# = 98

ORDER BY sleeps desc ;

7.接着查询sleep较多的子LATCH都对应有哪些对象

select distinct a.owner,a.segment_name,a.segment_type

from  dba_extents a,

        ( select dbarfil,dbablk

          from  x$bh

          where hladdr in ( select addr

                              from  ( select addr

                                      from  v$latch_children

                                      order by sleeps desc

                                    )

                              where rownum < 5

                            )

        ) b

where a.RELATIVE_FNO = b.dbarfil

and  a.BLOCK_ID <= b.dbablk

and  a.block_id + a.blocks > b.dbablk ;

8.找到对应的对象后,检查运行的SQL本身,是否产生错误的执行计划,或者表/索引的分析资料有误,特别注意表或索引的

并发度不能开太高,尤其是对OLTP系统,否则容易引起大量的(SESSION ALLOCATION/PROCESS ALLOCATION)的

LATCH FREE等待.

9.附:查看v$latchholder和v$lock视图,看是否有其他对象影响该进程.

SELECT SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK

FROM V$LOCK

WHERE SID > 8 ;

SELECT *

FROM  V$LATCHHOLDER ;

select A.segment_name,A.owner,SUM(A.BYTES)/1024/1024 from dba_segments a where A.tablespace_name='LS65_BILL_CASH_DATA_200910_04'

GROUP BY segment_name,A.owner ORDER BY SUM(A.BYTES)/1024/1024 DESC;

----------剩余空间

select tablespace_name,round(sum(bytes)/1024/1024,2) "M" from dba_free_space

where tablespace_name='ITC_BSS'

group by tablespace_name;

select tablespace_name,round(sum(bytes)/1024/1024,2) "M" from dba_free_space

where tablespace_name='BACKUP_DATA'

group by tablespace_name;

LS65_BILL_CASH_DATA_200910_04

看表空间比率

select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%"

from

(select tablespace_name,round(sum(bytes)/1024/1024) totalspace

from dba_data_files

group by tablespace_name) df,

(select tablespace_name,round(sum(bytes)/1024/1024) freespace

from dba_free_space

group by tablespace_name) fs

where df.tablespace_name=fs.tablespace_name;

查看可加的表空间,linux下敲

df -g

看索引是否失效

select * from all_indexes where TABLE_OWNER='LS65_ACCT' AND

index_name='I1_RB_FIX_LOCAL_T' ;

select * from all_tables a where a.table_name like '%LLJ%';

select * from  all_objects  a where a.object_name like 'LLJ%';

    Select segment_type,owner,Segment_Name,Sum(bytes)/1024/1024 From dba_Extents

where tablespace_name like '%OPER_DATA%' and owner='OPER_GZ'

Group By segment_type, owner,Segment_Name  order by sum(bytes) desc ;

select ds.bytes/1024/1024 "M",ds.*

from dba_segments ds

where ds.tablespace_name = 'LS6_BILL_DATA' and (owner='OPER_FZ' OR owner='OPER_GZ')

AND segment_name like '%ACCT_%'

AND ds.segment_type='TABLE'

and ds.bytes/1024/1024>100

order by ds.bytes/1024/1024 desc,ds.segment_name;

--4.5左右空间

truncate table OPER_FZ.DM_ACCT_ITEM;

truncate table OPER_GZ.DM_ACCT_FUND;

--移动表要另一个表空间  非分区表 要重新建立索引

alter table OPER_FZ.DM_DAT_RENT_ALL enable row movement;

alter table OPER_FZ.DM_DAT_RENT_ALL move tablespace LS6_OPER_DATA;

alter table OPER_FZ.DM_DAT_RENT_ALL disable row movement;

--分区表移动方式

select 'alter table '|| owner ||'.'||segment_name || ' move partition  ' ||partition_name || ' tablespace  bss_double_data;'  from dba_segments where owner='LS6_SID' and segment_name='JX_ACCT_T'

加表空间

select * from dba_data_files where tablespace_name like '%LS6_BILL_DATA_04%';

alter tablespace LS6_BILL_DATA_04 add datafile '/jftgora01/LS6_BILL_DATA_04_4g_04.dbf' SIZE 4094M AUTOEXTEND OFF;

--表空间包含的对象

SELECT * FROM dba_segments

  WHERE segment_name = 'ACCT_RECEIPT_DETAIL_T'and owner='LS6_BILL_NC';

---

SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) ,

COUNT(*)

FROM DBA_UNDO_EXTENTS@to_abmdb

GROUP BY TABLESPACE_NAME, STATUS;

select BEGIN_TIME,END_TIME,MAXQUERYLEN,MAXQUERYSQLID,TUNED_UNDORETENTION from DBA_HIST_UNDOSTAT@to_abmdb order by begin_time desc;

回闪被drop的数据

select * from user_recyclebin

FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO TEST2;

---

查看锁表

select c.sid,c.serial#,c.machine,c.program,c.osuser,c.username,b.object_name

from v$locked_object a,all_objects b,v$session c

where a.object_id=b.object_id and a.session_id=c.sid

and c.program like 'TUX%';

  SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

      id1,    id2, lmode, request, type

  FROM V$LOCK

  WHERE (id1, id2, type) IN

  (SELECT id1, id2, type FROM V$LOCK WHERE request>0)

    ORDER BY id1,      request;

select * from v$session_wait ;

select * from  v$session_longops;

　select object_name,machine,s.sid,s.serial#

　　from v$locked_object l,dba_objects o ,v$session s

　　where l.object_id = o.object_id and l.session_id=s.sid;

-----杀进程

select * from v$locked_object where object_id=1129274;

select * from v$session where sid=308;

alter system kill session '367,8664' immediate;

select 'alter system kill session ''' || sid || ',' || serial# ||

      '''immediate;'

  from v$session

where sid in

      (select session_id

          from v$locked_object

        where object_id in

              (select object_id

                  from all_objects

                where object_name = 'BILL_QUERY_ITEM_200705_T'))               

alter system kill session '308,16829'immediate;

--使用视图查找：

select * from longshine_table_type_t where table_name=upper('dm_merg_oldfee_price_relation');

--根据DB_TYPE找具体的连库方法

select * from ls6_para.database_info_t where db_type=601 and region_id =1016;

--视图的代用：

select * from longshine_tables where table_name=upper('dm_code_transfer_t');

select * from longshine_tablespace where table_type=upper('dm_code_transfer_t');

select * from longshine_region_group where use_type='SID';

--根据DB_TYPE找具体的连库方法

select * from ls6_para_sid.database_info_t where db_type=601 and region_id =1016;

---

对象查询

1、用户

                        查看当前用户的缺省表空间

                        SQL>select username,default_tablespace from user_users;

                    查看当前用户的角色

                    SQL>select * from user_role_privs;

                    查看当前用户的系统权限和表级权限

                    SQL>select * from user_sys_privs;

                    SQL>select * from user_tab_privs;

                2、表

                        查看用户下所有的表

                        SQL>select * from user_tables;

                        查看名称包含log字符的表

                        SQL>select object_name,object_id from user_objects

                            where instr(object_name,'LOG')>0;

                        查看某表的创建时间

                        SQL>select object_name,created from user_objects where

            object_name=upper('&table_name');

                        查看某表的大小

                        SQL>select sum(bytes)/(1024*1024) as size(M) from

            user_segments

                            where segment_name=upper('&table_name');

                        查看放在ORACLE的内存区里的表

                        SQL>select table_name,cache from user_tables where

            instr(cache,'Y')>0;

                3、索引

                        查看索引个数和类别

                        SQL>select index_name,index_type,table_name from

            user_indexes order by table_name;

                        查看索引被索引的字段

                        SQL>select * from user_ind_columns where

            index_name=upper('&index_name');

                        查看索引的大小

                        SQL>select sum(bytes)/(1024*1024) as size(M) from

            user_segments

                            where segment_name=upper('&index_name');

                4、序列号

                        查看序列号，last_number是当前值

                        SQL>select * from user_sequences;

                5、视图

                        查看视图的名称

                        SQL>select view_name from user_views;

                        查看创建视图的select语句

                        SQL>set view_name,text_length from user_views;

                        SQL>set long 2000;               

            说明：可以根据视图的text_length值设定set long 的大小

                        SQL>select text from user_views where

            view_name=upper('&view_name');

                6、同义词

                        查看同义词的名称

                        SQL>select * from user_synonyms;

                7、约束条件

                        查看某表的约束条件

                        SQL>select constraint_name,

            constraint_type,search_condition, r_constraint_name

                            from user_constraints where table_name =

            upper('&table_name');

                    SQL>select

c.constraint_name,c.constraint_type,cc.column_name

                        from user_constraints c,user_cons_columns cc

                        where c.owner = upper('&table_owner') and c.table_name =

            upper('&table_name')

                        and c.owner = cc.owner and c.constraint_name =

            cc.constraint_name

                        order by cc.position;

                8、存储函数和过程

                        查看函数和过程的状态

                        SQL>select object_name,status from user_objects where

            object_type='FUNCTION';

                        SQL>select object_name,status from user_objects where

            object_type='PROCEDURE';

                        查看函数和过程的源代码

                        SQL>select text from all_source where owner=user and

            name=upper('&plsql_name');

            三、查看数据库的SQL

            1、查看表空间的名称及大小

                select t.tablespace_name, round(sum(bytes/(1024*1024)),0)

ts_size

                from dba_tablespaces t, dba_data_files d

                where t.tablespace_name = d.tablespace_name

                group by t.tablespace_name;

            2、查看表空间物理文件的名称及大小

                select tablespace_name, file_id, file_name,

                round(bytes/(1024*1024),0) total_space

                from dba_data_files

                order by tablespace_name;

            3、查看回滚段名称及大小

                select segment_name, tablespace_name, r.status,

                (initial_extent/1024) InitialExtent,(next_extent/1024)

            NextExtent,

                max_extents, v.curext CurExtent

                From dba_rollback_segs r, v$rollstat v

                Where r.segment_id = v.usn(+)

                order by segment_name ;

            4、查看控制文件

                select name from v$controlfile;

            5、查看日志文件

                select member from v$logfile;

            6、查看表空间的使用情况

                select sum(bytes)/(1024*1024) as free_space,tablespace_name

                from dba_free_space

                group by tablespace_name;

                Select A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES

            FREE,

                (B.BYTES*100)/A.BYTES % USED,(C.BYTES*100)/A.BYTES % FREE

                FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

                Where A.TABLESPACE_NAME=B.TABLESPACE_NAME AND

            A.TABLESPACE_NAME=C.TABLESPACE_NAME;

            7、查看数据库库对象

                select owner, object_type, status, count(*) count# from

            all_objects group by owner, object_type, status;

            8、查看数据库的版本

                Select version FROM Product_component_version

                Where SUBSTR(PRODUCT,1,6)='Oracle';

            9、查看数据库的创建日期和归档方式

                Select Created, Log_Mode, Log_Mode From V$Database;

            四、ORACLE用户连接的管理

            用系统管理员，查看当前数据库有几个用户连接：

            SQL> select username,sid,serial# from v$session;

            如果要停某个连接用

            SQL> alter system kill session 'sid,serial#';

            如果这命令不行,找它UNIX的进程数

            SQL> select pro.spid from v$session ses,v$process pro where

            ses.sid=21 and ses.paddr=pro.addr;

            说明：21是某个连接的sid数

            然后用 kill 命令杀此进程号。

---

堵塞分析

营收tuxedo服务堵塞问题分析解决步骤：

查看是否存在分布式的锁：

查看是否有分布式的锁：

SELECT inst_id,DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode, request, type

  FROM gV$LOCK WHERE (id1, id2, type) IN

          (SELECT id1, id2, type FROM V$LOCK WHERE request>0)

  ORDER BY id1, request;

--这个语句可以看到是否有分布式的锁。有Holder的即表示有锁。

2. 查询oracle客户端的进程号:                 

        SELECT a.process,a.PROGRAM,a.MACHINE,a.COMMAND,a.sid,b.spid

                  from v$session a, v$process b

                where a.paddr = b.addr

                  and a.sid in ($sid$);

3. 根据进程号查询具体tuxedo的serverid

telnet到tuxedo应用服务器上。

ps -ef|grep $pid$;

ibas7 3010760      1  1 23:33:40      -  2:21 TUXPAY4 -C dom=ibas7_02 -g 11001 -i 10293 -u jxysap2 -U /ysapp/home/ibas7/bin/ULOG -m 0 -A -r -- -L30 -T0 -cLS65_BILL_NC/A143273934514E615@ysdb2 -s4 -h0 -a1 -b4 -S4 -B4

-i之后是serverid

4. 根据tuxedo服务的serverid查询正在执行的tuxedo交易名称service_name：

echo psr|tmadmin 2>a.out |grep $serverid$ //查询单个server正在执行的交易名

echo psr|tmadmin 2>a.out |grep -v "IDLE" //查询所有正在执行的交易名

5. 根据进程号查询执行的SQL:

select sql_text, executions, buffer_gets

  from v$sqlarea

where address in

      (SELECT a.sql_address

            from v$session a, v$process b

          where a.paddr = b.addr

            and b.spid in ($pid$));

6.core和异常自动重启也会导致数据库锁

查询是否存在core文件和core信息,cd bin; ls –al core;

dbx/gdb $SERVER_NAME$ core;

查询tuxedo的主要服务有没有重启记录:

grep died ULOG.101608; 查询当天的ULOG日志

7.根据4, 5, 6 查询到的service_name再结合第五步（5）查询到的执行的SQL，结合程序分析应用程序锁表的原因

应急措施：

杀掉orcal锁，重启进程。

-------------------------------------------------------------------

服务堵塞（非锁表状态，可能只是性能问题）查找方式

1、 登陆76 或者 78 执行 sh /ysapp/home/ibas7/tuxedomon.sh

2、如果第二个列表中有服务显示就代表这个服务正在排队，请求资源。

3、观察排队的服务在第一个列表中的状态，同一个ID多次显示RqDone值不变，代表此服务正在执行，如果时间比较久代表此服务性能可能有问题。取出此ID值进行下面操作。

4、 ps -ef|grep 上面有问题的ID 显示下面记录，可能是多条，查找 -i后面的值等于有问题的ID 这样就只有一条。取出下面第二列，系统进程号。 

ibas7  602358      1  0 09:25:32 pts/12  0:01 TUXDYNQRY1 -C dom=ibas7_02 -g 20 -i 7003 -u jxysap2 -U /ysapp/home/ibas7/bin/ULOG -m 0 -A -r -- -L30 -T0 -cLS65_BILL/AF4C6A6B6D6A767C627B417@ysdb1 -s1 -h0 -a1 -b1 -S1 -B1

5、根据进程号去数据库查询此进程正在进行什么操作。

SELECT * FROM V$SQL S WHERE S.HASH_VALUE IN(

SELECT SQL_HASH_VALUE FROM V$SESSION WHERE process in ('进程号'));

---

强杀分布式锁

蔡文峰(蔡文峰) 19:59:30

26、如何解决ORA-01591"一个事务提交的时候，全局事务还没未提交"

    错误案例（江西） ：Sql Error = ORA-01591: lock held by in-doubt distributed transaction 1.25.11662

    a、查看有没有异常的的分布式事务

    select * from dba_2pc_pending;

    b、如果有的话，首先需要执行

    rollback force 'LOCAL_TRANS_ID'或 commit force 'LOCAL_TRANS_ID'

    c、如果执行报错，使用下列命令清除：

      alter session set "_smu_debug_mode"=4;

      execute dbms_transaction.purge_lost_db_entry('9.9.3883786');

19:57:11|185128|      258|30|    -1|FormatOut.cpp      | 4887|数据库操作错误(ORA-01591:

lock held by in-doubt distributed transaction 79.49.34742

    a、查看有没有异常的的分布式事务

    select * from dba_2pc_pending;

    b、如果有的话，首先需要执行

    rollback force '8.62.180149' 或 commit force 'LOCAL_TRANS_ID'

    c、如果执行报错，使用下列命令清除：

      alter session set "_smu_debug_mode"=4;

      execute d

蔡文峰(蔡文峰) 11:33:37

bms_transaction.purge_lost_db_entry('72.1.11480');

---

分析sql

孙晓辉(孙晓辉) 10:02:24

在四台数据库主机

进入/home/ibas6/sxh目录下。

蔡文峰(蔡文峰) 10:02:41

？

孙晓辉(孙晓辉) 10:02:51

./top10cpu.sh  CPU前10位的正在执行的语句。

孙晓辉(孙晓辉) 10:03:11

./top10mem.sh  内存前10位正在执行的语句

主要问题在于ysdb1无法自动切换到ysdb2.

原来是ysdb1的tns配置是先访问72再访问74

所以当72主机存活而数据库不在时，72会返回一个信息，这样oracle就会去访问74即达到切换到ysdb2的效果。

而当72主机不存在时，72没有返回信息，所以oracle就一直等待直至超时之后才去访问74.

李立健(李立健) 10:40:56

所以后续建议：

当停掉某一台数据库主机之后，调整相当计费或者营收主机的tns配置，只访问完好的那台数据库

这样可以避免切换过程中出现问题。

如果数据库能正常切换，那么就不会存在问题

孙晓辉() 2009年09月07日 11:35:31

/ysapp/home/ibas7/Amdocs/Shai

孙晓辉() 2009年09月07日 11:35:34

78

孙晓辉() 2009年09月07日 11:35:43

sqlplus amdocs/Amdocs.JX.China@ysdb1

conn amdocs/Amdocs.JX.China@ysdb2

孙晓辉() 2009年09月07日 11:36:00

@spreport

sp_8214_8215_72_09_10_20090914

select distinct

  p.sql_id , OPERATION , EXECUTIONS ,

  OBJECT_OWNER,

  OBJECT_NAME , COST , DISK_READS , BUFFER_GETS , elapsed_time / 1000000 elapsed_time,sql_text

  from  v$sql_plan p, v$sqlarea s

  where OPTIONS = 'FULL'

  and p.sql_id = s.sql_id

  and s.elapsed_time > 1000000 -- one second

  and OBJECT_OWNER not like 'SYS%'

  and OBJECT_OWNER not like 'SYSTEM'

  and rownum < 1000

  order by elapsed_time desc

刘金华(刘金华) 09:34:10

    select username,sid,opname,

    round(sofar*100 / totalwork,0) || '%' as progress,

    time_remaining,sql_text

    from v$session_longops , v$sql

    where time_remaining <> 0

    and sql_address = address

    and sql_hash_value = hash_value 

刘金华(刘金华) 09:36:08

小李子:

    select username,sid,opname,

    round(sofar*100 / totalwork,0) || '%' as progress,

    time_remaining,sql_text

    from v$session_longops , v$sql

    where time_remaining <> 0

    and sql_address = address

    and sql_hash_value = hash_value 

张杭州(张杭州) 09:36:18

select /*+ ORDERED */ sql_text from v$sqltext

where hash_value in (

select sql_hash_value from v$session

where sid in (select a.sid

from v$session_wait a, v$session b

where a.sid=b.sid

and (a.state='-2' or a.state>='0')

and b.status='ACTIVE'))

刘金华(刘金华) 09:36:29

看哪些session进行中.......

张杭州(张杭州) 09:37:37

用这个select a.sid,b.serial#,a.state,b.status,b.username,a.event

from v$session_wait a, v$session b

where a.sid=b.sid

and (a.state='-2' or a.state>='0')

and b.status='ACTIVE'

select xidusn,b.object_name, session_id, locked_mode from v$locked_object a,dba_objects b where a.object_id=b.object_id;

查看等待时间

select a.sid,b.serial#,a.state,b.status,b.username,a.SECONDS_IN_WAIT,a.wait_time

from v$session_wait a, v$session b

where a.sid=b.sid and a.wait_time>0 and b.status='ACTIVE'

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,

      id1,    id2, lmode, request, type

  FROM V$LOCK

  WHERE (id1, id2, type) IN

  (SELECT id1, id2, type FROM V$LOCK WHERE request>0)

    ORDER BY id1,      request;

select * from v$session where sid=上一个语句查到的sess;--查到processid,到后台应用ps -ef|grep processid

--------销账时间

    select username,sid,opname,

    round(sofar*100 / totalwork,0) || '%' as progress,

    time_remaining,sql_text

    from v$session_longops , v$sql

    where time_remaining <> 0

    and sql_address = address

    and sql_hash_value = hash_value 

--列出使用频率最高的5个查询：

select sql_text,executions

from (select sql_text,executions,

  rank() over

    (order by executions desc) exec_rank

  from v$sql)

where exec_rank <=5;

--消耗磁盘读取最多的sql top5：

select disk_reads,sql_text

from (select sql_text,disk_reads,

  dense_rank() over

    (order by disk_reads desc) disk_reads_rank

  from v$sql)

where disk_reads_rank <=5;

--找出需要大量缓冲读取（逻辑读）操作的查询：

select buffer_gets,sql_text

from (select sql_text,buffer_gets,

  dense_rank() over

    (order by buffer_gets desc) buffer_gets_rank

  from v$sql)

where buffer_gets_rank<=5;

---

启停数据库

LINUX 操作系统重启Oracle数据库的方法步骤 收藏

　在实际的应用中，有时候工作数据库需要重新启动。本文介绍了一个特别实用的操作步骤，希望对大家有所帮助。

　　1. 停应用层的各种程序

　　2. 停Oralce的监听进程

　　$ lsnrctl stop

　　3. 在独占的系统用户下，备份控制文件:

　　$ sqlplus "/as sysdba"

　　SQL> alter database backup controlfile to trace;

　　4. 在独占的系统用户下，手工切换重作日志文件，确保当前已修改过的数据存入文件:

　　SQL> alter system switch logfile;

　　5. 在独占的系统用户下，运行下面SQL语句，生成杀数据库用户连接的kill_all_session.sql文件:

　　SQL> set head off;

　　SQL> set feedback off;

　　SQL> set newpage none;

　　SQL> spool ./kill_session.sql

　　SQL> select 'alter system kill session '''　　sid　　','　　serial#　　''';' from v$session where username is not null;

　　SQL> spool off;

　　6. 在独占的系统用户下，执行杀数据库用户连接的kill_session.sql文件

　　SQL> @./kill_session.sql

　　7. 在独占的系统用户下，用immediate方式关闭数据库:

　　SQL> shutdown immediate;

　　或者

　　SVRMGRL> shutdown immediate;

　　8. 启动oralce的监听进程

　　$ lsnrctl start

　　9. 进入独占的系统用户下，启动Oralce数据库

　　$ sqlplus /nolog

　　SQL> connect / as sysdba

　　SQL> startup;

　　或者

　　$ svrmgrl

　　SVRMGRL> connect internal;

　　SVRMGRL> startup;

　　10.启动应用层的各种程序

---

hint优化

HINT在判断SQL性能问题时，有时会启一个快捷作用，是一个调优SQL强大工具

1./*+ driving_site(table)  */ --将过滤的条件于远端完成再传过来，如在A服务器上过滤再将结果传到B服务器上,原则是哪个表数据小就将它与远端大表匹配再将结果返回；

2./*+ index(t idx_id) */--指定索引

3./*+ append */ --数据直接插入到高水位上面(与insert连用),外加并行/*+ append parallel(table,4) */

4./*+ index(view.t1 idx_name) */ --对视图中的基表进行指定走索引

5./*+ parallel(table,4) */eg. select /*+ parallel(t1,4) */ * from  t1;并发执行前加alter session enable parallel dml;

注意：当使用HINT时如果结果集不完整或有错，优化器将忽略它；

6.优化器相关

alter system set optimizer_mode=first_rows(all_rows) --系统级别设置，也可在会话级别session

select  /*+ first_rows(20) */ * from t1 where id<20; --CBO模式

select  /*+ all_rows */ * from t1 where id<20; --CBO模式

-在SQL中使用HINT的优先级要高于optimizer_mode的参数设定；

select  /*+ rule */ * from t1;  --RBO优化器模式

7.访问路径相关的hint

包含：cluster,full,hash,index,no_index,index_asc,index_desc,index_combine,index_join,index_ffs,index_ss,index_ss_asc,index_ss_desc,no_index_ffs,no_index_ss,ordered,leading,use_hash,no_use_hash;

select  /*+ full(t1) */ * from t1;--全表扫描

select  /*+ index(t1 idx_name) */ * from t1 where object_id>2;使用指定索引

select  /*+ no_index(t1 idx_name) */ * from t1 where object_id>2; --不使用指定索引

select  /*+ index_desc(t1 idx_name) */ * from t1 where object_id=2; --按索引降序顺序访问数据

select /*+ index_combine(t1 idx_name) */ * from t1;--选择位图索引

select /*+ index_ffs(t1  idx_name) */ from t1 where object_id <100; --索引快速全表扫描（把索引当作一个表看待）

select /*+ index_join(t1 idx_name1 idx_name2) */ * from t1 where object_id=5 and status='VALID'; --同时使用条件列上的相关索引

select /*+ index_ss(t1 index_name) */ * from t1 where object_id=99;  --跳跃式扫描

select /*+ leading(t1,t) */ t.* from t,t1 where t1.object_id=t.object_id; --指定t1为驱动作，优化器先访问此表

select /*+ ordered */ t.* from t,t1 where t1.id=t.id;   --指定按from 后面表的顺序选择驱表，t作为驱动表

select /*+ use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用NEST LOOP表连接，适合含有小表数据关联，如一大一小

select /*+ use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用HASH表连接，适合两个大表关联

select /*+ use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id;--使用合并排序表连接

select /*+ no_use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id; --不使用NEST LOOP表连接

select /*+ no_use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--不使用HASH表连接

select /*+ no_use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id; --不使用合并排序表连接

8.并行相关hint

select /*+ parallel(t 4) */ count(*) from t1; --开启表的4个并行度

select /*+ no_parallel(t) */ count(*) from t1; --不使用并行

表默认的degree(user_tables)如果不为0，默认开启并行针对select;

9.其它hint

select /*+ dynamic_sampling(t 4) */ * from t where id>134;--4为采样级别

select /*+ full(t1) cache(t1) */ object_id from t1;--将表t1放在LRU端最活跃处，相当于表属性的cache(keep);

---

awr分析

快速阅读ORACLE的AWR报告

1      Oracle AWR中的时间模型

理解AWR报告之前，要先理解其时间模型，才不致于被混淆。

时间模型，都是从Oracle前端进程（Foreground Processes）的角度去理解的，不单独统计后端进程（LGWR等）的开销

DBTime：有时又叫消逝时间（ElapsedTime）、响应时间（Response Time），包含CPU Time、Wait Time、IO Time。DB Time columns represents, total time spent by foreground processes. It includes CPU Time, IO Time and Wait time and excludes Idle wait time and time taken by background processes.

CPU Time：数据库的CPU运算时间，有时又叫Service Time，包含分析时间（Parse Time）、递归调用时间（Recursive CPU Usage）、其他CPU开销（CPU Other）。

l  注1：IOTime，在早期（Oracle 11之前）的Statspack报告及AWR中没有单列，在早期statspack中DBTime仅包含CPUTime 和WaitTime

l  注2：在statspack报告中才区分ParseTime/Recursive Usage/CPU Other，AWR中一般不再进一步区分CPU的使用类型，service time也是statspack报告中对CPU time的另一个称呼。

2      数据库整体负载信息

DBTime /(Elapsed * CPUs) >= 1,则通常说明数据库负载重，有明显等待事件

2.1      问题负载

CPU总共16个，60分钟的DB Time却达到8000多，差距这么大？！

Host Name

Platform

CPUs

Cores

Sockets

Memory (GB)

app2

Linux x86 64-bit

16

16

4

7.68

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

10445

31-Jan-15 11:00:08

400

1.2

End Snap:

10446

31-Jan-15 12:00:58

200

1.2

Elapsed:

60.84 (mins)

DB Time:

8,544.30 (mins)

2.2      正常负载

DB Name

DB Id

Instance

Inst num

Startup Time

Release

RAC

BSNYSDB

1180525933

bsnysdb1

1

22-7月 -15 02:07

11.2.0.3.0

YES

Host Name

Platform

CPUs

Cores

Sockets

Memory (GB)

sti55l01

AIX-Based Systems (64-bit)

48

12

192.00

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

22371

01-12月-15 12:00:13

4613

8.7

End Snap:

22375

01-12月-15 16:00:49

4679

9.0

Elapsed:

240.60 (mins)

DB Time:

4,029.99 (mins)

3      瓶颈在哪里？

3.1      整体处理原则：Top 5 Timed Foreground Eventà查看相应的TOP SQL

即先看TOP5 事件，重点处理排在第一位的事件；然后根据事件，查看对应的SQL排序列表，确定可能的问题SQL及优化方法。

3.2      Top 5 Timed Foreground Event

Top 5事件是非常重要的信息，通过它可以判断出这段时间数据库的瓶颈是什么，重点处理排在第一位的事件。

对HB/ABM而言，正常情况下排在第一位的事件为DB CPU，否则需要考虑优化。

常见事件如下表所示(……这个列表待刷新……)：

事件名

原因

处理办法

DB CPU

一般是SQL性能问题，访问量过大或没有走合适的索引

查看SQL ordered by CPU Time、SQL ordered by Gets，优化前面的SQL

db file sequential read

大量的索引扫描

查看SQL ordered by Reads、SQL ordered by physical reads，优化前面的SQL

db file scattered read

大量的全表扫描

查看SQL ordered by Reads、SQL ordered by physical reads，优化前面的SQL

log file sync

Commit太频繁了。当一个用户提交或回滚数据时，LGWR将会话的redo记录从日志缓冲区填充到日志文件中，用户的进程必须等待这个填充工作完成。

修改应用程序的提交频率, 为减少这个等待事件，须一次提交更多记录，或者将重做日志REDO LOG 文件放在不同的物理磁盘上，提高I/O的性能。

log file switch (checkpoint incomplete)

Redo log file太小了，导致日志需要切换时，前一个日志的数据还没有完全写到数据文件中

增加redo log日志组

log file switch (archiving needed)

这个等待事件出现时通常是因为日志组循环写满以后，第一个日志归档尚未完成，出现该等待。出现该等待，可能表示io 存在问题。

以考虑增大日志文件和增加日志组；移动归档文件到快速磁盘；调整log_archive_max_processes。

buffer busy waits

热块，并发的线程经常更新同一个数据块（不是相同的数据）

当一个会话试图修改一个数据块，但这个数据块正在被另一个会话修改时。

当一个会话需要读取一个数据块，但这个数据块正在被另一个会话读取到内存中时。

可以在awr的Buffer Wait Statistics部分检查是哪部分的热块，如果是数据的，查看Segments by Buffer Busy Waits部分，可以看到是哪个表的问题。解决办法：减少更新的频率，尽量将数据分布到不同的数据块中。

TOP 5事件举例：

Top 5 Timed Foreground Events

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

db file sequential read

15,188,833

81,138

5

33.56

User I/O

DB CPU

59,059

24.42

SQL*Net message from dblink

589,972

13,348

23

5.52

Network

enq: TX - row lock contention

63,138

10,465

166

4.33

Application

log file sync

3,157,833

4,888

2

2.02

Commit

3.3      TOP SQL

SQL统计信息包含了监控期间的SQL按各种维度的统计信息。主要含如下内容：

SQL列表

含义

适用场景

SQL ordered by Elapsed Time

总时间Elapsed Time最长的Top SQL

结合其余Top使用，较少单独使用

SQL ordered by CPU Time

总CPU时间最长的Top SQL

DB CPU事件时

SQL ordered by User I/O Wait Time

总IO等待时间最长的Top SQL

很少单独使用

SQL ordered by Gets

逻辑读最多的Top SQL

DB CPU事件时

SQL ordered by Reads

物理读最多的Top SQL

db file sequential read或

db file scattered read事件时

SQL ordered by Physical Reads (UnOptimized)

未优化的物理读最多的Top SQL

db file sequential read或

db file scattered read事件时

SQL ordered by Executions

执行次数最多的Top SQL

很少单独使用

SQL ordered by Parse Calls

软解析次数最多的Top SQL

?

SQL ordered by Sharable Memory

占用Library Cache最多的Top SQL

?

SQL ordered by Version Count

版本数最多的Top SQL

任何时候都值得分析，关注未使用绑定变量的SQL

SQL ordered by Cluster Wait Time

ClusterWaitTime最长的Top SQL

GC事件时？

Complete List of SQL Text

监控期间SQL完整语法列表

4      CPU是瓶颈吗？

正常情况下，DB CPU事件应是TOP1事件的。那DB CPU事件是TOP 1的时候系统就正常了？不一定，此时要结合AWR中的Host CPU与Instance CPU看。

Host CPU (CPUs: 48 Cores: 12 Sockets: )

Load Average Begin

Load Average End

%User

%System

%WIO

%Idle

12.61

14.47

15.3

4.9

5.1

79.8

Instance CPU

%Total CPU

%Busy CPU

%DB time waiting for CPU (Resource Manager)

9.2

45.6

0.0

Host CPU表示数据库主机的CPU情况，%Idle表示CPU的空闲程度，该值若低于40%，则说明数据库主机的CPU可能是瓶颈。

Instance CPU表示数据库实例占用的CPU情况，数据库主机CPU高（Host CPU中的%Idle值小）不一定是数据库实例导致的，也可能有其他进程在运行（这种情况比较少）。%Total CPU表示数据库实例占用的CPU，%Busy CPU表示数据库实例的CPU占用/服务器的CPU占用。

若确定是数据库实例的CPU导致的主机CPU高，则结合上述的TOPSQL，查看、优化对应的模块。

l  注3：在早期（Oracle 11之前）的AWR中没有Host CPU及Instance CPU，此时应考虑使用主机层面的TOP工具进行综合判断。

5      其他问题

5.1      时间跨度多长合适？

AWR是基于一段时长的统计报告。太长，问题可能被大量的正常信息给覆盖；太短，问题可能还未体现在报告中。

查问题时，一般以30分钟~1小时的AWR报告为宜。若要更短时间的分析，则一般用ASH报告，其分析方法与AWR报告类似。

---

tmp表空间的使用

Oracle查询temp表空间的名字和位置

select tablespace_name,file_name from dba_temp_files;

Oracle查询temp表空间的使用率

select tablespace_name,round(free_space/1024/1024/1024,2) "free(GB)",round(tablespace_size/1024/1024/1024,2） "total(GB)",round(nvl(free_space,0)*100/tablespace_size,3) "Free percent"

from dba_temp_free_space;