Least favorite feature of 11.2.0.4.
No, its not available from event 10046 trace.
How to tune a CTAS when you can't see the code?
You can generate an execution plan and one will still be available via
10046/tkprof.
Upgrade a testing environment to 12.1.0.2?
Paul
On Sep 9, 2016 3:39 PM, "Chris Taylor" <christopherdtaylor1994@xxxxxxxxx>
wrote:
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 <JBECKSTROM@xxxxxxxxx>
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
Thank you, I searched on metalink and couldn’t find the right keywords for"Ken Naim" <kennethnaim@xxxxxxxxx> 9/9/16 3:29 PM >>>
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
*Cc:* Ryan January <rjanuary@xxxxxxxxx>; Oracle-l Digest Users <
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> 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]
*Sent:* Friday, September 9, 2016 2:42 PM
*To:* kennethnaim@xxxxxxxxx
*Cc:* Oracle-l Digest Users <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> 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
.