Huaiyao's Notes

Check long running session

Check one long running session which reported by dev.

1
2
3
4
5
6
7
8
9
10
11
12
SQL> @auw
SID/ OS_PID/ Minutes Minutes Blocking
USERNAME OSUSER SERIAL# RMT_OS Wait Left MACHINE EVENT SQL_ID Session PGM
---------------------- ------------------ ------------ --------------- --------- --------- ------------ -------------------------------------------------- ------------- ---------- ----------------------
SYS oracle 352/12715 53533/53531 .0 rjind000dbs01 db file sequential read() 46qjvtr2pjrka sqlplus@rjind000dbs01
RJIN_APP zhiaaang 1382/7489 47675/5077 .0 7.4 LM-AAA-16501 db file scattered read(USERS) 8syhyu6g3h9m1 SQL Developer
RJIN_APP_01 jboss 1250/2433 43536/1234 .0 .0 rjinq077job00 db file sequential 13mjuu7rvjvcw JDBC Thin Client
read(S_TRANSACTIONS.SYS_P96126)
3 rows selected.

Check session 1382/7489

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
SQL> @gq
sql_id:8syhyu6g3h9m1
display plans (n):y
update locale set locale = 'en_ZA',
language_code = 'EN',
language_display_name = 'English'
where
country_code = 'ZA'
Displaying current cached plans
####################################################################################################################################################
SQL_ID 8syhyu6g3h9m1, child number 0
Plan hash value: 2565882638
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 2 (100)| |
| 1 | UPDATE | LOCALE | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| LOCALE | 1 | 29 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | LOCALE_UK2 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - ccess("COUNTRY_CODE"='ZA')

INDEX RANGE SCAN, it should be pretty fast.
Trace the session to see what’s doing now.

1
2
3
SQL> exec dbms_monitor.session_trace_enable(1382,7489,waits=>true,binds=>true);
PL/SQL procedure successfully completed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
D16QE077:/opt/oracle/diag/rdbms/d16qe077/D16QE077/trace>tail -100f D16QE077_ora_47675.trc
WAIT #18446744071440529048: nam='db file scattered read' ela= 5003 file#=272 block#=3302344 blocks=16 obj#=88714 tim=29907571200857
WAIT #18446744071440529048: nam='db file scattered read' ela= 4821 file#=272 block#=3302360 blocks=16 obj#=88714 tim=29907571206023
WAIT #18446744071440529048: nam='db file scattered read' ela= 4220 file#=272 block#=3302376 blocks=16 obj#=88714 tim=29907571210658
WAIT #18446744071440529048: nam='db file scattered read' ela= 4043 file#=272 block#=3302392 blocks=16 obj#=88714 tim=29907571215166
WAIT #18446744071440529048: nam='db file scattered read' ela= 4962 file#=272 block#=3302408 blocks=16 obj#=88714 tim=29907571220542
WAIT #18446744071440529048: nam='db file scattered read' ela= 5143 file#=272 block#=3302424 blocks=16 obj#=88714 tim=29907571226078
WAIT #18446744071440529048: nam='db file scattered read' ela= 5338 file#=272 block#=3302440 blocks=16 obj#=88714 tim=29907571231810
WAIT #18446744071440529048: nam='db file scattered read' ela= 6148 file#=272 block#=3302456 blocks=16 obj#=88714 tim=29907571238274
WAIT #18446744071440529048: nam='db file scattered read' ela= 5328 file#=272 block#=3302472 blocks=16 obj#=88714 tim=29907571244020
WAIT #18446744071440529048: nam='db file scattered read' ela= 5023 file#=272 block#=3302488 blocks=16 obj#=88714 tim=29907571249487
WAIT #18446744071440529048: nam='db file scattered read' ela= 4845 file#=272 block#=3302504 blocks=16 obj#=88714 tim=29907571254596
WAIT #18446744071440529048: nam='db file scattered read' ela= 4918 file#=272 block#=3302520 blocks=16 obj#=88714 tim=29907571259845
WAIT #18446744071440529048: nam='db file scattered read' ela= 4851 file#=272 block#=3302536 blocks=16 obj#=88714 tim=29907571265039
WAIT #18446744071440529048: nam='db file scattered read' ela= 5726 file#=272 block#=3302552 blocks=16 obj#=88714 tim=29907571271179
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> select * from dba_objects where object_id=88714
2 @pr
Pivoting output....
==============================
OWNER : RJIN
OBJECT_NAME : USERS
SUBOBJECT_NAME :
OBJECT_ID : 88714
DATA_OBJECT_ID : 3043517
OBJECT_TYPE : TABLE
CREATED : 31-OCT-13
LAST_DDL_TIME : 07-JUN-17
TIMESTAMP : 2017-03-23:01:28:00
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
NAMESPACE : 1
EDITION_NAME :
PL/SQL procedure successfully completed.

The session is scanning USERS table.

Check FK constraint.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL>
col TABLE_NAME for a50
col CONSTRAINT_NAME for a50
SELECT table_name, constraint_name,status
FROM dba_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name
FROM dba_CONSTRAINTS
7 WHERE table_name = upper('LOCALE') AND constraint_type IN ('U', 'P'));
TABLE_NAME CONSTRAINT_NAME STATUS
-------------------------------------------------- -------------------------------------------------- --------
USERS USER_PREFERRED_LOCALE_FK ENABLED
...
...

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
```
SQL> select * from dba_constraints where constraint_name='USER_PREFERRED_LOCALE_FK'
2 @pr
Pivoting output....
==============================
OWNER : RJIN
CONSTRAINT_NAME : USER_PREFERRED_LOCALE_FK
CONSTRAINT_TYPE : R
TABLE_NAME : USERS
SEARCH_CONDITION :
R_OWNER : RJIN
R_CONSTRAINT_NAME : LOCALE_UK
DELETE_RULE : NO ACTION
STATUS : ENABLED
DEFERRABLE : NOT DEFERRABLE
DEFERRED : IMMEDIATE
VALIDATED : NOT VALIDATED
GENERATED : USER NAME
BAD :
RELY :
LAST_CHANGE : 07-JUN-17
INDEX_OWNER :
INDEX_NAME :
INVALID :
VIEW_RELATED :
PL/SQL procedure successfully completed.
1
2
3
4
5
6
7
8
9
10
SQL> select dbms_metadata.get_ddl('TABLE','USERS','RJIN') a from dual;
CREATE TABLE "RJIN"."USERS"
...
"PREFERRED_LOCALE" VARCHAR2(10 CHAR) DEFAULT 'en_US',
...
CONSTRAINT "USER_PREFERRED_LOCALE_FK" FOREIGN KEY ("PREFERRED_LOCALE")
REFERENCES "RJIN"."LOCALE" ("LOCALE") ENABLE NOVALIDATE
...
...

Also, after checking, there is no index in the fk key column in child table.
So updating parent table causes full table scan in child table due to fk.

After creating index, the update completes very fast.

1
05:43:29 SQL> create index rjin.USERS_PREFERRED_LOCALE_IDX on rjin.users(PREFERRED_LOCALE) parallel 4;
1
2
3
4
5
6
7
8
9
update locale set locale = 'en_ZA',
language_code = 'EN',
language_display_name = 'English'
where
05:53:03 5 country_code = 'ZA';
1 row updated.
Elapsed: 00:00:00.30