RE: missing part of sql statement in v$sql
- From: "Ken Naim" <kennethnaim@xxxxxxxxx>
- To: "'Mark W. Farnham'" <mwf@xxxxxxxx>, "'Beckstrom Jeffrey'" <JBECKSTROM@xxxxxxxxx>, <christopherdtaylor1994@xxxxxxxxx>
- Date: Sat, 10 Sep 2016 22:52:00 -0400
Thank you for the suggestions, unfortunately this code is generated by an
application, hence my need to see the sql in order to tune it. I have this
version of the application running at other clients that do not experience this
issue. Unfortunately I don’t have dba or server level access at this client yet
so I can’t even try the patch that was suggested. I will dump the sql_fulltext
column to see if there are any strange unprintable chatacters.
From: Mark W. Farnham [mailto:mwf@xxxxxxxx] ;
Sent: Saturday, September 10, 2016 11:26 AM
To: kennethnaim@xxxxxxxxx; 'Beckstrom Jeffrey' <JBECKSTROM@xxxxxxxxx>;
christopherdtaylor1994@xxxxxxxxx
Cc: 'oracle-l-freelists' <oracle-l@xxxxxxxxxxxxx>; rjanuary@xxxxxxxxx
Subject: RE: missing part of sql statement in v$sql
The suspicion arises that somehow you have unprintable characters in the
original source string or some such and corresponding rubout characters.
If possible, view your source via some editor that shows a visible encoding of
unprintables, or failing that carefully manipulate your input sql string.
When (if) all that fails, I would resort to inserting
--< comment line>
between each source text line of the text, the entire thing by hand, if need be.
Something very fishy is afoot.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ken Naim
Sent: Saturday, September 10, 2016 12:21 AM
To: 'Beckstrom Jeffrey'; christopherdtaylor1994@xxxxxxxxx
<mailto:christopherdtaylor1994@xxxxxxxxx>
Cc: 'oracle-l-freelists'; rjanuary@xxxxxxxxx <mailto:rjanuary@xxxxxxxxx>
Subject: RE: missing part of sql statement in v$sql
Same, as v$sql.
From: Beckstrom Jeffrey [mailto:JBECKSTROM@xxxxxxxxx] ;
Sent: Friday, September 9, 2016 3:30 PM
To: christopherdtaylor1994@xxxxxxxxx <mailto:christopherdtaylor1994@xxxxxxxxx>
; kennethnaim@xxxxxxxxx <mailto:kennethnaim@xxxxxxxxx>
Cc: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
; rjanuary@xxxxxxxxx <mailto:rjanuary@xxxxxxxxx>
Subject: RE: missing part of sql statement in v$sql
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" <kennethnaim@xxxxxxxxx <mailto:kennethnaim@xxxxxxxxx> > 9/9/16
3:29 PM >>>
Thank you, I searched on metalink and couldn’t find the right keywords for
proper search results. I was hoping someone ran into it before.
From: Chris Taylor [
mailto:christopherdtaylor1994@xxxxxxxxx] ;
Sent: Friday, September 9, 2016 3:01 PM
To: kennethnaim@xxxxxxxxx <
mailto:kennethnaim@xxxxxxxxx>
Cc: Ryan January <rjanuary@xxxxxxxxx <
mailto:rjanuary@xxxxxxxxx> >; Oracle-l
Digest Users <oracle-l@xxxxxxxxxxxxx <
mailto:oracle-l@xxxxxxxxxxxxx> >
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 <kennethnaim@xxxxxxxxx
<
mailto:kennethnaim@xxxxxxxxx> > wrote:
Sql_fulltext is missing the 10-20 lines, sql_text has only the first 20 or so
characters. It’s so odd that the middle of a statement would be missing.
Thanks,
Ken
From: Ryan January [
mailto:rjanuary@xxxxxxxxx ;<
mailto:rjanuary@xxxxxxxxx> ]
Sent: Friday, September 9, 2016 2:42 PM
To: kennethnaim@xxxxxxxxx <
mailto:kennethnaim@xxxxxxxxx>
Cc: Oracle-l Digest Users <oracle-l@xxxxxxxxxxxxx
<
mailto:oracle-l@xxxxxxxxxxxxx> >
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 <kennethnaim@xxxxxxxxx
<
mailto:kennethnaim@xxxxxxxxx> > wrote:
I’ve 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 “CREATE TABLE CWC_148D1”. 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))) = -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 = 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 = 1)WEIGHT_BASE1
GROUP BY LEVEL_ID1,LEVEL_ID3,LEVEL_ID2)WEIGHT_BASE1
WHERE BASE0_COMBS.LEVEL_ID1 = ALL_COMBS.LEVEL_ID1
AND BASE0_COMBS.LEVEL_ID3 = ALL_COMBS.LEVEL_ID3
AND ALL_COMBS.LEVEL_ID1 = WEIGHT_BASE0.LEVEL_ID1(+)
AND ALL_COMBS.LEVEL_ID3 = WEIGHT_BASE0.LEVEL_ID3(+)
AND ALL_COMBS.LEVEL_ID1 = WEIGHT_BASE1.LEVEL_ID1(+)
AND ALL_COMBS.LEVEL_ID3 = WEIGHT_BASE1.LEVEL_ID3(+)
AND ALL_COMBS.LEVEL_ID2 = WEIGHT_BASE1.LEVEL_ID2(+)
AND (BASE0_COMBS.NEED_MERGE IS NULL
OR (BASE0_COMBS.NEED_MERGE = ALL_COMBS.IS_BASE1)))T_POPU_LIST
,T_EP_EBS_ACCOUNT
,PROMOTION
,T_EP_FISCAL_MONTH
WHERE T_POPU_LIST.LEVEL_ID1 = T_EP_EBS_ACCOUNT.T_EP_EBS_ACCOUNT_EP_ID
AND T_POPU_LIST.LEVEL_ID2 = PROMOTION.PROMOTION_ID
AND T_POPU_LIST.LEVEL_ID3 = T_EP_FISCAL_MONTH.T_EP_FISCAL_MONTH_ID
AND T_POPU_LIST.IS_BASE1 = 1
.
Other related posts:
