-1

I have a table with just 5 columns and 8500 rows. The following simple query on an Oracle database is taking around 8 seconds whereas if I import the same table into a Sql-Server database then, it takes less than 1 seconds.

SELECT CustomerList.* FROM CustomerList ORDER BY TypeID, OrderNr, Title

QUESTION: I am completely new to databases and have started acquiring knowledge about it, but 8 seconds for 8500 records is a way too long time. Is there anything that I can try to resolve the issue?

UPDATE: I exported the table from the Oracle database as a text file and then imported the test file into another fresh Oracle database to create the same table. When I executed the above query onto this newly created database, the execution time of the query is again the same as before (i.e. around 8 seconds).

user2756695
  • 676
  • 1
  • 7
  • 22
  • 1
    Database performance is a [broad subject](https://docs.oracle.com/cd/B19306_01/server.102/b14211/toc.htm) with many facets (hardware, query optimization, indexes, statistics, ...). The query plan is _usually_ a good place to start. Can you [share](https://www.brentozar.com/pastetheplan/) (sql-server) the query plans for both executions? I am not familiar with how the [Oracle query plan](https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#g42231) must be generated or interpreted. – Sander Sep 04 '20 at 09:00
  • From my point of view, whatever you do with 8500 rows should be *very fast* even in Excel on your PC, not to mention an Oracle database on a database server (even if it is your PC). 8500 rows is *close to nothing*. Does it take 8 seconds for several subsequent selects, or did it happen only once? Where is the database? What does the CPU do? Network? Which tool do you use? What happens if you remove ORDER BY clause? – Littlefoot Sep 04 '20 at 09:17
  • Can it be your HWM is set to a high value for your CustomerList table? Do a CTAS and try again (Oracle Database) – Bjarte Brandt Sep 04 '20 at 09:54
  • @Littlefoot: The Oracle database is present on a network server but the SQL-Server database is also present on the same network. Around 8 seconds is taken to read the table irrespective of the way I read it. For example, I executed the query from a C# program, I also tried to execute the query directly in Sql-developer and calculate the cummulative time taken. I also tried to remove/modify the ORDER BY clause but it always takes nearly the same time. – user2756695 Sep 04 '20 at 09:58
  • @BjarteBrandt: Sorry, as I said, I am completely new to the databases. Probably very silly question but what is HWM and CTAS? – user2756695 Sep 04 '20 at 09:59
  • I'd say that Oracle is *victim* here, not *culprit*. Though, I can't tell what is to blame. – Littlefoot Sep 04 '20 at 09:59
  • 1
    Check [here](https://stackoverflow.com/q/34975406/4808122) what you should investigate / post while having performance problems in Oracle. Check if the `CUSTOMERLIST`is not a (complex) *view** by `select OBJECT_TYPE from user_objects where object_name = 'CUSTOMERLIST'`. Learn to *google* e.g. `Oracle CTAS` – Marmite Bomber Sep 04 '20 at 10:07
  • 1
    How are querying the DB? using sqlplus? displaying the whole 8500 rows on the screen? – gsalem Sep 04 '20 at 12:59
  • @gsalem: First I was executing the query using nHibernate framework. The ececution of the line took around 8 seconds. theni executed the query directly in the SQL-Developer program and it again took the same time. – user2756695 Sep 07 '20 at 12:42
  • I think that you're probably loosing too much time over the network, but without any trace data, we can't be sure of anything. Can you try the query in sqldev, and show the execution statistics (look at it in the contextual menu). – gsalem Sep 08 '20 at 09:37

1 Answers1

1

Regarding High Water Mark (HWM). IN oracle, space for a table's rows is allocated in big chunks called an 'extent'. When an extent is filled up with rows of data a new extent is allocated. The HWM is the pointer to the highest allocated address.

If rows are deleted, the space occupied remains allocated to that table and available for new rows without have to acquire more space for them. And the HWM remains. Even if you delete ALL of the rows (simple DELETE FROM MYTABLE), all of the space remains allocated to the table and available for new rows without having to acquire more space. And the HWM remains. So say you have a table with 1 billion rows. Then you delete all but one of those rows. You still have the space for 1 billion, and the HwM set accordingly. Now, if you select from that table without a WHERE condition that would use an index (thus forcing a Full Table Scan, or FTS) oracle still has to scan that billion-row space to find all of the rows, which could be scattered across the whole space. But when you insert those rows into another database (or even another table in the same database) you only need enough space for those rows. So selecting against the new table is accordingly faster.

That is ONE possibility of your issue.

EdStevens
  • 3,708
  • 2
  • 10
  • 18