잡학문구

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 문의 복사본이 두 개 이상 있는 이유에 대한 알 수 있습니다.