OraFAQ Forum: Performance Tuning » ora-01555 snapshot too old. Query executed 4000s

Home » RDBMS Server » Performance Tuning » ora-01555 snapshot too old. Query executed 4000s (10g, 10.2.0.5, hp-unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ora-01555 snapshot too old. Query executed 4000s [message #652321]

Tue, 07 June 2016 04:47

ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta

Senior Member

Dear All,

In alert log, ora error is appearing as "ORA-01555". And query's execution time is 4000s. When we generate xplain plan (dbms_xplan.display_awr) for that sql id, cost is appearing as 3(100%) with index range scan. But when we, generate xplain plan (dbms_xplan.display), it exeecute with the FTS (900000 cost).

What could be the reason? Do we need to ask application team to re-write the query?

select TXT_CUSTOMER_NAME "Customer Name", Txt_customer_cd "Customer ID" 
from genmst_customer 
where upper(TXT_customer_name) Like :"sys_B_0"

Below is the plan (without taking sql id)-

   	PLAN_TABLE_OUTPUT
1	Plan hash value: 1639122825
2	 
3	-------------------------------------------------------------------------------------------------------
4	| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
5	-------------------------------------------------------------------------------------------------------
6	|   0 | SELECT STATEMENT    |                 |  5195K|   173M|   910K  (1)| 03:02:05 |       |       |
7	|   1 |  PARTITION RANGE ALL|                 |  5195K|   173M|   910K  (1)| 03:02:05 |     1 |     6 |
8	|*  2 |   TABLE ACCESS FULL | GENMST_CUSTOMER |  5195K|   173M|   910K  (1)| 03:02:05 |     1 |     6 |
9	-------------------------------------------------------------------------------------------------------
10	 
11	Predicate Information (identified by operation id):
12	---------------------------------------------------
13	 
14	   2 - filter(UPPER("TXT_CUSTOMER_NAME") LIKE :sys_B_0)

Plan is not available with sql id.

Request you to clear my doubt? Which plan should I refer for the further tuning (dbms_xplan.display_awr or dbms_xplan.display)?

Regards,
Ashish

Report message to a moderator

Re: ora-01555 snapshot too old. Query executed 4000s [message #652326 is a reply to message #652321]

Tue, 07 June 2016 06:02

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

EXPLAIN PAN doesn't expand bind variables, so the CBO has no idea how many rows will be returned. It is just guessing that an FTS might, on average, be best. The plan extracted from the AWR is what actually happened: the plan that was developed after peeking the bind value.
There are many reasons why EXPLAIN P{LAN may not be accurate. I described some of them in this tutorial,
http://www.skillbuilders.com/webinars/webinar.cfm?id=136&w=Oracle-SQL-Tuning-with-Explain-Plan

Report message to a moderator

Re: ora-01555 snapshot too old. Query executed 4000s [message #652331 is a reply to message #652326]

Tue, 07 June 2016 07:42

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>What could be the reason?
The long running SELECT is the victim; not the culprit.

Some session is concurrently doing DML against genmst_customer & doing frequent COMMIT. It is this session & SQL that is the culprit & needs to be changed so that it does a single COMMIT at the end.

Report message to a moderator

Re: ora-01555 snapshot too old. Query executed 4000s [message #652338 is a reply to message #652321]

Tue, 07 June 2016 10:05

FDAVIDOV
Messages: 20
Registered: December 2014

Junior Member

The use of "UPPER" might imply full table scan.

Can you avoid it? (for instance, if the customer name is always used for search, add a column with a copy of the customer name in upper case and set an index on it; once you have done that, you always will convert the parameter to upper case and then use it in the "where" clause).

By the way, your query has upper(TXT_customer_name) Like :"sys_B_0" where "sys" is in lower case.

Report message to a moderator

Re: ora-01555 snapshot too old. Query executed 4000s [message #659544 is a reply to message #652338]

Fri, 20 January 2017 10:58

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

your query has upper(TXT_customer_name) Like :"sys_B_0" where "sys" is in lower case.

And then? :"sys_B_0" is a bind variable not data which would be between single quote.

[Updated on: Fri, 20 January 2017 10:59]

Report message to a moderator

Previous Topic:	query Dead Slow
Next Topic:	Tuning update query - for more than 3 billion rows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 21:07:03 CST 2025