Analyze and Table locks
Mark, November 27, 2001 - 1:44 pm UTC
Could you confirm/deny the story that ANALYZE acquires table locks while running? If so, wouldn't this effect DML in an adverse way (taking into account WAIT/NOWAIT status).
Great Site Tom!
November 27, 2001 - 2:08 pm UTC
It will not affect DML, it will not block DML...
Correction
Vito, November 27, 2001 - 6:33 pm UTC
I think you are mistaken on this last one Tom.
We have 2 TB distributed database (8.1.7) and when some of our largest tables are being analyzed our application queries are not returning to the users until we kill the analyze statements. We had opened a TAR and found out that analyze places a lock on tables.
The symptom for us was that when analyze is running on a large table, the users queries are not returning.
November 27, 2001 - 8:31 pm UTC
If you run "analyze table validate structure" might lock it (in 9i, it can be done ONLINE).
A compute stats -- no, it won't.. I have a really large table for testing stuff with (lots of "big" questions recently. It has 37+ million rows in it. I kicked off:
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table bigger_table
2 compute statistics
3 for table
4 for all indexes
5 for all indexed columns;
and in another session ran:
ops$tkyte@ORA817DEV.US.ORACLE.COM> update bigger_table set deptid = 100 where empid < 1000;
999 rows updated.
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
and in yet another session ran:
ops$tkyte@ORA817DEV.US.ORACLE.COM> @count bigger_table 8
old 1: select /*+ FULL(&1) PARALLEL(&1,&2) */
new 1: select /*+ FULL(bigger_table) PARALLEL(bigger_table,8) */
old 3: from &1
new 3: from bigger_table
COUNT(*)
----------
34981888
Elapsed: 00:01:42.09
ops$tkyte@ORA817DEV.US.ORACLE.COM>
The analyze is STILL running (will be). So, lets look at a showsql to see whats going on in the database:
ops$tkyte@ORA817DEV.US.ORACLE.COM> @showsql
USERNAME SID_SERIAL STATUS MODULE ACTION CLIENT_INFO
--------------- --------------- ---------- --------------- --------------- ---------------
OPS$TKYTE '7,686' ACTIVE SQL*Plus
OPS$TKYTE '20,362' ACTIVE 01@ showsql.sql
Elapsed: 00:00:00.01
--------------------
OPS$TKYTE(20,362) ospid = 8776 command = 3 program = sqlplus@aria-dev (TNS V1-V3) dedicated server=8777
Tuesday 20:27 Tuesday 20:29 last et = 0
SELECT USERNAME || '(' || SID || ',' || SERIAL# || ') ospid
= ' || PROCESS || ' command = ' || COMMAND || ' program = '
|| PROGRAM USERNAME,TO_CHAR(LOGON_TIME,' Day HH24:MI') LOGON_T
IME,TO_CHAR(SYSDATE,' Day HH24:MI') CURRENT_TIME,SQL_ADDRESS,LAS
T_CALL_ET,SID,PADDR FROM V$SESSION WHERE STATUS = 'ACTIVE' A
ND RAWTOHEX(SQL_ADDRESS) != '00' AND USERNAME IS NOT NULL ORDE
R BY LAST_CALL_ET
--------------------
OPS$TKYTE(7,686) ospid = 8760 command = 62 program = sqlplus@aria-dev (TNS V1-V3) dedicated server=8761
Tuesday 20:26 Tuesday 20:29 last et = 156
analyze table bigger_table
compute statistics
for table
for all
indexes
for all indexed columns
Elapsed: 00:00:00.05
ops$tkyte@ORA817DEV.US.ORACLE.COM>
sure enought, sid 7 is doing the analyze -- looking at v$lock:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from v$lock where sid = 7;
Elapsed: 00:00:00.18
ops$tkyte@ORA817DEV.US.ORACLE.COM> select sid, type, lmode, request from v$lock;
SID TY LMODE REQUEST
---------- -- ---------- ----------
2 MR 4 0
2 MR 4 0
2 MR 4 0
2 MR 4 0
2 MR 4 0
2 MR 4 0
2 MR 4 0
2 MR 4 0
2 MR 4 0
2 MR 4 0
2 MR 4 0
3 RT 6 0
5 TS 3 0
Elapsed: 00:00:00.05
ops$tkyte@ORA817DEV.US.ORACLE.COM>
sid 7 has NOTHING locked....
Not sure what was going on in your case -- but an analyze won't lock the tables out.
<b>and...</b> and decided to try some updates via dblinks as well -- distributed transactions. I could NOT get them to block with an analyze to compute stats going on.
Confirmation of what is said
Dilip Dandekar, November 27, 2001 - 11:20 pm UTC
Tom you have said that analyze command is to be executed only when data is modified.
Does that mean if rows are getting added to transaction tables every day analyze table need not be performed ?
Thanks
November 28, 2001 - 7:22 am UTC
No, that is not what that means. If rows are getting added every day then the number of row and skew of the data (high/low values for columns and such) is constantly changing. When about 10% of the table is "different" then it was before, it should be analyzed again. You can automate this with MONITORING and DBMS_STATS.
search for monitoring on this site for details
Delete statistics
Michael, November 27, 2001 - 11:21 pm UTC
When the statistics collected were deleted the query ran.
SQL>analyze table table_name delete statistics;
The explanation I was given is that single tables should not be analyzed because this causes problems with the optimizer. I still find it difficult to believe. But then again I have no answer to give the DBA's since they deleted the statistics and everything was back to normal.
Could there be another problem ?? I know it is difficult to ascertain but thanks for all the information.
Michael
November 28, 2001 - 7:26 am UTC
Either analyze everything or analyze nothing.
Today -- with 8i -- you should be analyzing everything since so many features are unavailable without the CBO.
By analyzing one single object, you invoked the CBO but it had no information about any other object in the database and it "guessed". It guessed wrong. Give it proper information and it'll do its job.
Some info
Jim, November 28, 2001 - 6:07 am UTC
Michael,
Yes analyzing a few tables can cause performance
problems. If you are joining one or more tables with statistics with one or more tables that have not been
analzed and you are using the cpst base optimiser
then the optimiser makes up statistics for those tables
which have not been analysed and this of course can cause
it to use the wrong execution plan.
If you are using the the cost based optimiser
1: make sure ALL or NONE of the tables are analyzed
2: analyze tables that have volatile data regularly
If you need stats that the optimiser generates
then analyze the tables, save the results to another table
and delete the statistics
Hi Tom,
I used the following command at the SQL prompt ONLY for a few tables.
SQL>analyze table table_name estimate statistics sample 10 percent;
I was given to understand (by my DBA) that when I give this command, it keeps on
running in the background and decreases performance. He says that I must stop
the background processes. How do I do that ??
But according to what I have read this command is only used to update certain
data dictionary tables and this ends here itself. It also says that the tables
should be analyzed frequently to ensure accurate statistics.
Please elaborate and confirm my query.
Thanks in anticipation.
Michael
Locks
Andre Whittick Nasser, November 28, 2001 - 6:33 am UTC
Tom,
Yes, I myself was looking into this problem. Analyze stats won't (or shouldn't) lock anything.
I was thinking... since he is in a distributed environment -- hence, more prone to his overlooking some aspect, maybe:
a) Someone is explicitly locking the table.
b) Distributed transactions are locking resources to guarantee consistency, as in 2PC mechanism (sorry I have some blind spots here)
Would you please comment ?
More stuff:
c) I wasn't able to locate an on-line book containing the locking type each DDL command incurs. For example, an ALTER TABLE would put a share lock on the table but ANALYZE wouldn't. Any hint ?
d) Conceptually speaking, shouldn't ANALYZE have some kind of consistent view of the table so modifications wouldn't hinder its work ? Ok, it put no locks there. I imagine it might use rollback segments for that. As you said, ANALYZE is nothing more than a super select, hence the analogy. Right ?
Thanks again.
November 28, 2001 - 7:49 am UTC
b) is a likely culprit. during a brief window in the 2PC process, the modified data is not only locked from writes -- but reads. A very rare time that we do have blocking reads. Something like this could definitely jam the analyze stats process (it reads lots) but... on its own... would not be the cause of this issue. I mean -- they have this problem with or without analyze.
I hypothesize they were running a VALIDATE STRUCTURE -- which will lock stuff (until 9i)
c) I don't know of one. What I've done to analyze this is to create an "ALTER" trigger on a schema. In this alter trigger I put:
dbms_lock.sleep( 60 );
that gives me one minute to see what its doing (works in 8i and up)
it'll depend on the alter table as to what kind of lock it'll take.
d) correct. analyze is a "super select". in fact, if you use dbms_stats to analyze -- you'll many times see its just running queries
About the DELETE STATISTICS review above
Andre Whittick Nasser, November 28, 2001 - 8:12 am UTC
Tom,
The Oracle material for 8i is explicit when it says that in CHOOSE mode, it's enough that ONE table in the query be analyzed for the CBO to be used. If none is analyzed, the RBO is used.
In the first case, the tables not explicitly analyzed will be implicitly analyzed using other internal metadata, like using the ALL_ROWS and FIRST_ROWS hints do -- which don't need ANALYZE either, but force the use of the CBO.
Sorry, but do you think this would go so far as "jamming" a query, provided it hasn't a very, very inefficient plan ?
A note: You mentioned he might be using VALIDATE STRUCTURE, but he question was clear: he was using ESTIMATE STATISTICS.
November 28, 2001 - 8:52 am UTC
Yes, that could "jam" a query badly. It has very little information to go on. It could see a table with 1,000 blocks under the HWM and assume there must be 1,000,000 rows in there -- don't use an index. Upon analyzing, it could find there really are 5 rows in there (the rest were deleted) so using an index would be imperative.
As for the "note", agreed -- the original QUESTIONER was using estimate, but the followup guy who said I was wrong said:
<quote>
I think you are mistaken on this last one Tom.
We have 2 TB distributed database (8.1.7) and when some of our largest tables
are being analyzed our application queries are not returning to the users until
we kill the analyze statements. We had opened a TAR and found out that analyze
places a lock on tables.
The symptom for us was that when analyze is running on a large table, the users
queries are not returning.
</quote>
He just says "being analyzed". It is in response to HIM that i guessed he was doing a validate structure which will lock up data.
To the ORIGINAL poster - the answer is "analyzing to gather stats will not lock".
Excellent explanation!
Joel, November 28, 2001 - 9:27 am UTC
Thanks for the explanation. I also like the suggestion of using DBMS_STATS.
Mark, November 28, 2001 - 11:24 am UTC
I think I got my question answered. Thanks.
Reader
Reader, November 28, 2001 - 11:36 am UTC
Tom,
"
What I've done to analyze this is to create an "ALTER"
trigger on a schema. In this alter trigger I put:
dbms_lock.sleep( 60 );
that gives me one minute to see what its doing (works in 8i and up)
"
Could you give a simple example for doing this.
I just tested and found that
"alter table <table_name> validate structure"
gets a TM Mode 4 (share) Lock.
When I "analyze table <table_name> validate structure"
it errors with ORA-54 resource busy, if the table
holds TM Mode 3,5 and 6 (row exclusive, share row exclusive
and exclusive)
The ".... validate structure" succeeds if the table
holds TM mode 4,2 (share, row share)
Hence update and insert conflicts with "analyze .."
select for update and select, works with "analyze .."
Could you confirm this
Thanks
November 28, 2001 - 1:15 pm UTC
Here is the example of the trigger and a statement:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger alter_trigger
2 after alter on schema
3 begin
4 dbms_lock.sleep(60);
5 end;
6 /
Trigger created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> set timing on
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t add y int;
Table altered.
Elapsed: 00:01:01.57
ops$tkyte@ORA817DEV.US.ORACLE.COM>
took 1 minute to alter the table. That gave me more then enough time to get another session pokeing around to see:
1 select type, id1, lmode, request, block
2* from v$lock where sid = 20
ops$tkyte@ORA817DEV.US.ORACLE.COM> /
TY ID1 LMODE REQUEST BLOCK
-- ---------- ---------- ---------- ----------
TM 23305 6 0 0
TM 65 3 0 0
TM 66 3 0 0
TM 67 3 0 0
TM 68 3 0 0
TM 70 3 0 0
object 23305 is table T, I had a mode 6 lock on the table.
the analyze table validate structure takes a SHARE (4) table lock. It is compatible with RS (row share) and S (share) locks -- select and select for updates are OK with it.
Thanks
Michael, November 29, 2001 - 11:26 pm UTC
Thanks a lot for all the feedback !!
Michael
analyze and deadlock
A reader, January 01, 2003 - 7:05 am UTC
Hi Tom
I understand prior 9i we should not analyze SYS schema but in note.35934.1 on Metalink it suggests we could do it in 9i since RBO most probably will be desupported in 10i. The reason not to analyze SYS schema is analyze some tables such as fet$ and uet$ could cause deadlocks but since we are aware analyze dont put any type of locks on the tables so how can this cause deadlock?
January 01, 2003 - 9:22 am UTC
It goes onto say:
The dictionary can now be analyzed without
any problems. Note that whilst DBMS_UTILITY.ANALYZE_SCHEMA can be
used against the SYS schema Oracle does not perform any regression
tests with these tables analyzed and so it is possible that
deadlocks or performance problems may be encountered. Also note that a
significant number of large databases run extremely succesfully with
an analyzed dictionary.
it is a deadlock between the analyze and SMON that is an issue.
Explain plan get's locked
Christo Kutrovsky, June 20, 2003 - 4:36 pm UTC
Tom,
I noticed that when I am analyzing COMPUTE STATISTICS on a table, I cannot do explain plan on a query accessing that table.
But yet the query can run, and insert/updates are not a problem.
How would you comment this?
June 20, 2003 - 5:56 pm UTC
I cannot reproduce that bevahiour myself -- Oracle9ir2 9.2.0.3
I just kicked off a big analyze (still running) and
big_table@ORA920> explain plan for select * from big_table;
Explained.
no issues?
rule hint in 10G?
A reader, December 08, 2003 - 6:21 pm UTC
if a query has a rule hint in 10G, will CBO just
ignore it?
A reader, November 13, 2007 - 2:01 pm UTC
"make sure ALL or NONE of the tables are analyzed", is this true ?
<quote>
Michael,
Yes analyzing a few tables can cause performance
problems. If you are joining one or more tables with statistics with one or more tables that have
not been
analzed and you are using the cpst base optimiser
then the optimiser makes up statistics for those tables
which have not been analysed and this of course can cause
it to use the wrong execution plan.
If you are using the the cost based optimiser
1: make sure ALL or NONE of the tables are analyzed
2: analyze tables that have volatile data regularly
If you need stats that the optimiser generates
then analyze the tables, save the results to another table
and delete the statistics
</quote>
November 16, 2007 - 2:45 pm UTC
not so much so today in the year 2007 with 10g and above.
yes yesterday in versions like 8.1.6
Today, if some of the objects do not have indexes, we will at hard parse time DYNAMICALLY gather them using a small sample.
It would be best to have them of course, so we don't have to get them for every hard parse.
see in 10g, the dynamic sampling kicks in:
ops$tkyte%ORA10GR2> create table t1 as select * from all_users;
Table created.
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> create table t2 as select * from all_users;
Table created.
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35 | 630 | 3 (0)| 00:00:01
| 1 | TABLE ACCESS FULL| T1 | 35 | 630 | 3 (0)| 00:00:01
------------------------------------------------------------------------
ops$tkyte%ORA10GR2> select * from t2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35 | 1365 | 3 (0)| 00:00:01
| 1 | TABLE ACCESS FULL| T2 | 35 | 1365 | 3 (0)| 00:00:01
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
ops$tkyte%ORA10GR2> select * from t1, t2 where t1.username = t2.username;
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35 | 1995 | 7 (15)| 00:00:0
|* 1 | HASH JOIN | | 35 | 1995 | 7 (15)| 00:00:0
| 2 | TABLE ACCESS FULL| T1 | 35 | 630 | 3 (0)| 00:00:0
| 3 | TABLE ACCESS FULL| T2 | 35 | 1365 | 3 (0)| 00:00:0
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."USERNAME"="T2"."USERNAME")
Note
-----
- dynamic sampling used for this statement
ops$tkyte%ORA10GR2> @atoff
ops$tkyte%ORA10GR2> set autotrace off
In 9i, you would get the RBO for queries that had nothing gathered, and a CBO that was confused when you mix them up (some gathered, some not)
ops$tkyte%ORA9IR2> create table t1 as select * from all_users;
Table created.
ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
ops$tkyte%ORA9IR2> create table t2 as select * from all_users;
Table created.
ops$tkyte%ORA9IR2> @at
ops$tkyte%ORA9IR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select * from t1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=32 Bytes=544)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=32 Bytes=544)
ops$tkyte%ORA9IR2> select * from t2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T2'
ops$tkyte%ORA9IR2> select * from t1, t2 where t1.username = t2.username;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=4592)
1 0 HASH JOIN (Cost=5 Card=82 Bytes=4592)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=32 Bytes=544)
3 1 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=3198)
ops$tkyte%ORA9IR2> @atoff
ops$tkyte%ORA9IR2> set autotrace off