RE: missing part of sql statement in v$sql

We ran into this… but there was more than one bug in the mix.

Bug 17982832 prevented the SQL from being captured correctly in V$SQL Bug 20308798 prevented the SQL from being captured correctly in AWR (from V$SQL)

So, two 'links in the chain' were broken: getting the full SQL into V$SQL, and getting the full SQL from V$SQL to DBA_HIST_SQLTEXT.

The SQL was captured accurately in the audit trail (at least, to the limit of the NVARCHAR2(2000) column SQL_TEXT

Unfortunately, we reported the SQL was affected in AWR – and Oracle Support recommended only 20308798. But that did not fix our problem – because it was already chopped in V$SQL. We found Bug 17982832 by our own research.

And then we ran into the hurdle of ‘you need a special password to access patch Bug 17982832’ – grrrr….

Mike

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ken Naim Sent: Saturday, September 10, 2016 10:52 PM To: 'Mark W. Farnham' <mwf_at_rsiz.com>; 'Beckstrom Jeffrey' <JBECKSTROM_at_gcrta.org>; christopherdtaylor1994_at_gmail.com Cc: 'oracle-l-freelists' <oracle-l_at_freelists.org>; rjanuary_at_gmail.com Subject: RE: missing part of sql statement in v$sql

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_at_rsiz.com] Sent: Saturday, September 10, 2016 11:26 AM To: kennethnaim_at_gmail.com<mailto:kennethnaim_at_gmail.com>; 'Beckstrom Jeffrey' <JBECKSTROM_at_gcrta.org<mailto:JBECKSTROM_at_gcrta.org>>; christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com> Cc: 'oracle-l-freelists' <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>; rjanuary_at_gmail.com<mailto:rjanuary_at_gmail.com> 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_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ken Naim Sent: Saturday, September 10, 2016 12:21 AM To: 'Beckstrom Jeffrey'; christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com> Cc: 'oracle-l-freelists'; rjanuary_at_gmail.com<mailto:rjanuary_at_gmail.com> Subject: RE: missing part of sql statement in v$sql

Same, as v$sql.

From: Beckstrom Jeffrey [mailto:JBECKSTROM_at_gcrta.org] Sent: Friday, September 9, 2016 3:30 PM To: christopherdtaylor1994_at_gmail.com<mailto:christopherdtaylor1994_at_gmail.com>; kennethnaim_at_gmail.com<mailto:kennethnaim_at_gmail.com> Cc: oracle-l-freelists <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>; rjanuary_at_gmail.com<mailto:rjanuary_at_gmail.com> 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_at_gmail.com<mailto:kennethnaim_at_gmail.com>> 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_at_gmail.com] Sent: Friday, September 9, 2016 3:01 PM To: kennethnaim_at_gmail.com<mailto:kennethnaim_at_gmail.com> Cc: Ryan January <rjanuary_at_gmail.com<mailto:rjanuary_at_gmail.com>>; Oracle-l Digest Users <oracle-l_at_freelists.org<mailto:oracle-l_at_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 <kennethnaim_at_gmail.com<mailto:kennethnaim_at_gmail.com>> 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_at_gmail.com<mailto:rjanuary_at_gmail.com>] Sent: Friday, September 9, 2016 2:42 PM To: kennethnaim_at_gmail.com<mailto:kennethnaim_at_gmail.com> Cc: Oracle-l Digest Users <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> 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_at_gmail.com<mailto:kennethnaim_at_gmail.com>> 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

FROM CWP_14865_1045_1421068032
GROUP BY LEVEL_ID1,LEVEL_ID3)BASE0_COMBS

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 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

.

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