Re: Query Performance with params
Date: Sun, 30 Apr 2017 10:07:27 -0500
Message-ID: <CAP79kiR6Hr4RrqQGSG7bMHq8WtL78xXufZpUaVqbt7Fid1VJGQ_at_mail.gmail.com>
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:
- 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)
- 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
- 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/
- 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-lReceived on Sun Apr 30 2017 - 17:07:27 CEST