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 225451960738 for ; Fri, 9 Sep 2016 21:39:49 +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:39:49 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DF2732CC67; Fri, 9 Sep 2016 15:39:47 -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 p2dDwfkBk0W8; Fri, 9 Sep 2016 15:39:47 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DFCE72CC62; Fri, 9 Sep 2016 15:39:34 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 09 Sep 2016 15:38:13 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 093D22CC0B for ; Fri, 9 Sep 2016 15:38:13 -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 RmmOT_aXnl8f for ; Fri, 9 Sep 2016 15:38:12 -0400 (EDT) Received: from mail-vk0-f43.google.com (mail-vk0-f43.google.com [209.85.213.43]) (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 C5A592C9DB for ; Fri, 9 Sep 2016 15:38:12 -0400 (EDT) Received: by mail-vk0-f43.google.com with SMTP id f76so77626799vke.0 for ; Fri, 09 Sep 2016 12:38:12 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=YCy/2u7lMPBRFl08yHOCDKCKC2vIcapgK1tGFbbMSYc=; b=ZcyjhplufdSCEqUrCE25EnVbvwyesJT9Wysm5/5Q5Hulrv2RYKepKB7nfkOrDeyY1b S4ze9L9OdUFSf5kb+/UdzshSIHovhNo2/QvfPJeB65KSShQoORI6Mxzx4y5QqmoxQvzc NKA5ix2E4OS8W0Ng7pJc2362cUwG0q36o7ZnAZDmI9c9ue7i2r6Fv4Vqh+Yj2izEtDtT oFC2983+hdg/WVaAFpQ0YqVXXeDIwwvN/r/abLy7Q0xvfeTNm0y55UdPtikvc2xIcCtP QxkVbi+/f+Qd5V+PdomxdGW0aIhy2qT96AjaUmd3jWKIR1hKlHUnViz2+ahAQ3joQydy iuEg== X-Gm-Message-State: AE9vXwMLN0HVukzMXn4huWLygDYThFGbb8NERqXgzH8MuTKMB4AG+VwrExLFo5Lu+PAUL+2NPTzQJ5jY7djEKw== X-Received: by 10.31.140.77 with SMTP id o74mr3724443vkd.70.1473449892131; Fri, 09 Sep 2016 12:38:12 -0700 (PDT) MIME-Version: 1.0 Received: by 10.103.144.8 with HTTP; Fri, 9 Sep 2016 12:38:11 -0700 (PDT) In-Reply-To: <57D2D5780200000B000177E6@groupwise2014.gcrta.org> References: <014001d20ac9$7de464f0$79ad2ed0$@gmail.com> <014701d20aca$f085ae50$d1910af0$@gmail.com> <016301d20ad0$837abca0$8a7035e0$@gmail.com> <57D2D5780200000B000177E6@groupwise2014.gcrta.org> From: Chris Taylor Date: Fri, 9 Sep 2016 14:38:11 -0500 Message-ID: Subject: Re: missing part of sql statement in v$sql To: Beckstrom Jeffrey Cc: kennethnaim@gmail.com, oracle-l-freelists , rjanuary@gmail.com Content-Type: multipart/alternative; boundary=001a11425a326a38db053c1849cd X-archive-position: 66190 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: christopherdtaylor1994@gmail.com Precedence: normal Reply-To: christopherdtaylor1994@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 --001a11425a326a38db053c1849cd Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable It seems to get lost throughout the system from when I was seeing it. Also be aware its with 11.2.0.4 *PLUS *a specific quarterly patch. Chris On Fri, Sep 9, 2016 at 2:30 PM, Beckstrom Jeffrey wrote: > What about v$sqltext? > > Jeffrey Beckstrom > Lead Database Administrator > Information Technology Department > Greater Cleveland Regional Transit Authority > 1240 W. 6th Street > Cleveland, Ohio 44113 > > >>> "Ken Naim" 9/9/16 3:29 PM >>> > Thank you, I searched on metalink and couldn=E2=80=99t find the right key= words for > proper search results. I was hoping someone ran into it before. > > *From:* Chris Taylor [mailto:christopherdtaylor1994@gmail.com] > *Sent:* Friday, September 9, 2016 3:01 PM > *To:* kennethnaim@gmail.com > *Cc:* Ryan January ; 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) > > 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 o= r > so characters. It=E2=80=99s so odd that the middle of a statement would b= e missing. > > Thanks, > Ken > > *From:* Ryan January [mailto:rjanuary@gmail.com] > *Sent:* Friday, September 9, 2016 2:42 PM > *To:* kennethnaim@gmail.com > *Cc:* Oracle-l Digest Users > *Subject:* Re: missing part of sql statement in v$sql > > What about sql_fulltext? Is it complete, or also truncated? > > > On Sep 9, 2016, at 1:39 PM, Ken Naim wrote: > > 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 t= he > 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) 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 > > > > > > > . > > --001a11425a326a38db053c1849cd Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
It seems to get lost throughout the system from when I = was seeing it.

