Ram,
I've not seen many people actually offering solutions - only offering
advice to not use those 2 parameters.
Here's what I would recommend:
1. Make sure you have collected SYSTEM STATS if you have not - you can
collect different sets of system stats and swap them out for different
workloads (see links #2 & #3 below to get you started)
https://karenmorton.blogspot.com/2012/08/i-was-wrong.html
https://karenmorton.blogspot.com/2008/06/optimizer-noworkload-statistics.html
https://docs.oracle.com/cd/B19306_01/server.102/b14211/stats.htm#i41496
(starting at section 14.4)
2. Grab one of the good performing sqls and one of the poor performing sql
statements and run a 10053 trace to see what decisions the optimizer is
making and why it is making those decisions
3. Grab a 10046 trace for the SQL statements to see cost and times
associated with each step - run the 10046 trace file through OraSRP found
here:
http://oracledba.ru/orasrp/
4. Build a SQL script to call your sql statements with the trace info like
so:
set timing on
set head on
set verify on
set feed on
set pages 0
set lines 1500
set trims on
set wrap off
set echo off
set autoprint on
-- variable rc refcursor;
-- exec :rc := '';
/* This section below allows you to "play with" specific optimizer settings
if you want */
-- alter session set optimizer_dynamic_sampling=4;
-- alter session set optimizer_Mode=FIRST_ROWS_1;
-- alter session set workarea_size_policy=manual;
-- alter session set sort_area_size=536870912;
-- alter session set hash_area_size=536870912;
-- alter session set "_complex_view_merging"=FALSE;
-- alter session set "_sort_multiblock_read_count"=256;
-- alter session set "_hash_multiblock_io_count"=256;
-- alter session set "_unnest_subquery"=false;
-- alter session set "_optimizer_use_histograms"=false;
-- alter session set "_optimizer_max_permutations"=80000;
-- alter session set "_OPTIMIZER_COST_BASED_TRANSFORMATION"=EXHAUSTIVE;
-- alter session set "_optimizer_sortmerge_join_enabled"=false;
-- alter session set "_optimizer_join_sel_sanity_check" = true;
-- alter session set "_always_semi_join" = off;
-- alter session set optimizer_index_cost_adj=1;
-- alter session set optimizer_index_caching=60;
-- alter session set "_b_tree_bitmap_plans"=FALSE;
-- alter session set "_partition_view_enabled"=FALSE;
-- alter session set "_no_or_expansion"=FALSE;
-- alter session set db_file_multiblock_read_count=128;
-- alter session set optimizer_dynamic_sampling=1;
alter session set max_dump_file_size=unlimited ;
alter session set tracefile_identifier='MYTRACEFILE_';
alter session set events '10046 trace name context forever, level 12';
alter session set statistics_level='ALL';
/* Setup your BIND variables here if your SQL has BIND VALUES */
-- var B1 varchar2(30);
-- exec :B1 := to_date('12/12/2014','MM/DD/YYYY');
/* Set your CURRENT_SCHEMA here if needed */
-- alter session set current_schema=AR ;
spool q1_1.log
<insert your problematic SQL statement here>
/
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS
LAST'))
/
spool off
On Sat, Apr 29, 2017 at 8:02 PM, Ram Raman <veeeraman@xxxxxxxxx> wrote:
It would not be surprising, if you delve deep into the statement which
have a problem, that you will find one or two specific issues which affectThanks Dominic. Desperate times, desperate measures. I also remember
one or two specific tables/columns/access patterns and which have specific
solutions.
reading an article by Wolfgang that those are the two most abused
parameters.
Let me see what I can do with those statements. Perhaps I will post them
here
