OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.4.0.24.05 ai Free
방법 : 오라클 23ai 신기능 테이블 엑세스 기록 확인 뷰(dba_table_access_stats)
오라클 23ai 부터 테이블 엑세스 기록을 확인할수 있는 뷰가 생김
v$table_access_stats 와 dba_table_access_stats, all_table_access_stats, user_table_access_stats 뷰를 이용해 확인 가능함
dba_ 뷰와 all_ 뷰에서는 오라클이 생성한 스키마에서 생성된 테이블 정보는 나오지 않음
만약 보고싶은 경우에는 오라클에서 생성한 스키마에 접속 후 user_ 뷰를 보면 해당 정보를 볼수있음
뷰 설명
v$table_access_stats
object_id(number) : 테이블이나 파티션의 object id
read_count(number) : 인스턴스 시작 이후 집계된 스캔 수
last_accessed_time(timestamp(0)) : 가장 최근 스캔 날짜 및 시간
con_id(number) : 데이터가 속한 컨테이너의 id
con_id 값 종류
0: 전체 cdb와 관련된 데이터가 포함된 행에 표시되는 값(cdb가 아닌 행에도 사용됨)
1: root에 관련된 데이터가 포함된 행에 표시되는 값
n: 데이터가 포함된 행의 해당 컨테이너 ID
*db 재기동시 정보가 모두 사라짐
dba,all,user_table_access_stats
table_owner(varchar2(128)) : 테이블 owner
table_name(varchar2(128)) : 테이블 명
partition_name(varchar2(128)) : 테이블 파티션 명(비파티션 테이블의 경우 null로 표시됨)
instance_id(number) : 테이블이나 파티션이 읽혀진 인스턴스 id
read_count(number) : 인스턴스 시작 이후 집계된 스캔 수
last_accessed_time(timestamp(0)) 가장 최근 스캔 날짜 및 시간
*db 재기동시 정보가 모두 사라짐
본문에서는 각 뷰들을 조회해보고 테이블 엑세스시 last_accessed_time 과 read_count 가 정확하게 올라가는지 테스트해고
cdb, pdb 별 동작 차이와 테이블간 조인시, dml시 어떻게 동작하는지 테스트해봄
추가로 이 뷰의 원본쿼리도 함께 확인해봄
테스트
1. pdb 일반 유저에서 pdb 일반 유저 데이터 조회
2. pdb 시스템 유저에서 pdb 일반 유저 데이터 조회
3. cdb 시스템 유저에서 pdb 일반 유저 데이터 조회
4. cdb 시스템 유저에서 pdb 시스템 유저 데이터 조회
5. cdb 시스템 유저에서 cdb 시스템 유저 데이터 조회
6. 인덱스 스캔하여 테이블 스캔할시 read_count 변화 확인
7. 인덱스만 스캔할시 read_count 변화 확인
8. 조인시 read_count 변화 확인
9. dml 시 read_count 변화 확인
10. last_accessed_time 결과 문제와 뷰 내부 조회
11. 기타 버그
테스트
1. pdb 일반 유저에서 pdb 일반 유저 데이터 조회
pdb 접속 후 컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "IMSI"
CON_NAME
------------------------------
FREEPDB1
CON_ID
------------------------------
3
|
현재 con_id가 3인 FREEPDB1 pdb에 imsi 유저로 접속한 상태
emp 테이블 읽는 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 13-JUL-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 13-JUL-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
|
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'EMP';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 86968 EMP 1 2024/06/22 05:13:36 0
|
1번 조회해서 read_count가 1로 표시됨
read_count와 last_accessed_time이 잘 표시되었지만 con_id가 3이 아닌 0으로 표시됨
all_table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_owner for a15
col table_name for a20
col partition_name for a20
select table_owner, table_name, partition_name, read_count, to_char(last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, instance_id
from all_table_access_stats
where 1=1
and table_owner = 'IMSI'
and table_name = 'EMP';
TABLE_OWNER TABLE_NAME PARTITION_NAME READ_COUNT LAST_ACCESSED_TIME INSTANCE_ID
--------------- -------------------- -------------------- ---------- ------------------- -----------
IMSI EMP 1 2024/06/22 05:13:36 1
|
정보가 잘 표시됨
2. pdb 시스템 유저에서 pdb 일반 유저 데이터 조회
pdb 접속 후 컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "SYS"
CON_NAME
------------------------------
FREEPDB1
CON_ID
------------------------------
3
|
현재 con_id가 3인 FREEPDB1 pdb에 sys 유저로 접속한 상태
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'EMP';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 86968 EMP 1 2024/06/22 05:13:36 0
|
read_count와 last_accessed_time이 잘 표시되었지만 con_id가 3이 아닌 0으로 표시됨
all_table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_owner for a15
col table_name for a20
col partition_name for a20
select table_owner, table_name, partition_name, read_count, to_char(last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, instance_id
from all_table_access_stats
where 1=1
and table_owner = 'IMSI'
and table_name = 'EMP';
TABLE_OWNER TABLE_NAME PARTITION_NAME READ_COUNT LAST_ACCESSED_TIME INSTANCE_ID
--------------- -------------------- -------------------- ---------- ------------------- -----------
IMSI EMP 1 2024/06/22 05:13:36 1
|
정보가 잘 표시됨
3. cdb 시스템 유저에서 pdb 일반 유저 데이터 조회
컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "SYS"
CON_NAME
------------------------------
CDB$ROOT
CON_ID
------------------------------
1
|
현재 con_id가 1인 FREE cdb에 sys 유저로 접속한 상태
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, cdb_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'EMP';
no rows selected
|
no rows로 나옴(pdb의 데이터를 읽지 못함)
all_table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col table_owner for a15
col table_name for a20
col partition_name for a20
select table_owner, table_name, partition_name, read_count, to_char(last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, instance_id
from all_table_access_stats
where 1=1
and table_owner = 'IMSI'
and table_name = 'EMP';
no rows selected
|
no rows로 나옴(pdb의 데이터를 읽지 못함)
cdb_objects 뷰에서 오브젝트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select owner, object_name, con_id
from cdb_objects
where owner = 'IMSI'
and object_name = 'EMP';
OWNER OBJECT_NAME CON_ID
--------------- -------------------- ----------
IMSI EMP 3
|
con_id 가 3 인 FREEPDB1 의 imsi 유저의 emp 테이블이 cdb_objects 에서는 잘보임
4. cdb 시스템 유저에서 pdb 시스템 유저 데이터 조회
cdb 접속 후 컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "SYS"
CON_NAME
------------------------------
FREEPDB1
CON_ID
------------------------------
3
|
현재 con_id가 3인 FREEPDB1 pdb에 sys 유저로 접속한 상태
pdb의 시스템 유저에서 샘플 테이블 생성 후 1회 조회
|
1
2
3
|
SQL>
create table pdb_t1 (col1 number);
select * from pdb_t1;
|
cdb 접속 후 컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "SYS"
CON_NAME
------------------------------
CDB$ROOT
CON_ID
------------------------------
1
|
현재 con_id가 1인 FREE cdb에 sys 유저로 접속한 상태
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, cdb_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'SYS'
and obj.object_name = 'PDB_T1';
no rows selected
|
no rows로 나옴(pdb의 데이터를 읽지 못함)
all_table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col table_owner for a15
col table_name for a20
col partition_name for a20
select table_owner, table_name, partition_name, read_count, to_char(last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, instance_id
from cdb_table_access_stats
where 1=1
and table_owner = 'SYS'
and table_name = 'PDB_T1';
no rows selected
|
no rows로 나옴(pdb의 데이터를 읽지 못함)
cdb_objects 뷰에서 오브젝트 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select owner, object_name, con_id
from cdb_objects
where owner = 'SYS'
and object_name = 'PDB_T1';
OWNER OBJECT_NAME CON_ID
--------------- -------------------- ----------
SYS PDB_T1 3
|
con_id 가 3 인 FREEPDB1 의 imsi 유저의 pdb_t1 테이블이 cdb_objects 에서는 잘보임
5. cdb 시스템 유저에서 cdb 시스템 유저 데이터 조회
cdb 접속 후 컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "SYS"
CON_NAME
------------------------------
CDB$ROOT
CON_ID
------------------------------
1
|
현재 con_id가 1인 FREE cdb에 sys 유저로 접속한 상태
시스템 유저에서 샘플 테이블 생성 후 조회
|
1
2
3
|
SQL>
create table cdb_t1 (col1 number);
select * from cdb_t1;
|
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, cdb_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'SYS'
and obj.object_name = 'CDB_T1';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
SYS 89530 CDB_T1 2 2024/06/22 05:44:18 0
|
read_count와 last_accessed_time이 잘 표시되었지만 con_id가 1이 아닌 0으로 표시됨
all_table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col table_owner for a15
col table_name for a20
col partition_name for a20
select table_owner, table_name, partition_name, read_count, to_char(last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, instance_id
from all_table_access_stats
where 1=1
and table_owner = 'SYS'
and table_name = 'CDB_T1';
no rows selected
|
no rows로 나옴(SYS 유저로 만든 테이블들은 dba_, all_ 뷰에서 보이지 않음)
user_table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col table_name for a20
col partition_name for a20
select table_name, partition_name, read_count, to_char(last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, instance_id
from user_table_access_stats
where 1=1
and table_name = 'CDB_T1';
TABLE_NAME PARTITION_NAME READ_COUNT LAST_ACCESSED_TIME INSTANCE_ID
-------------------- -------------------- ---------- ------------------- -----------
CDB_T1 2 2024/06/22 05:44:18 1
|
정보가 잘 표시됨
6. 인덱스 스캔하여 테이블 스캔할시 read_count 변화 확인
pdb 접속 후 컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "IMSI"
CON_NAME
------------------------------
FREEPDB1
CON_ID
------------------------------
3
|
현재 con_id가 3인 FREEPDB1 pdb에 imsi 유저로 접속한 상태
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'EMP';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 86968 EMP 1 2024/06/22 05:13:36 0
|
현재 read_count가 1임
auto trace 실행
|
1
|
SQL> set autotrace on
|
emp 테이블 읽는 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> select * from emp where empno = 7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 13-JUL-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
|
pk_emp 인덱스를 이용해 스캔 한 뒤 테이블에 엑세스함
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'EMP';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 86968 EMP 2 2024/06/22 05:49:29 0
|
read_count 가 2로 증가됨
read_count, last_accessed_time이 잘 표시되었지만 con_id가 3이 아닌 0으로 표시됨
all_table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_owner for a15
col table_name for a20
col partition_name for a20
select table_owner, table_name, partition_name, read_count, to_char(last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, instance_id
from all_table_access_stats
where 1=1
and table_owner = 'IMSI'
and table_name = 'EMP';
TABLE_OWNER TABLE_NAME PARTITION_NAME READ_COUNT LAST_ACCESSED_TIME INSTANCE_ID
--------------- -------------------- -------------------- ---------- ------------------- -----------
IMSI EMP 2 2024/06/22 05:49:29 1
|
정보가 잘 표시됨
7. 인덱스만 스캔할시 read_count 변화 확인
pdb 접속 후 컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "IMSI"
CON_NAME
------------------------------
FREEPDB1
CON_ID
------------------------------
3
|
현재 con_id가 3인 FREEPDB1 pdb에 imsi 유저로 접속한 상태
auto trace 실행
|
1
|
SQL> set autotrace on
|
emp 테이블의 empno만 읽는 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select empno from emp where empno = 7788;
EMPNO
----------
7788
Execution Plan
----------------------------------------------------------
Plan hash value: 56244932
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
|
테이블 엑세스 없이 pk_emp 인덱스만 스캔함
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'EMP';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 86968 EMP 2 2024/06/22 05:49:29 0
|
read_count와 last_accessed_time이 변경되지 않음
all_table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col table_owner for a15
col table_name for a20
col partition_name for a20
select table_owner, table_name, partition_name, read_count, to_char(last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, instance_id
from all_table_access_stats
where 1=1
and table_owner = 'IMSI'
and table_name = 'EMP';
TABLE_OWNER TABLE_NAME PARTITION_NAME READ_COUNT LAST_ACCESSED_TIME INSTANCE_ID
--------------- -------------------- -------------------- ---------- ------------------- -----------
IMSI EMP 2 2024/06/22 05:49:29 1
|
인덱스만 스캔하고 테이블까지 엑세스하지 않은 경우에는 read_count와 last_accessed_time이 변경되지 않음
8. 조인시 read_count 변화 확인
pdb 접속 후 컨테이너, 유저 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> show user con_name con_id
USER is "IMSI"
CON_NAME
------------------------------
FREEPDB1
CON_ID
------------------------------
3
|
현재 con_id가 3인 FREEPDB1 pdb에 imsi 유저로 접속한 상태
샘플 테이블 생성
|
1
2
3
4
5
6
7
|
SQL>
drop table join_test1;
drop table join_test2;
create table join_test1 (col1 number);
insert into join_test1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
commit;
create table join_test2 as select * from join_test1;
|
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'JOIN_TEST1';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89192 JOIN_TEST1 1 2024/06/22 06:31:31 0
|
현재 read_count가 1임
hash 조인 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
select /*+ gather_plan_statistics leading(b) use_hash(a) */ *
from join_test1 a, join_test2 b
where a.col1 = b.col1;
COL1 COL1
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
10 rows selected.
|
xplan 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5 (100)| | 10 |00:00:00.01 | 10 | 1 | | | |
|* 1 | HASH JOIN | | 1 | 82 | 1312 | 5 (0)| 00:00:01 | 10 |00:00:00.01 | 10 | 1 | 2546K| 2546K| 622K (0)|
| 2 | TABLE ACCESS FULL| JOIN_TEST2 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 2 | 1 | | | |
| 3 | TABLE ACCESS FULL| JOIN_TEST1 | 1 | 82 | 1066 | 2 (0)| 00:00:01 | 10 |00:00:00.01 | 8 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------
|
hash 조인으로 인해 join_test1 테이블에 1번 엑세스됨
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'JOIN_TEST1';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89192 JOIN_TEST1 2 2024/06/22 06:31:46 0
|
hash 조인을 이용해 join_test1 테이블을 1번 엑세스 했기때문에 read_count 가 1 증가함
nl 조인 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
select /*+ gather_plan_statistics leading(b) use_nl(a) */ *
from join_test1 a, join_test2 b
where a.col1 = b.col1;
COL1 COL1
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
10 rows selected.
|
xplan 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 10 |00:00:00.01 | 74 |
| 1 | NESTED LOOPS | | 1 | 82 | 1312 | 7 (0)| 00:00:01 | 10 |00:00:00.01 | 74 |
| 2 | TABLE ACCESS FULL| JOIN_TEST2 | 1 | 10 | 30 | 3 (0)| 00:00:01 | 10 |00:00:00.01 | 3 |
|* 3 | TABLE ACCESS FULL| JOIN_TEST1 | 10 | 8 | 104 | 0 (0)| | 10 |00:00:00.01 | 71 |
---------------------------------------------------------------------------------------------------------------------------
|
nl조인으로 인해 join_test1 테이블에 10번 엑세스됨(Starts 10)
v$table_access_stats 뷰 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'JOIN_TEST1';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89192 JOIN_TEST1 3 2024/06/22 06:33:09 0
|
nl 조인을 이용해 join_test1 테이블을 10번 엑세스 했지만 read_count 가 1만 증가함
플랜에서 10번 엑세스하더라도 한 쿼리단위로 read_count 가 증가하는듯함
9. dml 시 read_count 변화 확인
샘플 테이블 생성 후 조회
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
drop table dml_test purge;
create table dml_test (col1 number);
insert into dml_test values (1);
select * from dml_test;
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'DML_TEST';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89209 DML_TEST 1 2024/06/22 06:51:50 0
|
현재 read_count가 1임
데이터 insert 후 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> insert into dml_test values (1);
1 row created.
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'DML_TEST';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89209 DML_TEST 1 2024/06/22 06:51:50 0
|
read_count 값이 증가하지 않음
데이터 update 후 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> update dml_test set col1 = 2;
2 row updated.
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'DML_TEST';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89209 DML_TEST 2 2024/06/22 06:52:25 0
|
update 시 read_count 값이 증가함
데이터 delete 후 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> delete dml_test;
2 row deleted.
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'DML_TEST';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89209 DML_TEST 3 2024/06/22 06:52:43 0
|
delete 시 read_count 값이 올라감
rollback 후 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> rollback;
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'DML_TEST';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89209 DML_TEST 3 2024/06/22 06:52:43 0
|
rollback 을 해도 read_count 가 다시 내려가지는 않음
10. 시간표기 문제와 뷰 내부 조회
2024년 06월 22일 기준 테스트시 last_accessed_time 이 실제 시간이 아닌 9시간 전으로 표시되고 있음
sysdate 조회
|
1
2
3
4
5
6
7
|
SQL>
select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss') sysdt,
to_char(systimestamp, 'yyyy/mm/dd hh24:mi:ss') systs from dual;
SYSDT SYSTS
------------------- -------------------
2024/06/22 16:26:10 2024/06/22 16:26:10
|
실제 시간은 16시 26분임
dml 테스트 테이블 조회 후 재확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> select * from dml_test;
COL1
----------
1
SQL>
set lines 200 pages 1000
col owner for a15
col object_name for a20
select obj.owner, tas.object_id, obj.object_name, tas.read_count, to_char(tas.last_accessed_time, 'yyyy/mm/dd hh24:mi:ss') last_accessed_time, tas.con_id
from v$table_access_stats tas, dba_objects obj
where tas.object_id = obj.object_id
and obj.owner = 'IMSI'
and obj.object_name = 'DML_TEST';
OWNER OBJECT_ID OBJECT_NAME READ_COUNT LAST_ACCESSED_TIME CON_ID
--------------- ---------- -------------------- ---------- ------------------- ----------
IMSI 89209 DML_TEST 6 2024/06/22 07:26:22 0
|
직후에 dml 을 수행하고 확인 했을때 07시 26분으로 표시됨
9시간 차이가 남
원인을 확인하기 위해 v$table_access_stats 뷰의 원본 쿼리 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select view_definition
from v$fixed_view_definition
where view_name = 'GV$TABLE_ACCESS_STATS';
VIEW_DEFINITION
-------------------------------------------------------------------------------
select inst_id, objn, cnt
,cast(timestamp '1970-01-01 00:00:00'
+ NUMTODSINTERVAL(trunc(time / (24 * 60 * 60)), 'DAY')
+ NUMTODSINTERVAL(MOD(time, 24 * 60 * 60), 'SECOND') as timestamp(0)), con_id
from x$qesma_access_count;
|
x$qesma_access_count 이 뷰에서 time이라는 컬럼을 통해 last_accessed_time 을 계산함
검증을 위해 sysdate 도 같이 출력
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
select inst_id, objn, cnt, time,
cast(timestamp '1970-01-01 00:00:00'
+ NUMTODSINTERVAL(trunc(time / (24 * 60 * 60)), 'DAY')
+ NUMTODSINTERVAL(MOD(time, 24 * 60 * 60), 'SECOND') as timestamp(0)) last_accessed_time
,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') sysdt, con_id
from x$qesma_access_count
where objn = 89209;
INST_ID OBJN CNT TIME LAST_ACCESSED_TIME SYSDT CON_ID
---------- ---------- ---------- ---------- ---------------------- ------------------- ----------
1 89209 6 1719041182 22-JUN-24 07.26.22 AM 2024/06/22 16:26:47 0
|
89209(dml_test 테이블)에 실제 쿼리를 수행한 시간은 2024/06/22 16:26:22임
하지만 기존 수식으로 계산했을때 07시 26분으로 표시됨(LAST_ACCESSED_TIME)
gpt를 통해 개선된 계산식을 만들어냄
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
SELECT inst_id, objn, cnt, time,
CAST(
FROM_TZ(
TIMESTAMP '1970-01-01 00:00:00'
+ NUMTODSINTERVAL(TRUNC(time / (24 * 60 * 60)), 'DAY')
+ NUMTODSINTERVAL(MOD(time, 24 * 60 * 60), 'SECOND'),
'UTC'
) AT TIME ZONE SESSIONTIMEZONE AS TIMESTAMP(0)
) AS last_accessed_time,
TO_CHAR(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') AS sysdt,
con_id
FROM x$qesma_access_count
WHERE objn = 89209;
INST_ID OBJN CNT TIME LAST_ACCESSED_TIME SYSDT CON_ID
---------- ---------- ---------- ---------- ---------------------- ------------------- ----------
1 89209 6 1719041182 22-JUN-24 04.26.22 PM 2024/06/22 16:26:57 0
|
이제 04.26.22 PM(16시 26분 22초)로 정상적으로 표시됨
v$뷰와 dba 뷰 플랜 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
SQL> set autotrace on
SQL> select * from v$table_access_stats where 1=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2775249253
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | FIXED TABLE FULL| X$QESMA_ACCESS_COUNT | 514 | 10794 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
SQL> select * from dba_table_access_stats where 1=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3263713895
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 24 | 2448 | 58 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$QESMA_ACCESS_COUNT | 514 | 10794 | 0 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 3472 | 274K| 58 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| USER$ | 1 | 24 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_OBJ2 | 3472 | 193K| 53 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
|
두가지 뷰 모두 X$QESMA_ACCESS_COUNT 를 사용함
v$뷰는 x$뷰 하나만 읽고, dba_ 뷰는 x$ 뷰와 user$ 뷰 같이 사용함(table_owner 정보를 가져오기 위함인듯함)
X$QESMA_ACCESS_COUNT 뷰 구조 확인
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> desc X$QESMA_ACCESS_COUNT
Name Null? Type
---------- -------- --------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
CON_ID NUMBER
OBJN NUMBER
CNT NUMBER
TIME NUMBER
|
두가지 뷰 모두 X$QESMA_ACCESS_COUNT 를 사용하기 때문에
재기동시 데이터가 두가지 뷰 모두에서 초기화됨
그래서 컬럼 설명에 "read_count(number) : 인스턴스 시작 이후 집계된 스캔 수" 라고 명시되어 있음
11. 기타 버그
테스트를 하던 도중 갑자기 pdb의 일반유저에서 유저 테이블을(emp 등) 조회할때 v$table_access_stats나 all_table_access_stats 에 데이터가 안쌓이는것을 확인함
재기동, 로그스위치, 체크포인트 등등을 시도해 보았지만 현상은 동일했음
그러던중 혹시나 싶어 statistics_level 를 all로 변경한 뒤 조회해보니 정상적으로 수집이 되었음
이후 세션을 재접속하여 statistics_level 를 기본값인 typical 상태로 만든다음 다시 테이블을 조회했을때
여전히 데이터가 정상적으로 쌓이는것을 확인하였음
(테스트를 하면서 착오했을수도 있지만 참고용으로 작성해둠)
결론 :
1. pdb 일반 유저에서 pdb 일반 유저 데이터 조회
: read_count와 last_accessed_time이 정상적으로 업데이트되나 con_id가 3이 아닌 0으로 표시됨
2. pdb 시스템 유저에서 pdb 일반 유저 데이터 조회
: read_count와 last_accessed_time이 정상적으로 업데이트되나 con_id가 3이 아닌 0으로 표시됨.
3. cdb 시스템 유저에서 pdb 일반 유저 데이터 조회
: v$table_access_stats와 all_table_access_stats에서 pdb의 데이터를 읽지 못함
4. cdb 시스템 유저에서 pdb 시스템 유저 데이터 조회
: v$table_access_stats와 all_table_access_stats에서 pdb의 데이터를 읽지 못함
5. cdb 시스템 유저에서 cdb 시스템 유저 데이터 조회
: read_count와 last_accessed_time이 정상적으로 업데이트되나 con_id가 1이 아닌 0으로 표시됨
6. 인덱스 스캔하여 테이블 스캔할시 read_count 변화 확인
: 인덱스를 통한 테이블 스캔 시 read_count가 증가함
7. 인덱스만 스캔할시 read_count 변화 확인
: 인덱스만 스캔할 경우 read_count가 증가하지 않음
8. 조인시 read_count 변화 확인
: 조인 시 read_count가 쿼리 단위로 증가하며, 조인 횟수만큼 증가하지 않음
9. dml 시 read_count 변화 확인
: insert는 read_count에 영향을 주지 않으나, update와 delete는 read_count를 증가시킴, commit, rollback 에 영향을 받지 않고 dml 직후 read_count가 증가함
10. last_accessed_time 결과 문제와 뷰 내부 조회
: last_accessed_time이 실제 시간보다 9시간 전으로 표시되며, 개선된 계산식을 통해 정상적으로 표시되도록 할 수 있음
11. 기타 버그
: 테스트중 가끔 v$table_access_stats에 데이터가 수집되지 않는 경우가 발생했는데,
이때 statistics_level을 all로 설정 후 데이터를 조회하면 그 이후부터 데이터가 정상적으로 수집됨
테이블 액세스 기록 뷰는 테이블 엑세스 패턴을 분석하는 데 유용하지만, 현재 몇 가지 문제점이 존재함
con_id가 정확하게 표시되지 않는 문제와 last_accessed_time의 시간대 문제 등
free 버전이라서 그런것일수도 있어서 추후 버전에서 개선될것으로 보이고 free 버전에서 테스트시에는 이점을 인지하고 테스트 해야할듯함
20240627 업데이트 : 오라클에서 이 사실을 인지했고, 수정할 예정이라고함
https://forums.oracle.com/ords/apexds/post/v-table-access-stats-last-accessed-time-issue-7048
참조 :
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1929
https://docs.oracle.com/en/database/oracle/oracle-database/23/nfcoa/appdev_general.html#GUID-93601-2
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/DBA_TABLE_ACCESS_STATS.html
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/ALL_TABLE_ACCESS_STATS.html
https://docs.oracle.com/en/database/oracle/oracle-database/23//refrn/V-TABLE_ACCESS_STATS.html
https://forums.oracle.com/ords/apexds/post/v-table-access-stats-last-accessed-time-issue-7048
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 세미나 발표자료(202406) (0) | 2024.06.29 |
|---|---|
| 오라클 23ai 신기능 sqlplus 추가 명령 (0) | 2024.06.26 |
| 오라클 23ai 10046 트레이스 이전 버전과 차이점 비교 (0) | 2024.06.20 |
| 오라클 23ai 신기능 Table Value Constructor(TVC) (0) | 2024.06.18 |
| 오라클 19c 인덱스 리빌드 전후 용량 차이 확인 테스트 (0) | 2024.06.17 |