Also be aware its with 11.2.0.4 PLUS= a specific quarterly patch. =C2=A0

Chris


On Fri, = Sep 9, 2016 at 2:30 PM, Beckstrom Jeffrey <JBECKSTROM@gcrta.org>= wrote:
What about v$sqltext?

Jeffre= y Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
= =C2=A0
>= ;>> "Ken Naim" <kennethnaim@gmail.com> 9/9/16 3:29 PM >>>= ;
Tha= nk 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.=
=C2= =A0
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
=C2=A0
THere's a= bug in 11.2.0.4 with missing SQL_TEXT - you may be hitting it (and apparen= tly several related bugs)
=C2=A0

V$S= QL.SQL_FULLTEXT For Create Table Statement Is Trimmed (Doc ID 1922923.1)=

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 t= he first 20 or so characters. It=E2=80=99s so odd that the middle of a stat= ement would be missing.
=C2=A0
Thanks,
Ken
=C2=A0
From: Ryan January [mailto:rjanuary@gmail.com] Sent: Friday, September 9, 2016 2:42 PM
To: kennethnaim@gmail.comCc: Oracle-l Digest Users <oracle-l@freelists.org>
Subject: Re= : missing part of sql statement in v$sql
=C2=A0
What about sql_fulltext?=C2= =A0 Is it complete, or also truncated?
=C2=A0
On Sep = 9, 2016, at 1:39 PM, Ken Naim <kennethnaim@gmail.com> wrote:
<= /div>
=C2=A0=
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 th= e first create table statement. Sql_text has just =E2=80=9CCREATE TABLE CWC= _148D1=E2=80=9D. DB version is 11.2.0.4.
=C2=A0
CREATE=C2=A0TABLE= =C2=A0CWC_148D1=C2=A0AS=C2=A0IS_MODIFIED1
FROM=C2=A0(SELECT=C2=A0= LEVEL_ID1,LEVEL_ID2,LEVEL_ID3
,MAX(IS_BASE0)=C2=A0AS=C2=A0IS_BASE= 0
,MAX(IS_BASE1)=C2=A0AS=C2=A0IS_BASE1
<= div class=3D"MsoNormal" style=3D"margin:1em 0em">,MIN(SEC_BASE0)=C2=A0AS=C2= =A0SEC_BASE0
,MIN(SEC_BASE1)=C2=A0AS=C2=A0SEC_BASE1
,MAX(IS_MOD= IFIED0)=C2=A0AS=C2=A0IS_MODIFIED0
,MAX(IS_MODIFIED1)=C2=A0AS=C2= =A0IS_MODIFIED1
FROM=C2=A0CWP_14865_1045_1421068032=
GROUP=C2= =A0BY=C2=A0LEVEL_ID1,LEVEL_ID2,LEVEL_ID3)ALL_COMBS
=
,(SELECT=C2=A0= LEVEL_ID1,LEVEL_ID3
,(CASE=C2=A0WHEN=C2=A0((MIN(NVL(IS_BASE1,-1))= *MAX(NVL(IS_BASE1,-1)))=C2=A0=3D=C2=A0-1)=C2=A0THEN=C2=A01=C2=A0END)= =C2=A0AS=C2=A0NEED_MERGE
FROM=C2=A0CWP_14865_1045_1421068032
GROUP=C2=A0BY=C2=A0LEVEL_ID1,LEVEL_ID3)BASE0_COMBS
,(SELECT=C2= =A0LEVEL_ID1,LEVEL_ID3,COUNT(*)=C2=A0AS=C2=A0WEIGHT_BASE0
FROM=C2= =A0(SELECT=C2=A0DISTINCT=C2=A0ITEM_ID,LOCATION_ID,LEVEL_ID1,LEVEL_ID3
FROM=C2=A0CWP_14865_1045_1421068032
WHERE=C2=A0IS_BASE0=C2= =A0=3D=C2=A01)WEIGHT_BASE0
GROUP=C2=A0BY=C2=A0LEVEL_ID1,LEVEL_ID3= )WEIGHT_BASE0
,(SELECT=C2=A0LEVEL_ID1,LEVEL_ID3,LEVEL_ID2,CO= UNT(*)=C2=A0AS=C2=A0WEIGHT_BASE1
FROM=C2=A0(SELECT=C2=A0DISTINCT= =C2=A0ITEM_ID,LOCATION_ID,PROMOTION_ID,LEVEL_ID1,LEVEL_ID3,LEVEL_= ID2
FROM=C2=A0CWP_14865_1045_1421068032
=
WHERE=C2=A0IS_BASE1=C2=A0= =3D=C2=A01)WEIGHT_BASE1
GROUP=C2=A0BY=C2=A0LEVEL_ID1,LEVEL_ID3,LEVEL_ID2)WEIGHT_BASE1
WHERE=C2=A0BASE0_COMBS.LEVEL_ID1=C2=A0=3D=C2= =A0ALL_COMBS.LEVEL_ID1
AND=C2=A0BASE0_COMBS.LEVEL_ID3=C2=A0=3D=C2= =A0ALL_COMBS.LEVEL_ID3
AND=C2=A0ALL_COMBS.LEVEL_ID1=C2=A0=3D=C2= =A0WEIGHT_BASE0.LEVEL_ID1(+)
AND=C2=A0ALL_COMBS.LEVEL_ID3=C2=A0= =3D=C2=A0WEIGHT_BASE0.LEVEL_ID3(+)
AND=C2=A0ALL_COMBS.LEVEL_ID1= =C2=A0=3D=C2=A0WEIGHT_BASE1.LEVEL_ID1(+)
AND=C2=A0ALL_COMBS.LEVEL= _ID3=C2=A0=3D=C2=A0WEIGHT_BASE1.LEVEL_ID3(+)
=
AND=C2=A0ALL_COMBS.L= EVEL_ID2=C2=A0=3D=C2=A0WEIGHT_BASE1.LEVEL_ID2(+)
AND=C2=A0(BASE0_= COMBS.NEED_MERGE=C2=A0IS=C2=A0NULL
OR=C2=A0(BASE0_COMBS.NEED_MERG= E=C2=A0=3D=C2=A0ALL_COMBS.IS_BASE1)))T_POPU_LIST
,T_EP_EBS_A= CCOUNT
,PROMOTION
,T_EP_FISCAL_MONTH
WHERE=C2=A0T_POPU_LIST.= LEVEL_ID1=C2=A0=3D=C2=A0T_EP_EBS_ACCOUNT.T_EP_EBS_ACCOUNT_EP_ID
AND=C2=A0T_POPU_LIST.LEVEL_ID2=C2=A0=3D=C2=A0PROMOTION.PROMOTION_I= D
AND=C2=A0T_POPU_LIST.LEVEL_ID3=C2=A0=3D=C2=A0T_EP_FISCAL_MONTH.= T_EP_FISCAL_MONTH_ID
AND=C2=A0T_POPU_LIST.IS_BASE1=C2=A0=3D=C2=A0= 1
=C2=A0
<= /u>=C2=A0
.

--001a11425a326a38db053c1849cd-- -- http://www.freelists.org/webpage/oracle-l