Shulou(Shulou.com)05/31 Report--

This article mainly explains "analyzing parameter table management problems in SQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "analyzing parameter table management problems in SQL".

1. Query the SQL to be executed, whether there are child cursors, no child cursors

SYS@honor > select sql_id,child_number,sql_text from v$sql where sql_text like'% SELECT count (*) FROM t%' SQL_ID CHILD_NUMBER SQL_TEXT--gxfdy6zpjvmtc 0 select sql_id Child_number,sql_text from v$sql where sql_text like'% SELECT coun

two。 Query the current session sid,session1 to execute related stored procedures

Session1 > select * from v$mystat where statistic#=0 SID STATISTIC# VALUE -49 0session1 > DECLARE 2 a number 3 v varchar2 (20): = ''; 4 BEGIN 5 FOR c IN 1.. 100000 6 LOOP 7 EXECUTE IMMEDIATE' ALTER SESSION SET optimizer_index_cost_adj = 1 million; EXECUTE IMMEDIATE 'SELECT count (*) FROM t'into a; 9 END LOOP; 10 END; 11 / PL/SQL procedure successfully completed.

two。 Query the current session sid,session2 to execute related stored procedures

Session2 > select * from v$mystat where statistic#=0 SID STATISTIC# VALUE -- 58 0 # session2 > DECLARE 2 a number 3 v varchar2 (20): = ''; 4 BEGIN 5 FOR c IN 1.. 100000 6 LOOP 7 EXECUTE IMMEDIATE' ALTER SESSION SET optimizer_index_cost_adj = 2 million; EXECUTE IMMEDIATE 'SELECT count (*) FROM t'into a; 9 END LOOP; 10 END; 11 / PL/SQL procedure successfully completed.

3. Query SQL subcursors and found that there are two subcursors

SYS@honor > select sql_id,child_number,sql_text from v$sql where sql_text like'% SELECT count (*) FROM t%' SQL_ID CHILD_NUMBER SQL_TEXT--5nbjnx26pn4rh 0 DECLARE a number BEGIN FOR c IN 1.. 100000 LOOP EXECUTE IMMEDIAT5tjqf7sx5dzmj 0 SELECT count (*) FROM t5tjqf7sx5dzmj 1 SELECT count (*) FROM tf14srtthcadyq 0 DECLARE a number; v varchar2 (20): = ''; BEGIN FOR c IN 1.. 100000 Lgxfdy6zpjvmtc 0 select sql_id,child_number,sql_text from v$sql where sql_text like'% SELECT coun

4. During the execution of the query session, wait for the event. According to the principle, during the execution of the two stored procedures, the select sentence cursor will cause cursor pin S wait on x contention caused by the handle of the parent cursor pointing to the child cursor, but it is unexpectedly found that latch free occurs. After querying the latch type, it is parameter table management, and has not seen the relevant latch. To check the Mos, it is found that the bug is hit, fixed in 12.2, or patched.

SYS@honor1 > select sid,USERNAME,EVENT,sql_id,SQL_CHILD_NUMBER,p1,p2,p3,WAIT_CLASS,WAIT_TIME,BLOCKING_SESSION,BLOCKING_SESSION_STATUS from v$session where sid in ('49th June 58') SID USERNAME EVENT SQL_ID SQL_CHILD_NUMBER P1 P2 P3 WAIT_CLASS WAIT_TIME BLOCKING_SESSION BLOCKING_SESSION_STATUS-- -49 LIBAI latch free 5nbjnx26pn4rh 0 1610665040 24 0 Other-1 NOT IN WAIT 58 LIBAI latch free 5tjqf7sx5dzmj 1 1610665040 24 0 Other-1 NOT IN WAIT SYS@honor1 > select to_char ('1610665040' 'xxxxxxxxxxxx') from dual TO_CHAR ('1610-6000cc50SYS@honor1 > select addr,latch#,hash,name from v$latch where addr like'% 6000CC50%' ADDR LATCH# HASH NAME -000000006000CC50 24 722869772 parameter table management

5.bug description:

Session Waiting on 'Parameter Table Management' Latch (Doc ID 2271591.1)

APPLIES TO:

Oracle Database-Enterprise Edition-Version 11.2.0.4 to 12.1.0.2 [Release 11.2 to 12.1]

Oracle Database Cloud Schema Service-Version NCMA and later

Oracle Database Exadata Cloud Machine-Version NCMA and later

Oracle Cloud Infrastructure-Database Service-Version Nhamba An and later

Oracle Database Exadata Express Cloud Service-Version NCMA and later

Information in this document applies to any platform.

SYMPTOMS

Database wait's for latch free

ASH report shows following event values:

Top Event P1/P2/P3 Values

Event% Event P1 Value, P2 Value, P3 Value% Activity Parameter 1 Parameter 2 Parameter 3

Latch free 24.60 "1610670304", "30", "0" 24.52 address number tries

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.