Quiz

Because I’m off now for the next 4 days (I’m back on Monday) I decided to leave you a little Oracle quiz. You’ll get the answer then next week. So here we go:

You’ve two sessions: SessionA and SessionB. Both of them perform a insert on the same table (insert into data…) without a commit.
The question now is: Can one insert block the other one?

Just post your thoughts as comment!

Cheers!

Imported statistics doesn’t get populated to table

Oracle provides a PL/SQL package for handling statistics called DBMS_STATS. With this package you’re able to gather, delete, export and import statistics and some more stuff. So today a colleague came to me and told me that he just wanted to import the stats of a table from one schema to another one on a different database but the stats doesn’t get populated. I said him that he should provide the commands he executed and checked them – they were ok. The import routine of the DBMS_STATS package always gets successfully executed. After a short look into the stats table and on Metalink I was surprised that the package seems to be designed for saving and restoring statistics of a schema in case of crash or upgrades but not for importing statistics into another schema. And Metalinks only workaround (look at section “Different schema” in Doc 117203.1) is to modify the stats table before re-importing although the documentation explicit says not to modify anything in the stats table: (:D)
CREATE_STAT_TABLE Procedure
This procedure creates a table with name stattab in ownname‘s schema which is capable of holding statistics. The columns and types that compose this table are not relevant as it should be accessed solely through the procedures in this package.)

So let’s see what the problem actually is:

First of all we need two users where we want to transfer the statistics, so we create user test and test1:

SQL> create user test identified by test;
User created.

SQL> grant connect, resource to test;
Grant succeeded.

SQL> create user test1 identified by test1;
User created.

SQL> grant connect, resource to test1;
Grant succeeded.

Next thing is to create a table, populate it with data and gather table stats:

SQL> conn test/test@TEST1
Connected.

SQL> create table datatable (line number);
Table created.

SQL> insert into datatable select rownum from all_objects;
40779 rows created.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats (user, ‘DATATABLE’);
PL/SQL procedure successfully completed.

SQL> select num_rows from user_tables;
NUM_ROWS
———-
40779

As you can see stats showing 40,779 rows in the table.

Next thing now is to export the statistics into a stats table. Therefore we need to create the stats table and export the stats into it:

SQL> exec dbms_stats.create_stat_table(user, ‘STATS’);
PL/SQL procedure successfully completed.

SQL> select * from stats;
no rows selected

SQL> exec dbms_stats.export_table_stats(user, ‘DATATABLE’,null,’STATS’);
PL/SQL procedure successfully completed.

As you can see the table was empty before the export, now it’s already populated:

SQL> set heading off;
SQL> select * from stats;

T          4          2
DATATABLE

TEST                                40779         65          4      40779

16-JUL-08

C          4          2
DATATABLE
LINE

TEST                                40779 .000024522      40779      40779
0          4      40774          5
16-JUL-08
C105
C305084B

SQL> set heading on;

Looking at the C5 column which contains the owner of the object we can see that these stats belong to owner TEST:

SQL> select c5 from stats;

C5
——————————
TEST
TEST

Now let’s export the stats table and import it in the other schema (using create table as select statement for this, but this would be also the same with exp/imp and datapump:

SQL> conn sys@TEST1 as sysdba
Connected.
SQL> create table test1.stats as select * from test.stats;
Table created.

So now let’s create the same data table and import the statistics:

SQL> conn test1/test1@TEST1
Connected.
SQL> create table datatable (line number);
Table created.

SQL> exec dbms_stats.import_table_stats(user, ‘DATATABLE’, null, ‘STATS’);
PL/SQL procedure successfully completed.

Stats are successfully imported the procedure completed successfully. Looking at the statistics we should see now stat but instead of that we see following:

SQL> select num_rows from user_tables;

NUM_ROWS
———-

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
—————————— ———-
STATS
DATATABLE

No stats there, looking at the column C5 we still see the old user of course:

SQL> select c5 from stats;

C5
——————————
TEST
TEST

So the workaround is: Update the C5 column with the correct owner (the new schema) and import it:

SQL> update stats set c5 = user;
2 rows updated.

SQL> commit;
Commit complete.

SQL> exec dbms_stats.import_table_stats(user, ‘DATATABLE’, null, ‘STATS’);
PL/SQL procedure successfully completed.
And there you go:

SQL> select table_name, num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
—————————— ———-
STATS
DATATABLE                           40779

I’m a little bit confused that the package doesn’t support export and re-import into another schema. For me that’s a design issue. But I read on Metalink that an enhancement request has been opened by the Oracle support guy. Let’s see if it will get implemented.

Feeling like the exterminator

Sometimes I’m thinking I’m the exterminator in the Company. I always find the trickiest bugs in some components of the software and I’m afraid of that because I’m the end user in some cases. QA should have found them already all – ok nearly all. Anyway, it’s just annoying when you’re trying to do your work but you always fall from one problem into the next one….

Going to Paris

My boss just called me. We’ve possibly a new client who is currently doing a functional POC (proof of concept) and wants to do a performance POC afterwards in the second week of August. So someone of my group has to go there and my boss decided to send me to Paris and get that thing running if the POC is successful. (Un) fortunately I’m on vacation the first week but he just said that either we reschedule the performance POC to start in the second week of August or someone from New York is going there the first week and I go there in the second week. But he definitely wants me there (I’ve two other team mates here in Austria) so I’m very honored at the moment. And of course my motivation is also back to the top!

Protected: Isn’t it cool…..

This post is password protected. To view it please enter your password below:

Password: