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

This article mainly introduces oracle how to find the unbound variable sql within a period of time, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

The method of finding the unbound variable sql over a period of time

The FORCE_MATCHING_SIGNATURE column in the v$SQL dynamic performance view can help us quickly navigate to the sql of unbound variables. The column means "The signature used when the CURSOR_SHARING parameter is set to FORCE". This means that if the CURSOR_SHARING parameter is set to force,sql text, a feature value is calculated under this parameter. Sql,oracle with the same feature value believes that cursors can be shared by binding variables to reduce hard parsing.

Query the unbound variable sql over a period of time

When there is hard parsing in the database, you can also use the following statement to catch the problem sql

You can also remove the time constraints and simply find the unbound variable sql for all periods of time in the database.

Select * from v$sql where FORCE_MATCHING_SIGNATURE in (select a.FORCE_MATCHING_SIGNATURE from (

Select to_char (FORCE_MATCHING_SIGNATURE) FORCE_MATCHING_SIGNATURE

Count (1) counts

From v$sql

Where FORCE_MATCHING_SIGNATURE > 0

And FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

And EXECUTIONS 100

Order by 2 desc) a)

You can replace the old one with the sql above.

SELECT substr (sql_text, 1,60), count (1)

FROM v$sql

GROUP BY substr (sql_text, 1,60)

HAVING count (1) > 10

ORDER BY 2

The idea of this sql is basically to intercept the sql text before the where condition, because the difference between shareable sql lies in the where condition.

There is another script, but the execution speed of that script is slow and the readability of the result is poor. Like this

SQL > @ find_literal.sql

Literal:select null from optstat_hist_control$ where sn address: 0000000082C6AA78

Literal:select sd.inst_id, ts.tsnam, segment_file, segment_block, ex address: 0000000085ADE880

Literal:SELECT S.SCHEMA, S.QUEUE_ID, S.SUBSCRIBER_ID, S.QUEUE_NAME, address: 0000000080198268

Literal:SELECT / * + qb_name (gv_ashv) no_merge ordered use_nl (a) * / address: 0000000085B690F0

Literal:select NUM, NAME, TYPE, VALUE, DISPLAY_VALUE, DEFAULT_V address: 000000007845B490

Literal:select count (*) from undo$ address: 00000000839B7D30

Literal:select inst_id, kqlfxpl_phad, kqlfxpl_hash address: 0000000070CB2458

Literal:select tsn, tsv from x$ktfbnstat where flag = 1 address: 0000000083FDFBC0

Literal:select FORCE_MATCHING_SIGNATURE, count (1) from v$sql wher address: 000000006AC3DD48

Literal:SELECT / * + NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) * address: 0000000080E6E4E8

Literal:select s.inst_id,s.addr,s.indx,s.ksuseser,s.ksuudses,s.ksuse address: 000000007AA44288

Literal:select / * test*/ * from t_flash where OBJECT_ID=23708 address: 0000000076DCE188

