Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from puck1183.startdedicated.com (localhost [127.0.0.1]) by puck1183.startdedicated.com (Postfix) with ESMTP id 1D9931960726 for ; Fri, 9 Sep 2016 21:31:20 +0200 (CEST) Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by puck1183.startdedicated.com (Postfix) with ESMTPS for ; Fri, 9 Sep 2016 21:31:19 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B25922CB35; Fri, 9 Sep 2016 15:31:18 -0400 (EDT) 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 8HOi7RWnA88K; Fri, 9 Sep 2016 15:31:18 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C37E52CB41; Fri, 9 Sep 2016 15:31:05 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 09 Sep 2016 15:29:44 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EF86F2C9DB for ; Fri, 9 Sep 2016 15:29:43 -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 rLboXhP9XpKv for ; Fri, 9 Sep 2016 15:29:43 -0400 (EDT) Received: from mail-yw0-f169.google.com (mail-yw0-f169.google.com [209.85.161.169]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id B79BB2C92A for ; Fri, 9 Sep 2016 15:29:43 -0400 (EDT) Received: by mail-yw0-f169.google.com with SMTP id g192so53287110ywh.1 for ; Fri, 09 Sep 2016 12:29:43 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:to:cc:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=IBj7Q7jdSgfnTmBIsowohm1sz9gNzd85EuxJ6o7rk5w=; b=PDcQq4r1JMcRvbtKOdaDjoLRo6FMjrdbJCOhnQpg+9kWgO8hdZ8rlc5OPD++HtO0Pl IEqF+utnCTDtMpgYPewpaKrN9fGmKWsSG5T/OKIwjsKf+7AJxwjZDDswwaQplR94oXLy /cRyeTEvqu/SRJiqcjiWoSXe39S9gORCaZIbyDK0DMOWqwVxzk9Y+GxuGwsotV9rih2W p0F4/olzuyV3k0uWG2iuYu9TVa3G65/OaSFVa9fmGVuR1Ym8M2g/ObBQ/vSgeE6BkRin mf3UG4/mk1kBPKFdPkySSQ13q2YvmxRfqVt3kIBEZqIc1/cVa75Ee+IS217RJQcff0GZ UciQ== X-Gm-Message-State: AE9vXwNyMrCdz+YYRdB7nu235j5Od4vuKNvcf5A/+0sXT+TxIAIL2Gdqn0AJgYg2oXX2wg== X-Received: by 10.13.192.131 with SMTP id b125mr4687908ywd.161.1473449383099; Fri, 09 Sep 2016 12:29:43 -0700 (PDT) Received: from KenPC10 (c-76-110-164-226.hsd1.fl.comcast.net. [76.110.164.226]) by smtp.gmail.com with ESMTPSA id z123sm1687071ywf.49.2016.09.09.12.29.41 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 09 Sep 2016 12:29:42 -0700 (PDT) From: "Ken Naim" To: "'Chris Taylor'" Cc: "'Ryan January'" , "'Oracle-l Digest Users'" References: <014001d20ac9$7de464f0$79ad2ed0$@gmail.com> <014701d20aca$f085ae50$d1910af0$@gmail.com> In-Reply-To: Subject: RE: missing part of sql statement in v$sql Date: Fri, 9 Sep 2016 15:29:39 -0400 Message-ID: <016301d20ad0$837abca0$8a7035e0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0164_01D20AAE.FC6AA340" Content-Language: en-us X-archive-position: 66188 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: kennethnaim@gmail.com Precedence: normal Reply-To: kennethnaim@gmail.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 ------=_NextPart_000_0164_01D20AAE.FC6AA340 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Thank you, I searched on metalink and couldn=E2=80=99t find the right = keywords for proper search results. I was hoping someone ran into it = before. =20 From: Chris Taylor [mailto:christopherdtaylor1994@gmail.com]=20 Sent: Friday, September 9, 2016 3:01 PM To: kennethnaim@gmail.com Cc: Ryan January ; Oracle-l Digest Users = Subject: Re: missing part of sql statement in v$sql =20 THere's a bug in 11.2.0.4 with missing SQL_TEXT - you may be hitting it = (and apparently several related bugs) =20 V$SQL.SQL_FULLTEXT For Create Table Statement Is Trimmed (Doc ID = 1922923.1) =20 On Fri, Sep 9, 2016 at 1:49 PM, Ken Naim > wrote: Sql_fulltext is missing the 10-20 lines, sql_text has only the first 20 = or so characters. It=E2=80=99s so odd that the middle of a statement = would be missing. =20 Thanks, Ken =20 From: Ryan January [mailto:rjanuary@gmail.com = ]=20 Sent: Friday, September 9, 2016 2:42 PM To: kennethnaim@gmail.com =20 Cc: Oracle-l Digest Users > Subject: Re: missing part of sql statement in v$sql =20 What about sql_fulltext? Is it complete, or also truncated? =20 On Sep 9, 2016, at 1:39 PM, Ken Naim > wrote: =20 I=E2=80=99ve queried v$sql for a particular poorly performing sql_id and = I get the first and last part but it is missing maybe 10-20 lines from = just after the first create table statement. Sql_text has just = =E2=80=9CCREATE TABLE CWC_148D1=E2=80=9D. DB version is 11.2.0.4. =20 CREATE TABLE CWC_148D1 AS IS_MODIFIED1 FROM (SELECT LEVEL_ID1,LEVEL_ID2,LEVEL_ID3 ,MAX(IS_BASE0) AS IS_BASE0 ,MAX(IS_BASE1) AS IS_BASE1 ,MIN(SEC_BASE0) AS SEC_BASE0 ,MIN(SEC_BASE1) AS SEC_BASE1 ,MAX(IS_MODIFIED0) AS IS_MODIFIED0 ,MAX(IS_MODIFIED1) AS IS_MODIFIED1 FROM CWP_14865_1045_1421068032 GROUP BY LEVEL_ID1,LEVEL_ID2,LEVEL_ID3)ALL_COMBS ,(SELECT LEVEL_ID1,LEVEL_ID3 ,(CASE WHEN ((MIN(NVL(IS_BASE1,-1))*MAX(NVL(IS_BASE1,-1))) =3D -1) THEN = 1 END) AS NEED_MERGE FROM CWP_14865_1045_1421068032 GROUP BY LEVEL_ID1,LEVEL_ID3)BASE0_COMBS ,(SELECT LEVEL_ID1,LEVEL_ID3,COUNT(*) AS WEIGHT_BASE0 FROM (SELECT DISTINCT ITEM_ID,LOCATION_ID,LEVEL_ID1,LEVEL_ID3 FROM CWP_14865_1045_1421068032 WHERE IS_BASE0 =3D 1)WEIGHT_BASE0 GROUP BY LEVEL_ID1,LEVEL_ID3)WEIGHT_BASE0 ,(SELECT LEVEL_ID1,LEVEL_ID3,LEVEL_ID2,COUNT(*) AS WEIGHT_BASE1 FROM (SELECT DISTINCT = ITEM_ID,LOCATION_ID,PROMOTION_ID,LEVEL_ID1,LEVEL_ID3,LEVEL_ID2 FROM CWP_14865_1045_1421068032 WHERE IS_BASE1 =3D 1)WEIGHT_BASE1 GROUP BY LEVEL_ID1,LEVEL_ID3,LEVEL_ID2)WEIGHT_BASE1 WHERE BASE0_COMBS.LEVEL_ID1 =3D ALL_COMBS.LEVEL_ID1 AND BASE0_COMBS.LEVEL_ID3 =3D ALL_COMBS.LEVEL_ID3 AND ALL_COMBS.LEVEL_ID1 =3D WEIGHT_BASE0.LEVEL_ID1(+) AND ALL_COMBS.LEVEL_ID3 =3D WEIGHT_BASE0.LEVEL_ID3(+) AND ALL_COMBS.LEVEL_ID1 =3D WEIGHT_BASE1.LEVEL_ID1(+) AND ALL_COMBS.LEVEL_ID3 =3D WEIGHT_BASE1.LEVEL_ID3(+) AND ALL_COMBS.LEVEL_ID2 =3D WEIGHT_BASE1.LEVEL_ID2(+) AND (BASE0_COMBS.NEED_MERGE IS NULL OR (BASE0_COMBS.NEED_MERGE =3D ALL_COMBS.IS_BASE1)))T_POPU_LIST ,T_EP_EBS_ACCOUNT ,PROMOTION ,T_EP_FISCAL_MONTH WHERE T_POPU_LIST.LEVEL_ID1 =3D T_EP_EBS_ACCOUNT.T_EP_EBS_ACCOUNT_EP_ID AND T_POPU_LIST.LEVEL_ID2 =3D PROMOTION.PROMOTION_ID AND T_POPU_LIST.LEVEL_ID3 =3D T_EP_FISCAL_MONTH.T_EP_FISCAL_MONTH_ID AND T_POPU_LIST.IS_BASE1 =3D 1 =20 =20 ------=_NextPart_000_0164_01D20AAE.FC6AA340 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Thank you, I searched on metalink and couldn=E2=80=99t find the right = keywords for proper search results. I was hoping someone ran into it = before.

 

From:<= /b> = Chris Taylor [mailto:christopherdtaylor1994@gmail.com]
Sent: = Friday, September 9, 2016 3:01 PM
To: = kennethnaim@gmail.com
Cc: Ryan January = <rjanuary@gmail.com>; Oracle-l Digest Users = <oracle-l@freelists.org>
Subject: Re: missing part of = sql statement in v$sql

 

THere's = a bug in 11.2.0.4 with missing SQL_TEXT - you may be hitting it (and = apparently several related bugs)

 

V$= SQL.SQL_FULLTEXT For Create Table Statement Is Trimmed (Doc ID = 1922923.1)<= /h3>

 

On Fri, Sep 9, 2016 at 1:49 PM, Ken Naim <kennethnaim@gmail.com> = wrote:

Sql_fulltext is missing the 10-20 lines, sql_text has only the first 20 = or so characters. It=E2=80=99s so odd that the middle of a statement = would be missing.

 

Thanks,

Ken

 

From:<= /b> = Ryan January [mailto:rjanuary@gmail.com]
Sent: Friday, = September 9, 2016 2:42 PM
To: kennethnaim@gmail.com
Cc: Oracle-l = Digest Users <oracle-l@freelists.org>
Subject: Re: = missing part of sql statement in = v$sql

 <= /o:p>

What about = sql_fulltext?  Is it complete, or also = truncated?

 <= /o:p>

On Sep 9, = 2016, at 1:39 PM, Ken Naim <kennethnaim@gmail.com> = wrote:

 <= /o:p>

I=E2=80=99ve = queried v$sql for a particular poorly performing sql_id and I get the = first and last part but it is missing maybe 10-20 lines from just after = the first create table statement. Sql_text has just =E2=80=9CCREATE = TABLE CWC_148D1=E2=80=9D. DB version is = 11.2.0.4.

 =

CREATE TABLE CWC_148D1 AS IS_MODIFIED1

FROM (SELECT LEVEL_ID1,LEVEL_ID2,LEVEL_ID3

,MAX(IS_BASE0<= span style=3D'color:blue'>) AS IS_BASE0

<= div>

,MAX(IS_BASE1<= span style=3D'color:blue'>) AS IS_BASE1

<= div>

,MIN(SEC_BASE0= ) AS SEC_BASE0

=

,MIN(SEC_BASE1= ) AS SEC_BASE1

=

,MAX(IS_MODIFI= ED0) AS IS_MODIFIED0

,MAX(IS_MODIFI= ED1) AS IS_MODIFIED1

FROM CWP_14865_1045_1421068032<= /p>

GROUP BY LEVEL_ID1,LEVEL_ID2,LEVEL_ID3)ALL_COMBS

,(SELECT LEV= EL_ID1,LEVEL_ID3

,(CASE WHEN ((MIN(NVL(IS_BASE1,-1))*MAX(NVL(IS_BASE1,-1))) =3D -1) THEN 1 END) AS NEED_MERGE

FROM CWP_14865_1045_1421068032<= /p>

GROUP BY LEVEL_ID1,LEVEL_ID3)BASE0_COMBS

=

,(SELECT LEV= EL_ID1,LEVEL_ID3,COUNT(*) AS WEIGHT_BASE0

FROM (SELECT DISTINCT ITEM_ID,LOCATION_ID,LEVEL_ID1,LEVEL_ID3

FROM CWP_14865_1045_1421068032<= /p>

WHERE IS_= BASE0 =3D 1)WEIGHT_BASE0

GROUP BY LEVEL_ID1,LEVEL_ID3)WEIGHT_BASE0

,(SELECT LEV= EL_ID1,LEVEL_ID3,LEVEL_ID2,COUNT(*) AS WEIGHT_BASE1

FROM (SELECT DISTINCT ITEM_ID,LOCATION_ID,PROMOTION_ID,LEVEL_ID1,LEVEL_ID3,LEVEL_ID2

FROM CWP_14865_1045_1421068032<= /p>

WHERE IS_= BASE1 =3D 1)WEIGHT_BASE1

GROUP BY LEVEL_ID1,LEVEL_ID3,LEVEL_ID2)WEIGHT_BASE1

WHERE BAS= E0_COMBS.LEVEL_ID1 =3D ALL_COMBS.LEVEL_ID1

AND BAS= E0_COMBS.LEVEL_ID3 =3D ALL_COMBS.LEVEL_ID3

AND ALL= _COMBS.LEVEL_ID1 =3D WEIGHT_BASE0.LEVEL_ID1(+)

AND ALL= _COMBS.LEVEL_ID3 =3D WEIGHT_BASE0.LEVEL_ID3(+)

AND ALL= _COMBS.LEVEL_ID1 =3D WEIGHT_BASE1.LEVEL_ID1(+)

AND ALL= _COMBS.LEVEL_ID3 =3D WEIGHT_BASE1.LEVEL_ID3(+)

AND ALL= _COMBS.LEVEL_ID2 =3D WEIGHT_BASE1.LEVEL_ID2(+)

AND (BASE0_COMBS.NEED_MERGE IS NULL

OR (BASE0_COMBS.NEED_MERGE =3D ALL_COMBS.IS_BASE1)))T_POPU_LIST

,T_EP_EBS_ACCOUNT

,PROMOTION

,T_EP_FISCAL_MONTH

WHERE T_P= OPU_LIST.LEVEL_ID1 =3D T_EP_EBS_ACCOUNT.T_EP_EBS_ACCOUNT_EP_ID

=

AND T_P= OPU_LIST.LEVEL_ID2 =3D PROMOTION.PROMOTION_ID

AND T_P= OPU_LIST.LEVEL_ID3 =3D T_EP_FISCAL_MONTH.T_EP_FISCAL_MONTH_ID

AND T_P= OPU_LIST.IS_BASE1 =3D 1

 <= /o:p>

 

------=_NextPart_000_0164_01D20AAE.FC6AA340-- -- http://www.freelists.org/webpage/oracle-l