Abdul Hafeez Kalsekar -- Oracle database tuning case studies

  ==> Asm  commmands  set pagesize 2000 set lines 2000 set long 999 col path for a54 select name, path, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mb from v$asm_disk; SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup; select * from V$ASM_DISK_IOSTAT; ==> Mount/dismount ASM disk groups -- For mount a diskgroup,(This is instance specific, for mounting on all nodes, run the same on all nodes) SQL>alter diskgroup DATA mount; or asmcmd>mount DATA -- For umount a diskgroup,(This is instance specific, for unmounting on all nodes, run the same on all nodes) SQL>alter diskgroup DATA dismount; Or asmcmd>umount DATA -- To mount/Dismount all the diskgroups SQL>alter diskgroup ALL mount; SQL>alter diskgroup ALL dismount; ==> Drop ASM diskgroup -- To drop a diskgroup, make sure the diskgroup has been dismounted from all the remote nodes, It should be mounted ...

  ORADEBUG DOC EVENT NAME SQL_TRACE ORADEBUG EVENT SQL_TRACE BIND=TRUE,WAIT=TRUE ORADEBUG EVENT SQL_TRACE PLAN_STAT=ALL_EXECUTIONS ORADEBUG EVENT SQL_TRACE LEVEL=4 ORADEBUG EVENT SQL_TRACE OFF prompt &_C_REVERSE *** Enable tracing specified query by sid and sql_id ORADEBUG SETOSPID &OSPID oradebug TRACEFILE_NAME; oradebug EVENT sql_trace [sql: sql_id=&SQLID] prompt Execute "oradebug EVENT sql_trace off;" later for disabling... ALTER SYSTEM SET EVENTS sql_trace  [sql: sql_id=3s1yukp05bzg6|aca4xvmz0rzup] bind=true, wait=true';  SQL> oradebug setospid  <SPID>   SQL> oradebug unlimit  SQL> oradebug tracefile_name  SQL> oradebug event sql_trace [sql: 5qcyrymp65fak] level=12  关闭 event ++：  SQL>  oradebug event sql_trace [sql: 5qcyrymp65fak] off  SQL> alter system set events 'sql_trace [sql: 5qcyrymp65fak] level=12';  SQL>  alter system set events 'sql_trace [sql: 5qcyrymp65fak] off'; -...

  select username,osuser,status,sid,serial#,machine,process,terminal,program from v$session where saddr in ( select k2gtdses from sys.x$k2gte ); select * from v$session where machine='remote_machine'; select /*+ ORDERED */ substr(s.ksusemnm,1,10)||'-'|| substr(s.ksusepid,1,10) "ORIGIN", substr(g.K2GTITID_ORA,1,35) "GTXID", substr(s.indx,1,4)||'.'|| substr(s.ksuseser,1,5) "LSESSION" , s2.username, substr( decode(bitand(ksuseidl,11), 1,'ACTIVE', 0, decode( bitand(ksuseflg,4096) , 0,'INACTIVE','CACHED'), 2,'SNIPED', 3,'SNIPED', 'KILLED' ),1,1 ) "S", substr(s2.event,1,10) "WAITING" from x$k2gte g, x$ktcxb t, x$ksuse s, v$session s2 where g.K2GTDXCB =t.ktcxbxba and g.K2GTDSES=t.ktcxbses and s.addr=g.K2GTDSES and s2.sid=s.indx; prompt Enter filters(empty for any)... accept _sid         prompt "Sid           : "; accept _globalid    prompt "Globalid mask : ...

  --------------------------------------------------------------------------------- --------------------------------------------------------------------------------- ------- Name        :  options_packs_usage_statistics.sql -------                MOS DOC ID 1317265.1 ------- ------- Applies to:    Oracle Database - Version 11.2 and later ------- ------- Usage       :  Use SQL*Plus to connect to the database (locally or remotely) -------                with any user having SELECT ANY DICTIONARY privilege: -------                    sqlplus <UserName>/<Password> @options_packs_usage_statistics.sql ------- -------                Collect output file spooled in the current directory: -------                ...