Plan stability


My Database is on CHOOSE mode . the querries are working fine except one .It works good when we set the session mode as first_rows .

Since this database is used by a third party application nothing can be done .How to go about only for this particular sql statement ..
Posted On: Friday 16th of November 2012 02:38:27 AM Total Views:  395
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




tkprof wont show execution_plan

Do you have any idea why my tkprof on a session wont show the execution plan. select sysdate from dual call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 I do have the plan_table.
VIEWS ON THIS POST

153

Posted on:

Saturday 3rd November 2012
View Replies!

seeking explanations/examples on Sar/vmstat/netstat etc

can somebody pls give me examples on sar and vmstat tools we use in unix I searched dba support but didnt find anything
VIEWS ON THIS POST

184

Posted on:

Saturday 3rd November 2012
View Replies!

quick explanation of semaphore values..

meta link is down right now, and I need to get a better definition of the /etc/system settings. I am doing a data warehouse, and I believe I will need vastly different setting than an OLTP system.
VIEWS ON THIS POST

175

Posted on:

Saturday 3rd November 2012
View Replies!

query plan is wrong

Hi I have a query which needs to be hinted to choose the correct acesses plan .Can somebody let me know why this is [code] explain plan for SELECT b.dsd_zone_cd, a.cat_nbr, b.zone_retl_amt, b.zone_cost_amt, TO_CHAR (a.eff_dt, 'MM/DD/YYYY') FROM dsd_zone_store a, dsd_sku_price_zone b WHERE b.sku_nbr = 136254 AND a.store_nbr = 3412 AND a.dsd_zone_cd = b.dsd_zone_cd AND a.cat_nbr = b.cat_nbr I am uploading 10053 trace file
VIEWS ON THIS POST

219

Posted on:

Saturday 3rd November 2012
View Replies!

tips in interpreting explain plan

Hi can any one spend 2 minutes , if they can suggest some changes in below query . SELECT t1.p_s_d, t1.c_n, t1.s_c_id, t3.s_m_id, t3.s_s_id, t3.s_e_hist, t3.s_id, t3.p_num, t3.r_code, t4.s_m_id, t4.l_name, t4.f_name, t4.m_inital, t2.s_c_id, t2.net_amt, t1.diag_1, t1.diag_2, t6.s_a_t, t6.c_num, t5.s_grp_id, t5.grp_id, SUBSTR(t5.grp_id,1,5), SUBSTR(t5.grp_id,1,6) FROM t6, t2, t1, t3, t5, t4 WHERE t6.s_a_t = t2.s_a_t AND t1.s_c_id = t2.s_c_id AND t1.s_m_id = t2.s_m_id AND t4.s_m_id = t2.s_m_id AND t4.s_m_id = t1.s_m_id AND t4.s_m_id = t3.s_m_id AND t3.s_grp_id = t5.s_grp_id AND t2.g_r_code '797' AND t3.s_e_hist IN (SELECT MAX(s_e_hist) FROM t3 WHERE s_m_id = t3.s_m_id ) UNION ALL SELECT t1.p_s_d, t1.c_n, t1.s_c_id, t3.s_m_id, t3.s_s_id, t3.s_e_hist, t3.s_id, t3.p_num, t3.r_code, t4.s_m_id, t4.l_name, t4.f_name, t4.m_inital, t2.s_c_id, t2.net_amt, t1.diag_1, t1.diag_2, 0, NULL, t5.s_grp_id, t5.grp_id, SUBSTR(t5.grp_id,1,5), SUBSTR(t5.grp_id,1,6) FROM t2, t1, t3, t5, t4 WHERE t2.s_c_id = t1.s_c_id AND t1.s_m_id = t2.s_m_id AND t4.s_m_id = t2.s_m_id AND t4.s_m_id = t1.s_m_id AND t4.s_m_id = t3.s_m_id AND t3.s_grp_id = t5.s_grp_id AND t2.g_r_code = '797' AND t3.s_e_hist IN (SELECT MAX(s_e_hist) FROM t3 WHERE s_m_id = t3.s_m_id ) please find explain plan attached.
VIEWS ON THIS POST

241

Posted on:

Sunday 4th November 2012
View Replies!

