CODEWITHSUNDEEP
oracle-databaseperformance
user2676007
user2676007

Reputation: 11

How can you check query performance with small data set

All the Oracles out here, I have an Oracle PL/SQL procedure but very small data that can run on the query. I suspect that when the data gets large, the query might start performing back. Are there ways in which I can check for performance and take corrective measure even before the data build up? If I wait for the data buildup, it might get too late. Do you have any general & practical suggestions for me? Searching the internet did not get me anything convincing.

Upvotes: 0

Views: 148

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

Better to build yourself some test data to get an idea of how things will perform. Its easy to get started, eg

create table MY_TEST as select * from all_objects;

gives you approx 50,000 rows typically. You can scale that easily with

create table MY_TEST as select a.* from all_objects a ,
  ( select 1 from dual connect by level <= 10);

Now you have 500,000 rows

create table MY_TEST as select a.* from all_objects a ,
  ( select 1 from dual connect by level <= 10000);

Now you have 500,000,000 rows!

If you want unique values per row, then add rownum eg

create table MY_TEST as select rownum r, a.* from all_objects a ,
  ( select 1 from dual connect by level <= 10000);

If you want (say) 100,000 distinct values in a column then TRUNC or MOD. You can also use DBMS_RANDOM to generate random numbers, strings etc.

Also check out Morten's test data generator

https://github.com/morten-egan/testdata_ninja

for some domain specific data, and also the Oracle sample schemas on github which can also be scaled using techniques above.

https://github.com/oracle/db-sample-schemas

Upvotes: 1

Related Questions