Solved

ORA-01555 error

Posted on 2006-11-17
11
920 Views
Last Modified: 2012-06-21
I am getting this when I run the following script after about an hour and 45minutes of processing:



="ORA-01555: snapshot too old: rollback segment number 1 with name ""_SYSSMU1$"" too small

 

how can i fix this problem?
0
Comment
Question by:jung1975
11 Comments
 
LVL 10

Assisted Solution

by:ravindran_eee
ravindran_eee earned 100 total points
ID: 17970249
Either tune ur query to run quickly or increase the rollback segment size. Please go thru this link for the actual meaning of that error message.

ORA-01555:      snapshot too old: rollback segment number string with name "string" too small
Cause:      rollback records needed by a reader for consistent read are overwritten by other writers
Action:      If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

http://www.cryer.co.uk/brian/oracle/ORA01555.htm

Good luck!
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 100 total points
ID: 17971549
the space required by oracle to maintain read consistency ( i mean it has to ensure that the data remains the same for you when you opened a cursor/fetched it but at the same time many other users are updating the data in the table which have already fetched. ).

Oracle uses rollback segments to go ahead with the above and also to undo the changes in case if users issue rollback.

In your case,  oracle processes has overwritten your program/sql query read data kepy in buffer ( memory ) and so now end up with this error.

As suggested already, either u need to increase size of the rollback segments or try to see as to what other operations/programs are happening/running on the data you are processing and see as to why is this happening at the same time.

let us say, in your case by mistake a scheduling of job which updates the table from some other table can be running at the same time or something of that sort is happening if rollback segments have enough size and you dont want to increase it any more.

Thanks
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 17972085
My first choice would be what ravindran suggested: tune the script so it runs faster, maybe by using existing indexes better, or by adding, changing (or even removing) indexes to support the query, or by using a global temporary table, etc.  Unless you have a huge database, or a very complex script, it shouldn't take an hour and a half to run.  If you post the script here, we may be able to offer some suggestions for tuning it.

You could try making the rollback segments larger, but this may not help.  If you have Oracle9 or 10, you could try using automatic undo instead of rollback segments.

Two other options:
1. run this script at times when the database is relatively quiet, not during the peak use time of the day
2. change the script so it does frequent commits
0
 
LVL 2

Assisted Solution

by:BarryMcGillin
BarryMcGillin earned 100 total points
ID: 17978086
how about using savepoints?

Put a savepoint in at the start, then commit as often as you need to to.  this will keep down your undo and hence keep ur rollback in check.

If you come across a problem, rollback to the savepoint.  

The reason for suggesting this is that your process, whatever it is doing is taking a long time, so you have no control over it.  this way, you have control over what is commited or rolled back without the worry of blowing up ur rollback segments.

B
0
 

Author Comment

by:jung1975
ID: 17980604
Here is the query that I am trying to run.. what should i change to make it works?

SELECT

'1-oct-05' as service_period_from,

