[ The imperialist Japanese military must officially acknowledge and apologize for the coercion of young women into sexual slaves. / 幼い女性を性の奴隷にした帝國主義日本軍の?制性を公式に認め、謝罪しなければならない. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [71]
02.File Book
File 자료실 [96]
Site Link [14]
개인폴더 [201]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [25]
MySQL [43]
DB2 [79]
ORACLE [3054]
PSQL [135]
CUBRID [5]
기타정보 [158]
운영체제 [607]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: ORACLE

 회원가입

session monitor [ dbms_xplan sql binds ]
최길호 [LIST]   2010-06-30 08:36:05, 조회 : 85,394

set linesize 300
set pagesize 1000
set feed off
col db format 9
col sid format 9999
col serial# format 9999999
col stat format a4
col event_name format a18
col username format a10
col module_nm format a20
col object_name format a20
col WaitSec format 999999
col "ENQ-mode / p1-p2-p3" format a30
col LATCH_NAME format a16
col p3 format a3

col p1 for 99999
col p2 for 9999999999
col p3 for 99999999999999999999

--ttitle ' - Session Wait Status - '

select
        a.inst_id db
       , a.sid
       , b.serial#
       , substrb(b.status,1,4) stat
       , substrb(a.event,1,18) event_name
       , b.username
       , substrb(b.module,1,20) module_nm
       , substrb((SELECT OBJECT_NAME FROM DBA_OBJECTS C WHERE C.OBJECT_ID = B.ROW_WAIT_OBJ# ),1,20) object_name
       , a.seconds_in_wait WaitSec
       , substrb((SELECT NAME FROM V$LATCH WHERE LATCH# = a.p2 and a.event = 'latch free' ),1,16) LATCH_NAME
       , DECODE(a.event, 'enqueue',  chr(bitand(a.p1,-16777216) / 16777215) || chr(bitand(a.p1, 16711680) / 65535) || '-' || to_char( bitand(a.p1, 65535) ||' '||a.p1||'-'||a.p2||'-'||a.p3)) "ENQ-mode / p1-p2-p3"
       --, (SELECT NAME||' GMR%:'||ROUND((MISSES/GETS)*100,2)||' S1:'||SLEEP1||' S2:'||SLEEP2||' S3:'||SLEEP3||' S4:'||SLEEP4 FROM V$LATCH WHERE LATCH#= a.p2 and a.event = 'latch free') LATCH_NAME
       , b.sql_id
  from gv$session_wait a, gv$session b
where a.event not like '%message%'
   and a.event not like '%SQL*Net%'
   and a.event not like '%timer%'
   and a.event not like 'PX%'
   and a.event not like 'jobq slave wait'
   and b.type ='USER'
   and a.inst_id = b.inst_id
   and a.sid = b.sid
order by a.inst_id desc ,a.event ,a.sid ;

//////////////////////////////////////////////////////////////////////////////

select * from table(dbms_xplan.display_cursor('&SQL_ID',null));
select * from table(dbms_xplan.display_cursor('&SQL_ID',null,'ALLSTATS LAST +peeked_binds'));
set lines 200 pages 200
col value_string for a40
select distinct inst_id,name,datatype_string,value_string from gv$sql_bind_capture where sql_id='&SQL_ID' order by 1,2;

awrsqrpt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set lines 200
set pages 200
column L_DBID new_value V_DBID
column L_INST_NUM new_value V_INST_NUM
column L_BID new_value V_BID
column L_EID new_value V_EID
select dbid l_dbid from v$database;
select instance_number l_inst_num from v$instance;
select max(snap_id)-1 l_bid, max(snap_id) l_eid from dba_hist_snapshot;
select output from table(dbms_workload_repository.awr_sql_report_text
( &V_DBID,&V_INST_NUM,&V_BID, &V_EID,'&SQL_ID'));

-- bind
var b1 varchar2(32);
var b2 varchar2(32);
begin
:b1 := 00186696 ;
:b2 := 01 ;
end;
/

select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'))
select plan_table_output from table(dbms_xplan.display('plan_table',null,''OUTLINE serial'))
select plan_table_output from table(dbms_xplan.display_cursor](…,…,'OUTLINE [other options]');

3.239.9.151


최길호
9i
SQL> select * from table(dbms_xplan.display('V$SQL_PLAN','2245332340'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
An uncaught error happened in fetching the records : ORA-00904: "STATEMENT_ID":
invalid identifier

ORA-00904: "STATEMENT_ID": invalid identifier

///////////////////////////////////////////////////////////////////////////
create or replace view liveplan as select sp.*,to_char(hash_value) statement_id,sysdate timestamp
from v$sql_plan sp where hash_value=&&SQLHV;
select * from table( dbms_xplan.display('LIVEPLAN'));
or
select * from table( dbms_xplan.display('LIVEPLAN','&&SQLHV'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4585 |
| 1 | SORT ORDER BY | | 2 | 746 | 4585 |
|* 2 | TABLE ACCESS FULL | MWL | 2 | 746 | 4574 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

2 - filter("REPLICA_DTTM"='A')

출처: http://www.scaleabilities.co.uk/index.php/James/Using-DBMS_XPLAN.DISPLAY-on-V$SQL_PLAN.html
2010-09-27
10:22:50



최길호
select SQL_FULLTEXT

set lines 80
set serveroutput on size unlimited format word_wrapped

begin
for i in (select SQL_FULLTEXT from v$sql where sql_id='bua0qdbuuks2r') loop
dbms_output.put_line( i.SQL_FULLTEXT );
end loop;
end;
/

추처: target=_blank>http://kr.forums.oracle.com/forums/thread.jspa?threadID=641126
2011-09-05
22:54:28



  LIST

제목 작성자 작성일 조회
Troubleshoot  ORA-03137: TTC    최길호 2018/04/24 1007007
WebLogic  weblogic cache    최길호 2018/02/28 237691
WebLogic  weblogic cluster session share [ 세션 공유 login.jsp login chk, weblogic.management.ManagementException: [Deployer:149188] ]    최길호 2018/03/03 124608
Troubleshoot  IMP-00003 ORA-32603: invalid FREEPOOLS LOB storage option value [ move lob table, lob index ]    최길호 2013/05/26 120225
Troubleshoot  proc, windows, vc 2003 , pcmake , Files\Microsoft은(는) 예상되지 않았습니다    최길호 2006/10/02 100261
Troubleshoot  dblink ORA-02068 ORA-03113 ORA-02080 [ ALTER SESSION CLOSE DATABASE LINK RDBNM; ]  [4]  최길호 2010/07/10 99054
Troubleshoot  ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1]    최길호 2021/09/23 98788
WebLogic   WEB-INF/lib 우선 적용, index-directory-enabled 인덱스 디렉토리    최길호 2018/02/27 97316
WebLogic  java -jar fmw_12.2.1.3.0_wls.jar The OpenJDK JVM is not supported on this platform.    최길호 2018/02/06 86610
Troubleshoot  TRIGGER,Function ORA-02069 ORA-06512 ORA-04088 ORA-02070    최길호 2008/12/19 86504
Script  session monitor [ dbms_xplan sql binds ]  [2]  최길호 2010/06/30 85394
Troubleshoot  ORA-03297 Resizing Empty Datafile    최길호 2009/08/11 84496
Admin  dd copy datafile raw device , filesystem  [4]  최길호 2009/09/07 81827
SQL,PL*SQL  dbms_random 사용예  [1]  최길호 2010/04/12 81288
Troubleshoot  ODBC {Microsoft ODBC for Oracle}, ORA-12154, ODBC NA000 IM006 01000  [1]  최길호 2008/01/04 77685
Troubleshoot  ORA-12537 , <unknown connect data> * 12537  [2]  최길호 2009/11/21 68019
Troubleshoot  ALTER SESSION CLOSE DATABASE LINK    최길호 2010/07/11 66248
Admin  ORACLE과 MSSQL간의 DBLINK 방법    최길호 2008/09/19 65350
Troubleshoot  ld: 0711-224 WARNING: Duplicate symbol: p_xargc    최길호 2010/06/08 65325
Tool  RAC jdbc connection test [ thin java test.java sample ]  [3]  최길호 2010/06/14 61906

    목록보기   다음페이지 1 [2][3][4][5][6][7][8][9][10]..[153]   [다음 10개]
       

Copyright 1999-2024 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
2021/06/30
윈도우 서버에서 sqlplus .... ORA-...
by 최길호
2021/06/30
ORA-28500: connection from O...
by 최길호
2019/05/02
pkg 확인 SELECT * FROM AL...
by 최길호
최근 게시물
03/04
[기타정보]
아두이노 wifi web test.
by 최길호
03/01
[기타정보]
아두이노 스마트하우스 V2.
by 최길호
02/28
[ORACLE]
plsql run sql from column / 테....
by 최길호