잡학문구

sqlselect * from DBA_HIST_SQLTEXT  where sql_id ='4b6z0rh5jrktx';
select * from  DBA_HIST_SQL_PLAN  where sql_id ='4b6z0rh5jrktx' order by plan_hash_value, id;
select * from  DBA_HIST_SQLSTAT where sql_id ='4b6z0rh5jrktx';
select * from  v$sql  where sql_id ='4b6z0rh5jrktx';

select sql_id, plan_hash_value, id, count(*) from dba_hist_sql_plan group by sql_id, plan_hash_value, id
having count(*) > 1;
WRH$_SQL_PLAN
select * from(
select a.aid, b.bid from 
(select distinct sql_id as aid from DBA_HIST_SQLTEXT) A full outer join
(select sql_id as bid from DBA_HIST_SQLSTAT ) B
on a.aid = b.bid) c
where c.bid is null;

select * from(
select a.aid, b.bid from 
(select distinct sql_id as aid from sys.WRH$_SQLTEXT) A full outer join
(select sql_id as bid from sys.WRH$_SQLSTAT ) B
on a.aid = b.bid) c
where c.bid is null;

select * from sys.WRH$_SQLTEXT  where sql_id ='6rjdxthsp2yd4';
select * from sys.WRH$_SQLSTAT  where sql_id ='6rjdxthsp2yd4';
select * from sys.WRH$_SQL_PLAN where sql_id ='6rjdxthsp2yd4';

select * 
     FROM WRM$_SNAPSHOT sn, WRH$_SQLSTAT sql
    WHERE     sn.snap_id = sql.snap_id
          AND sn.dbid = sql.dbid
          AND sn.instance_number = sql.instance_number
          AND sn.status = 0;
          
select * From dba_hist_snapshot where snap_id = 96410 order by 1 desc;
select * From sys.WRH$_SQLTEXT b where not exists(select 'x' from sys.WRH$_SQLstat a where a.sql_id = b.sql_id);

select * From dba_objects where object_name='AWR_ROOT_SQLTEXT';
select * From dict where table_name='AWR_ROOT_SQLTEXT';


select sql_id, plan_hash_value, count(*) 
from  DBA_HIST_SQLSTAT s  
where    s.parsing_schema_name = 'KALMHS'
group by sql_id, plan_hash_value order by 1
;
----------------------
-- sql_id 추출
----------------------
select sql_id, sql_text, replace(dbms_lob.substr(sql_text, 20, 1), chr(10), ' '), 
       (select command_name from DBA_HIST_SQLCOMMAND_NAME where command_type=t.command_type) type
from   DBA_HIST_SQLTEXT T
where  exists (select 'EXIST' 
               from   DBA_HIST_SQLSTAT S
               where  t.sql_id = s.sql_id
               and    s.parsing_schema_name = 'KALMHS');
;

select * from (
select t.sql_id,
       t.sql_text,
       replace(dbms_lob.substr(t.sql_text, 20, 1), chr(10), ' '),
       (select command_name from DBA_HIST_SQLCOMMAND_NAME where command_type=t.command_type) type,
       p.plan_hash_value 
from DBA_HIST_SQLTEXT T,
     (select sql_id, plan_hash_value from
         (select sql_id, plan_hash_value,
                row_number() over (partition by sql_id order by sql_id, timestamp desc) row_num
          from  DBA_HIST_SQL_PLAN
          where 1 = 1) -- 전체 SQL
      where row_num = 1) P
where t.sql_id = p.sql_id(+))
where plan_hash_value is null and type='SELECT';

select a.sql_id, b.sql_id, b.command_type from 
(select distinct sql_id from v$sql) a, DBA_HIST_SQLTEXT b
where a.sql_id = b.sql_id(+)
;-- where sql_id='3hc96wfvxp4tk';
-- 56196, 9610
select distinct sql_id from DBA_HIST_SQLTEXT;
--> 5540 

