Home » RDBMS Server » Performance Tuning » ora-01555 snapshot too old. Query executed 4000s (10g, 10.2.0.5, hp-unix)
ora-01555 snapshot too old. Query executed 4000s [message #652321] Tue, 07 June 2016 04:47 Go to next message
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
Re: ora-01555 snapshot too old. Query executed 4000s [message #652326 is a reply to message #652321] Tue, 07 June 2016 06:02 Go to previous messageGo to next message
John Watson
Messages: 7620
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

Re: ora-01555 snapshot too old. Query executed 4000s [message #652331 is a reply to message #652326] Tue, 07 June 2016 07:42 Go to previous messageGo to next message
BlackSwan
Messages: 26207
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.
Re: ora-01555 snapshot too old. Query executed 4000s [message #652338 is a reply to message #652321] Tue, 07 June 2016 10:05 Go to previous messageGo to next message
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.
Re: ora-01555 snapshot too old. Query executed 4000s [message #659544 is a reply to message #652338] Fri, 20 January 2017 10:58 Go to previous message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Mon Oct 22 00:18:45 CDT 2018