setting sql trace/explain plan

Hi all, If you wanted to get just the explain plan of a sql without the sql executing, I thought doing "set autotrace traceonly explain" would give you the path without executing the sql itself. But it appears this works only for select sql. But it would actually delete or update before giving the optimizer path for delete and update sql. How do you produce a sql path for delete/update without the sql taking effect
VIEWS ON THIS POST

214

Posted on:

Sunday 4th November 2012
View Replies!

Reverse Engineer the Explain plan to get the HINTS??

I have an SQL stmt which has join on three tables ENTRY, JOURNAL and BATCH. I added certain condition (say AND RECORD# > = 0, which will be always true) to make use of one of my index. Now I got the Exp Plan as follows. I want to get the same result by using HINTS instead of having the extraneous AND RECORD# > = 0 condition. Can some one, by looking at the following Exp Plan output tell me what would be the equivalent HINT 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=98) 1 0 SORT (ORDER BY) (Cost=9 Card=1 Bytes=98) 2 1 NESTED LOOPS (Cost=7 Card=1 Bytes=98) 3 2 NESTED LOOPS (Cost=6 Card=1 Bytes=90) 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'ENTRY' (Cost=4 C ard=1 Bytes=49) 5 4 INDEX (RANGE SCAN) OF 'IX_ENTRY_ACCOUNT#' (NON-U NIQUE) (Cost=3 Card=1) 6 3 TABLE ACCESS (BY INDEX ROWID) OF 'BATCH' (Cost=2 C ard=155 Bytes=6355) 7 6 INDEX (UNIQUE SCAN) OF 'PK_BATCH' (UNIQUE) (Cost =1 Card=155) 8 2 TABLE ACCESS (BY INDEX ROWID) OF 'JOURNAL' (Cost=1 C ard=6 Bytes=48) 9 8 INDEX (UNIQUE SCAN) OF 'PK_JOURNAL' (UNIQUE)
VIEWS ON THIS POST

300

Posted on:

Sunday 4th November 2012
View Replies!

SQL trace changes explain plan

I've got a select query that is taking 20 secs when it should take under 1 sec. I've done some tracing etc and have noticed that when I put a trace on the query it chooses a different (and correct) plan than when there's no tracing. Can anyone help me understand why! 9204 Oracle on Solaris: Code: SQL> variable b1 varchar2(20) SQL> exec :b1 := 'ABC'; PL/SQL procedure successfully completed. SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:20.93 SQL> alter session set events '10046 trace name context forever, level 12'; Session altered. Elapsed: 00:00:00.00 SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:00.57 SQL> alter session set events '10046 trace name context off'; Session altered. Elapsed: 00:00:00.01 SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:20.76 SQL> alter session set sql_trace=true; Session altered. Elapsed: 00:00:00.00 SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:00.54 SQL> alter session set sql_trace=false; Session altered. Elapsed: 00:00:00.01 SQL> @sel COUNT(*) ---------- 0 Elapsed: 00:00:20.83
VIEWS ON THIS POST

243

Posted on:

Wednesday 7th November 2012
View Replies!

Why did the plan change

I know I am going to be hounded for this, but my question is truly a generic one. But if you want specifics, then consider it to be a 10.2.0.4 database on linux machine. My question is how do I find out why a plan changed for a query. I know the probable causes can be data changes (which is not the case I am looking into) or stats change or index rebuild. But my question is how do I pin point the cause of a plan change (what views/reports can help). I know the plan has changed, now what caused it keeping in mind its not a data change and how do I find it out with the help of DBA_HIST or V$ views.
VIEWS ON THIS POST

242

Posted on:

Wednesday 7th November 2012
View Replies!

Tuning (Optimizer uses a bad plan)

Let's take this SQL statement : SELECT g_t1.codeinternearticle, g_t2.codedepot, SUM (g_t1.qteaprelever) FROM strd.lignes_liste_picking g_t1, strd.listes_picking g_t2 WHERE g_t2.codelistepicking = g_t1.codelistepicking AND lignes_liste_picking = 1 GROUP BY g_t1.codeinternearticle, g_t2.codedepot lignes_liste_picking listes_picking nb rows : 8000000 (8M) 350000 (350K) PK on : g_t1.codelistepicking & a FK on g_t2.codelistepicking g_t2.codeetatlistepicking could have 2 values (0 & 1). 99% of g_t2 rows have 0 value for this column (!!! : important -- we can have from time to time 100% of 1 values). There is a Unique index (enforced via a FK) on g_t2.codeetatlistepicking Stat was collected by dbms_stats. The execution plan is the following WITHOUT HINTS: Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Hint=CHOOSE 71 K 130117 SORT GROUP BY 71 K 1 M 130117 HASH JOIN 4 M 100 M 62341 TABLE ACCESS FULL LISTES_PICKING 188 K 2 M 792 TABLE ACCESS FULL LIGNES_LISTE_PICKING 9 M 95 M 14699 ---- exec time : 20s 176 recursive calls 8 db block gets 102427 consistent gets 102090 physical reads 0 redo size 152251 bytes sent via SQL*Net to client 24015 bytes received via SQL*Net from client 344 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 5124 rows processed And the exectution plan with RULE HINT : Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Hint=HINT: RULE SORT GROUP BY NESTED LOOPS TABLE ACCESS BY INDEX ROWID LISTES_PICKING INDEX RANGE SCAN LISTES_PICKING_FK2 TABLE ACCESS BY INDEX ROWID LIGNES_LISTE_PICKING INDEX RANGE SCAN LIGNES_LISTE_PICKING_PK ---- exec time : 1s 0 recursive calls 0 db block gets 7667 consistent gets 0 physical reads 0 redo size 152251 bytes sent via SQL*Net to client 23995 bytes received via SQL*Net from client 344 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 5124 rows processed Any solution to push Oracle optimizer to use an execution plan like that used with RULE based method (or better) (NB: we can't add hints, we can use outlines...). RDBMS : Oracle 8.1.7.4 (under Aix 4.3.3) , You might check your index_optimizer_cost_adj value - get it down to between 10 and 20.
VIEWS ON THIS POST