select sql_id, sql_text, sql_t, type, plan_hash_value, parsing_schema_name
from (
    select t.sql_id,
           t.sql_text,
           replace(dbms_lob.substr(t.sql_text, 20, 1), chr(10), ' ') sql_t,
           (select command_name from DBA_HIST_SQLCOMMAND_NAME where command_type=t.command_type) type,
           p.plan_hash_value,
           (select parsing_schema_name from DBA_HIST_SQLSTAT S where 1=1 and   s.sql_id = t.sql_id
                   and   s.plan_hash_value = p.plan_hash_value and rownum = 1) parsing_schema_name 
    from DBA_HIST_SQLTEXT T,
         (select sql_id, plan_hash_value from
             (select sql_id, plan_hash_value,
                    row_number() over (partition by sql_id order by sql_id, timestamp desc) row_num
              from  DBA_HIST_SQL_PLAN
              where 1 = 1) -- 전체 SQL
          where row_num = 1) P
    where t.sql_id = p.sql_id
    and   exists ( select 'X' 
                   from DBA_HIST_SQLSTAT S 
                   where s.parsing_schema_name in ('KALMHS','KALPPO','NETBACKUP','KALPPO_SSO','KALSTD')
                   and   module like '%%'
                   and   s.sql_id = t.sql_id
                   and   s.plan_hash_value = p.plan_hash_value)
)
order by 1;

select t.*,
       sql_id, replace(dbms_lob.substr(t.sql_text, 20, 1), chr(10), ' ') sql_t,
       (select command_name from DBA_HIST_SQLCOMMAND_NAME where command_type=t.command_type) type,
       parsing_schema_name
 From v$sql t
 where t.parsing_schema_name in ('KALMHS','KALPPO','NETBACKUP','KALPPO_SSO','KALSTD');

where plan_hash_value is null and type='SELECT';

select sql_id, count(*) From v$sql group by sql_id having count(*) > 1;


select * From dba_tab_columns where column_name = 'MODULE' order by table_name;

select * from v$sql where sql_id='grvaq6wc3s189';

select * From dict where table_name like '%SHARED%';
select * from V$SQL_SHARED_CURSOR where sql_id='grvaq6wc3s189';

SELECT  
       A.SQL_ID,
       A.SQL_TEXT, 
       A.COMMAND_TYPE, --> IF NEEDED
       B.PLAN_HASH_VALUE,
       B.ID,
       B.OPERATION,
       B.OPTIONS,
       B.OBJECT_OWNER,
       B.OBJECT_NAME,
       B.OBJECT_TYPE,
       B.OPTIMIZER,
       B.PARTITION_START,
       B.PARTITION_STOP,
       B.PARTITION_ID
FROM   DBA_HIST_SQLTEXT A, DBA_HIST_SQL_PLAN B
WHERE  A.DBID = B.DBID
AND    A.SQL_ID = B.SQL_ID
AND    A.SQL_ID IN('03y5xw17vncxd')
AND    EXISTS
       (SELECT 'E' 
        FROM  DBA_HIST_SQLSTAT C 
        WHERE A.DBID = C.DBID 
        AND   A.SQL_ID = C.SQL_ID 
        AND   B.SQL_ID = C.SQL_ID
        AND   B.PLAN_HASH_VALUE <> C.PLAN_HASH_VALUE
        AND   PARSING_SCHEMA_NAME  IN('KALMHS'))
ORDER BY A.DBID, A.SQL_ID, B.PLAN_HASH_VALUE, B.ID;

V$SQL에 뷰에는 SQL 문의 복사본이 두 개 이상 있을 수 있습니다. 예를 들어, 두 명의 사용자에게 각각 T 테이블이 있다고 가정해 보겠습니다. USERA가 SELECT * FROM T를 쿼리합니다. 마찬가지로 USERB도 SELECT * FROM T를 실행합니다. 이 두 SQL 문이 같은 것처럼 보이지만 다른 쿼리입니다. 따라서 V$SQL에는 두 개의 SQL문이 존재합니다.

주어진 SQL 문에 대해 V$SQL에 둘 이상의 커서가 존재하는 데에는 여러 가지 이유가 있습니다. 쿼리할 예제 테이블을 생성합니다.

the optimizer is told that there are 1,000,000 rows in it. Now we'll ensure that the shared pool has no cached copies of SQL against this table (this is for demonstration purposes—do not do this on a production system!):

옵티마이저에게 1,000,000개의 행이 있다고 알려줍니다. 공유 풀에 이 테이블에 대해 캐시된 SQL 복사본이 없는지 확인합니다(이는 데모용입니다. 프로덕션 시스템에서는 이 작업을 수행하면 안됩니다.)

SQL> alter system flush shared_pool; 

SQL> select sql_id, sql_text from v$sql where upper(sql_text) like 'SELECT % T LOOK_FOR_ME %B1_'; 

이제 Listing 1과 같이 동일한 쿼리 텍스트를 네 번 실행하는 PL/SQL 블록을 구성합니다.

Code Listing 1: PL/SQL block executing query text four times

SQL> declare 

     l_x_number     number;
     l_x_string     varchar2(30);
