Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORA-01555 error

Posted on 2006-11-17
11
Medium Priority
?
944 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 10

Assisted Solution

by:ravindran_eee
ravindran_eee earned 400 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 400 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 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 800 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Assisted Solution

by:BarryMcGillin
BarryMcGillin earned 400 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
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 800 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 35

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 35

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 35

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

722 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