Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 69B5010033C8CD for ; Thu, 28 Jun 2018 15:43:58 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D57D623FA5; Thu, 28 Jun 2018 09:43:56 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1530193436; bh=UDVbs5ZjbXp9ADA28A4YcQ7irWcgSuk1cwQN+wC942w=; h=From:To:Subject:Date:Reply-To:List-help:List-unsubscribe:List-Id: List-subscribe:List-owner:List-post:List-archive; b=Pis7YQORclI2/NHoONYKn1vGg2XaYHj41rbDDTQiQXZkUWKpbM0omuWPSg5YeCWvy IpkF7AX0qneRELeDpTneZWQc/8Ab/hBTJg8ftZZdrBy/xHMvFo4TJB99Q74rs/JHJS TQld82ovS56gNiGD2UpnlxRmyRbP9nSFk1PTT23s= X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id dRTF31Pv1fnO; Thu, 28 Jun 2018 09:43:56 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EEED120113; Thu, 28 Jun 2018 09:43:43 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1530193436; bh=UDVbs5ZjbXp9ADA28A4YcQ7irWcgSuk1cwQN+wC942w=; h=From:To:Subject:Date:Reply-To:List-help:List-unsubscribe:List-Id: List-subscribe:List-owner:List-post:List-archive; b=Pis7YQORclI2/NHoONYKn1vGg2XaYHj41rbDDTQiQXZkUWKpbM0omuWPSg5YeCWvy IpkF7AX0qneRELeDpTneZWQc/8Ab/hBTJg8ftZZdrBy/xHMvFo4TJB99Q74rs/JHJS TQld82ovS56gNiGD2UpnlxRmyRbP9nSFk1PTT23s= Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 28 Jun 2018 09:42:22 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1489621979 for ; Thu, 28 Jun 2018 09:42:22 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id G0dwYJXx37oy for ; Thu, 28 Jun 2018 09:42:21 -0400 (EDT) Received: from smtp1.vontobel.ch (smtp1.vontobel.ch [194.56.165.25]) (using TLSv1.2 with cipher AECDH-AES256-SHA (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 9BA422160B for ; Thu, 28 Jun 2018 09:42:21 -0400 (EDT) Received: from smtp1.vontobel.ch (localhost [127.0.0.1]) by smtp1.vontobel.ch (Mission Control Email Shield, 18401) with SMTP id 41Ggv00gNSzcWr9 for ; Thu, 28 Jun 2018 15:42:20 +0200 (MEST) Received: from trusted client by smtp1.vontobel.ch; Thu Jun 28 15:42:20 2018 Authentication-Results: vonn-mx001-ch-zur-1; dkim=none Received: from trusted client by smtp1.vontobel.ch; Thu Jun 28 15:42:20 2018 Received: from trusted client by smtp1.vontobel.ch; Thu Jun 28 15:42:20 2018 Received: from trusted client by smtp1.vontobel.ch; Thu Jun 28 15:42:20 2018 From: Noveljic Nenad To: "ORACLE-L (oracle-l@freelists.org)" Subject: sql_id in ADS trace Thread-Topic: sql_id in ADS trace Date: Thu, 28 Jun 2018 13:42:13 +0000 Message-ID: <77834_1530193340_5B34E5BC_77834_8602_1_ECDEF0CC6716EC4596FCBC871F48292AB19C43D9@ZRH-S231> Accept-Language: de-CH, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-moderation-data: 28.06.2018 15:42:18 Content-Type: multipart/alternative; boundary="_000_ECDEF0CC6716EC4596FCBC871F48292AB19C43D9ZRHS231_" MIME-Version: 1.0 X-archive-position: 71800 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: nenad.noveljic@vontobel.com Precedence: normal Reply-To: nenad.noveljic@vontobel.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --_000_ECDEF0CC6716EC4596FCBC871F48292AB19C43D9ZRHS231_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable A DYNAMIC_SAMPLING_RESULT sql plan directive got created for a join cardina= lity misestimate. The dynamic sampling sql_id stored in notes is 7wckkp93ya8a2: NEWNO{(V.T1, num_rows=3D1001000) - (V.T2, num_rows=3D1001) - (SQL_ID:7wckk= p93ya8a2, T.CARD=3D943807[-2 -2])} The sql_id in the RDBMS.ADS trace is encoded differently: kkoadsComputeSqlid: sql_id=3D9093421488918569282: newText=3DSELECT /* DS_SV= C */ NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "= T1#1") */ 1 AS C1 FROM "T1" "T1#1", "T2" "T2#0" WHERE ("T2#0"."N2"=3D2) AN= D ("T1#1"."N1"=3D"T2#0"."N1") AND ("T1#1"."N2"=3D2) AND ("T1#1"."N3"=3D2)) = innerQuery, startPos =3D 20, stopPos =3D 120, newTextLen =3D 244, sqlLen = =3D 343 How can I convert the sql_id from the RDBMS.ADS into the conventional forma= t? Many thanks in advance. Nenad http://nenadnoveljic.com/blog/ ____________________________________________________ Please consider the environment before printing this e-mail. Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.


Important Notice

This message is intended only for the individual named. It may contain conf= idential or privileged information. If you are not the named addressee you = should in particular not disseminate, distribute, modify or copy this e-mai= l. Please notify the sender immediately by e-mail, if you have received thi= s message by mistake and delete it from your system.
Without prejudice to any contractual agreements between you and us which sh= all prevail in any case, we take it as your authorization to correspond wit= h you by e-mail if you send us messages by e-mail. However, we reserve the = right not to execute orders and instructions transmitted by e-mail at any t= ime and without further explanation.
E-mail transmission may not be secure or error-free as information could be= intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also p= rocessing of incoming e-mails cannot be guaranteed. All liability of Vontob= el Holding Ltd. and any of its affiliates (hereinafter collectively referre= d to as "Vontobel Group") for any damages resulting from e-mail use is excl= uded. You are advised that urgent and time sensitive messages should not be= sent by e-mail and if verification is required please request a printed ve= rsion.
Please note that all e-mail communications to and from the Vontobel Group a= re subject to electronic storage and review by Vontobel Group. Unless state= d to the contrary and without prejudice to any contractual agreements betwe= en you and Vontobel Group which shall prevail in any case, e-mail-communica= tion is for informational purposes only and is not intended as an offer or = solicitation for the purchase or sale of any financial instrument or as an = official confirmation of any transaction.
The legal basis for the processing of your personal data is the legitimate = interest to develop a commercial relationship with you, as well as your con= sent to forward you commercial communications. You can exercise, at any tim= e and under the terms established under current regulation, your rights. If= you prefer not to receive any further communications, please contact your = client relationship manager if you are a client of Vontobel Group or notify= the sender. Please note for an exact reference to the affected group entity the corpora= te e-mail signature. For further information about data privacy at Vontobel Group please consult= www.vontobel.com.

--_000_ECDEF0CC6716EC4596FCBC871F48292AB19C43D9ZRHS231_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

A DYNAMIC_SAMPLING_RESULT = sql plan directive got created for a join cardinality misestimate.

 

The dynamic sampling sql_i= d stored in notes is 7wckkp93ya8a2:

 

<spd_note><intern= al_state>NEW</internal_state><redundant>NO</redundant>= <spd_text>{(V.T1, num_rows=3D1001000) - (V.T2, num_rows=3D1001) - (SQL_ID:7wckkp93ya8a2, T.CARD=3D943807[-2 -2])}</spd_text></spd_note>

 

The sql_id in the RDBMS.AD= S trace is encoded differently:

 

kkoadsComputeSqlid: sql_id=3D9093421488918569282: newText=3DSE= LECT /* DS_SVC */  NVL(SUM(C1),0) FROM (SELECT /*+ qb_name("i= nnerQuery") NO_INDEX_FFS( "T1#1")  */ 1 AS C1 FROM &quo= t;T1" "T1#1", "T2" "T2#0" WHERE ("T= 2#0"."N2"=3D2) AND ("T1#1"."N1"=3D"= T2#0"."N1") AND ("T1#1"."N2"=3D2) AND ("T1#1"."N3&q= uot;=3D2)) innerQuery, startPos =3D 20, stopPos =3D 120, newTextLen =3D 244= , sqlLen =3D 343

 

How can I convert the sql_= id from the RDBMS.ADS into the conventional format?

 

Many thanks in advance.

 

Nenad

 

http://nenadnoveljic.com/blog/

 

_______________________________________________= _____

Please consider the environment before printing= this e-mail.

Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.<= span style=3D"color:green">


Important Notice

This message is intended only for the individual named. It may contain conf= idential or privileged information. If you are not the named addressee you = should in particular not disseminate, distribute, modify or copy this e-mai= l. Please notify the sender immediately by e-mail, if you have received thi= s message by mistake and delete it from your system.
Without prejudice to any contractual agreements between you and us which sh= all prevail in any case, we take it as your authorization to correspond wit= h you by e-mail if you send us messages by e-mail. However, we reserve the = right not to execute orders and instructions transmitted by e-mail at any t= ime and without further explanation.
E-mail transmission may not be secure or error-free as information could be= intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also p= rocessing of incoming e-mails cannot be guaranteed. All liability of Vontob= el Holding Ltd. and any of its affiliates (hereinafter collectively referre= d to as "Vontobel Group") for any damages resulting from e-mail use is excl= uded. You are advised that urgent and time sensitive messages should not be= sent by e-mail and if verification is required please request a printed ve= rsion.
Please note that all e-mail communications to and from the Vontobel Group a= re subject to electronic storage and review by Vontobel Group. Unless state= d to the contrary and without prejudice to any contractual agreements betwe= en you and Vontobel Group which shall prevail in any case, e-mail-communica= tion is for informational purposes only and is not intended as an offer or = solicitation for the purchase or sale of any financial instrument or as an = official confirmation of any transaction.
The legal basis for the processing of your personal data is the legitimate = interest to develop a commercial relationship with you, as well as your con= sent to forward you commercial communications. You can exercise, at any tim= e and under the terms established under current regulation, your rights. If= you prefer not to receive any further communications, please contact your = client relationship manager if you are a client of Vontobel Group or notify= the sender. Please note for an exact reference to the affected group entity the corpora= te e-mail signature. For further information about data privacy at Vontobel Group please consult= www.vontobel.com.

--_000_ECDEF0CC6716EC4596FCBC871F48292AB19C43D9ZRHS231_-- -- http://www.freelists.org/webpage/oracle-l