Re: query uses function based index in DEV1 db but does NOT use it in DEV2 db

From: Karl Arao <karlarao_at_gmail.com>
Date: Wed, 25 Apr 2012 11:44:14 -0500
Message-ID: <CACNsJndsmruWeEak37Hr4tQULAHqdOE3xYJ4hQh8XCi4=RPUyA_at_mail.gmail.com>

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


--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 25 2012 - 11:44:14 CDT