why using cursor to delete table is very slowly - Python

rr=curs.execute ("delete from python_modules where FILE_PATH = 'haha'")---- 6900.27
db.commit() ---- 3.3
using curs to delete table is very very slowly, why ???

Do you have an index in FILE_PATH column? Do you have your statistics up to date? 

It depends on a number of factors, most of which are to do with the database and nothing to do with Python:
- How many rows are in the table?
- How big is each row?
- How much space do you have allocated to rollback segments, temporary tablespaces and swap space?
- How much memory do you have on the database server?
- What other transactions are going on in the database when you issue the delete command?
- What other processes are running on the database server?
Having said that, the first thing to investigate is how long this statement takes without Python getting involved. Issue the delete statement from a SQL*Plus command line and compare the execution time. If it is similar to the time you posted using Python then I think that you can rule out 'using cursor to delete table is very slowly'


ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], []

Hi everyone,
I'd like to start by thanking the OWM-team for promptly answering to all my pains/questions regarding the OWM.
It seems we encountered another weird thing which i could pin down to the OWM only today ... here it goes :
We have about 20-30 tables out of only one is version-enabled (by the name USERSTATUS) and has 31 workspaces (one for each day of the month, since we need to keep track of what users (entries in the table) did on a daily basis).
Now, there are about over 2000 queries that we run on our tables (lot of inner/outer joins with filters) generating different reports (saving the query-results in files), but for some queries at certain points (NOT CONSISTENTLY !!!! sometimes they work sometimes they don't) we get the following error message :
ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [],
Where the arguments sometimes vary (as in there's something else instead of the "qernsRowP") in the error message.
We've found a workaround but I'm realy not satisfied with this, especially because of something that happened today (i didn't get the error message while running a query, but wrong results as in one of the filters (eg. REGION.PKID = 4) in the query wasn't counted for) .
I pinpointed this to the OWM since the query joins 5 tables, one of them being version-enabled.
My test was the following :
1. i copied the contents of the version-enabled table into a newly created table
2. replaced in the query all the "USERSTATUS" with "USR_BAK"
And the result of the query was correct.
So the moment I ran the same query but on a table without versioning that contained the same data as the version-enabled data, my results were correct.
As I already stated we found a workaround ... we started changing the contents of the "WHERE" clause (put the clauses in different orders, changed the filters to different tables (where we could)) and by doing so we don't get the error message.
One of the simplest ways to trick oracle not giving us the "ORA-00600 : internal error ..." was to put a condition like : " OR 0 = 1 " (which is bogus but it works).
To give you an example for a better understanding :
if i run my query with the condition
".... REGION.PKID = 4 AND ......" it won't work, but if i do it like this
".... ( REGION.PKID = 4 OR 0 = 1 ) AND ...." it works perfectly.
My newest concern is that we don't even get the "internal error" message in certain situations, but get bogus results (like today ... one of the conditions was totally ignored).
It seems that it also has something to do with "GROUP BY" statements, since all the problems we had till now were with queries containing the "GROUP BY" statement.
I realy need a fast answer on this since we delivered a product that will actually go into testing on the client side tomorrow and the client starts to have serious doubts about the product.
Another question :
Same version-enabled table (USERSTATUS) with 31 workspaces and about 10000 entries. The structure is LIVE workspace which has 31 workspaces.
I did a "DELETE FROM" from the LIVE workspace, removing all data from the LIVE workspace.
Afterwards I did a refresh on all the workspaces, as a result having them all empty.
Took a peek into USERSTATUS_LT for curiosity and found about 130,000 entries. How come ? What would that data represent ? Please note that I'm not modifying the _LT table just looking into it out of pure curiosity and performance doubts.
Info :
ORACLE version is
Thank you in advance,
This appears to be a problem with the optimizer. I have listed a few potential workarounds that may work in your case. I am unable to say definitively if the issue you are seeing is identical to the ones I had found based on the qernsRowP error code. They are :
SQL> alter system flush shared_pool ;
SQL> alter system set events '10119 trace name context forever';
SQL> alter system set events '10195 trace name context forever';
One of the 'alter system' statements should fix your problem. The shared pool needs to be flushed beforehand so that the old optimizer plan is not used. Another possibility is to use the /*+ RULE */ hint, but this may adversely affect performance.
As for rows not being deleted from LT.  When a delete is done on a versioned table, this does not translate to a delete from LT since those affected rows may be needed by other workspaces. Instead, we flag the row as deleted. So, the solution would be to call compress on the LIVE workspace. Compress will remove all rows that are not the LATEST rows in the workspace.
SQL> execute dbms_wm.compressworkspace('LIVE') ;
You can also choose to only compress between specified savepoints as with the following:
SQL> execute dbms_wm.compressworkspace('LIVE', '<savepoint1>', '<savepoint2>') ;
Hope this helps,
thank you for your time.
Regarding the LT table size, I will try to do the Compress. Remember i have cleaned out ALL workspaces (including LIVE) from ALL data, and i still had a 120K entries in the LT.
I will try later on today the CompressWorkspace();
I have another question regarding performance. I played a bit yesterday with performance since we had at a point (sometimes after flushing LIVE, but still keeping data in the 31 child workspaces) a major peformance decrease.
At a glance the same query with the versione-nabled table took about 20 minutes, while the same query but with a table with the same contents as the version-enabled table but not version enabled (done with 'create table xxx_bak as select * from xxx' where xxx would be the version-enabled table) took around 5-6 seconds.
What realy troubles me Ben, is that before getting the "ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [] " my query returned erroneous results. I had a filtering condition ("REGION.PKID = 3") which wasn't taken into account.
As long as i get error messages is one thing but the moment i get an ok but results that are erroneous i start to worry.
You have any clue how could've this happened?
I still kept the state of our machine the same in case we can investigate further.
Let me know if you need more info on this and have some time for it.
Basically I had a :
AND Y.COL_2 = 3
And the results (the SUM) contained not just Y.COL_2 = 3 but all of them Y.COL_2 -s, and funnny thing is that X.COL_2 was shown as "3". So instead of having in the sum only a part i had all of them summed up, while still showing X.COL_2 as only what i was interested in.
Thank you for your patience and time,
1. When you say you have cleaned out all of the workspace, how are you doing it ? Only removeworkspace and compressworkspace translate into deletes from the LT tables, and removeworkspace cannot be run on LIVE.  All of the dmls(insert, update, and delete) will either insert or update a row from the LT table.
2. This is hard to say without more information. Is this a join or a simple select ? If its a join, are all of the tables versioned enabled or only a select number? How big are the tables, and how many rows are typically returned ?
Also, anytime you take a table/view and create a smaller table based on only the data that you need for an individual query, the performance is going to improve. However, I do agree that the statement should not be performing as badly as it appears to be. If you could look at the plans with and without the versioned table(feel free to post them) and see if an index is being used in 1 case, but not in the other, that would indicate a potential issue. In other words, I just need a lot more information to be able to figure out what is going on here.
3. This is most likely an optimizer problem. If you could post the plan that the database is using, that may help. If you have a small example(with actual data) that would demonstrate this problem, please post that as well.
SQL> #?/rdbms/admin/utlxplan
SQL> set tab off
SQL> set pagesize 10000
SQL> set pagesize 150
SQL> explain plan for <sql statement> ;
SQL> #?/rdbms/admin/utlxpls
Also, is Y.COL_2 always a constant ? If so, you could remove the 'group by' since X.COL_2 can only have one value which would be known before running the statement...
1. I'm sorry i used a very non-technical terminology for it. What i meant is that I delete all contents of the workspaces ('gotoWorkspace' followed by 'DELETE FROM') including the contents of the LIVE workspace, therefore I will have no data AT all in all of my workspaces (in LIVE and in it's 31 child workspaces), while i still have data in the _LT table (eg. 120,000 rows).
And i was wondering why i still have versioned rows for something I don't have anymore in any of the workspaces.
2. it is a JOIN between 5 tables (only one is Version Enabled). Tables are a this point of the following sizes (TABLE_1 -> 20 rows , TABLE_2 -> 20 rows, TABLE_3 -> 200 rows, TABLE_4 -> 1,500 rows (version enabled !!!!), TABLE_5 -> 150,000 rows.
The sizes of these tables are small because we're still in testing phase. Estimated sizes in production of TABLE_4 (version enabled) is 9,000,000 and TABLE_5 around 2,000,000,000 rows (for which we intend to implement table partitioning).
Returned row-number should be 1 (aggregate function SUM is in use).
Interestingly the flushing of the SHARED_POOL instantly brought the performance back for this query. Which realy troubles me since I'm not sure when I will be encountering the same performance degradation again.
Any hint will be highly appreciated on this matter.
3. Unfortunately I ran the commands you suggested (flushing the shared pool, setting events : 10119 and 10195). And one of them fixed the problem.
I was looking on the metalink for a few days now. But till now all i found is that inaccurate results are returned in some cases with (RPAD,LPAD, RTRIM and LTRIM) which is not the case (not using them in this particular query).
The Y.COL_2's value comes as a parameter from "outside" and I'm using it for a JOIN later on (I'm doing a JOIN with the result set), so I cannot give up on the GROUP BY.
Let me know if we can still do something with the explain plan so that i would post it.
If you could please guide me more on this subject since this subject is crucial for us (not having accurate results of queries).
Thank you in advance,
1. When you issue a 'delete from <versioned_table>' this will not delete the row from the LT table.  So what you are seeing is expected.  The reason that we do this is because the row(s) that are being deleted may be needed by other workspaces.  So if we physically deleted these rows, it could lead to inconsistencies in these other workspaces.  It would be extremely expensive to determine if a particular row is no longer needed by any workspace in the system, and these rows may be needed for historical reference regardless by using the HIST view or gotoSavePoint/gotoDate APIs.
Instead we either insert or update a row(depending on the current version of the row) into the _LT table and flag it as being deleted for this particular workspace so that it does not show up in the versioned view for that workspace only.  If you need to physically delete the rows, and you are sure that you no longer need any of the old data, then you can use one of the following APIs: compressworkspace, rollbackworkspace, rollbacksp, or removeworkspace.  Please see the workspace manager documentation for a description of each and their intended use.
2. It seems that since flushing the shared pool fixed the performance problem, the database was using a stale plan for the statement which was no longer appropriate. If you have a explained plan of the statement when there had been a performance problem, please post that along with the new plan and the actual sql statement.
3. I have found instances where a merge join in a plan can potentially lead to invalid results. Is there one in the plan ? Please post the actual sql statement and the explained plan, as that will be able to tell me if this is the case.
Also, if posting the explained plans for #2 and #3, please include any indexes on the tables that are involved in the query, and please format any explained plans for easy reading by doing the following:
SQL> set tab off
SQL> set linesize 150
SQL> set pagesize 10000
Thank You,
Thanks again for your answer Ben.
1. I'm sorry my explanation must've been foggy.
I did a 'DELETE FROM' in all the workspaces and in 'LIVE' too. So there was no workspace that still contained anything. Again there was no row that would still be in any workspace.
My initial question was why is a row still in the _LT while it is in no workspace anymore (including 'LIVE') ?
I think i got my answer though. That i should use 'CompressWorkspace'
2. Another question : We did a disable versioning on a table of 10080 entries which has 31 workspaces. On a V240 (dual processor, with SCSI hard drives 10K RPM, 8MB Cache) it took a loooot of time, to be more precise over 4 hours.
Did i miss an index or ... ?
Thank you in advance,
Yes, compressworkspace is the best way to remove rows from the _LT table that you no longer need.
As for disableversioning taking a long time, could there have been a lock on the table being held by another session that was blocking the procedure from moving forward ? Also, does the LT table have 10080 entries or is that the result of a 'select count(*) from <tablename>' from a particular workspace?
If the problem cannot be attributed to a lock, then there is a particular statement during disableversioning that is most likely taking up a large amount of the time. To identify this statement, you can run the following (if you are able to reproduce the performance problem) :
SQL> alter session set events '10046 trace name context forever, level 12' ;
SQL> exec dbms_wm.disableversioning('<table_name>') ;
You can then run tkprof of the resulting trace file which will allow you to identify which statement(s) are taking the majority of the time. Please post any such statements and any accompanying information(plans, wait events) from the file generated by tkprof.

embedded database storage dimension

We are planning to use Oracle XE as an embedded database in our application. Since we store data taken from our control application, some tables grow in a fast way. I implemented a PL/SQL procedure with the purpose to monitor the number of rows in the table and delete a predefined percentage of the old rows. In this way the number of row should always be under a predefined limit. Is this enough in order to keep the storage size always under a predefined value? Should I do some operations on other tablespaces (i.e. undo tablespace)?
it depends how you insert new rows, if you are appending them using /*+APPEND*/ then you will quickly go over the space.  However if you delete rows then re-add the normally the space should be re-used                                                                                                                                                                                                                                                                                                                                                                                                                    
I don't know the function of the /*+APPEND*/ statement, I add new rows with the INSERT statement.                                                                                                                                                                                                    
/*+APPEND*/ inserts above the high water mark, so it doesnt re-use any existing free space.
Seeing as though you dont know what it is, I guess you arent using it. With a normal insert statement you are able to reuse the space freed up by deletes. So you should be fine 
Ok, thanks,

Exadata INSERT and massive "cell single block physical reads" on UNDO tablespace?

For simple INSERTs on table with no contention or locks from other sessions -- first, a general question -- the INSERT will of course generate UNDO, but while the INSERT is running, why would it need to read UNDO? In several different Exadata environments that I work with -- same code base -- on a random basis for a small subset of tables, the "cell single block physical read" against UNDO accounts for I/O that's 10x or 100x the size of the table+index itself. When INSERT is killed, rolled back, and re-run, it runs in a fraction of the time (45 minutes instead of 10+ hours). The ASH report doesn't show the writes, they are really a very small percentage of the overall I/O: Top Event P1/P2/P3 ValuesEvent% EventP1 Value, P2 Value, P3 Value% ActivityParameter 1Parameter 2Parameter 3cell single block physical read37.45"1141149241","3623262296","8192"1.72cellhash#diskhash#bytescell single block physical read37.44816586921850079744816586921850079745"1796605293","1614600483","8192"1.69cellhash#diskhash#bytescell single block physical read37.44816586921850079744816586921850079745"1796605293","1070539091","8192"1.63cellhash#diskhash#bytes Top SQL with Top EventsSQL IDFullPlanhashPlanhashSampled # of Executions% ActivityEvent% EventTop Row Source% RwSrcSQL TextContainer Name0cb9s2ypsczyr29188048744077444551100.00CPU + Wait for CPU62.33LOAD TABLE CONVENTIONAL62.04INSERT /*+ NOAPPEND */ INTO "C...ORAP080cb9s2ypsczyr29188048744077444551100cell single block physical read37.45LOAD TABLE CONVENTIONAL37.45ORAP08Back to Top SQL Back to TopTop SQL with Top Row SourcesSQL IDFullPlanHashPlanHashSampled # of Executions% ActivityRow Source% RwSrcTop Event% EventSQL TextContainer Name0cb9s2ypsczyr29188048744077444551100.00LOAD TABLE CONVENTIONAL99.71CPU + Wait for CPU62.04INSERT /*+ NOAPPEND */ INTO "C...ORAP08 Top DB ObjectsWith respect to Application, Cluster, User I/O, buffer busy waits and In-Memory DB events only.Tablespace name is not available for reports generated from the root PDB of a consolidated database.Object ID% ActivityEvent% EventObject Name (Type)TablespaceContainer Name56868036.62cell single block physical read36.46CLARITY.MYC_PT_USER_ACCSS (TABLE)EPIC_REPORTINGORAP0856868036.61881977671451355661881977671451355662cell single block physical read36.46CLARITY.MYC_PT_USER_ACCSS (TABLE)UNDOTBS2ORAP08  I can't find a "bad" SQL Monitor report in AWR; only the short ones are being monitored. What else can I do to monitor what is going on with all the UNDO activity? Again, there is no other session with any transactions open on the table, much less any share locks or any activity at all.
Ok, the question is a bit older, I found it by accident.The reason for your issue might be that the database is following the undo chain to construct a read consitent image of a block. This blog might explain why your statement is not showing in sql_monitor: Real-Time SQL Monitoring - Statement Not Appearing - Doug's Oracle Blog 
Thanks -- that's what we initially thought, but in no cases were any other processes doing any DML or even SELECT or had any uncommitted transactions.
Has the insert a select part where you are directly or indirectly retrieving the rows you are inserting?Make a copy of your table and rerun the insert against this to find out if there are any specifics of the table involved.
When the insert is running it may scan the undo to ensure the ITL slots of that block are clean and do not have any pending transactions left over with the previous DML transactions(insert,update,delete) on the same block since the header of the block may/may not get updated as soon as the transaction is committed due to dealyed block cleanout.Dealyed block clean out is when the active transactions happen it will update the datablock header and maintain the rollback seg/transaction info for any rollback and after user  commits the data,the header of the block may not get updated as commited and cleaned,but the rollback segment will have the status as COMMITTED for that block. For subsequent transactions hitting the same block,database will scan through undo segments,see the status of COMMITTED and cleanout the header of the block and do the actual transaction by claiming the ITL slot of that block. ThanksSreeram 
That seems very plausible -- these INSERTs are generally preceded by a massive number of DELETEs (with a COMMIT after the DELETE). These tables might get 10% of the rows deleted, then most of those rows re-inserted plus another 1-2% of the total number of rows. So how to confirm (with what kind of trace) that a delayed block cleanout is happening, and more importantly, how to avoid the delayed block cleanout? Force a more efficient cleanout before the INSERT? Thanks.
Hi You can check from the below sql for the amount of cleanouts and rollbacks,consistent read gets.To avoid this as  a simple test,can you do a simple select on the tables that are invovled in the delete before you actually insert it ,the reason is SELECT once would clear all the clean outs for those blocks and your insert would certainly eliminate scanning undo segments. SELECT name, valueFROM v$mystat, v$statnameWHERE v$mystat.statistic#=v$statname.statistic#AND name IN ('consistent gets','db block gets','cleanouts and rollbacks - consistent read gets','cleanouts only - consistent read gets')ORDER BY 1 ThanksSreeram
Thanks, I'll try that. We ran a lot of ASH reports, saw a huge amount of UNDO access -- but not sure if the "delayed block cleanout" event wait would show up there.
Well, actually the commit does not change things.To analyze the exasnapper could be helpfull too: https://github.com/tanelpoder/tpt-oracle/blob/master/exadata/exasnapper_install_latest.sqlHow will you avoid delayed block cleanout? Well, first of all do not delete. Delete is an extremly expensive operation. Sometimes it is better to create a new table and insert the rows you want to keep. Drop the old table, rename to new table to the old tables name, etc.The more obvious option would be to use direct path insert instead of the conventional insert. That will write after the HWM and therefore no delayed block cleanout will happen.You will waste some space though.
Thanks -- we are definitely moving in that direction for our ETL, especially for Exadata customers -- even on traditional Oracle platforms, it may very well be faster to copy the table to a new one, exclude the "deleted" rows, and add the "new" rows. What's troubling is that we could be satisfied for now with consistent performance, even if it's not super-fast. But when we have 1000 DELETEs and 1200 INSERTs on a given day, and it runs in 20 minutes on one day but takes 6 hours another day with the same available resources, that's what makes end users very unhappy.
If your version is >= 12 you can try to find a longruning statement in historical monitoring. You can set a threshold for the runtime. In the script below it is 1000 seconds. You should als query for the sql_id. https://oracleriddleblog.wordpress.com/2018/01/12/how-to-create-multiple-sql-monitor-reports-from-history/
Thanks, I'll give this a try. Even when I make sure there's enough space in the shared pool, use the MONITOR hint, run in parallel, and set the hidden max plan lines parameter very high, it's sometimes still hit or miss as to whether the historical SQL Monitor report is saved in the AWR.
Yes, i know this issue. I assume it happens when there is not enough memory in the shared pool. I guess if the resource is not there, all warranty is off.

Warning! : SYSTEM tablespace devoured by ERRORS table

I'm indexing 150,000 rows in a table
and the DR$INDEX_ERROR table fills up.
Not only does it fill up the table, it fills
up the rest of the SYSTEM tablespace as well.
I fed it over 1Gb but it still used all this
to generate rows in the error table. I
estimate that at least three times as much
error data was generated than actual data!
This has the net effect of swamping the
entire database. Plus, It's not possible
to delete this table properly, since it
creates loads of horrendously large TEMP
segments floating around that I can't get
rid of.
Anyone else suffering from these symptoms?
(version 8.1.5 on solaris 2.6)
Yep, I had the same experience. I was trying to index a table with 750,000 rows and varchar(4000). The process kept running from noon into the wee hours of the night until I figured something must be wrong. I killed the process and was stuck deleting the dr$index_error table with 8 million rows. This took a while and eventually reduced the drsys tablespace to a managable size.
However, the side effect was the incredibly expanding rollback tablespace.
I had to drop user ctxsys cascade, and rebuild the intermedia user and objects from scratch, and drop the rollback tablespace and rebuild it from scratch because it wouldn't resize from 800MB down to 300MB with shrunken rollback segments and no pending transactions.
The following day I tried a significantly more managable job: index 750000 rows of varchar(200). Job was completed, but I'm still suffering from the experience. Must be a bug.
This was 8.1.6 and linux.
Thanks, I'll check this with Oracle.
I'll try the same data, but this time store it in CLOB columns and see if that makes any difference.
I hope there's a patch available for this.

sanphot too old - read consistiency

I think this is a basic thing (release I must say I don't know how rollback segments really work.
A table, where new records are continuously inserted and the old ones can be updated in short transactions, should be purged every day by deleting old records.
This purge has never been done and as a result it has now almost 4 million records, and when I launch the stored procedure that deletes the old records I get the "snapshot too old" error because of the read consistency.
If I launch the procedure after stopping the application that inserts and updates in the table, then I don't get the error. I guess the problem is that meanwhile the procedure is being executed other transactions also need to use rollback segments so that the rollback segment space that the snapshot needs isn't enough. Do you think this is the problem?
If this is the case then I suppose that the only solution is increasing the size of the only datafile of the only tablespace for my 4 rollback segments. Am I wrong?
One possible solution could be not deleting the whole table at once but only a few records? Would this work? Why when I try "select count(*) from my_table where rownum = 1" I also get "snapshot too old" when other transactions are running?
Why do I get the same error in two different databases, in the first the size of the datafile of the undo tablespace is 2GB whilst in the second it is only 2MB? How can I know how big the datafile has to be?
(One more question:
- What is a discrete transaction?)
I'm a developer, not a dba, but don't tell me to ask my dba because it isn't that easy. Thanks in advance.
Thaks in advance.
Try increasing the undo retention time