Yesterday I was trying to figure out if any queries on a particular production database were using subpartition statistics on a certain table. We are having trouble getting the statistics gathering job to finish gathering stats on all the subpartitions of this table in the window of time we have given the stats job. My thought was that we may not even need stats on the subpartitions, so I wanted to find queries that would prove me wrong.
My understanding of Oracle optimizer statistics is that there are three levels – table or global, partition, and subpartition. The table I am working on is partitioned by range and subpartitioned by list. So, I think that the levels are used in these conditions:
- Global or table: Range that crosses partition boundaries
- Partition: Range is within one partition but specifies more than one list value
- Subpartition: Range is within one partition and specifies one list value
In the table I was working on it was partitioned by week and subpartitioned by location so a query that specified a particular week and an individual location should use the subpartition stats.
So, I did some experimentation and came up with this query:
select
p.PLAN_HASH_VALUE,
min(p.sql_id),
count(*)
from
DBA_HIST_SQL_PLAN p
where
p.OBJECT_OWNER='MYOWNER' and
p.OBJECT_NAME='MYTABLE' and
p.partition_start=p.partition_stop and
substr(p.partition_start,1,1)
in ('0','1','2','3','4','5','6','7','8','9') and
p.sql_id in (select sql_id from DBA_HIST_SQLSTAT)
group by p.PLAN_HASH_VALUE
order by p.PLAN_HASH_VALUE;
I’ve replaced the real owner and table name with MYOWNER and MYTABLE. The point of this query is to find the distinct plans that use subpartition statistics and one sql query as an example of each plan. There were multiple queries with the same plans but slightly different constants in their where clause so I just needed one example of each.
In my experimentation I found that plans that had the same numbers for the partition stop and start were the plans that used subpartition stats. I’m not sure about the plans that don’t have numbers in their partition start and stop columns.
Here is what the output looks like:
PLAN_HASH_VALUE MIN(P.SQL_ID) COUNT(*) --------------- ------------- ---------- 151462653 fugdxj00cnwxt 1 488358452 21kr79rst8663 2 634063666 5fp4rnzgw6gvc 1 1266515004 98zbx8gw95zf8 2 1397966543 37gaxy58sr1np 2 1468891601 5fp4rnzgw6gvc 1 1681407819 001aysuwx1ba4 230 1736890182 64tmnnap05m6b 2 2242394890 2tp8jx3un534j 1 2243586448 9fcd80ms6h7j4 2 2418902214 64tmnnap05m6b 1 2464907982 5fp4rnzgw6gvc 1 3840767159 05u7fy79g0jgr 143 4097240051 5mjgz2v8a3p6h 1
This is the output on our real system. Once I got this list I built a script to dump out all of these plans and the one sql_id for each:
select * from table(DBMS_XPLAN.DISPLAY_AWR('fugdxj00cnwxt',151462653,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('21kr79rst8663',488358452,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('5fp4rnzgw6gvc',634063666,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('98zbx8gw95zf8',1266515004,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('37gaxy58sr1np',1397966543,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('5fp4rnzgw6gvc',1468891601,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('001aysuwx1ba4',1681407819,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('64tmnnap05m6b',1736890182,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('2tp8jx3un534j',2242394890,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('9fcd80ms6h7j4',2243586448,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('64tmnnap05m6b',2418902214,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('5fp4rnzgw6gvc',2464907982,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('05u7fy79g0jgr',3840767159,NULL,'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('5mjgz2v8a3p6h',4097240051,NULL,'ALL'));
Here is a edited down output of just the relevant part of the first plan:
Plan hash value: 151462653 ------------------------------------------------------------------ | Id | Operation | Name | Pstart| Pstop | ------------------------------------------------------------------ | 31 | TABLE ACCESS STORAGE FULL | MYTABLE | 41017 | 41017 | ------------------------------------------------------------------
This query had conditions in its where clause like this:
LIST_COLUMN = 1234 AND
(RANGE_COLUMN BETWEEN
TO_DATE('20130609000000','YYYYMMDDHH24MISS') AND
TO_DATE('20130615000000','YYYYMMDDHH24MISS'))
I’ve renamed the real column for the list subpartioning to LIST_COLUMN and renamed the real column for the range partitioning to RANGE_COLUMN.
One interesting thing I realized was that since we are on an Exadata system and there are no visible indexes on the subpartitioned table the subpartition stats aren’t being used to determine whether the query will use an index scan or full scan. But, they are used in these queries to determine the number of rows the full scan will return so that could impact the plan.
I’m thinking of using table preferences to just turn off the subpartition stats gathering using a call like this:
begin
DBMS_STATS.SET_TABLE_PREFS ('MYOWNER','MYTABLE',
pname=>'GRANULARITY',
pvalue=>'GLOBAL AND PARTITION');
end;
/
As it is the table has 40,000 subpartitions and the daily stats job isn’t finishing anyway so regardless of the queries that use the subpartition stats I think we should set the preference. Maybe just leave dynamic sampling to handle the queries that actually use the one subpartition’s stats or have some application job gather stats on the one subpartition when it is initially loaded. It is a work in progress, but I thought I would share what I’ve been doing.
– Bobby
