Why is the schema/structure dump so slow

dumporacle-11g-r2oracle-xerails

I'm using Rails 6 with activerecord-oracle_enhanced-adapter adapter for Oracle 11 XE database.
At the beginning of the project I was working with Postgres, but due to customer requirements I had to migrate to Oracle.

My question is very similar to https://stackoverflow.com/questions/592444/rails-rake-dbmigrate-very-slow-on-oracle, but it's from 11 years ago, i'm using the updated version of the adapter.

I know that after all migrations are applied to database then rake db:migrate calls db:schema:dump task to generate schema.rb file from current database schema. I only have one schema, with many tables (around 90)

The answer for the previous question was something like

One way how to debug this issue would be if you would put some debug
messages in oracle_enhanced_adapter.rb file so that you could identify
which method calls are taking so long time.

== 20200820164111 RemoveSemesterFromProject: migrated (0.1347s) ===============
real    13m26,038s
user    0m5,494s
sys 0m0,401s

All migrations finished in an reasonable time, so I think the error occurs in the following action "db: schema: dump" but i don't have oracle_enchaced_adapter.rb neither oracle_enhanced_adapter.rb, so i don't know where to look at.

What can i do for improve this behaviour?

Time that takes when i run db:schema:dump

real    9m44,074s
user    0m3,701s
sys 0m0,327s

and this is the time that takes when i run db:structure:dump

real    21m40,073s
user    0m5,046s
sys 0m0,413s

Best Answer

This may be a consequence of slow queries issued by activerecord-oracle_enhanced-adapter to determine the database structure, eg. issues/2004

Related Solutions

Error when load db dump (cross platform)

Ok ! Let me describe how this issue was solved:

1) There was definitely a corruption issue caused by a problem with vmware tools on solaris 10. When the network interface had high transfer/load operations (sample: copy of a 2 GB DB ....), it just stoped working, in the middle of the operation. To put the interface working again, I had to disconnect and connect the network interface again (in the vmware interface !). Basically, I had to unistall vmware tools on solaris virtual machine. There was one catch, the highest transfer rates that could be achieved was about 300 Kb. Basically I could take hours to perform a simple ftp transfer of a 2 GB database, but there wasn´t any corruption at all. How to prove/test that there is/inst´t any corruption. I just packed (on the source machine) the database dump into a tar file (yes, an extra 20kb), but after the download complete, on the target server, I was able to untar the file, and if the untar operation was sucessfull that proved that there wasn´t any corruption on the file that was just transfered.

2) After being sure that the dump was ok, I got a diferent error:

Apr 17 14:24:20 2012: Backup Server: 4.188.1.1: Database wfcv2: 158936 kilobytes (1%) LOADED.
Apr 17 14:24:47 2012: Backup Server: 4.188.1.1: Database wfcv2: 303212 kilobytes (2%) LOADED.
Apr 17 14:25:16 2012: Backup Server: 4.188.1.1: Database wfcv2: 447104 kilobytes (3%) LOADED.
Apr 17 14:25:39 2012: Backup Server: 4.124.2.1: Archive API error for device='compress::1::/data4/20120413_wfcv2_zdump::000': Vendor application name=Compress API, Library version=1, API routine=syb_read(), Message=syb_read: gzread() error=0, msg=Error 0
Apr 17 14:25:39 2012: Backup Server: 4.124.2.1: Archive API error for device='compress::1::/data4/20120413_wfcv2_zdump::000': Vendor application name=Compress API, Library version=1, API routine=syb_close(), Message=syb_close: gzclose() error=-3 msg=Input/output buffer is corrupt
Apr 17 14:25:39 2012: Backup Server: 6.32.2.3: compress::1::/data4/20120413_wfcv2_zdump::000: volume not valid or not requested (server: , session id: 20.)
Apr 17 14:25:39 2012: Backup Server: 1.14.2.4: Unrecoverable I/O or volume error.  This DUMP or LOAD session must exit.

Ok ! Sybase configuration issues !

I had to configure some parameters that were related to load operations, such as:

number of large i/o buffers -> 32 max memory

And a Solaris issue !

I also had to adjust the Operating System Shared Memory to the sybase engine ... !

And I finally was able to load the DB (size > 2.1 GB) !

;-) Cheers !

What must be in place to validate a XMLTYPE against a schema

My test case ran fine on multiple other databases and multiple sources (including oracle support) asked if the ANONYMOUS user had been dropped. Although it had not, there were some XML DB Repository changes that were made without a full understanding. To try short cut the problem I re-installed XDB using note 1292089.1. This allowed my test case to work correctly and after dropping the created types and tables and re-registering our XML schema, everything worked correctly.

Related Question