Home » RDBMS Server » Performance Tuning » Help needed to tune the SQL (merged 2)
| Help needed to tune the SQL (merged 2) [message #565690] |
Thu, 06 September 2012 05:11  |
parthaspaul
Messages: 9
Registered: December 2006
Location: KOLKATA
|
Junior Member |
 
|
|
Hi I have following query (Oracle 11g) which reads a table (mps_Cont_par_line) of almost more than 400000000 data and it takes almost 20 sec to fetch the data. Please help to tune this SQL
SELECT f.mps_cpl_uid,
f.rev_no,
f.mps_ch_uid,
f.mps_cc_uid,
f.mps_ctl_uid,
f.orgn_lcn,
f.load_port,
f.load_port_grp,
f.tsh_port,
f.dsch_port,
f.dsch_port_grp,
f.dest_lcn,
SUBSTR(REPLACE(c.cmd_dscr, CHR(10), ' '), 1, 100) cmd_dscr,
f.eqp_typ,
f.shpr_cont,
c.haz_flg,
f.bas_code,
f.base_rate,
f.base_curr,
f.all_in_flg,
f.imdl_mode_orgn,
f.imdl_mode_dest,
f.canal,
f.rate_per,
f.line_code,
f.trade_lane,
f.orgn_imdl_rate,
f.orgn_imdl_curr,
f.ocean_rate,
f.ocean_curr,
f.dest_imdl_rate,
f.dest_imdl_curr,
f.move_orgn,
f.move_dest,
f.section_note_ref,
c.cmd_grp_code,
f.load_orgn,
f.unload_dest,
f.ppd_col,
f.pay_ofc,
f.pricing_sec,
f.bl_ofc,
DECODE(b.ref_no, v_rating_hdr_rec.nmd_cust_ref_no, 16777216, 0)
+ DECODE(b.ref_no, 'ALL', 8388608, 0)
+ DECODE(h.ref_no, v_rating_hdr_rec.nmd_cust_ref_no, 16777216, 0) --DMR#LN11059
+ DECODE(h.ref_no, 'ALL', 8388608, 0) --DMR#LN11059
+ DECODE(c.haz_flg, v_rating_oth_param_rec.haz_flg, 4194304, 0)
+ DECODE(NVL(c.haz_flg, 'N'), 'N', 2097152, 0)
+ DECODE(f.orgn_lcn, v_rating_hdr_rec.place_rcpt, 1048576, 0)
+ DECODE(f.orgn_lcn, SUBSTR(v_rating_hdr_rec.place_rcpt, 1, 5), 524288, 0)
+ DECODE(f.orgn_lcn, v_pr_metro, 262144, 0)
+ DECODE(f.load_port, v_rating_hdr_rec.load_port, 131072, 0)
+ DECODE(f.load_port, SUBSTR(v_rating_hdr_rec.load_port, 1, 5), 65536, 0)
+ DECODE(Mp_Conproc_Pks.Chk_Lcn_Exist_in_LPDP_Group(f.load_port_grp, v_rating_hdr_rec.load_port), 'Y', 32768, 0) --DMR#LN11057
+ DECODE(f.dsch_port, v_rating_hdr_rec.dsch_port, 16384, 0)
+ DECODE(f.dsch_port, SUBSTR(v_rating_hdr_rec.dsch_port, 1, 5), 8192, 0)
+ DECODE(Mp_Conproc_Pks.Chk_Lcn_Exist_in_LPDP_Group(f.dsch_port_grp, v_rating_hdr_rec.dsch_port), 'Y', 4096, 0) --DMR#LN11057
+ DECODE(f.dest_lcn, v_rating_hdr_rec.place_deliv, 2048, 0)
+ DECODE(f.dest_lcn, SUBSTR(v_rating_hdr_rec.place_deliv, 1, 5), 1024, 0)
+ DECODE(f.dest_lcn, v_pd_metro, 512, 0)
+ DECODE(f.tsh_port, v_rating_hdr_rec.tsh_port, 256, 0)
+ DECODE(f.tsh_port, SUBSTR(v_rating_hdr_rec.tsh_port, 1, 5), 128, 0)
+ DECODE(f.line_code, v_rating_hdr_rec.rep_line, 64, 0)
+ DECODE(f.move_orgn, v_rating_hdr_rec.move_orgn, 32, 0)
+ DECODE(f.move_orgn, 'CY-C', 16, 0)
+ DECODE(f.move_orgn, 'CY-D', 8, 0)
+ DECODE(f.move_dest, v_rating_hdr_rec.move_dest, 4, 0)
+ DECODE(f.move_dest, 'CY-C', 2, 0)
+ DECODE(f.move_dest, 'CY-D', 1, 0) total
FROM mps_cont_hdr a,
mps_cont_nmd_cust b,
mps_cont_nmd_asso h,
mps_cont_cmd c,
mps_cont_trade_lane e,
mps_cont_par_line f
WHERE a.contract_no = v_rating_hdr_rec.contract_no
AND a.status IN ('A', 'E')
AND NVL(a.pcc_flg, 'N') = 'N'
AND v_rating_hdr_rec.changed_access_dt >= f.eff_dt
AND v_rating_hdr_rec.changed_access_dt <= NVL(f.exp_dt, a.exp_dt)
AND ((b.ref_no = v_rating_hdr_rec.nmd_cust_ref_no OR b.ref_no = v_alt_ref_no)
OR (h.ref_no = v_rating_hdr_rec.nmd_cust_ref_no OR h.ref_no = v_alt_ref_no))
AND (NVL(c.haz_flg, 'N') = NVL(v_rating_oth_param_rec.haz_flg, 'N') OR NVL(c.haz_flg, 'N') = 'N')
-- AND f.mps_ch_uid = v_mps_ch_uid
AND f.trade_lane = p_trade_lane
--AND DECODE(f.orgn_lcn, v_rating_hdr_rec.place_rcpt, 'T', SUBSTR(v_rating_hdr_rec.place_rcpt, 1, 5), 'T', v_pr_metro, 'T', 'F') = 'T'
AND (f.orgn_lcn = v_rating_hdr_rec.place_rcpt
OR f.orgn_lcn = SUBSTR(v_rating_hdr_rec.place_rcpt, 1, 5)
OR f.orgn_lcn = v_pr_metro)
AND (f.move_orgn = v_rating_hdr_rec.move_orgn
OR (f.move_orgn = 'CY-C' AND v_rating_hdr_rec.move_orgn = 'CY')
OR (f.move_orgn = 'CY-D' AND v_rating_hdr_rec.move_orgn = 'CY')
OR (f.move_orgn = 'CY-C' AND v_rating_hdr_rec.move_orgn = 'CFS')
OR (f.move_orgn = 'CY-D' AND v_rating_hdr_rec.move_orgn = 'DOOR')
)
AND (
(f.load_port = v_rating_hdr_rec.load_port)
OR (f.load_port = SUBSTR(v_rating_hdr_rec.load_port, 1, 5))
OR (Mp_Conproc_Pks.Chk_Lcn_Exist_in_LPDP_Group(f.load_port_grp, v_rating_hdr_rec.load_port) = 'Y')
OR (f.load_port IS NULL AND f.load_port_grp IS NULL)
)
--AND DECODE(f.tsh_port, v_rating_hdr_rec.tsh_port, 'T', SUBSTR(v_rating_hdr_rec.tsh_port, 1, 5), 'T', NULL, 'T', 'F') = 'T'
AND (f.tsh_port = v_rating_hdr_rec.tsh_port
OR f.tsh_port = SUBSTR(v_rating_hdr_rec.tsh_port, 1, 5)
OR f.tsh_port IS NULL)
-- AND DECODE(f.line_code, v_rating_hdr_rec.rep_line, 'T', NULL, 'T', 'F') = 'T'
AND (f.line_code = v_rating_hdr_rec.rep_line
OR f.line_code IS NULL)
AND (
(f.dsch_port = v_rating_hdr_rec.dsch_port)
OR (f.dsch_port = SUBSTR(v_rating_hdr_rec.dsch_port, 1, 5))
OR (Mp_Conproc_Pks.Chk_Lcn_Exist_in_LPDP_Group(f.dsch_port_grp, v_rating_hdr_rec.dsch_port) = 'Y')
OR (f.dsch_port IS NULL AND f.dsch_port_grp IS NULL)
)
AND (f.move_dest = v_rating_hdr_rec.move_dest
OR (f.move_dest = 'CY-C' AND v_rating_hdr_rec.move_dest = 'CY')
OR (f.move_dest = 'CY-D' AND v_rating_hdr_rec.move_dest = 'CY')
OR (f.move_dest = 'CY-C' AND v_rating_hdr_rec.move_dest = 'CFS')
OR (f.move_dest = 'CY-D' AND v_rating_hdr_rec.move_dest = 'DOOR')
)
-- AND DECODE(f.dest_lcn, v_rating_hdr_rec.place_deliv, 'T', SUBSTR(v_rating_hdr_rec.place_deliv, 1, 5), 'T', v_pd_metro, 'T', 'F') = 'T'
AND (f.dest_lcn = v_rating_hdr_rec.place_deliv
OR f.dest_lcn = SUBSTR(v_rating_hdr_rec.place_deliv, 1, 5)
OR f.dest_lcn = v_pd_metro)
AND f.cncl_flg = 'N'
AND f.eqp_typ = Mp_Gen_Pks.Get_Gpm_Eqp_Code(v_rating_oth_param_rec.eqp_typ) --FOR LCL and BLK get_gpm_eqp_code return LCL and BLK
AND NVL(f.shpr_cont, 'N') = NVL(v_rating_oth_param_rec.shpr_cont, 'N')
AND b.mps_ch_uid = a.mps_ch_uid
AND b.rev_no = a.rev_no
AND b.mps_cnc_uid = h.mps_cnc_uid(+)
AND b.rev_no = h.rev_no(+)
AND c.mps_cnc_uid = b.mps_cnc_uid
AND c.rev_no = b.rev_no
AND e.mps_cc_uid = c.mps_cc_uid
AND e.rev_no = c.rev_no
AND e.mps_ctl_uid = f.mps_ctl_uid
AND e.rev_no = f.rev_no
ORDER BY f.rev_no DESC, total DESC
|
|
|
|
|
|
|
|
|
|
| Re: Help needed to tune the SQL (merged 2) [message #566493 is a reply to message #565770] |
Sat, 15 September 2012 22:32  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I tune stuff like this at work all the time. It would take me about three hours to do basic tuning on this, after which you would know a lot, but might in fact not actually have a way to make it go faster. Since I am not getting paid, I won't spend three hours on it. But... I can give you some basic steps:
1) first format the query so it is easier to read. Do this manually, or use a formatting tool like Toad, or Instant Formatter.
SELECT f.mps_cpl_uid,
f.rev_no,
f.mps_ch_uid,
f.mps_cc_uid,
f.mps_ctl_uid,
f.orgn_lcn,
f.load_port,
f.load_port_grp,
f.tsh_port,
f.dsch_port,
f.dsch_port_grp,
f.dest_lcn,
Substr(Replace(c.cmd_dscr, Chr(10), ' '), 1, 100) cmd_dscr,
f.eqp_typ,
f.shpr_cont,
c.haz_flg,
f.bas_code,
f.base_rate,
f.base_curr,
f.all_in_flg,
f.imdl_mode_orgn,
f.imdl_mode_dest,
f.canal,
f.rate_per,
f.line_code,
f.trade_lane,
f.orgn_imdl_rate,
f.orgn_imdl_curr,
f.ocean_rate,
f.ocean_curr,
f.dest_imdl_rate,
f.dest_imdl_curr,
f.move_orgn,
f.move_dest,
f.section_note_ref,
c.cmd_grp_code,
f.load_orgn,
f.unload_dest,
f.ppd_col,
f.pay_ofc,
f.pricing_sec,
f.bl_ofc,
Decode(b.ref_no, v_rating_hdr_rec.nmd_cust_ref_no, 16777216, 0)
+ Decode(b.ref_no, 'ALL', 8388608, 0)
+ Decode(h.ref_no, v_rating_hdr_rec.nmd_cust_ref_no, 16777216, 0)
--DMR#LN11059
+ Decode(h.ref_no, 'ALL', 8388608, 0) --DMR#LN11059
+ Decode(c.haz_flg, v_rating_oth_param_rec.haz_flg, 4194304, 0)
+ Decode(Nvl(c.haz_flg, 'N'), 'N', 2097152, 0)
+ Decode(f.orgn_lcn, v_rating_hdr_rec.place_rcpt, 1048576, 0)
+ Decode(f.orgn_lcn, Substr(v_rating_hdr_rec.place_rcpt, 1, 5), 524288, 0
)
+ Decode(f.orgn_lcn, v_pr_metro, 262144, 0)
+ Decode(f.load_port, v_rating_hdr_rec.load_port, 131072, 0)
+ Decode(f.load_port, Substr(v_rating_hdr_rec.load_port, 1, 5), 65536, 0)
+ Decode(mp_conproc_pks.Chk_lcn_exist_in_lpdp_group(f.load_port_grp,
v_rating_hdr_rec.load_port), 'Y', 32768, 0) --DMR#LN11057
+ Decode(f.dsch_port, v_rating_hdr_rec.dsch_port, 16384, 0)
+ Decode(f.dsch_port, Substr(v_rating_hdr_rec.dsch_port, 1, 5), 8192, 0)
+ Decode(mp_conproc_pks.Chk_lcn_exist_in_lpdp_group(f.dsch_port_grp,
v_rating_hdr_rec.dsch_port), 'Y', 4096, 0) --DMR#LN11057
+ Decode(f.dest_lcn, v_rating_hdr_rec.place_deliv, 2048, 0)
+ Decode(f.dest_lcn, Substr(v_rating_hdr_rec.place_deliv, 1, 5), 1024, 0)
+ Decode(f.dest_lcn, v_pd_metro, 512, 0)
+ Decode(f.tsh_port, v_rating_hdr_rec.tsh_port, 256, 0)
+ Decode(f.tsh_port, Substr(v_rating_hdr_rec.tsh_port, 1, 5), 128, 0)
+ Decode(f.line_code, v_rating_hdr_rec.rep_line, 64, 0)
+ Decode(f.move_orgn, v_rating_hdr_rec.move_orgn, 32, 0)
+ Decode(f.move_orgn, 'CY-C', 16, 0)
+ Decode(f.move_orgn, 'CY-D', 8, 0)
+ Decode(f.move_dest, v_rating_hdr_rec.move_dest, 4, 0)
+ Decode(f.move_dest, 'CY-C', 2, 0)
+ Decode(f.move_dest, 'CY-D', 1, 0) total
FROM mps_cont_hdr a,
mps_cont_nmd_cust b,
mps_cont_nmd_asso h,
mps_cont_cmd c,
mps_cont_trade_lane e,
mps_cont_par_line f
WHERE a.contract_no = v_rating_hdr_rec.contract_no
AND a.status IN ( 'A', 'E' )
AND Nvl(a.pcc_flg, 'N') = 'N'
AND v_rating_hdr_rec.changed_access_dt >= f.eff_dt
AND v_rating_hdr_rec.changed_access_dt <= Nvl(f.exp_dt, a.exp_dt)
AND ( ( b.ref_no = v_rating_hdr_rec.nmd_cust_ref_no
OR b.ref_no = v_alt_ref_no )
OR ( h.ref_no = v_rating_hdr_rec.nmd_cust_ref_no
OR h.ref_no = v_alt_ref_no ) )
AND ( Nvl(c.haz_flg, 'N') = Nvl(v_rating_oth_param_rec.haz_flg, 'N')
OR Nvl(c.haz_flg, 'N') = 'N' )
-- AND f.mps_ch_uid = v_mps_ch_uid
AND f.trade_lane = p_trade_lane
AND ( f.orgn_lcn = v_rating_hdr_rec.place_rcpt
OR f.orgn_lcn = Substr(v_rating_hdr_rec.place_rcpt, 1, 5)
OR f.orgn_lcn = v_pr_metro )
AND ( f.move_orgn = v_rating_hdr_rec.move_orgn
OR ( f.move_orgn = 'CY-C'
AND v_rating_hdr_rec.move_orgn = 'CY' )
OR ( f.move_orgn = 'CY-D'
AND v_rating_hdr_rec.move_orgn = 'CY' )
OR ( f.move_orgn = 'CY-C'
AND v_rating_hdr_rec.move_orgn = 'CFS' )
OR ( f.move_orgn = 'CY-D'
AND v_rating_hdr_rec.move_orgn = 'DOOR' ) )
AND ( ( f.load_port = v_rating_hdr_rec.load_port )
OR ( f.load_port = Substr(v_rating_hdr_rec.load_port, 1, 5) )
OR ( mp_conproc_pks.Chk_lcn_exist_in_lpdp_group(f.load_port_grp,
v_rating_hdr_rec.load_port) = 'Y' )
OR ( f.load_port IS NULL
AND f.load_port_grp IS NULL ) )
AND ( f.tsh_port = v_rating_hdr_rec.tsh_port
OR f.tsh_port = Substr(v_rating_hdr_rec.tsh_port, 1, 5)
OR f.tsh_port IS NULL )
AND ( f.line_code = v_rating_hdr_rec.rep_line
OR f.line_code IS NULL )
AND ( ( f.dsch_port = v_rating_hdr_rec.dsch_port )
OR ( f.dsch_port = Substr(v_rating_hdr_rec.dsch_port, 1, 5) )
OR ( mp_conproc_pks.Chk_lcn_exist_in_lpdp_group(f.dsch_port_grp,
v_rating_hdr_rec.dsch_port) = 'Y' )
OR ( f.dsch_port IS NULL
AND f.dsch_port_grp IS NULL ) )
AND ( f.move_dest = v_rating_hdr_rec.move_dest
OR ( f.move_dest = 'CY-C'
AND v_rating_hdr_rec.move_dest = 'CY' )
OR ( f.move_dest = 'CY-D'
AND v_rating_hdr_rec.move_dest = 'CY' )
OR ( f.move_dest = 'CY-C'
AND v_rating_hdr_rec.move_dest = 'CFS' )
OR ( f.move_dest = 'CY-D'
AND v_rating_hdr_rec.move_dest = 'DOOR' ) )
AND ( f.dest_lcn = v_rating_hdr_rec.place_deliv
OR f.dest_lcn = Substr(v_rating_hdr_rec.place_deliv, 1, 5)
OR f.dest_lcn = v_pd_metro )
AND f.cncl_flg = 'N'
AND f.eqp_typ = mp_gen_pks.Get_gpm_eqp_code(
v_rating_oth_param_rec.eqp_typ)
--FOR LCL and BLK get_gpm_eqp_code return LCL and BLK
AND Nvl(f.shpr_cont, 'N') = Nvl(v_rating_oth_param_rec.shpr_cont, 'N')
AND b.mps_ch_uid = a.mps_ch_uid
AND b.rev_no = a.rev_no
AND b.mps_cnc_uid = h.mps_cnc_uid(+)
AND b.rev_no = h.rev_no(+)
AND c.mps_cnc_uid = b.mps_cnc_uid
AND c.rev_no = b.rev_no
AND e.mps_cc_uid = c.mps_cc_uid
AND e.rev_no = c.rev_no
AND e.mps_ctl_uid = f.mps_ctl_uid
AND e.rev_no = f.rev_no
ORDER BY f.rev_no DESC,
total DESC
2) next open up excel and list the tables in your from clause
3) note the number of rows in each table on your spreadsheet
4) decompose the query into FILTERING QUERIES for each table in the FROM clause such that each FILTERING QUERY uses the filtering criteria available to each table, so that we can get the filtered row count from each table. Filtered Rowcount is the rows that will flow into the query from each table based on the constant tests for that table in the WHERE clause, and it will give us an idea of what should be the driving table for this query and the join order for tables in this query. Using table A in your example the FILTERING QUERY would be this:
select count(*)
from mps_cont_hdr a
where 1 = 1
AND a.status IN ( 'A', 'E' )
AND Nvl(a.pcc_flg, 'N') = 'N'
/
Notice, no joins, just filter criteria. Construct one such query for each table in your FROM clause and run it. There may be tables for which there are no filter criteria. In these cases, don't bother with a FILTERING QUERY as the filtered rowcount is the same as the table rowcount since all rows from the table can flow into the query.
Note these filtered rowcounts in your spreadsheet and then do some simple math to determine the percentage of rows filtered away. (1-$1$C/$1$B%) or something like that.
5) order the tables in your spreadhsheet based on which tables have the highest percentage of their rows filtered out by their filtering criteria (that means sort descending on the filtered away% you calculated above). This will be your PREFERRED JOIN ORDER. It is possible that this join order will be the OPTIMAL JOIN ORDER for this query which is where we are headed but we can never really know if we have the OPTIMAL JOIN ORDER. For now just know that we are trying to keep the size of intermediary rowsets as small as possible and this strategy based on FILTERED ROWCOUNTS yields smallest intermediary rowset sizes 99% of the time. It turns out to be a very good strategy for tuning SQL queries and the primary strategy I use. Seems sad in a way, that SQL tuning might be reducable down to such a simple idea but I get paid a crap-load of money to use this method so what can I say. It is a practical way to start your tuning process if you are tuning an idividual sql statement.
6) contruct a FROM CLAUSE DIAGRAM of your query joins. This will show you the routes bewteen tables in your query, and where the constant tests you used above to construct your filtering queries sit on the diagram as well. A simple example would be this:
select *
from T1,T2,T3
where t1.a = 1
and t3.c = 3
and t1.pk = t2.fk
and t2.pk = t3.fk
/
T1 ----- T2 ----- T3
| |
| |
(c) (c)
7) merge your PREFERRED JOIN ORDER and your FROM CLAUSE DIAGRAM such that you modify the join order to account for the need to pass through tables to get from one preferred table to another, because the joins may require it. For example, suppose T3 had 99% of its rows filtered away by its constant test, and T1 had 80% of its rows filtered away. It should be obvious of course that T2 has 0% of its rows filtered since there are no filters for T2 in the query. Given this your PREFERRED JOIN ORDER would be T3,T1,T2. However, if you start with T3 and want to get to T1, the joins say you must go through T2. Thus your INITIAL JOIN ORDER will be T3,T2,T1.
at this point you have done two things:
a) determined the likely best driving table
b) determined the likely best join order
8) reorder the tables in the FROM CLAUSE or your initial query to the same order as the INITIAL JOIN ORDER you have discovered, and use the ORDERED hint to force table access to occur in the INITIAL JOIN ORDER. Assuming you have done your basic database diligence (3rd normal form design, datatypes, constraints, nullability, statistics, etc.), you should see a plan that produces a runtime superior to what you had previously.
At this point something should be dawning on you. If it is this easy, then WHY? didn't Oracle CBO figure it out. The answer is almost always that cardinality is wrong somewhere for some unknown reason. But we first have to figure this out and this is how I do it.
9) if the INITIAL JOIN ORDER does not produce a query that reduces your runtimes then you need to determine if access methods and join strategies used in the query are wrong somewhere. If you are reading a small percentage of rows from some table (<2%?) then you should most likely be using INDEX LOOKUP and NESTED LOOP JOINS for reading the table and joining to the next table. But if you are reading more than 2% of the rows from a table, you should likely be doing TABLE SCAN and HASH JOIN. So analyze the plan and actual rowcounts from each step and determine if there is somewhere that you are not using the proper access method and join strategy; this is most likely due to bad cardinality somewhere in the plan.
10) if you are on a database version that will allow you to run a query and get ACTUAL ROWCOUNTS out of the query plan then do that. Otherwise, create RECONSTRUCTION QUERIES and reconstruct your main query one table at a time, running each RECONSTRUCTION QUERY as you make it. For example, continuing with our simple select above, run this:
given we startd with this:
select *
from T1,T2,T3
where t1.a = 1
and t3.c = 3
and t1.pk = t2.fk
and t2.pk = t3.fk
/
construct and run these queries:
select /*+ ordered */ count(*)
from t3 where t3.c = 3
/
select /*+ ordered */ count(*)
from t3,t2
where t3.c = 3
and t3.fk = t2.pk
/
select /*+ ordered */ count(*)
fro t3,t2,t1
where t3.c = 3
and t3.fk = t2.pk
and t1.a = 1
and t2.fk = t1.pk
/
don't forget to
set timing on
set time on
set feedback 1
These RECONSTRUCTION QUERIES will show you how much cost each new table adds to the query. For complex queries like yours this can also show where JOIN CARDINALITY can suggest a change in INITIAL JOIN ORDER (this is known in 11g as cardinality feedback or adaptive cursor sharing). You may find for example, that after adding table T1 to the query above, the query goes from taking 2 seconds to 22 minutes. You can then start to ask WHY?
In the end, you are looking to make sure that the returned rowcounts from your RECONSTRUCTION QUERIES match the estimated counts in the query plan, for each table. If these are not close to each other for some table, then you have a problem wherein Oracle may not be able to get the right cardinality estimates for the query, at which point you will have to step in with hints (like ordered), or statistics changes, or SQL changes (if you can find ways to do the same thing only cheaper), etc., because Oracle is having a problem getting it right.
Hey, Oracle generates a good plan for 99% of all SQL on your database. It is just the few really bad ones that come your way to be fixed. It should be no surprise that the reason they are giving you trouble is because there is something keepting the CBO from getting it right.
Remember, every GOOD PERFORMING query has four things going for it:
1) good choice of driving table
2) good choice of join order for tables
3) good choice of table access method for each table
4) good choice of join method between tables being joined
The above steps and your own thinking and knowledge of the rows being returned from each table, will clue you to if your query has these four things right or not. If you think not, then you need to fix that somehow. I suggest trying the following in no particular order (there might be a good order but I am not suggesting any):
1) statistics
2) indexes
3) ORDERED hint
4) constraints
5) semantically equivelant rewrites (consider: ANALYTICS, Scalar Subquery Predicates, ROWNUM tricks)
6) database parameter changes
OK, this actually took me an hour to write so I guess I could have tuned this for you if I had access to your database. But enough already. Hey, if I wrote a book about this stuff and put it on Amazon, would anybody read it?
Good luck. Kevin
[EDITED by LF: disabled smilies in this message, as 8) became a sun-glasses smiley] which kind of spoiled the PERFECT demonstration]
[Updated on: Sun, 16 September 2012 01:06] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Help needed to tune the SQL (merged 2) [message #566495 is a reply to message #566494] |
Sun, 16 September 2012 01:10 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
I recall that Stephane already wrote 2 books about this... but one more will not harm.
Is it not weird that the first step that elders recommend to write or tume a query is to correctly write this one when SQL formatters did not exist when they started to work with SQL?
How is this that new ones do not understand this? It seems they do not understand their own query.
Regards
Michel
[Edit: fix url]
[Updated on: Sun, 16 September 2012 12:32] Report message to a moderator |
|
|
|
| Re: Help needed to tune the SQL (merged 2) [message #566517 is a reply to message #566495] |
Sun, 16 September 2012 11:03 |
|
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
As always, you are so on-the-money Michel.
I explain it by referring to an old and always true axiom of business. The biggest problem in any business is GOOD COMMUNICATION. Getting people to communicate well with each other always has been and always will be the biggest challenge for any business. People who can communicate effectively are already twice as valuable as those who can't or won't; and anything we can do to improve our ability to communicate is worth the effort.
So...
1) always format your code before you give it to someone else.
2) when possible, make sure your code actually compiles before you give it to someone else.
3) when possible, make sure your code gives the right answer before you give it to someone else.
Neat-ness counts in this business.
I also ordered the two book from amazon. Should be here in about a week. We will see what else I don't know. Thanks for this Michel.
Kevin
|
|
|
|
Goto Forum:
Current Time: Thu Nov 21 15:32:06 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
