Extremely slow updates to small Oracle table from rails app (oracle r12 and r19)
1
vote
0
answers
274
views
I'm not sure if this is a rails issue, or a database issue, but as this seems to be a schema specific issue, I'm looking at it from the database side first. (Other customers with the same setup on the same app/database server are not having the same problem)
I have a rails 4.2 app connecting to an Oracle database. From the application an simple update to a 39 record table via primary key can take upwards of 80 seconds:
SQL (83098.4ms) UPDATE "ENTITY" SET "ENTITY"."ADDRESS_1" = NULL, "ENTITY"."ADDRESS_2" = NULL, "ENTITY"."ADDRESS_3" = NULL, "ENTITY"."ADDRESS_4" = '' WHERE "ENTITY"."ENT_ID" = :a1 [["ent_id", 2144]]
If I run this update from sqlplus, it runs almost instantly:
SQL> UPDATE "ENTITY" SET "ENTITY"."ADDRESS_1" = NULL, "ENTITY"."ADDRESS_2" = NULL, "ENTITY"."ADDRESS_3" = NULL, "ENTITY"."ADDRESS_4" = '' WHERE "ENTITY"."ENT_ID" = 2144;
1 row updated.
Elapsed: 00:00:00.018
I'm not sure if bind variables could impact this, or if this is the same from sqlplus, but trying with bind variables also runs near instantaneously:
SQL> exec :id := 2144;
SQL> UPDATE "ENTITY" SET "ENTITY"."ADDRESS_1" = NULL, "ENTITY"."ADDRESS_2" = NULL, "ENTITY"."ADDRESS_3" = NULL, "ENTITY"."ADDRESS_4" = '' WHERE "ENTITY"."ENT_ID" = :id;
1 row updated.
Elapsed: 00:00:00.012
There is an primary key + index on this table, that is valid and rebuilds also near instantly:
SQL> select count(*) from entity;
COUNT(*)
39
SQL> select constraint_type, constraint_name, status from user_constraints where table_name = 'ENTITY';
CONSTRAINT_TYPE CONSTRAINT_NAME STATUS
P ENT_PK ENABLED
SQL> select index_name, index_type, status from user_indexes where table_name = 'ENTITY';
INDEX_NAME INDEX_TYPE STATUS
ENT_PK NORMAL VALID
SQL> alter index ent_pk rebuild;
Index ENT_PK altered.
Elapsed: 00:00:00.015
There is only one trigger on this table - an INSERT trigger, so shouldn't be involved in an update (I don't think), but disabling it has no impact either way.
The explain plan for the update seems pretty straight forward - just update via index rowid:
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 233 | 1 (0)| 00:00:01 |
| 1 | UPDATE | ENTITY | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| ENTITY | 1 | 233 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | ENT_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
The issue persists if I export the schema and import it into a different schema, and even into a different database - even going from an Oracle 12 database to an Oracle 19 database.
As mentioned at the start, I'm at a loss as to how the same application server and database run this update fine on most schemas (which are perforce identical in structure), but this one particular schema runs this one update very very slowly - but only when run via OCI and not when run from SQL.
Is there any way to get more information from Oracle as to what it's doing during this update?
Asked by Dave Smylie
(145 rep)
Nov 4, 2021, 03:37 AM
Last activity: Nov 4, 2021, 04:08 AM
Last activity: Nov 4, 2021, 04:08 AM