Most of the databases, foreign Keys needs to be indexed otherwise deleting records from parent table will lock down child table(s). Sometimes this may also lead to a extensive locking and even deadlock situation!
Here is a convenient script I needed once to index all referenced columns of a table - say MEDICAL_CONTACT.
set pagesize 0
set linesize 200
SPOOL medical-contact-fk-index.sql
SELECT 'CREATE INDEX ' || a.table_name || '_' || c.column_name || '_I ON ' || a.table_name || '(' || c.column_name
|| ') ONLINE TABLESPACE INDX01_16K COMPUTE STATISTICS;'
FROM user_constraints a, user_constraints b, user_cons_columns c
WHERE a.r_constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND a.constraint_type='R'
AND b.table_name='MEDICAL_CONTACT';
SPOOL OFF;
Might needs to modify medical-contact-fk-index.sql to change index names if cross max length for names.
--
Dec 8, 2009
Dec 7, 2009
Linking Oracle features
To turn Real Application Testing (RAT) ON, I need to re-link oracle executables with "rat_on" option/flag.
Here are the quick steps -
1. Shutdown Instance
2. Shutdown Listener and EM
3. Run the make file in $ORACLE_HOME/rdbms/lib/
# ls -l *.mk
-rw-r--r-- 1 oracle oinstall 107073 Nov 7 2008 env_rdbms.mk
-rw-r--r-- 1 oracle oinstall 27158 Nov 7 2008 ins_rdbms.mk
# /usr/ccs/bin/make -f ins_rdbms.mk rat_on ioracle
The make file will save the existing version in $ORACLE_HOME/bin appending a 'O' at the end (could be deleted later if there is a disk space pressure) -
# ls -l oracle*
-rwsr-s--x 1 oracle oinstall 129141416 Dec 1 06:05 oracle
-rwsr-s--x 1 oracle oinstall 129141416 Nov 17 04:05 oracleO
4. Start Instance and other services.
That's it!
There is another thing with 'relink' that might be needed in the following circumstances -
* New installation failed in relinking phase
* After OS upgrade
* Changes made to the OS system libraries
* Oracle patch applied with explicit relink instruction
"relink" command is available from $ORACLE_HOME/bin and "relink" takes the following parameters values -
all, oracle, network, client, client_sharedlib, interMedia, precomp, utilities, oemagent
From Oracle -
http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/cnfg_prd.htm#CHDECBHC
This is a sophisticated one, it could damage Oracle installations if not done properly!
***
Here are the quick steps -
1. Shutdown Instance
2. Shutdown Listener and EM
3. Run the make file in $ORACLE_HOME/rdbms/lib/
# ls -l *.mk
-rw-r--r-- 1 oracle oinstall 107073 Nov 7 2008 env_rdbms.mk
-rw-r--r-- 1 oracle oinstall 27158 Nov 7 2008 ins_rdbms.mk
# /usr/ccs/bin/make -f ins_rdbms.mk rat_on ioracle
The make file will save the existing version in $ORACLE_HOME/bin appending a 'O' at the end (could be deleted later if there is a disk space pressure) -
# ls -l oracle*
-rwsr-s--x 1 oracle oinstall 129141416 Dec 1 06:05 oracle
-rwsr-s--x 1 oracle oinstall 129141416 Nov 17 04:05 oracleO
4. Start Instance and other services.
That's it!
There is another thing with 'relink' that might be needed in the following circumstances -
* New installation failed in relinking phase
* After OS upgrade
* Changes made to the OS system libraries
* Oracle patch applied with explicit relink instruction
"relink" command is available from $ORACLE_HOME/bin and "relink" takes the following parameters values -
all, oracle, network, client, client_sharedlib, interMedia, precomp, utilities, oemagent
From Oracle -
http://download.oracle.com/docs/cd/B19306_01/server.102/b15658/cnfg_prd.htm#CHDECBHC
This is a sophisticated one, it could damage Oracle installations if not done properly!
***
Dec 4, 2009
Software Design Principles
These are some of my all time favorite design principles I learned my earlier days as a developer. Still, whenever it comes in front of my eyes, I don't miss reading!
Don't Let Architecture Astronauts Scare You
http://www.joelonsoftware.com/articles/fog0000000018.html
KISS principle
http://en.wikipedia.org/wiki/K.I.S.S.
You ain't gonna need it
http://en.wikipedia.org/wiki/YAGNI
***
Don't Let Architecture Astronauts Scare You
http://www.joelonsoftware.com/articles/fog0000000018.html
KISS principle
http://en.wikipedia.org/wiki/K.I.S.S.
You ain't gonna need it
http://en.wikipedia.org/wiki/YAGNI
***
Dec 3, 2009
Evaluating Jailer - database subsetting tool
We need a small subset of production data out of hundreds of gigabytes - i,e. the records for a test user across all the tables where there is a FK reference. Having the small set of data for all tables, we could build a database instance on developers' machine quickly. The referential integrity was the main concern. It seemed that Jailer handles things well. It's an open source platform independent tool.
What is Jailer?
Jailer is a tool for database subsetting and sampling, schema browsing, and rendering. It exports consistent, referentially intact row-sets from relational databases. It removes obsolete data without violating integrity.
But the problem I faced - we don't have a common column (for example, user_id or agency_id for which I wanted to pull records from all tables) directly referenced. Even, it's slow with some 100 tables where we have huge amount of data.
However, I love the tool. It's a good one for database analysis and data sampling for small schema!
For details -
http://jailer.sourceforge.net/
***
What is Jailer?
Jailer is a tool for database subsetting and sampling, schema browsing, and rendering. It exports consistent, referentially intact row-sets from relational databases. It removes obsolete data without violating integrity.
But the problem I faced - we don't have a common column (for example, user_id or agency_id for which I wanted to pull records from all tables) directly referenced. Even, it's slow with some 100 tables where we have huge amount of data.
However, I love the tool. It's a good one for database analysis and data sampling for small schema!
For details -
http://jailer.sourceforge.net/
***
Nov 10, 2009
Proactive Tuning - finding out the full table scans
Let's do some query/index tuning where we could have some problems due to data growth over time. I am looking for those big tables for which queries are doing full scans. I love the following small but useful sqls to find out those areas of tuning -
SET LONG 1000000
COL object_name FORMAT A25
COL object_name FORMAT A25
COL sql_fulltext FORMAT A50
SELECT sp.object_name, dtab.num_rows, sa.sql_fulltext, sa.executions
FROM v$sql_plan sp
JOIN dba_tables dtab ON (dtab.table_name = sp.object_name)
JOIN v$sqlarea sa ON (sa.address = sp.address AND sa.hash_value =sp.hash_value)
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY sa.executions DESC;
Another faster sql version could be -
SELECT sp.object_name
,(SELECT num_rows FROM dba_tables WHERE table_name = sp.object_name AND owner = sp.object_owner) num_rows
,(SELECT sql_fulltext FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) sql_fulltext
,(SELECT executions FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) full_scans
FROM v$sql_plan sp
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY full_scans DESC;
From the results above, we could easily avoid those small tables where we have less number of records (say, < 30,000).
Now we need to apply both technical and business knowledge to find out why those sqls are doing full scans and if re-indexing would be helpful or something out of the box!
***
SET LONG 1000000
COL object_name FORMAT A25
COL object_name FORMAT A25
COL sql_fulltext FORMAT A50
SELECT sp.object_name, dtab.num_rows, sa.sql_fulltext, sa.executions
FROM v$sql_plan sp
JOIN dba_tables dtab ON (dtab.table_name = sp.object_name)
JOIN v$sqlarea sa ON (sa.address = sp.address AND sa.hash_value =sp.hash_value)
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY sa.executions DESC;
Another faster sql version could be -
SELECT sp.object_name
,(SELECT num_rows FROM dba_tables WHERE table_name = sp.object_name AND owner = sp.object_owner) num_rows
,(SELECT sql_fulltext FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) sql_fulltext
,(SELECT executions FROM v$sqlarea sa WHERE sa.address = sp.address AND sa.hash_value =sp.hash_value) full_scans
FROM v$sql_plan sp
WHERE sp.operation = 'TABLE ACCESS'
AND sp.options = 'FULL'
AND sp.object_owner NOT IN ('SYS','SYSTEM', 'SYSMAN')
AND sp.object_owner = 'PROD'
ORDER BY full_scans DESC;
From the results above, we could easily avoid those small tables where we have less number of records (say, < 30,000).
Now we need to apply both technical and business knowledge to find out why those sqls are doing full scans and if re-indexing would be helpful or something out of the box!
***
Nov 6, 2009
Quick Steps for upgrading/patching database
These are few quick steps I performed to patch the database. Keeping here for future references -
1. Get the patch downloaded from metalink
2. Run the installation process with interactive mode - follow instruction in
the downloaded document
3. When above steps done, start database in upgrade mode
SQL> STARTUP UPGRADE
4. Run catalog upgrade script and monitor errors (if any)
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
5. SQL> SHUTDOWN IMMEDIATE
6. SQL> STARTUP
7. Recompile invalid PL/SQL packages -
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
We are done!
***
1. Get the patch downloaded from metalink
2. Run the installation process with interactive mode - follow instruction in
the downloaded document
3. When above steps done, start database in upgrade mode
SQL> STARTUP UPGRADE
4. Run catalog upgrade script and monitor errors (if any)
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
5. SQL> SHUTDOWN IMMEDIATE
6. SQL> STARTUP
7. Recompile invalid PL/SQL packages -
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
We are done!
***
Oct 8, 2009
Sample size (estimate_percent) affects correctness of database statistics
There are many arguments we can pass in dbms_stats.gather_table_stats procedure - "method_opt" and "estimate_percent" are two very important ones.
The default values (in 10g) for "method_opt" and "estimate_percent" are AUTO - meaning that Oracle will decide if Histogram Stats or No Histogram is appropriate for columns.
Sometimes, we customizes sample size to make stats collection job faster. This customization should be perfect otherwise a low sample size could result in non-representative statistics.
For example, when I collect statistics with 5% sample size, it does not create histograms for the two following columns.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TAB1', degree=>2, estimate_percent=>5);
SQL> SELECT column_name, num_buckets, num_distinct, sample_size, histogram
FROM user_tab_col_statistics
WHERE table_name='TAB1';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
------------------------------ ----------- ------------ ----------- ---------------
UPDATED 1 6382137 633875 NONE
CREATED 1 7858623 633875 NONE
VERSION 15 15 633875 FREQUENCY
ID 1 12677500 633875 NONE
TRASH_DATE 1 294401 221149 NONE
Now, when I increased the sample size to 50%, the procedure collected Height Balanced Histograms for the two columns. See below -
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TAB1', degree=>2, estimate_percent=>50);
SQL> SELECT column_name, num_buckets, num_distinct, sample_size, histogram
FROM user_tab_col_statistics
WHERE table_name='TAB1';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
------------------------------ ----------- ------------ ----------- ---------------
UPDATED 254 8929376 6347812 HEIGHT BALANCED
CREATED 254 9793629 6345628 HEIGHT BALANCED
VERSION 21 21 6341908 FREQUENCY
ID 1 12692852 6346426 NONE
TRASH_DATE 1 1217543 2208858 NONE
The different statistics could affect the execution plan for the queries where CREATED/UPDATED columns are used. So, these things should be carefully tested and sample size should not be underestimated. In fact, in my experience I have seen, larger sample size works better for big tables.
P.S. I planned to write a post on this probably in January this year, when I did the experiments on database statistics with histogram and no histogram options. Later became busy with other stuffs and forgot. Few days ago, I worked again to write custom scripts for collecting database stats. The sample size problem stroked - I set the sample size 50% in my scripts - working perfectly!
***
The default values (in 10g) for "method_opt" and "estimate_percent" are AUTO - meaning that Oracle will decide if Histogram Stats or No Histogram is appropriate for columns.
Sometimes, we customizes sample size to make stats collection job faster. This customization should be perfect otherwise a low sample size could result in non-representative statistics.
For example, when I collect statistics with 5% sample size, it does not create histograms for the two following columns.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TAB1', degree=>2, estimate_percent=>5);
SQL> SELECT column_name, num_buckets, num_distinct, sample_size, histogram
FROM user_tab_col_statistics
WHERE table_name='TAB1';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
------------------------------ ----------- ------------ ----------- ---------------
UPDATED 1 6382137 633875 NONE
CREATED 1 7858623 633875 NONE
VERSION 15 15 633875 FREQUENCY
ID 1 12677500 633875 NONE
TRASH_DATE 1 294401 221149 NONE
Now, when I increased the sample size to 50%, the procedure collected Height Balanced Histograms for the two columns. See below -
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST', tabname=>'TAB1', degree=>2, estimate_percent=>50);
SQL> SELECT column_name, num_buckets, num_distinct, sample_size, histogram
FROM user_tab_col_statistics
WHERE table_name='TAB1';
COLUMN_NAME NUM_BUCKETS NUM_DISTINCT SAMPLE_SIZE HISTOGRAM
------------------------------ ----------- ------------ ----------- ---------------
UPDATED 254 8929376 6347812 HEIGHT BALANCED
CREATED 254 9793629 6345628 HEIGHT BALANCED
VERSION 21 21 6341908 FREQUENCY
ID 1 12692852 6346426 NONE
TRASH_DATE 1 1217543 2208858 NONE
The different statistics could affect the execution plan for the queries where CREATED/UPDATED columns are used. So, these things should be carefully tested and sample size should not be underestimated. In fact, in my experience I have seen, larger sample size works better for big tables.
P.S. I planned to write a post on this probably in January this year, when I did the experiments on database statistics with histogram and no histogram options. Later became busy with other stuffs and forgot. Few days ago, I worked again to write custom scripts for collecting database stats. The sample size problem stroked - I set the sample size 50% in my scripts - working perfectly!
***
Oct 5, 2009
Optimizer glitch for partitioned table in version 10.2.0.4.0
Recently discovered that, partitioned tables are being accessed through global index rather than partitioned index, even though there are enough reasons to access via local/partitioned index.
Initially thought, as we turned off bind variable peek for execution plan stability (hidden system parameter _optim_peek_user_binds to FALSE), optimizer is not looking at the bind values and doesn't know which partition to access. So, accessing through a global index seemed logical. But there were other reasons!
At some point, we will need to access partitions through local index which is more efficient than accessing through global index. So I did more experiments on these. In my testing, I used literals instead of bind variables - but strangely, the execution plan did not change according to my expectation and was showing old plan with global index access!
The plan looked like -
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 750 | 1748 (2)| 00:00:02 | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT ORDER BY | | 5 | 750 | 1748 (2)| 00:00:02 | | |
| 3 | HASH UNIQUE | | 5 | 750 | 1747 (1)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 5 | 750 | 1746 (1)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 204 | 11424 | 1333 (1)| 00:00:02 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | TAC_LOGIN_ATOK | 11 | 275 | 23 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TAC_LA_LOGIN_ID | 74 | | 3 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| TAC_FORM_ATOK | 19 | 589 | 353 (1)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX RANGE SCAN | TAC_FA_ATOK_I | 38 | | 2 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | TAC_FORM_SUMMARY | 1 | 94 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 11 | INDEX UNIQUE SCAN | TAC_FS_UFID_PK | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Something must be wrong! Thought, it could be statistics or could be some other parameters.Rebuilt local index and recollected statistics (histogram/no histogram both), but still the same execution plan.
Then looked at metalink for any possible bug (this good practice I learned from my senior). Bingo! found a related issue reported but not the same thing. Let me share with you -
Bug No. 7210921 - STATISTICS WITH SUBPARTITIONS ARE NOT CORRECTLY INTERPRETED IN EXPLAIN PLANS
It explained that there are optimizer problems for partitioned tables - after generating execution plan by the optimizer, the execution plan shows wrong stats for partitions. Deleting table statistics was the workaround mentioned in metalink for the problem!
I thought, as the problem seems similar, what if I delete the partition table statistics?
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname =>'PROD', tabname =>'TAB1');
It helped!! The execution plan got changed and showed partitioned index access. I was not convinced to this solution of deleting table stats - specially for such a big table. There are other queries which would access the table and dynamic sampling (absence of statistics) could be another nightmare.
Then thought, what if I use earlier version of the optimizer?
SQL> alter session set optimizer_features_enable='10.2.0.3';
It worked - execution plan changed! The problem is with the 10.2.0.4.0 version.
If we don't want to change system level or session level settings for optimizer version (which may not be good for a large application), there are ways using hints to force particular optimizer version like this -
SELECT /*+ optimizer_features_enable('10.2.0.3') */ fs.form_type, fs.prov_id, fs.pgm_id, fs.client_id,
fs.created_by_id, fs.created_date, fs.notif_level, fs.state, fs.form_id, fs.title, fs.summary
FROM .......
Then the execution plan looked like follows, this was what I looked for -
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 8550 | 6160 (43)| 00:00:07 | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT ORDER BY | | 57 | 8550 | 6160 (43)| 00:00:07 | | |
| 3 | HASH UNIQUE | | 57 | 8550 | 6159 (43)| 00:00:07 | | |
|* 4 | HASH JOIN | | 57 | 8550 | 6158 (43)| 00:00:07 | | |
|* 5 | HASH JOIN | | 2376 | 129K| 4267 (51)| 00:00:05 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| TAC_LOGIN_ATOK | 126 | 3150 | 238 (2)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TAC_LA_LOGIN_ID | 859 | | 4 (0)| 00:00:01 | |
| 8 | PARTITION RANGE ITERATOR | | 1090K| 32M| 3882 (52)| 00:00:04 | KEY | KEY |
|* 9 | TABLE ACCESS FULL | TAC_FORM_ATOK | 1090K| 32M| 3882 (52)| 00:00:04 | KEY | KEY |
| 10 | PARTITION RANGE ITERATOR | | 13852 | 1271K| 1889 (23)| 00:00:02 | KEY | KEY |
|* 11 | TABLE ACCESS FULL | TAC_FORM_SUMMARY | 13852 | 1271K| 1889 (23)| 00:00:02 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------
Perfect! This solves our issue. Probably, I will use the hint solution in production.
Summary of solutions
1. Deleting stats for the partitioned table - not be a good approach, don't like
2. Using earlier optimizer version (10.2.0.3.0) for the database - not very recommended, their might be other fixes in the new version (10.2.0.4.0) even though this particular problem exists.
3. Using query hints to use earlier optimizer (10.2.0.3.0) - seems sensible to me and I planned to fix case by case.
***
Initially thought, as we turned off bind variable peek for execution plan stability (hidden system parameter _optim_peek_user_binds to FALSE), optimizer is not looking at the bind values and doesn't know which partition to access. So, accessing through a global index seemed logical. But there were other reasons!
At some point, we will need to access partitions through local index which is more efficient than accessing through global index. So I did more experiments on these. In my testing, I used literals instead of bind variables - but strangely, the execution plan did not change according to my expectation and was showing old plan with global index access!
The plan looked like -
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 750 | 1748 (2)| 00:00:02 | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT ORDER BY | | 5 | 750 | 1748 (2)| 00:00:02 | | |
| 3 | HASH UNIQUE | | 5 | 750 | 1747 (1)| 00:00:02 | | |
| 4 | NESTED LOOPS | | 5 | 750 | 1746 (1)| 00:00:02 | | |
| 5 | NESTED LOOPS | | 204 | 11424 | 1333 (1)| 00:00:02 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | TAC_LOGIN_ATOK | 11 | 275 | 23 (0)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TAC_LA_LOGIN_ID | 74 | | 3 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| TAC_FORM_ATOK | 19 | 589 | 353 (1)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX RANGE SCAN | TAC_FA_ATOK_I | 38 | | 2 (0)| 00:00:01 | | |
|* 10 | TABLE ACCESS BY GLOBAL INDEX ROWID | TAC_FORM_SUMMARY | 1 | 94 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 11 | INDEX UNIQUE SCAN | TAC_FS_UFID_PK | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Something must be wrong! Thought, it could be statistics or could be some other parameters.Rebuilt local index and recollected statistics (histogram/no histogram both), but still the same execution plan.
Then looked at metalink for any possible bug (this good practice I learned from my senior). Bingo! found a related issue reported but not the same thing. Let me share with you -
Bug No. 7210921 - STATISTICS WITH SUBPARTITIONS ARE NOT CORRECTLY INTERPRETED IN EXPLAIN PLANS
It explained that there are optimizer problems for partitioned tables - after generating execution plan by the optimizer, the execution plan shows wrong stats for partitions. Deleting table statistics was the workaround mentioned in metalink for the problem!
I thought, as the problem seems similar, what if I delete the partition table statistics?
SQL> EXEC DBMS_STATS.DELETE_TABLE_STATS(ownname =>'PROD', tabname =>'TAB1');
It helped!! The execution plan got changed and showed partitioned index access. I was not convinced to this solution of deleting table stats - specially for such a big table. There are other queries which would access the table and dynamic sampling (absence of statistics) could be another nightmare.
Then thought, what if I use earlier version of the optimizer?
SQL> alter session set optimizer_features_enable='10.2.0.3';
It worked - execution plan changed! The problem is with the 10.2.0.4.0 version.
If we don't want to change system level or session level settings for optimizer version (which may not be good for a large application), there are ways using hints to force particular optimizer version like this -
SELECT /*+ optimizer_features_enable('10.2.0.3') */ fs.form_type, fs.prov_id, fs.pgm_id, fs.client_id,
fs.created_by_id, fs.created_date, fs.notif_level, fs.state, fs.form_id, fs.title, fs.summary
FROM .......
Then the execution plan looked like follows, this was what I looked for -
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 57 | 8550 | 6160 (43)| 00:00:07 | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT ORDER BY | | 57 | 8550 | 6160 (43)| 00:00:07 | | |
| 3 | HASH UNIQUE | | 57 | 8550 | 6159 (43)| 00:00:07 | | |
|* 4 | HASH JOIN | | 57 | 8550 | 6158 (43)| 00:00:07 | | |
|* 5 | HASH JOIN | | 2376 | 129K| 4267 (51)| 00:00:05 | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| TAC_LOGIN_ATOK | 126 | 3150 | 238 (2)| 00:00:01 | | |
|* 7 | INDEX RANGE SCAN | TAC_LA_LOGIN_ID | 859 | | 4 (0)| 00:00:01 | |
| 8 | PARTITION RANGE ITERATOR | | 1090K| 32M| 3882 (52)| 00:00:04 | KEY | KEY |
|* 9 | TABLE ACCESS FULL | TAC_FORM_ATOK | 1090K| 32M| 3882 (52)| 00:00:04 | KEY | KEY |
| 10 | PARTITION RANGE ITERATOR | | 13852 | 1271K| 1889 (23)| 00:00:02 | KEY | KEY |
|* 11 | TABLE ACCESS FULL | TAC_FORM_SUMMARY | 13852 | 1271K| 1889 (23)| 00:00:02 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------
Perfect! This solves our issue. Probably, I will use the hint solution in production.
Summary of solutions
1. Deleting stats for the partitioned table - not be a good approach, don't like
2. Using earlier optimizer version (10.2.0.3.0) for the database - not very recommended, their might be other fixes in the new version (10.2.0.4.0) even though this particular problem exists.
3. Using query hints to use earlier optimizer (10.2.0.3.0) - seems sensible to me and I planned to fix case by case.
***
Subscribe to:
Posts (Atom)