'30-sep-06' AS service_period_to,

 unit_abbr,

 unit_name,

 ACCT,

 personal_first_name,

 personal_m_name,

 personal_last_name,

 personal_address,

 personal_city,

 personal_state,

 personal_zip,

 CLAIM_NUM,

 UNIT_NUM,

 CLAIM_SRV_DATE_FROM,

 CLAIM_SRV_DATE_THRU,

 Sum(total_Charges) total_charges,

 Sum(totalAdjustments) total_adjustments,

 Sum(Account_balance) total_balance,

 Sum(insurance_payments) insurance_payments,

 Sum(patient_payments) patient_payments,

 Sum(insurance_pending) insurance_pending,

 Sum(patient_balance) patient_balance,

 

 TREATMENT_ITEM_DESCRIPTION,

 insurance_name,

 hipaa_ins_name

 

 from

 

 

 

 

 (

        SELECT          --this section of the union pulls total charges, total adjustments, account balance

 

 

        un.unit_abbr,

        un.unit_name,

        pp.personal_ref_id AS ACCT,

        pp.personal_first_name,

        pp.personal_m_name,

        pp.personal_last_name,

        pp.personal_address,

        pp.personal_city,

        pp.personal_state,

        pp.personal_zip,

        CH.CLAIM_NUM,

        CH.UNIT_NUM,

        CH.CLAIM_SRV_DATE_FROM,

                  CH.CLAIM_SRV_DATE_THRU,

        CH.CLAIM_TOTAL_CHARGE total_Charges,

        CH.CLAIM_TOTAL_ADJUSTMENT totalAdjustments,

        (CH.CLAIM_TOTAL_CHARGE - (CH.CLAIM_TOTAL_CREDIT + CH.CLAIM_TOTAL_ADJUSTMENT)) as Account_balance,

        NULL AS insurance_payments,

        NULL AS patient_payments,

        NULL AS insurance_pending,

        NULL AS patient_balance,

 

                          ra.GET_ITEM_DESCR_FOR_PBP_RPT (CH.CLAIM_NUM) TREATMENT_ITEM_DESCRIPTION,

      i1.insurance_name,

      i1.hipaa_ins_name

 

       FROM ra.CLAIM_HEADER CH

       left join ra.patient_personal pp ON ch.personal_num = pp.personal_num

       left join ra.unit un ON ch.unit_num = un.unit_num

       left join ra.insurance i1 ON ch.insurance_num_p  = i1.insurance_num

       left join ra.insurance i2 ON ch.insurance_num_s  = i2.insurance_num

       left join ra.insurance i3 ON ch.insurance_num_t  = i3.insurance_num

 

 

       WHERE

 

       --(un.unit_abbr = 'A01') AND

                           (CH.CLAIM_STATUS <> 'C') AND (CH.CLAIM_STATUS <> 'N') AND

 

         ('1-oct-05' IS NULL OR CH.CLAIM_SRV_DATE_FROM >= '1-oct-05') AND

         ('30-sep-06' IS NULL OR CH.CLAIM_SRV_DATE_THRU <= '30-sep-06') --AND

 

               

 

 

        GROUP BY

        un.unit_abbr,

        un.unit_name,

        pp.personal_ref_id ,

        pp.personal_first_name,

        pp.personal_m_name,

        pp.personal_last_name,

        pp.personal_address,

        pp.personal_city,

        pp.personal_state,

        pp.personal_zip,

        CH.CLAIM_NUM, CH.UNIT_NUM, CH.CLAIM_SRV_DATE_FROM, CH.CLAIM_SRV_DATE_THRU, CH.CLAIM_TOTAL_CHARGE,

        CH.CLAIM_TOTAL_CREDIT, CH.CLAIM_TOTAL_ADJUSTMENT, ra.GET_ITEM_DESCR_FOR_PBP_RPT (CH.CLAIM_NUM), i1.insurance_name, i1.hipaa_ins_name

 

 

 

 

 

 

 UNION ALL

 

 

 SELECT                   ---this section of the union pulls insurance payments and insurance pending

 

 

        un.unit_abbr,

        un.unit_name,

        pp.personal_ref_id AS ACCT,

        pp.personal_first_name,

        pp.personal_m_name,

        pp.personal_last_name,

        pp.personal_address,

        pp.personal_city,

        pp.personal_state,

        pp.personal_zip,

        CH.CLAIM_NUM,

        CH.UNIT_NUM,

        CH.CLAIM_SRV_DATE_FROM,

                 CH.CLAIM_SRV_DATE_THRU,

        NULL as total_Charges,

 

        NULL as totalAdjustments,

        NULL as Account_balance,

 

     cp.claim_page_total_credit AS insurance_payments,

       NULL AS patient_payments,

     cp.current_balance AS insurance_pending,

       NULL AS patient_balance,

 

                          ra.GET_ITEM_DESCR_FOR_PBP_RPT (CH.CLAIM_NUM) TREATMENT_ITEM_DESCRIPTION,

      i1.insurance_name,

      i1.hipaa_ins_name

      --i2.insurance_name,

      --i2.hipaa_ins_name,

      --i3.insurance_name,

      --i3.hipaa_ins_name ,

 

   FROM ra.CLAIM_HEADER CH

   JOIN ra.claim_page  cp ON cp.claim_num = ch.claim_num

   left join ra.patient_personal pp ON ch.personal_num = pp.personal_num

   left join ra.unit un ON ch.unit_num = un.unit_num

   left join ra.insurance i1 ON ch.insurance_num_p  = i1.insurance_num

   left join ra.insurance i2 ON ch.insurance_num_s  = i2.insurance_num

   left join ra.insurance i3 ON ch.insurance_num_t  = i3.insurance_num

 

   left join ra.insurance cpi ON cp.insurance_num = cpi.insurance_num

 

   WHERE

 

 

       --(un.unit_abbr = 'A01') AND

                           (CH.CLAIM_STATUS <> 'C') AND (CH.CLAIM_STATUS <> 'N') AND

 

         ('1-oct-05' IS NULL OR CH.CLAIM_SRV_DATE_FROM >= '1-oct-05') AND

         ('30-sep-06' IS NULL OR CH.CLAIM_SRV_DATE_THRU <= '30-sep-06') --AND

 

 

   AND cpi.hipaa_ins_name <> 'PATIENT'

 

 

    UNION ALL

 

 

 SELECT                              --this section of the union pulls patient payments and patient balance

 

        un.unit_abbr,

        un.unit_name,

        pp.personal_ref_id AS ACCT,

        pp.personal_first_name,

        pp.personal_m_name,

        pp.personal_last_name,

        pp.personal_address,

        pp.personal_city,

        pp.personal_state,

        pp.personal_zip,

        CH.CLAIM_NUM,

        CH.UNIT_NUM,

        CH.CLAIM_SRV_DATE_FROM,

                 CH.CLAIM_SRV_DATE_THRU,

        NULL as total_Charges,

 

        NULL as totalAdjustments,

        NULL as Account_balance,

 

        NULL AS insurance_payments,

        cp.claim_page_total_credit AS patient_payments,

        NULL AS insurance_pending,

        cp.current_balance AS patient_balance,

 

                          ra.GET_ITEM_DESCR_FOR_PBP_RPT (CH.CLAIM_NUM) TREATMENT_ITEM_DESCRIPTION,

      i1.insurance_name,

      i1.hipaa_ins_name

      --i2.insurance_name,

      --i2.hipaa_ins_name,

      --i3.insurance_name,

      --i3.hipaa_ins_name ,

 

   FROM ra.CLAIM_HEADER CH

   JOIN ra.claim_page  cp ON cp.claim_num = ch.claim_num

   left join ra.patient_personal pp ON ch.personal_num = pp.personal_num

   left join ra.unit un ON ch.unit_num = un.unit_num

   left join ra.insurance i1 ON ch.insurance_num_p  = i1.insurance_num

   left join ra.insurance i2 ON ch.insurance_num_s  = i2.insurance_num

   left join ra.insurance i3 ON ch.insurance_num_t  = i3.insurance_num

 

   left join ra.insurance cpi ON cp.insurance_num = cpi.insurance_num

 

   WHERE

 

   --(un.unit_abbr = 'A01') AND

                           (CH.CLAIM_STATUS <> 'C') AND (CH.CLAIM_STATUS <> 'N') AND

 

         ('1-oct-05' IS NULL OR CH.CLAIM_SRV_DATE_FROM >= '1-oct-05') AND

         ('30-sep-06' IS NULL OR CH.CLAIM_SRV_DATE_THRU <= '30-sep-06') --AND

 

 

   AND cpi.hipaa_ins_name = 'PATIENT'

 

  ) AA

     

  WHERE acct NOT IN

  (

775,

49161,

98472,

98500,

100456,

106456,

108343,

171788,

199170,

205832,

225211,

249176,

267319,

269308,

275134,

287032,

290764,

295498,

302875,

303718,

315322,

329061,

332919,

333778,

340579,

352675,

353951,

358831,

369513,

370446,

375669,

378186,

378484,

380845,

384558,

389466,

394651,

398894,

399808,

401309,

402905,

403042,

411853,

414435,

416424,

418126,

418320,

418522,

419131,

419619,

420118,

421391,

421416,

421831,

422567,

423318,

424489,

425213,

425465,

426121,

426185,

1008548,

1019939,

1020028,

1025637,

1027229,

1030463,

1050078,

1054939,

1055560,

1055657,

1055721,

1056328,

1089946,

1098382,

1104552,

1112333,

1112633,

1114231,

1151719,

1161019,

1163892,

1169417,

1174067,

1174146,

1175257,

1176561,

1176988,

1177801,

1179328,

1180274,

1192750,

1193085,

1193794,

1196321,

1196716,

1201827,

1202555,

1205156,

1206233,

1207270,

1210481,

1215814,

1216454,

1218932,

1220054,

1221355,

1221780,

1223382,

1223663,

1224474,

1234723,

1235429,

1236743,

1242401,

1243010,

1245351,

1246320,

1246731,

1251756,

1252028,

1252181,

9000172,

9000210,

9000214,

9000287,

9000521,

9000631,

9000691,

9000700,

9000763,

9000859,

9000883,

9000889,

9001012

)

 

  GROUP BY

 

 unit_abbr,

 unit_name,

 ACCT,

 personal_first_name,

 personal_m_name,

 personal_last_name,

 personal_address,

 personal_city,

 personal_state,

 personal_zip,

 CLAIM_NUM,

 UNIT_NUM,

 CLAIM_SRV_DATE_FROM,

 CLAIM_SRV_DATE_THRU,

 TREATMENT_ITEM_DESCRIPTION,

 insurance_name,

 hipaa_ins_name

 

 HAVING (sum(patient_balance))  <> 0

 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 200 total points
