I am converting an SQL Server system to run on Oracle (11g). The system has all been set up, data loaded, etc. The Oracle system was loaded using all standard default values.
The amount of data is quite large (160m records in tables, etc.). The server is an HP DL380 with 1tb raid 1+0 and 96gb memory.
FYI SGA is currently running at 25gb and PGA 10gb with 40gb max allocated for the Oracle system.
The ONLY changes to the standard Oracle install were to increase REDO files from 50mb to 10gb and the TEMP file to 30gb to stop switching/checkpointing.
In testing, I find the inexplicable, in that an SQL insert statement using 3-4 tables takes over 90 minutes to run, whereas the same logic using cursors takes about 3 minutes!
This takes about 8 minutes for cursors and 2-3 minutes for SQL on SQL Server (which is what I expect).
In other words Oracle SQL is 30-50 times slower than a cursor. It gives me the impression that the Oracle system is very fast at OLTP (individual record select/inserts) but really slow at manipulating larger tables in SQL.
I am certain this is not the usual case, and so am asking, are there any Oracle 'parameters' that need to be adjusted?
Below is an example of the SQL used:
INSERT INTO TMP_TXN ...... SELECT ....... FROM TXN (160m rows), CUSTOMERS (1m rows), PRODUCTS (1k rows) WHERE TXN.TXN_DATE = p_date AND TXN.TXN_AMOUNT > 0 AND TXN.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND PRODUCTS.PRODTYPE_ID IN ('1', '12', '13'); AND TXN.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID AND CUSTOMERS.CUSTOMER_STATUS LIKE 'A%'
FOR item IN (SELECT ..... FROM TXN (160m rows), CUSTOMERS (1m rows), PRODUCTS (1k rows) WHERE TXN.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID AND CUSTOMERS.CUSTOMER_STATUS LIKE 'A%' AND TXN.PRODUCT_ID = PRODUCTS.PRODUCT_ID AND PRODUCTS.PRODTYPE_ID IN ('1', '12', '13') AND TXN.TXN_DATE = p_date AND TXN.TXN_AMOUNT > 0 ORDER BY CUSTOMER_ID); LOOP IF item.customer_id <> customer_id_old THEN etc.
Please don't get hung up about this example SQL. It is one of literally hundreds and EVERY SINGLE ONE runs slow. But when using cursors, the scripts run 10-50 times faster.
I checked the SQL and cursors side by side in sql developer. They are functionally identical and produce the same results.
When running the SQL, I look at the resource monitor and see that the Data tablespace is hardly touched (<1mb/sec) whereas the UNDO / REDO tablespaces are 3-50mb/sec. The REDO log files were resized to 10gb as they were previously switching about every 1-2 seconds!
I don't think the problem is the SQL. I think the problem is the Oracle SQL subsystem / the optimiser / memory managements, or something similar. Why? Well these are the common denominators for 100 pieces of code (not specific indexes, tables, etc.).
Something is wrong. Doing an internal Oracle function (backup/restore) has data transfers of 300-500mb/sec. Doing an SQL insert has data transfers of <1mb/sec. So my guess is that something on Oracle 11g is being throttled - and my thought was that there must be some configuration that is optimal for Oracle as a DWH with large data/table handling rather than for single insert OLTP.
As this is totally wrong, my guess is that there is some Oracle setup problem that I have missed. Any ideas for this incredibly slooooow performance from Oracle SQL?