Home » RDBMS Server » Performance Tuning » While Analyzing Schema
While Analyzing Schema [message #204986] Thu, 23 November 2006 00:14 Go to next message
dattatraya
Messages: 2
Registered: November 2006
Junior Member
Hello Friends,

Can I analyze schema while some other user working on that ( insert / update )?

Thanks
Dattatrya
Re: While Analyzing Schema [message #204995 is a reply to message #204986] Thu, 23 November 2006 00:29 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi

Yes you can.



session 1 > 

SQL> conn sys as sysdba
Enter password:
Connected.


SQL> set timing on
SQL> exec dbms_stats.gather_schema_stats ( 'HR' );

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.81


session 2 >

SQL> create table big_table as select * from all_objects where 1 = 2;

Table created.


SQL> set timing on
SQL> insert into  big_table  select * from all_objects
  2  union all
  3  select * from all_objects
  4  union all
  5  select * from all_objects
  6  union all
  7  select * from all_objects
  8  union all
  9  select * from all_objects
 10  /

194940 rows created.

Elapsed: 00:00:47.61
SQL>

During analyze process "big_table" status.

SQL> select table_name,num_rows,blocks
  2  from dba_tables
  3  where owner = 'HR';

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
REGIONS                                 4          5
COUNTRIES                              25
LOCATIONS                              23          5
DEPARTMENTS                            27          5
JOBS                                   19          5
EMPLOYEES                             107          5
JOB_HISTORY                            10          5
TAB1                                    5          5
TAB2                                    4          5
BIG_TABLE                               0        558

10 rows selected.

Elapsed: 00:00:01.64


After processing and after complete insert operation.

SQL> exec dbms_stats.gather_table_stats('HR','BIG_TABLE');

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.17
SQL> select table_name,num_rows,blocks
  2  from dba_tables
  3  where owner = 'HR' and table_name = 'BIG_TABLE';

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
BIG_TABLE                               0       2764

Elapsed: 00:00:00.00



hope this helps
Taj
Re: While Analyzing Schema [message #205011 is a reply to message #204986] Thu, 23 November 2006 00:56 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
You must be aware that analyzing schema/db will cause all SQL statements referencing the analyzed objects to be invalidated, so
you have to expect re-parsing for these statements.

HTH.
Re: While Analyzing Schema [message #205013 is a reply to message #205011] Thu, 23 November 2006 01:05 Go to previous message
dattatraya
Messages: 2
Registered: November 2006
Junior Member
Thanks michael_bialik and user52

Regards
Dattatraya

Previous Topic: Partitioning Tables
Next Topic: latch waits
Goto Forum:
  


Current Time: Sat Dec 14 14:45:22 CST 2024