How to setup one database to simulate online database?-CodePudding

There is a simulated database environment that runs like production environment. but no data in this database, and the statistical information comes from the real production environment. The evironment is the latest version of oceanbase community edition.

Is there a way to achieve that the execution plan of the database depends on statistical information？ so the execution plan we get on the simulated database environment will be same as the production environment.

In this way, the execution plan of some SQL statements can be analyzed on the simulated database environment，to determine whether there is a performance problem in SQL.

Due to the particularity of our work, we cannot obtain the execution plan in the production environment.

Now, during our test, the oceanbase's execution plan depends on the estimated number of rows in the storage layer mainly,not statistical information.

By the way, this usage scenario can be run when we used the Oracle before.

Any suggestions on how to setup one database to simulate online database?

Thanks.

CodePudding user response：

You can use DBMS_STATS.EXPORT_... routines to copy all statistics for a table, schema, or even database from one database to another. (You would then import them with DBMS_STATS.IMPORT...)

The issue here is that whilst you'll probably then get identical execution plans across the two databases, I'm not sure what benefit that yields.

For example, lets say you see this on your empty database

SQL> select * from emp
  2  where ename = 'JOE';

Execution Plan
--------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |
--------------------------------------------------

(or even your production database for that matter).

Is that a good plan? A bad plan? It's really impossible to say in the majority of cases. Copying stats can be good technique when you have comparable data sizes between databases. But if one is empty, I don't see a lot of benefit.