begin
     execute immediate 'alter session set optimizer_mode=all_rows';
     for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
     for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
     
     execute immediate 'alter session set optimizer_mode=first_rows';
     for x in (select * from t look_for_me where x = l_x_number) loop null; end loop;
     for x in (select * from t look_for_me where x = l_x_string) loop null; end loop;
end;
/

Listing 2와 같이 모두 정확히 동일한 SQL_ID를 가지고 있다는 것을 알 수 있기 때문에 그것들이 동일하다는 것을 압니다.

해당 블록을 실행한 후 V$SQL을 살펴보면 Listing 1에서 실행한 동일한 SQL 문에 대해 하나씩 4개가 있음을 알 수 있습니다. Listing 2와 같이 모두 동일한 SQL_ID를 가지고 있다는 것을 알 수 있습니다.

Code Listing 2: Query on V$SQL showing same SQL_ID for four query executions

SQL> select sql_id, sql_text 2 from v$sql 3 where upper(sql_text) 4 like 5 'SELECT % T LOOK_FOR_ME %B1_'; 

SQL_ID SQL_TEXT
------------- ----------------------------------------
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
1qqtru155tyz8 SELECT * FROM T LOOK_FOR_ME WHERE X = :B1

4 rows selected.

이 4개의 SQL은 동일하게 보이지만 서로 다릅니다. 

• Cursor 1 used ALL_ROWS and bound a NUMBER datatype.
• Cursor 2 used ALL_ROWS and bound a VARCHAR2 datatype.
• Cursor 3 used FIRST_ROWS with a NUMBER datatype.
• Cursor 4 used FIRST_ROWS with a VARCHAR2 datatype.

Listing 3은  커서 1과 2에 대한 계획을 보여줍니다. 이 두 계획(자식 번호 0과 자식 번호 1, Oracle Database 번호가 0부터 시작)은 바인드변수 때문에 다릅니다. 문자열을 숫자와 비교할 때 암시적으로 to_number()가 문자열에 배치됩니다. to_number(x)를 인덱싱하지 않았으므로 첫 번째 커서에 대해 전체 테이블 스캔을 수행하고 VARCHAR2에 바인딩된 두 번째 커서는 인덱스를 사용합니다.

Code Listing 3: Plans for cursors 1 and 2

SQL> select * from table( dbms_xplan.display_cursor('1qqtru155tyz8', 0 ) );

SQL_ID  1qqtru155tyz8, child number 0
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
 
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 27112 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    30 | 27112   (1)| 00:00:02 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
     1 - filter(TO_NUMBER("X")=:B1)

SQL> select * from table( dbms_xplan.display_cursor('1qqtru155tyz8', 1 ) );

SQL_ID  1qqtru155tyz8, child number 1
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
 
Plan hash value: 2324989435
---------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    30 |     0   (0)|
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0010204 |     1 |       |     0   (0)|
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X"=:B1)

Listing 4 커서 3과 4(자식 번호 2와 3)에 대한 실행계획을 살펴봅니다. 커서 3과 4에 대한 계획은 처음 두 커서(커서 1, 2)와 같이 ALL_ROWS가 아닌 FIRST_ROWS로 최적화되었기 때문에 실행계획이 다를 수 있습니다. 따라서 옵티마이저 모드가 다르기 때문에 옵티마이저 환경이 다르므로 자식 커서도 다릅니다. V$SQL_SHARED_CURSOR를 통해 무엇이 다른지 확인할 수 있습니다.

Code Listing 4: Plans for cursors 3 and 4

SQL> select * from table( dbms_xplan.display_cursor('1qqtru155tyz8', 2 ) );

SQL_ID  1qqtru155tyz8, child number 2
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
 
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 27112 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    30 | 27112   (1)| 00:00:02 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("X")=:B1)
 
SQL> select * from table( dbms_xplan.display_cursor('1qqtru155tyz8', 3 ) );

SQL_ID  1qqtru155tyz8, child number 3
-------------------------------------
SELECT * FROM T LOOK_FOR_ME WHERE X = :B1
 
Plan hash value: 2324989435
---------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    30 |     0   (0)|
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0010204 |     1 |       |     0   (0)|
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("X"=:B1)
 
SQL> select child_number, 
       bind_mismatch B, 
       optimizer_mode_mismatch O
  from v$sql_shared_cursor
  where sql_id = '1qqtru155tyz8';

CHILD_NUMBER B O
------------ - -
0 N N
1 N N
2 N Y
3 N Y

V$SQL_SHARED_CURSOR를 사용하면 공유 풀에 지정된 SQL 문의 복사본이 두 개 이상 있는 이유에 대한 알 수 있습니다.