DBMS_STATS does it create an exclusive lock 2

[Scunningham99]

We are about to implement dbms_stats.gather_schema_stats as we are moving away from analyze.

We are aware that the compute in analyze creates exclusive locks on the table, but not sure if dbms_stats will create these exclusive locks aswell.

I have read up on dbms_stats and read somewhere that it can run in paralell. does this mean it does not create any locks.

Any help would be creatly appreciated!

Thanks in advance.

Simon

Sy UK

 

[AOLEURO]

Hi Sy

Are you sure that analyze locks the table?

I know that if you are doing DDL you can't make queries or DML – but same rule for analyze. But Analyze do not set a exclusive lock on the table.

I have tried the following in 9i R2 (pacth 4).

In Schema Scott I created a BIG_TABLE with more than 1 million rows as select from dba_objects repeated times.

I then open a session and locked the table in exclusive mode with: LOCK TABLE BIG_TABLE IN EXCLUSIVE MODE;

I then looked in v$locked_object and saw the exclusive lock.

I then deleted all rows with: DELETE FROM BIG_TABLE;

In another session I then wrote:

Analyze table BIG_TABLE compute statistics for table for all indexes for all columns;

And the analyze just started – and after analyze ended I then viewed the DBA_TABLES and saw

TABLE_NAME                       NUM_ROWS     BLOCKS TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- -----------------
BIG_TABLE                         1423968      19401 28-01-04 13:26:06

So it’s still possible to make analyze even if the table is exclusive locked and all rows deleted (not committed).

Now if I am right – then the session making analyze must use RBS because ALL rows are deleted (not committed). We can see it with trace files.

analyze /* Table still deleted completly without commit */ table big_table 
  compute statistics for table for all indexes for all indexed columns

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.11       0.09          0          0          0           0
Execute      1     65.25     130.01      59624

1482175

          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     65.36     130.11      59624    1482175          2           0

And now I rollback deleted rows (in the first session) - so lock and RBS are gone.

analyze /* Table now rolled back now all rows in table */ table big_table 
  compute statistics for table for all indexes for all indexed columns

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     12.66      15.91      19366

19405

          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     12.66      15.92      19366      19405          2           0

So the conclusion is – that analyze doesn’t make an exclusive lock – and analyze doesn’t “see” any DML statements from other sessions – (like deleting all rows).

DBMS_STAT makes the same as analyze with the above test. But still I would also use DBMS_STAT because Oracle wants us to use DBMS_STAT rather than analyze, because under the cover – it make more and more than just the analyze command.

Now if you are using R2 of 9i – then you also may want to take a look on OPTIMIZER_DYNAMIC_SAMPLING and OPTIMIZER_FEATURES_ENABLED. These are new in R2 and helps if using global temporary tables because the CBO never knows the contents.


Regards
Allan
Icq: 346225948

 

[Scunningham99]

Thanks alot for your response.

But viewing the log files from analyze we have the following ora- messages.

for example

analyze table PREMIER.P_ADV_DEPOSIT_REQUEST
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
PREMIER.P_ADV_DEPOSIT_REQUEST

And if i check in dba_tables we can see it has not being analyzed.

Any ideas?



Sy UK

 

[AOLEURO]

Hi

I think it’s because something locks the table as an object in library. Analyze can’t update information on object because object is locked. It’s not the Analyze who is locking but something else. And it’s not the table data – but the table object itself in library.

DBMS_STAT don’t change the problem, because an object library blocks for updating the object.

Regards
Allan
Icq: 346225948

 

[Scunningham99]

Ahh. all is clear. i shall investigate pinned objects etc next time it is running and try and identify object.

Should i query v$lock and v$locked_object





Sy UK

 

[AOLEURO]

Hi

Yes and no……


Try look in the manual Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)

You got v$lock, v$locked_object, v$session, v$sql and so on.


Regards
Allan
Icq: 346225948

 

[Scunningham99]

great help... thank u very much!



Sy UK

 

[Scunningham99]

I am running the following, which does not work... This is really strange as i cannot see any locks in v$locked_object and my session is not waiting in dba_waiters.

Is there any way i can check to see what has got the lock when the dbms_stats is running?

SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'PREMIER', TABNAME => 'P_R
ATE_CODE', CASCADE => TRUE, ESTIMATE_PERCENT => 35);


BEGIN DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'PREMIER', TABNAME => 'P_RATE_COD
E', CASCADE => TRUE, ESTIMATE_PERCENT => 35); END;

*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object PREMIER.P_RATE_CODE
ORA-06512: at "SYS.DBMS_STATS", line 9136
ORA-06512: at "SYS.DBMS_STATS", line 9150
ORA-06512: at line 1

Sy UK

 

[AOLEURO]

Hi

Am I not correct – that you can activate the statement (DBMS_STATS and analyze) – and the statement starts – making lots of IO and after some time it returns the message – or does the message come immediately (the time out may take "some" time).


Regards
Allan
Icq: 346225948

 

[Scunningham99]

approx 5 mins b4 timout occurs.

Sy UK

 

[AOLEURO]

Hi

Because it’s not a lock on DML (othervise you would see locks in v$lock_object), you need to investigate the system.

You may try dba_locks and views for latches.

Is it the only table you can’t analyze?


Regards
Allan
Icq: 346225948

 

[Scunningham99]

hi thanks for reply!

no there are 287 in total. I have exported the tableand imported in to another db, which i can run dbms stats no problem.

I have issued delete from without commiting in one session and in another session the dbms stats works fine.

Also i have issued lock table in exclusive mode and the dbms stats still works in the other database.

I am confused as i thought exclusive lock is the most restrictive, but yet dbms stats still works.

So how come it does not work in the database i am having problems with?

Sy UK

 

[AOLEURO]

Hi

You got a hanging or spinning something – so you can’t change or lock objects in library. I think that you can’t even make DDL on these tables – because Oracle needs to lock library objects.

Is your system MTS?

Do you have lots of users?

Investigate each session and kill unknown sessions. Is Oracle making some sort of recovery?


…As a last options – if you got a service window restart server. And before users logon try analyze.


Regards
Allan
Icq: 346225948

 

[Scunningham99]

Yes there are alot of users. Currently 378, But more will be logging on shortly taking up to approx 1000+.

This is a mission critical system. All processes are dedicated.

How can I identify if session is hanging from v$session, v$process?

Thanks for ur help!

Sy UK