Re: query uses function based index in DEV1 db but does NOT use it in DEV2 db
- From: Karl Arao <karlarao@xxxxxxxxx>
- To: litanli@xxxxxxxxx
- Date: Wed, 25 Apr 2012 11:44:14 -0500
To systematically identify why is it behaving differently on DEV2 you can
make use of SQLTXPLAIN (sqltxtract) on both environments
and do a sqltcompare http://karlarao.tiddlyspot.com/#SQLT-compare
Also you can make use of the SQLT test case builder to replicate the plan
that you have on the DEV1 environment
http://karlarao.tiddlyspot.com/#%5B%5Btestcase%20-%20SQLT-tc%20(test%20case%20builder)%5D%5D
,
the *set_cbo_env.sql will execute a couple of "alter system" commands and
you can just comment that part when executing the test case on the
application schema.
So do this:
COMPARE
-----------------
1) Execute sqltxtract <sql_id> on DEV1
2) Execute sqltxtract <sql_id> on DEV2
3) copy the sqlt_s<ID>.zip generated from DEV1 to DEV2, then extract it
4) look for sqlt_s<ID>_tc.zip and unzip, then
execute ./sqlt_<ID>_import.sh.. that will import the data points from DEV1
to the DEV2 SQLT repository
5) Follow the "query the statement_ids and plan_hash_values" from
http://karlarao.tiddlyspot.com/#SQLT-compare
6) Follow the "execute compare" from
http://karlarao.tiddlyspot.com/#SQLT-compare
7) Open the sqltcompare HTML file and look for the red highlighted text
those are the differences between the two environments
TEST CASE - reproduce the same execution plan
-------------------------------------------------------------------------
1) On DEV2, go to the sqlt_s<ID>_tc.zip that you unzipped from the sqlt of
DEV1
2) The new version of SQLTXPLAIN has xpress.sh which executes
the xpress.sql, the xpress.sql executes the following:
- restore schema object stats from DEV1
- restore system statistics from DEV1
- the sqlt_<ID>_set_cbo_env.sql prompts you to connect as the application
schema
- the tc.sql executes the test case script
3) Now if you want to have the same plan as the DEV1, just execute the
xpress.sh BUT.. read on the scripts, and be aware
that sqlt_<ID>_set_cbo_env.sql and q.sql executes "alter system" commands
because it tries to make the environments the same. So if you don't want
those "alter system" commands executed just comment them out, you can do
this with the restore schema and object stats as well.
So whenever I do SQL troubleshooting I always run SQLTXPLAIN.. and it
helped me a lot on a bunch of scenarios like:
- pure OLTP system that upgraded from an old to new hardware the CPU speed
was faster on the new environment that made it to change a lot of plans -
then pushing the system stats back to the old hardware value made it go
back to the old plans. How did I discover it? I made use of sqltcompare and
sqlt test case builder
- troubleshooting stats differences and stats problems
- missing indexes
- finding out a locking issue caused by a trigger from one of the tables
- troubleshooting a storage problem from an old and new environment
- parameter changes on the old and new environment
- plan changes caused by parameter change
- etc.
--
Karl Arao
karlarao.wordpress.com
karlarao.tiddlyspot.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