157

Posted on:

Wednesday 7th November 2012
View Replies!

plan_table and users

Just out of curiosity, what are the advantages of creating plan_table per schema when compared to that of creating one plan_table and then a public synonym and granting access to all the users to the same plan_table (haven't tried the feasibility of option two physically ). Thanx, Sam
VIEWS ON THIS POST

184

Posted on:

Wednesday 7th November 2012
View Replies!

resources planning

Dear members I would like to know what is base definition How can a DBA estimate the future requirements of the resources required(i.e. space requirement, RAM etc.)
VIEWS ON THIS POST

147

Posted on:

Wednesday 7th November 2012
View Replies!

Resource Mgr plan window is not getting activated with Oracle10g scheduler

We tried to setup scheduler windows with resource plans, the resource plan did not get activated when the window started. Before window starts i have issued the command ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = my_plan I can c active resource plan is available in v$rsrc_plan. Any idea
VIEWS ON THIS POST

224

Posted on:

Wednesday 7th November 2012
View Replies!

plan

explain plan output: select statement Optimizer=choose hash join table access full of 'small_table' table access by index rowid *** index range scan *** can somebody tell me how does this work I can understand the following plan: select statement Optimizer=choose hash join table access full of 'small_table' table access full of 'big_table' first we build a hash table for big_table, and then probe it by small_table. but for index_range scan , i don't understand how it work. I think index is not useful at this case, why it being used I think oracle always need to read all data from big_table and build hash table, why use index range scan
VIEWS ON THIS POST

128

Posted on:

Wednesday 7th November 2012
View Replies!

Query execution plans are different on 8i and 10g?

, We have migrated database from Oracle 8i to 10g and found following. Same query, same datastucture and data, but query executions plans are different on Oracle 8i and on 10g. On 8i we are using OPTIMIZER_MODE = CHOOSE and on 10g OPTIMIZER_MODE = all_rows which is default. Resultset on both the database version is same, but since execution plan are different, default sorting is not happening on 10g. So from application we see difference in sorting. Below I have given query and indexes on respective tables, plan on different versions. Indexes: XIE1MASTERTABLE - (VORGANGSID, LEISTUNG) and XPKKATALOGTABLE - CODE SELECT MASTERTABLE.EINRICHTUNG, MASTERTABLE.ITBRowID, ZAEHLER, LEISTUNG, ARTIKEL, BEZEICHNUNG, VERABREICHTDATUM, VERABREICHZEIT, ANZAHL, MENGENEINHEIT, DOSIS, DOSIERUNGSEINHEIT, VERABREICHUNGSART, CHARGENNREINGZWANG, HOSTTRANSAKTIONSID, IstZuUebertragen, MASTERTABLE.PREIS, VERBRAUCHSORT FROM MASTERTABLE, KATALOGTABLE WHERE MASTERTABLE.ARTIKEL = CODE (+) AND VORGANGSID = 3769 AND MASTERTABLE.EINRICHTUNG = 'Ein1' ; On Oracle 8i: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=23 Bytes=2139) 1 0 NESTED LOOPS (OUTER) (Cost=28 Card=23 Bytes=2139) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTERTABLE' (Cost=5 Card=23 Bytes=1035) 3 2 INDEX (RANGE SCAN) OF 'XIE1MASTERTABLE' (NON-UNIQUE) (Cost=3 Card=23) 4 1 TABLE ACCESS (BY INDEX ROWID) OF 'KATALOGTABLE' (Cost=1 Card=2309 Bytes=110832) 5 4 INDEX (UNIQUE SCAN) OF 'XPKKATALOGTABLE' (UNIQUE) ---------------------------------------------------------------------- On Oracle 10g: Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=22 Bytes=2046) 1 0 HASH JOIN (OUTER) (Cost=16 Card=22 Bytes=2046) 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MASTERTABLE' (TABLE) (Cost=5 Card=22 Bytes=990) 3 2 INDEX (RANGE SCAN) OF 'XIE1MASTERTABLE' (INDEX) (Cost=3 Card=22) 4 1 TABLE ACCESS (FULL) OF 'KATALOGTABLE' (TABLE) (Cost=10 Card=2309 Bytes=110832) But when I set 'ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS' on 10g, query changed plan as shown below. SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=29 Card=22 Bytes=2046) NESTED LOOPS (OUTER) (Cost=29 Card=22 Bytes=2046) TABLE ACCESS (BY INDEX ROWID) OF TBLVERBRAUCH (TABLE) (Cost=7 Card=22 Bytes=990) INDEX (RANGE SCAN) OF XPKVERBRAUCH (INDEX (UNIQUE)) (Cost=3 Card=22) TABLE ACCESS (BY INDEX ROWID) OF KATARTIKEL (TABLE) (Cost=1 Card=1 Bytes=48) INDEX (UNIQUE SCAN) OF XPKKATARTIKEL (INDEX (UNIQUE)) (Cost=0 Card=1) and result was sorted as it is on Oracle 8i. Does this mean to maintain default sorting of queries we need use optimizer first_rows and not all_rows on 10g Why all_rows does HASH JOIN (OUTER)than NESTED LOOPS (OUTER). Why same data structure has different explain plan on 8i and 10g and to maintain same query plans of 8i on 10g what we need to do Please guide us and share your experiences.
VIEWS ON THIS POST

