Query slow on the sharded table

by user2244933   Last Updated August 14, 2019 13:06 PM

I created a sharding db env on oracle 12c and 19c. I found a strange problem: The select query on the sharded table is very slow(17s to 300s) even there is only 10 rows in the table, but it's only 0.052s to execute the same query on each shard db. Here are the information on the sharding env:

Name                Shard Group         Status    State       Region    Availability 
----                -----------         ------    -----       ------    ------------ 
sh1                 primary_shardgroup  Ok        Deployed    region1   ONLINE       
sh2                 primary_shardgroup  Ok        Deployed    region1   ONLINE  

DDL:

CREATE SHARDED TABLE Customers
(
CustId VARCHAR2(60) NOT NULL,
name VARCHAR2(60),
CONSTRAINT pk_customers PRIMARY KEY (CustId)
) TABLESPACE SET TSP_SET_SD 
PARTITION BY CONSISTENT HASH (CustId) PARTITIONS AUTO;

Testing rows:

insert into customers(custid,name) values(1,'u1');
insert into customers(custid,name) values(2,'u2');
insert into customers(custid,name) values(3,'u3');
insert into customers(custid,name) values(4,'u4');
insert into customers(custid,name) values(5,'u5');
insert into customers(custid,name) values(6,'u6');
insert into customers(custid,name) values(7,'u7');
insert into customers(custid,name) values(8,'u8');
insert into customers(custid,name) values(9,'u9');
insert into customers(custid,name) values(10,'u10');

Query:

select * from customers;

Any comments would be appreciated!

Tags : oracle sharding


Related Questions



Issue in Shard Distribution

Updated April 27, 2018 06:06 AM

MongoDB, sharding with multi DB instance

Updated February 21, 2019 13:06 PM


Long running query on replica worker set in mongo

Updated March 28, 2015 06:02 AM