ID: 17980654
If you want Oracle queries to perform well, you avoid things like this:
1. nested sub-queries that return large sets of rows
2. "where" clauses like: [some column] <> [some value]
3. "or" conditions in "where" clauses
4. "not in" sub-queries in "where clauses"
5. "union" queries

This query includes all of these!  
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17980703
(I accidentally posted my previous comment before I finished it.)

No wonder that query is slow!!!

No, there is not a simple suggestion I can give you to speed this up.  There are many options that could be considered or tried:
1. materialized views
2. global temporary tables
3. PL\SQL tables
4. the "with" clause (if you have Oracle10)

But, this will be a major project that will require some testing, and may require some application an/or database changes.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17980723
Another option to try would be function-based indexes.

Basically it looks like you have a complex application that was written by someone who knew SQL well enough to get the correct answers, but that person either didn't know how to design an application for Oracle or write SQL efficiently for Oracle, or didn't care if the SQL performed poorly in Oracle.
0
 

Author Comment

by:jung1975
ID: 17991249
how can i increase the size of rollback segment?

this is the production server, so I want to really careful... is they any way it can harm the database by increasing the size of rollback segment?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 18229653
I did provide a number of suggestions that could most likely help significantly, but we never heard back from the person who posted the question on whether any of them were tried or not.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now