Re: Query Performance with params

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_at_gmail.com> 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 affect
>> one or two specific tables/columns/access patterns and which have specific
>> solutions.
>>
> Thanks Dominic. Desperate times, desperate measures. I also remember
> 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
>

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