126

Posted on:

Wednesday 7th November 2012
View Replies!

priviledge error explain plan

Hi all While explaining i am getting this error please help. SQL> set autotrace on SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report and SQL> set autotrace traceonly explain SQL> select * from cat; Execution Plan ---------------------------------------------------------- ERROR: ORA-01039: insufficient privileges on underlying objects of the view SP2-0612: Error generating AUTOTRACE EXPLAIN report SQL>
VIEWS ON THIS POST

297

Posted on:

Wednesday 7th November 2012
View Replies!

TM on plan_table

Why plan_table got locked after a 'explain plan for...' When the lock suppose to be released
VIEWS ON THIS POST

148

Posted on:

Thursday 15th November 2012
View Replies!

Tablespace Transplant

I have a unique situation here at work. I have to take the tablspace file(s) from one system backup of oracle and place into our test database so as to extract information from a particular table. We seem exhausted other means of recovering this table. If I bring these tablespace files which are a backup from our live database into test will it work when i restart the instance If this is possible how would i go about it
VIEWS ON THIS POST

294

Posted on:

Thursday 15th November 2012
View Replies!

Same query for different execution plans

Hi everybody ! We're trying to optimize a query. We have two databases on two different boxes (Solaris 8, Oracle release 8.1.7.0). The databases contain the same data : same objects, same statistics, same # of extents/object (=1), same tablespaces, same datafile'size. The only difference that we can noticed is that the server #1 has 2 cpu and the server #2 has 4 cpu. We obtain for the same query two different explain plan : server #1 : Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Hint=CHOOSE 4 M 137697 MERGE JOIN 4 M 864 M 137697 MERGE JOIN 4 M 729 M 132320 SORT JOIN 544 K 79 M 83592 HASH JOIN OUTER 544 K 79 M 46966 MERGE JOIN 544 K 64 M 17303 SORT JOIN 136 K 10 M 5622 HASH JOIN 136 K 10 M 1650 TABLE ACCESS FULL COMMUNE 14 K 328 K 11 TABLE ACCESS FULL ADRESSE 136 K 7 M 313 SORT JOIN 544 K 22 M 11681 TABLE ACCESS FULL EXERCICE 544 K 22 M 2135 TABLE ACCESS FULL HOPITAL 55 K 1 M 143 SORT JOIN 4 M 65 M 48728 TABLE ACCESS FULL T_MEDECIN 4 M 65 M 2484 SORT JOIN 332 K 9 M 5377 TABLE ACCESS FULL INDIVIDU 332 K 9 M 929 server #2 : Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Hint=CHOOSE 4 M 56119 MERGE JOIN 4 M 854 M 56119 MERGE JOIN 4 M 721 M 53210 SORT JOIN 544 K 79 M 25868 HASH JOIN 544 K 79 M 6038 TABLE ACCESS FULL COMMUNE 14 K 328 K 11 HASH JOIN OUTER 544 K 67 M 5475 HASH JOIN 544 K 52 M 3470 TABLE ACCESS FULL ADRESSE 136 K 7 M 313 TABLE ACCESS FULL EXERCICE 544 K 22 M 2136 TABLE ACCESS FULL HOPITAL 55 K 1 M 144 SORT JOIN 4 M 64 M 27342 TABLE ACCESS FULL T_MEDECIN 4 M 64 M 2610 SORT JOIN 332 K 9 M 2909 TABLE ACCESS FULL INDIVIDU 332 K 9 M 928 Here is the query : SELECT E.IND_NUM_TVF as individu, E.EX_CD, TM.MATRICULE, I.IND_NOM, I.IND_NOM || ' ' ||I.IND_PRENOM, I.IND_PRENOM, I.SPE_CD, H.HOP_NOM, AD.ADRESSE, AD.CP_COMMUNE, C.NOM_COMMUNE, RTRIM(NVL(E.EX_TEL, AD.ADR_TEL)), AD.UGA_CD, E.SUR_RDV, E.HOP_NUM_TVF_EX, E.SER_NUM_TVF_EX, E.EX_STATUT, E.PROFIL, TM.TOP_DELEGUE, F_MAX_VISITE(TM.MATRICULE,TM.INDIVIDU), F_MAX_VISITE(NULL,TM.INDIVIDU), E.TYPE_ORG, E.GRP1, E.GRP2, I.POT_PRESC, I.IND_STATUT, H.TET_CD FROM EXERCICE E, INDIVIDU I, ADRESSE AD, COMMUNE C, HOPITAL H, T_MEDECIN TM WHERE TM.INDIVIDU = E.IND_NUM_TVF AND TM.EXERCICE = E.EX_CD AND E.IND_NUM_TVF = I.IND_NUM_TVF AND E.ADR_CD = AD.ADR_CD AND AD.CODE_INSEE = C.CODE_INSEE AND AD.CP_COMMUNE = C.CP_COMMUNE AND E.HOP_NUM_TVF_EX = H.HOP_NUM_TVF (+); Does someone have encountered the problem
VIEWS ON THIS POST

187

Posted on:

Thursday 15th November 2012
View Replies!

re: interpreting explain plan

hi! Guys , Can some body be kind enough to post a sample explain plan and show how to interpret it \t
VIEWS ON THIS POST

206

Posted on:

Friday 16th November 2012
View Replies!