Literal:select max (FA#) from SYS_FBA_FA address: 0000000087A37A90

Literal:SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER ('address: 00000000792A3F20)

Literal:select sid, db_sid, serial#, con_id from gv$xs_sessions whe address: 00000000675FF790

Literal:select count (*) from SYS.chnf$_reg_queries address: 0000000087B8B7E8

Literal:SELECT DECODE ('Achilles, Magna, pas,') FROM SYS.DUAL address: 0000000077B63FE8

Literal:select sql_id,child_number,open_versions,PARSE_CALLS,IS_OBSO address: 0000000072F2B578

Literal:SELECT INST_ID, USERID, OBJID, ID_TYPE, NAME, DECODE address: 0000000065CE9BB8

Literal:select / * test*/ * from t_flash where OBJECT_ID=23731 address: 00000000736ED5B0

Literal:select x.installeiddiary x.indxpost1 ksppstdv address: ksppin

Literal:select sum (used_blocks), ts.ts# from GV$SORT_SEGMENT gv, t address: 0000000083FC9870

Literal:SELECT / * + OPT_PARAM ('_ parallel_syspls_obey_force' 'false') address: 0000000082CEC6C0

Literal:SELECT DECODE (USER, 'XS$NULL', XS_SYS_CONTEXT (' XS$SESSION', address: 0000000065184210)

Literal:select ts#, inc# from ts$ where online$=1 and bitand (flags,1 address: 0000000083FD0918

Literal:SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE F address: 000000006B90E120

Literal:select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SI address: 000000008784F698

Literal:select / * test*/ * from t_flash where OBJECT_ID='23731' address: 00000000619F2018

Literal:select max (scn) from smon_scn_time address: 000000008399B9C8

Literal:SELECT / * + NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) * address: 0000000080F15FC8

Literal:select inst_id, sessid, dbsessnum, dbsernum, con_id from Xerox address: 000000007BF4F150

Literal:SELECT / * + NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) * address: 0000000080E25790

Literal:select sql_text from v$sql where FORCE_MATCHING_SIGNATURE in address: 00000000675B9180

Literal:select decode (u.typewriter, 2, u.ext_username, u.name), o.name, address: 00000000839D58F8

Literal:select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 000000007799CD88

Literal:select sql_id,sql_text,child_number,open_versions,PARSE_CALL address: 00000000709E5730

PL/SQL procedure successfully completed.

Experimental process

SQL > conn ming/oracle@mingpdb1

Connected.

SQL > sho user con_name

USER is "MING"

CON_NAME

-

MINGPDB1

Alter system flush shared_pool

SQL > sho parameter cursor_shar

NAME TYPE VALUE

-

Cursor_sharing string EXACT

Execute:

Select / * test*/ * from t_flash where OBJECT_ID=23731

Select / * test*/ * from t_flash where OBJECT_ID=23708

Select / * test*/ * from t_flash where OBJECT_ID='23731'

Set line 300

Col SQL_TEXT for a55

Col FORCE_MATCHING_SIGNATURE for 999999999999999999999

Col EXACT_MATCHING_SIGNATURE for 999999999999999999999

Select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE

FROM V$SQL

WHERE sql_text like'% test%'

And sql_text not like'% like%'

SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE

Select / * test*/ * from t_flash where OBJECT_ID='23731' 13459100552049599574 16467051488950643767

Select / * test*/ * from t_flash where OBJECT_ID=23731 13459100552049599574 5586102026751624810

Select / * test*/ * from t_flash where OBJECT_ID=23708 13459100552049599574 636726165116306616

You can see that sql,oracle, which involves implicit conversions and different values, thinks that the problem that cursors cannot be shared can be solved by setting CURSOR_SHARING to force.

In actual production, the number that count (*) is greater than needs to be changed a little bit.

Select FORCE_MATCHING_SIGNATURE, count (1)

From v$sql

Where FORCE_MATCHING_SIGNATURE > 0

And FORCE_MATCHING_SIGNATURE! = EXACT_MATCHING_SIGNATURE

Group by FORCE_MATCHING_SIGNATURE

Having count (1) > 1

Order by 2

FORCE_MATCHING_SIGNATURE COUNT (1)

-

13389370700329599909 2

13459100552049599574 3

Continue to search based on the above results:

SQL > select sql_text from v$sql where FORCE_MATCHING_SIGNATURE in ('13389370700329599909')

SQL_TEXT

SELECT SYS_CONTEXT ('USERENV','cdb_name') FROM DUAL

Select / * test*/ * from t_flash where OBJECT_ID='23731'

Select / * test*/ * from t_flash where OBJECT_ID=23731

Select / * test*/ * from t_flash where OBJECT_ID=23708

SELECT SYS_CONTEXT ('USERENV','con_id') FROM DUAL

The corresponding 3 of 13459100552049599574 are the three sql initiated in the experiment.

Thank you for reading this article carefully. I hope the article "how to find the unbound variable sql within a period of time" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.