How to find out the original SQL resulted the ERROR message? 2005-10-05 - By zhu chao
V$sql_plan did show some pq SQL, but it is very limited. For that SQL listed
in alert log, I can't find the corresponding in v$sql_plan.
Maybe query v$sql where sql_text like '%parallel%' is also an possible way,
though sometimes table degree >1 will use parallel without hint.
On 10/5/05, Lou Fangxin <anysql@(protected)> wrote:
>
> Try to query the v$sql_plan where other_tag is not null, this will list
> the query that parallel executed, then we need manually compare the sql.
>
> On 10/5/05, zhu chao <zhuchao@(protected)> wrote:
> >
> > Sorry, pressed enter before finish the email.
> > Sometimes we see some error message in alert, saying some SQL report
> > ORA-1555 (See ORA-1555.ora-code.com). For example, the following SQL is logged in alert.
> > Obviously it is not from normal application. I am wondering how I can
> > find out the original SQL, so I contact with developer for this.
> > Another SQL like:
> > ARC1: Completed archiving log 4 thread 1 sequence 9781 Tue Oct 4
> > 22:07:31 2005
> > ORA-01555 (See ORA-01555.ora-code.com) caused by SQL statement below (SCN: 0x0000.e3a53f18):
> > Tue Oct 4 22:07:31 2005
> > SELECT /*+ Q109000 NO_EXPAND ROWID(A1) */ A1.ROWID FROM
> > "BES1"."EXCEPTION" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1 WHERE
> > A1."CONSUMER_ID"='KenanToSiteProcessor'
> > AND A1."CREATION_DATE">=TO_DATE('2005-09-29 00:00:00', 'yyyy-mm-dd
> > hh24:mi:ss')
> > AND A1."CREATION_DATE"<=:B1 AND A1."EXCEPTION_STATUS"=3 AND
> > A1."EXCEPTION_REASON" LIKE '3501 DATA_SOURCE_DOWN%'
> > Tue Oct 4 22:12:34 2005
> > This is the spawned from some PQ query. If PQ process has exit, or not
> > running any more, can we still find out the original SQL?
> > Thanks
> > On 10/5/05, zhu chao <zhuchao@(protected)> wrote:
> > >
> > > Hi, All,
> > >
> > > ORA-01555 (See ORA-01555.ora-code.com) caused by SQL statement below (SCN: 0x0314.01fa70b6):
> > > Mon Oct 3 18:52:31 2005
> > > SELECT /*+NESTED_TABLE_GET_REFS+*/ "FDBK_USER"."TRANSACT_RECORD".*
> > > FROM "FDBK_USER"."TRANSACT_RECORD" where trx_type=1
> > >
> > > --
> > > Regards
> > > Zhu Chao
> > > www.cnoug.org <http://www.cnoug.org/>
> > >
> >
> >
> >
> > --
> > Regards
> > Zhu Chao
> > www.cnoug.org <http://www.cnoug.org/>
> >
>
>
>
> --
> Welcome, Fangxin.Lou from China
> Home: http://www.anysql.net/en/
> Mydul: http://www.anysql.net/en/mydmp.html
>
--
Regards
Zhu Chao
www.cnoug.org <http://www.cnoug.org>
<div>V$sql_plan did show some pq SQL, but it is very limited. For that SQL
listed in alert log, I can't find the corresponding in v$sql_plan.</div>
<div> </div>
<div>Maybe query v$sql where sql_text like '%parallel%' is also an possible way
, though sometimes table degree >1 will use parallel without hint.<br><br>
</div>
<div><span class="gmail_quote">On 10/5/05, <b class="gmail_sendername">Lou
Fangxin</b> <<a href="mailto:anysql@(protected)">anysql@(protected)</a>>
wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">Try to query the v$sql_plan where other_tag
is not null, this will list the query that parallel executed, then we
need manually compare the sql.
<div><span class="e" id="q_106c0b652fdf9948_1"><br><br>
<div><span class="gmail_quote">On 10/5/05, <b class="gmail_sendername">zhu chao
</b> <<a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto
:zhuchao@(protected)" target="_blank">zhuchao@(protected)</a>> wrote:
</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">
<div>Sorry, pressed enter before finish the email.</div>
<div> </div>
<div>Sometimes we see some error message in alert, saying some SQL report ORA
-1555. For example, the following SQL is logged in alert.</div>
<div>Obviously it is not from normal application. I am wondering how I can find
out the original SQL, so I contact with developer for this.<br> </div>
<div>Another SQL like:</div>
<div>ARC1: Completed archiving log 4 thread 1 sequence 9781 Tue Oct
4 22:07:31 2005<br>ORA-01555 (See ORA-01555.ora-code.com) caused by SQL statement below (SCN: 0x0000
.e3a53f18):<br>Tue Oct 4 22:07:31 2005<br>SELECT /*+ Q109000 NO_EXPAND
ROWID(A1) */
A1.ROWID FROM "BES1"."EXCEPTION" PX_GRANULE(0, BLOCK_RANGE,
DYNAMIC) A1 WHERE A1."CONSUMER_ID"='KenanToSiteProcessor' <br
>AND A1."CREATION_DATE">=TO_DATE('2005-09-29 00:00:00', 'yyyy-mm-dd
hh24:mi:ss')
<br>AND A1."CREATION_DATE"<=:B1 AND A1."EXCEPTION_STATUS"
;=3 AND A1."EXCEPTION_REASON" LIKE '3501 DATA_SOURCE_DOWN%'<br>Tue Oct
4 22:12:34 2005<br>This is the spawned from some PQ query. If PQ process
has exit, or not running any more, can we still find out the original SQL?
</div>
<div> </div>
<div>Thanks<br> </div>
<div><span>
<div><span class="gmail_quote">On 10/5/05, <b class="gmail_sendername">zhu chao
</b> <<a onclick="return top.js.OpenExtLink(window,event,this)" href="mailto
:zhuchao@(protected)" target="_blank">zhuchao@(protected)</a>> wrote:
</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0
.8ex; BORDER-LEFT: #ccc 1px solid">
<div>Hi, All,</div>
<div><br clear="all">ORA-01555 (See ORA-01555.ora-code.com) caused by SQL statement below (SCN: 0x0314
.01fa70b6):<br>Mon Oct 3 18:52:31 2005<br>SELECT /*+NESTED_TABLE_GET_REFS+
*/ "FDBK_USER"."TRANSACT_RECORD".* FROM "FDBK_USER"
;."TRANSACT_RECORD" where trx_type=1
<br> </div>
<div><br>-- <br>Regards<br>Zhu Chao<br><a onclick="return top.js.OpenExtLink
(window,event,this)" href="http://www.cnoug.org/" target="_blank">www.cnoug.org<
/a><br> </div></blockquote></div><br><br clear="all"><br>-- <br>
Regards<br>Zhu Chao<br><a onclick="return top.js.OpenExtLink(window,event,this)
" href="http://www.cnoug.org/" target="_blank">www.cnoug.org</a><br></span></div
></blockquote></div><br><br clear="all"><br></span></div><span class="sg">
-- <br>Welcome, Fangxin.Lou from China<br>Home: <a onclick="return top.js
.OpenExtLink(window,event,this)" href="http://www.anysql.net/en/" target="_blank
">http://www.anysql.net/en/</a><br>Mydul: <a onclick="return top.js.OpenExtLink
(window,event,this)" href="http://www.anysql.net/en/mydmp.html" target="_blank">
http://www.anysql.net/en/mydmp.html</a><br></span></blockquote></div><br><br
clear="all"><br>-- <br>Regards<br>Zhu Chao<br><a href="http://www.cnoug.org"
>www.cnoug.org</a><br>
|
|
