I have a DB 18.104.22.168 ON Unix,
e have a performance issue the delete statements on the database are running very slow they are taking about 5 hours.
delete from FACT_VENTAS_DIA where ID_FECHA between to_date('01/01/2008','DD/MM/YYYY') AND TO_DATE('15/01/2008','DD/MM/YYYY');
And the delete doesn't end.
Couple suggestions you may have already done (or not).
Look in the alert log for any errors that might give clues.
Make sure there are no mount points full on your operating system (e.g., where archives go, etc.).
Then, you can look at your os performance and make sure you are not I/O bound or somethng else.
Lots of things to look for. Once you start doing your initial research, then you can query v$transaction, v$session_longops, v$sysstat, etc...
How much data are you expecting to delete?
What does it say in the v$session_event and v$session_wait views for the session which is deleting the data?
Are there high disk queues?
Are there lots of indexes which have to be maintained while the delete is happening?
In short, we need more information to help you. Slow compared to what? Is it somehting you normally do and it's taking longer, for example? It might be slow but it might be the quickest that your system is able to process the data so it might be optimal but you are asking it to be faster than is possible. There are so many possible answers to this question at the moment it's hard to help.
1. Is ID_FECHA indexed?
Yes, the ID_FECHA was indexed
2. Is there another table with a foreign key that points to ID_FECHA? If so, is that column in the other table indexed?
Yes.. and was indexed
But the problem persist,
Thank you. But DDL (create table... / create index...) would have been a lot more useful. You can use DBMS_METADATA.GET_DDL to obtain the DDL of already-built database objects.
So is ID_FECHA part of a unique key on this table? What are all the child tables of FACT_VENTAS_DIA? It is odd to have a table with the term "fact" in it be a parent table to other child tables. But you indicated that there are indeed other tables with foreign keys that point back to this table. That's a red flag.
what is the size of the index on the date column?
ACT_VENTAS_DIA' AND COLUMN_NAME = 'ID_FECHA');
how many rows in the table?
But for my where has 20000 registers
what is the size of the table?