Restoring Stats and Execution Plans
Dave, October 29, 2008 - 5:24 pm UTC
Is there a way to invalidate just that one statement from the shared pool? We absolutely want to make sure that we have the "right" bind value peeked the first time. In many of these cases, it happens very rarely that the wrong values are peeked at, but need to figure out the best approach on how to recover when this happens in a very short time-frame. Any other ideas would be greatly appreciated.
October 29, 2008 - 7:43 pm UTC
there is not - not for a single statement.
You could touch the stats for that single table and invalidate all cursors that reference it.
Sounds like you might want plan stability for this - you could "freeze" the plan.
referring tables
suhin, October 30, 2008 - 4:22 am UTC
And how to quickly identify all cursors referring a table
October 30, 2008 - 8:32 am UTC
put stats on the table and see what gets invalidated :)
sorry, as far as I know - that information is not exposed in the v$ tables really - not at that level of detail.
Is it possible in 11g?
A reader, October 30, 2008 - 9:17 am UTC
Tom,
Is it possible to invalidate a single query plan in shared pool in 11g?
Thanks...
October 30, 2008 - 9:55 am UTC
no
Is it possible to invalidate a single query plan in shared pool ?
amit poddar, October 30, 2008 - 5:06 pm UTC
Starting in 10.2.0.4 and 11g single cursor of object can be pruged
from
shard pool. using dbms_shared_pool.purge procedure. For details please read the file $ORACLE_HOME/rdbms/admin/dbmspool.sql
November 02, 2008 - 4:03 pm UTC
nice, I'll file a doc bug, no reason the code shouldn't match the actual documentation after all....
Can the peaked values be found after the problem arises?
Henrik, November 03, 2008 - 5:04 am UTC
We had a similar problem.
We "solved" it by doing a alter table estimate statistics, which caused the query plan (and probably a few others).
To learn more about this problem, we would like to know what values caused the problem.
Is it possible to find the "peaked" bind values, after the plan has been generated?
November 10, 2008 - 2:47 pm UTC
ops$tkyte%ORA10GR2> create table t
2 as
3 select 99 id, a.*
4 from stage a
5 where rownum <= 20000;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> update t set id = 1 where rownum = 1;
1 row updated.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(id);
Index created.
ops$tkyte%ORA10GR2> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T',
4 method_opt=>'for all indexed columns size 254',
5 estimate_percent => 100,
6 cascade=>TRUE );
7 end;
8 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> variable n number
ops$tkyte%ORA10GR2> exec :n := 1
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select count(object_type) from t n_is_1_first where id = :n;
COUNT(OBJECT_TYPE)
------------------
1
ops$tkyte%ORA10GR2> exec :n := 99
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select count(object_type) from t n_is_1_first where id = :n;
COUNT(OBJECT_TYPE)
------------------
19999
ops$tkyte%ORA10GR2> select * from
2 table( dbms_xplan.display_cursor(null,null,'ADVANCED'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID 2x454rqkvtpbc, child number 0
-------------------------------------
select count(object_type) from t n_is_1_first where id = :n
Plan hash value: 1789076273
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / N_IS_1_FIRST@SEL$1
3 - SEL$1 / N_IS_1_FIRST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "N_IS_1_FIRST"@"SEL$1" ("T"."ID"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
<b>
1 - :N (NUMBER): 1
</b>
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"=:N)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT("OBJECT_TYPE")[22]
2 - "OBJECT_TYPE"[VARCHAR2,19]
3 - "N_IS_1_FIRST".ROWID[ROWID,10]
52 rows selected.
so, you can see the peeked bind was 1 when we ran it with 99
Could not find PURGE
Sarayu K, November 03, 2008 - 9:24 am UTC
Hi,
I could not find PURGE in dbms_shared_pool. I am on 10.2.0.4
Am i missing something. Please advice.
Thanks,
Sarayu
08:53:55 SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
5 rows selected.
Elapsed: 00:00:00.01
08:54:03 SQL> desc dbms_shared_pool
PROCEDURE ABORTED_REQUEST_THRESHOLD
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
THRESHOLD_SIZE NUMBER IN
PROCEDURE KEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
PROCEDURE SIZES
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
MINSIZE NUMBER IN
PROCEDURE UNKEEP
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FLAG CHAR IN DEFAULT
08:54:09 SQL>
Purge Procedure
Kerry Osborne, November 05, 2008 - 11:32 am UTC
The purge procedure should be there in 10.2.0.4. You may need to re-create the dbms_shared_pool package by re-running $ORACLE_HOME/rdbms/admin/dbmspool.sql - check it first to see if the purge procedure is defined. As Tom mentioned above it is not currently documented although there are some usage notes in the package definition itself. Also be aware that in the back port to 10g there is a bug (5614566) that you have to work around - so I'd be leery of using it in production. There are a couple of posts on this - one on my blog at
http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/ and another at Fairlie Rego's blog,
http://el-caro.blogspot.com/search?q=flush+shared+pool
November 11, 2008 - 1:14 pm UTC
thanks!
Recovering from Peeked Bind Plan...
Dave Watson, December 29, 2008 - 12:35 pm UTC
Hey Tom, as a quick follow-up, we had a similiar situation occur where the peeked bind variables resulted in 'Plan B' and we wanted to use 'Plan A' for the remainder of the day's executions as it was much more efficient. In one of our testing environments, we had 'Plan A' in the SGA and decided to try to create a stored outline off of it (using DBMS_OUTLN.create_outline with the hash value/child number/catagory). We then fired off the application process/select statement and it opened up a child cursor and did use the stored outline as seen in the v$sql view. The problem is that when we checked v$sql_plan and ran the advanced "select * from table(dbms_xplan.display_cursor..." it continued to generate and use 'Plan B'.
This was my first time testing out stored outlines and the only explanation I could think of was that the outline is basically a compilation of hints and they were being ignored or that bind peeking was still occuring and overriding the outline's forced plan? Also, each time we ran this, it opened up a new child cursor (=similiar), would that possibly have something to do with the results we saw? Needless to say, we never did end up exporting the outlines from our testing region.
Thanks,
Dave
December 29, 2008 - 3:55 pm UTC
did you have the alter session to use the stored outline in the application?
Follow-Up
Dave, December 29, 2008 - 4:10 pm UTC
Yes, sorry about forgetting to mention that, we had the alter session set use_stored_outlines=(our catagory)and the query_rewrite_enabled parameter is set to TRUE. We also ran... SELECT name, category, used FROM user_outlines; before and after our tests which showed the outline going from unused before our test to used after the test.
Thanks once again.
December 29, 2008 - 4:20 pm UTC
ok, explain this bit more for me:
We then fired off the application process/select statement and it
opened up a child cursor and did use the stored outline as seen in the v$sql view. The problem is
that when we checked v$sql_plan and ran the advanced "select * from
table(dbms_xplan.display_cursor..." it continued to generate and use 'Plan B'.
the application did the right thing (used the outline) but...not sure what you mean by the rest of the stuff.
A reader, December 30, 2008 - 6:00 am UTC
Hi Tom,
you have used 'ADVANCED' as third argument (format) in dbms_xplan.display_cursor call, but this value is not mentioned neither in documentation, not in dbms_xplan source code (10.2.0.4). May you comment on it?
January 05, 2009 - 9:08 am UTC
it shows 'advanced' output (you see what it did)... It just dumps more information...
beware as it is not documented, it could change what it produces release to release, disappear, whatever...
harmless to use
Gathering stats and quering table in parallel leading to a performance issue
Pavel, January 19, 2009 - 3:26 pm UTC
Hi Tom
Our schema's tables are partitioned on reporting date and "worksapce" (concatenated together in one VARCHAR2(12) partition_key).
We run parallel calculations on different partitions.
We experience the following problem: A big cursor of calculation on partition A reads from the main result table (the queried columns are not all on one index). At the same time, calculation on partition B starts gathering statistics on this result table (on partition level). This invalidates the cursor of A. Well, so far so good. However, I cannot find an explanation for what happens next - the DB gets somehow stuck: Both calculations A and B get very, very slow. B goes on with stats gathering but performs very poor. Calculation A stays at the cursor for a minute or two, then gets invalidated again. This behaviour goes for hours and hours (without B, A would get over the cursor for a minute or so; without A, B would be ready in 5 secs with the statistics). Another interesting observation is that at this time one CPU of the machine jumps at 90% utilization and above, while the others seem to do almost nothing. This ends after B has gathered the stats (e.g. after 27 hours!). The CPU utilization gets normal then and A passes over the cursor.
Our DBA suggested to reduce SHARED_POOL_SIZE to 512M (currently 800M). He however doesn't have an explanation for the problem either.
I would be very grateful if you could help.
Many thanks in advance & cheers,
Pavel
January 19, 2009 - 9:52 pm UTC
Not following you here, a query that is being executed will never change plans - so - the fact that a query plan is invalidated would not affect it - it is a plan that is run from start to finish.
so, not sure what is really happening here - can you describe better?
Gathering stats and quering table in parallel leading to a performance issue
Pavel, January 20, 2009 - 10:46 am UTC
I have a session A reading from partition A of table T. The cursor joins to other tables too, there's no index of T covering all its columns queried in the cursor.
Now, parallel to A, another session B starts gathering statistics on partition B of table T (using dbms_stats.gather_table_stats).
Cursor of A gets invalidated - this is the expected behavior.
The unexpected one is that now both sessions get stuck. Especially I don't understand why the stats gathering session B gets stuck. It continues gathering the statistics but this takes hours and would normally take 5 secs.
Some further info:
.) This doesn't happen with each cursor - currently only with two
.) We cannot reproduce this on a test environment
.) We haven't observed this for this cursor until previous month
.) Just in case here's what "show sga" returns
Total System Global Area|3622801288|bytes
Fixed Size | 745352|bytes
Variable Size |1056964608|bytes
Database Buffers |2533359616|bytes
Redo Buffers | 31731712|bytes
January 20, 2009 - 11:18 am UTC
please utilize support if you believe there is a hang and not just "massive disk contention"
use v$session_wait/event to see what you can see - what are things waiting on.
Gathering stats and quering table in parallel leading to a performance issue
Pavel, January 20, 2009 - 5:07 pm UTC
Fine, many thanks for your attention. I hope we'll find a solution/workaround - I'll post it then.
Cheers from Vienna
Gathering stats and quering table in parallel leading to a performance issue
Pavel, January 22, 2009 - 11:18 am UTC
Hello
We took a look at v$session_wait and we got the following picture during the problematic phase. Using A and B as described in the previous postings, A = SID 243, B = SID 241.
The query we used is:
select sid, seq#, state, seconds_in_wait, event, (p1text||chr(10)||p1) p1_info, (p2text||chr(10)||p2) p2_info, (p3text||chr(10)||p3) p3_info
from v$session_wait where sid in (&&MYSID);
SID| SEQ#|STATE | s|EVENT |P1_INFO |P2_INFO|P3_INFO
-----|------|---------|-----|-----------------------|----------|-------|-------
241| 50471|WAITING | 158|library cache lock |handle add|lock ad|100*mod
| | | | |ress |dress |e+names
| | | | |5044031615|5044031|pace
| | | | |15329896 |6162103|301
| | | | | |4184 |
243| 40432|WAITED KN| 158|latch free |address |number |tries
| |OWN TIME | | |5044031610|157 |1
| | | | |21514712 | |
It seems that session B (SID 241), which gathers statistics of T, waits very long to access the structure, then after it can access it, it gathers the stats for one column, and as it tries to start with the next column, the problem occurs again and it has to wait again a long time.
We plan to make a copy of the instance to be able to analyze it deeper. The DBA's suggestion is that either shared_pool is set too big or the memory gets too fragmented with time, or both...
Gathering stats and quering table in parallel leading to a performance issue
A reader, February 26, 2009 - 7:38 am UTC
Hi Pavel,
Since you are gathering the stats on part. B of table T. Do you use dbms_stats.gather_table_stats('OWNER','TABLE','B',cascade=>...,granularity=>'PARTITION')
if not, then it goes for the entire table along with the global stats. In that case the behavior is expected.
Hope it helps.
-- A fellow DBA --
Outlines for RULE hint
A, March 30, 2009 - 10:07 am UTC
Hello Tom,
On a acedamic interest, I was trying to pass the RULE hint to an query using stored outlines in Oracle 9i.
Since this query is generated from front-end application, I can't pass the RULE hint.
How do I achieve using the stored outlines? Can you give me an example.
March 30, 2009 - 5:13 pm UTC
you wouldn't 'pass a hint', you would get into an environment where the RBO was being used and execute the query while generating stored outlines. That would capture that generated plan.
for example:
ops$tkyte%ORA10GR2> create table t
2 as
3 select *
4 from all_objects
5 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(object_id);
Index created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> alter session set optimizer_mode = all_rows;
Session altered.
ops$tkyte%ORA10GR2> select count(subobject_name) from t where object_id > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 225 (3)| 00:00:0
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | TABLE ACCESS FULL| T | 50181 | 343K| 225 (3)| 00:00:0
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID">0)
ops$tkyte%ORA10GR2> alter session set optimizer_mode = rule;
Session altered.
ops$tkyte%ORA10GR2> select count(subobject_name) from t where object_id > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 1789076273
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | TABLE ACCESS BY INDEX ROWID| T |
|* 3 | INDEX RANGE SCAN | T_IDX |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">0)
Note
-----
- rule based optimizer used (consider using cbo)
ops$tkyte%ORA10GR2> alter session set optimizer_mode = all_rows;
Session altered.
<b>
so, we have different plans under RBO than CBO...</b>
ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set optimizer_mode = rule;
Session altered.
<b>we get into RBO environment....</b>
ops$tkyte%ORA10GR2> alter session set create_stored_outlines = my_stored_outlines;
Session altered.
<b>we use the alter session (as opposed to create outline command - you can use either) to generate a category of plans...</b>
ops$tkyte%ORA10GR2> select count(subobject_name) from t where object_id > 0;
COUNT(SUBOBJECT_NAME)
---------------------
509
ops$tkyte%ORA10GR2> alter session set create_stored_outlines = FALSE;
Session altered.
<b>we ran query and generated outline using the RBO plan</b>
ops$tkyte%ORA10GR2> alter session set optimizer_mode = all_rows;
Session altered.
<b>we want the CBO to be the default again...</b>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set use_stored_outlines = my_stored_outlines;
Session altered.
<b>tell the database to use our generated outline</b>
ops$tkyte%ORA10GR2> select count(subobject_name) from t where object_id > 0;
COUNT(SUBOBJECT_NAME)
---------------------
509
ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID 5gyk85q0279qx, child number 3
-------------------------------------
select count(subobject_name) from t where object_id > 0
Plan hash value: 1789076273
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1290 (10
| 1 | SORT AGGREGATE | | 1 | 7 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 50181 | 343K| 1290 (
|* 3 | INDEX RANGE SCAN | T_IDX | 50181 | | 116 (
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_ID">0)
Note
-----
- outline "SYS_OUTLINE_09033016005755501" used for this statement
24 rows selected.
<b>and that verifies that it in fact did...</b>
Thanks
A, March 31, 2009 - 12:18 pm UTC
Hello Tom,
It's working for me.
SQL>
SQL> exec dbms_stats.gather_table_stats( user, 'T1451' );
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.08
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> alter session set optimizer_mode = all_rows;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> SELECT count(1)
2 FROM T4908
3 WHERE (T4908.C5 != '.')
4 AND (T4908.C7 < 4)
5 AND (T4908.C2 = 'GSC')
6 AND (T4908.C4 IS NOT NULL) ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=247 Card=1 Bytes=62)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=247 Card=714 Bytes=44268)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T102' (Cost=178 Card=713 Bytes=38502)
4 3 INDEX (RANGE SCAN) OF 'I102_C2_C7' (NON-UNIQUE) (Cost=6 Card=713)
5 2 TABLE ACCESS (FULL) OF 'T1451' (Cost=68 Card=3780 Bytes=30240)
SQL>
SQL> alter session set optimizer_mode = rule;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL>
SQL> SELECT count(1)
2 FROM T4908
3 WHERE (T4908.C5 != '.')
4 AND (T4908.C7 < 4)
5 AND (T4908.C2 = 'GSC')
6 AND (T4908.C4 IS NOT NULL) ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1451'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T102'
5 4 INDEX (RANGE SCAN) OF 'I102_C2_C7' (NON-UNIQUE)
6 3 INDEX (RANGE SCAN) OF 'I1451_C3_1' (NON-UNIQUE)
SQL> set autotrace off
SQL> alter session set optimizer_mode = rule;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> alter session set create_stored_outlines = rule_stored_outlines;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> SELECT count(1)
2 FROM T4908
3 WHERE (T4908.C5 != '.')
4 AND (T4908.C7 < 4)
5 AND (T4908.C2 = 'GSC')
6 AND (T4908.C4 IS NOT NULL) ;
COUNT(1)
----------
226
Elapsed: 00:00:00.00
SQL>
SQL> alter session set create_stored_outlines = FALSE;
Session altered.
Elapsed: 00:00:00.00
SQL> alter session set optimizer_mode = all_rows;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> alter session set create_stored_outlines = rule_stored_outlines;
Session altered.
Elapsed: 00:00:00.00
SQL>
SQL> set autotrace traceonly explain
SQL>
SQL>
SQL> SELECT count(1)
2 FROM T4908
3 WHERE (T4908.C5 != '.')
4 AND (T4908.C7 < 4)
5 AND (T4908.C2 = 'GSC')
6 AND (T4908.C4 IS NOT NULL) ;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=247 Card=1 Bytes=62)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=247 Card=714 Bytes=44268)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T102' (Cost=178 Card=713 Bytes=38502)
4 3 INDEX (RANGE SCAN) OF 'I102_C2_C7' (NON-UNIQUE) (Cost=6 Card=713)
5 2 TABLE ACCESS (FULL) OF 'T1451' (Cost=68 Card=3780 Bytes=30240)
April 01, 2009 - 7:25 am UTC
I didn't use autotrace, which uses explain plan. I printed out the ACTUAL plan used using dbms_xplan.
use tkprof and review the ROW SOURCE OPERATION
or do it the way I did.