1

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%' 

and

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?

  • Are you using the bulk insert in PLSQL to insert the records? – JSapkota Nov 5 '17 at 9:20
  • Hi. I have tried table nologging and /*+ append */. This did make things faster - but not by much. – Kingbeard Nov 5 '17 at 9:24
  • Did you check the runtime of the select part alone? Do you use real temp tables in sql server? Does the cursor solution use single inserts, your sample code does not show this part and the transaction handling. Are you inserting duplicates which are avoided in the cursor version? – eckes Nov 5 '17 at 9:45
  • 2
    I'd take a 10046 trace and see where the time is going. Also, take an EXPLAIN PLAN for both, post the results of that. – EdStevens Nov 5 '17 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.