Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Performance Tuning :: How To Change Execution Order

May 2, 2012

Performance issues with the below mentioned sql.After gone through execution plan we have found out the reason but we couldn't able to change the execution plan the way we want.

If we could able to join

HRMGR.HR_EXPANDED_BOOK table with MISBOMGR.ibm_client_mgr7_empid, MISBOMGR.ibm_client_mgr6_empid at earlier stage means before HRMGR.HR_EMP_STATUS_LOOKUP then my issue will be solved but somehow optimizer is not considering that path. Even i have added push_subq hint which will push sub queries to execute at earlier stage but no use. Why push_subq hint is not working in this scenario and what can be the other alternative to change the driving path.

Query :-

select /*+ push_subq */CEMP.EMP_ID,
CEMP.EMP_STATUS_CD,
EMP_STATUS_DESC,
MGR_6_EMP_ID,
MGR_7_EMP_ID
FROM
[code]........

Execution plan :-
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16958 | 927K| 12008 (2)| 00:02:25 | | |
|* 1 | FILTER | | | | | | | |
| 2 | MERGE JOIN OUTER | | 173K| 9511K| 12008 (2)| 00:02:25 | | |
| 3 | REMOTE | HR_EXPANDED_BOOK | 173K| 7303K| 12005 (2)| 00:02:25 | INFODB | R->S |
|* 4 | SORT JOIN | | 11 | 143 | 3 (34)| 00:00:01 | | |
| 5 | REMOTE | HR_EMP_STATUS_LOOKUP | 11 | 143 | 2 (0)| 00:00:01 | INFODB | R->S |
|* 6 | TABLE ACCESS FULL| IBM_CLIENT_MGR7_EMPID | 1 | 8 | 2 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL| IBM_CLIENT_MGR6_EMPID | 1 | 8 | 3 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ USE_HASH ("IBM_CLIENT_MGR7_EMPID") */ 0 FROM
"MISBOMGR"."IBM_CLIENT_MGR7_EMPID" "IBM_CLIENT_MGR7_EMPID" WHERE "MGR_7_EMP_ID"=:B1) OR EXISTS
(SELECT 0 FROM "MISBOMGR"."IBM_CLIENT_MGR6_EMPID" "IBM_CLIENT_MGR6_EMPID" WHERE "MGR_6_EMP_ID"=:B2))
4 - access("CEMP"."EMP_STATUS_CD"="EMPLU"."EMP_STATUS_CD"(+))
filter("CEMP"."EMP_STATUS_CD"="EMPLU"."EMP_STATUS_CD"(+))
6 - filter("MGR_7_EMP_ID"=:B1)
7 - filter("MGR_6_EMP_ID"=:B1)

Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "EMP_ID","EMP_STATUS_CD","MGR_6_EMP_ID","MGR_7_EMP_ID" FROM
"HRMGR"."HR_EXPANDED_BOOK" "SYS_ALIAS_2" WHERE "EMP_STATUS_CD"='P' (accessing 'INFODB' )
5 - SELECT "EMP_STATUS_CD","EMP_STATUS_DESC" FROM "HRMGR"."HR_EMP_STATUS_LOOKUP" "EMPLU"
(accessing 'INFODB' )

View 3 Replies View Related

Performance Tuning :: Different Execution Plan In Different DB

Jul 20, 2013

Why the query is behaving differently with the different database.(execution plan)

Whatever the production database is having same database instance replicated to a new schema. I tried both the queries running on both environment.In prod the index has been used but in newdev it is not. This case existing primary key index were not been used.

View 6 Replies View Related

Performance Tuning :: Avg Time Taken By Execution Plan

Apr 12, 2013

How can i check the avg time taken by an execution plan. Actually i have a very big query and it changes its execution plan very often, we would like to lock the best execution plan and to find it , i would like to know the Average Execution Time the query takes when it runs using different different execution plans.

View 7 Replies View Related

Performance Tuning :: Execution Plan Of SQL Statement

Mar 25, 2012

I have queries on the execution plan of a sql statement

Following is the example

create table t1 as select s1.nextval id,a.* from dba_objects a;
create table t2 as select s2.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
commit;

create index i1 on t1(id);
create index i2 on t2(id);
create index i11 on t1(object_type);

exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

select count(*) from t1 where object_type='VIEW';

COUNT(*)
----------
8934

set autotrace traceonly explain

Can we say in the following case, that,

(1) First index on object_type is accessed to get rowids - t1.object_type='VIEW'
(2) Then the filter on owner is applied - t1.owner='SYS'
(3) Then the table T1 is accessed to fetch data from the rowids returned by the index I11 and filer application - TABLE ACCESS BY INDEX ROWID

Though I am unable to understand how filter can be applied to the rowids retrieved from index, we can see from the plan below that The rows accessed have reduced from 8550 to 1221 before we access the table...Thus filter "t1.owner='SYS'" is applied in between. Right?

another question is

Case 1 - do we retrieve a rowid from index for a given value, then retrieve required values from table for that rowid
Thus row at a time in both ... in loop
OR
Case 2 - we first fetch all rowids from index and then retrieve values from table one row at a time from the collection of rowids fetched?

Suppose Case 1 is what is happening then can we say, both the steps mentioned by IDS 2,3 in plan below are executed exactly equal number of times and the filter "t1.owner='SYS'" is applied at some later stage? Of course in this case the values in ROWS stand misleading then

select * from t1,t2 where t1.id = t2.id and t1.object_type='VIEW' and t1.owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 26873579
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1221 | 233K| 915 (1)| 00:00:11 |
|* 1 | HASH JOIN | | 1221 | 233K| 915 (1)| 00:00:11 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1221 | 116K| 381 (1)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | I11 | 8550 | | 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 161K| 15M| 533 (1)| 00:00:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."OWNER"='SYS')
3 - access("T1"."OBJECT_TYPE"='VIEW')

View 7 Replies View Related

Performance Tuning :: SQL Execution Plan Different On Different Hardware

Oct 31, 2012

So the situation is like this

- Database A (20 tables)
- Database B (20 tables)

- Both A and B are Oracle 11gR2

- Both of these databases run on different hardware (A is a VM, B is on a physical host)

- The 20 tables in A and B have exactly same number of rows and after preparing the data, the schemas were analysed using the same DBMS_STATS parameters

Despite this, the execution plans appear to be quite different for the same queries between A and B

I imagine there is something outside of the Oracle table rowcounts, table stats, column stats, index stats that's resulting in the different execution plans.

View 3 Replies View Related

Performance Tuning :: Achieve Same Execution Plan?

Apr 30, 2012

refere to below 2 queries and their execution plans:

First Query
INSERT INTO temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag)
SELECT /*+ USE_NL ( vd1 ,vd2 ,vd3 ) leading ( vd1 ,vd2 ,vd3 , tvd) */
vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U'
FROM vendor_data vd1,

[code]...

Second Query
SELECT vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U'
FROM ( select * from vendor_data vd1
where vd1.study_seq_no = 99903
AND vd1.control_column_seq_no = 435361232

[code]...

Both are to achieve same output but written in different ways. CAn I get same exectuion plan from 1st query as there is for 2nd using hints

View 10 Replies View Related

Performance Tuning :: Execution Plan Changing With Same SQL Profile

Mar 5, 2013

One of our clients is using Rule Based Optimizer on Oracle 10.2.0.3.0

2-3 weeks backs, during performance issue in one of the sql queries, one of our team members executed tuning adviser for it, created SQL profile and the subsequent execution of the SQL did not took much time (less I/O). Now it took hardly a minute to execute

When this happened I checked that the SQL profile forced that particular query to use CBO (say plan_hash_value is PHV1 here). Yesterday the same query again took 15-20 minutes for execution. I checked that even for this execution the query used the same SQL profile but "this time" with different plan_hash_value - say PHV2.

Today again the query executed in less than a minute and used the plan_hash_value as PHV1.

select distinct plan_hash_value,timestamp from dba_hist_sql_plan where sql_id='mysqlid' order by 1,2;

PLAN_HASH_VALUE TIMESTAMP
--------------- --------------------
890360113 20-feb-2013 16:38:39
3736413466 04-mar-2013 08:12:52
1237282258 03-jan-2013 17:15:02

I confirmed from awrsqrpt as well that different plans were used for different plan_hash_values and every time same SQL profile was used

SQL> select name,CATEGORY,SIGNATURE,CREATED,LAST_MODIFIED,TYPE,STATUS,FORCE_MATCHING from dba_sql_profiles;

NAME CATEGORY SIGNATURE CREATED LAST_MODIFIED TYPE STATUS FOR
------------------------------ ------------------------------ ---------- -------------------- -------------------- --------- -------- ---
SYS_SQLPROF_015ffffcc3e1c5b000 DEFAULT 1.5512E+19 20-feb-2013 16:30:48 20-feb-2013 16:30:48 MANUAL ENABLED NO

I am unable to understand how execution plan and thus plan_hash_value is changing for the same SQL Profile. I read that SQL Profile (unlike stored outline) keeps up with increasing data volume and may not keep up with changing data distribution.

I checked that values for 4 bind variables out of 81 are different for execution between today and yesterdays' run(queried v$sql_bind_capture based on last_captured)

My questions are
1) does the different plan_hash_values with different execution plans for query using same SQL profile mean the query was hard parsed multiple times and still used the same SQL profile?
2) If that is the case why I never saw child_number = 1 in any of the views for the same sql_id. I tried it repeatedly over last 2 weeks and always found child_number=0 in v$sql (also loaded_versions=1)
3) Does the different values of bind variable are causing this flip-flop of the plans? How can I conclude this?

I have 2 plans with 2 different plan_hash_values. I know which would be better. How can I force the sql to use better plan in the two in this case where I am using Rule Based Optimizer and have SQL profile created If this is not possible then how can I create stored outline from the existing plan (not waiting for subsequent execution to take place).

View 6 Replies View Related

Performance Tuning :: How To Change Execution Plan Of Currently Executing Statement

Feb 8, 2011

refer following sql statements and code

Session 1
create table tab1 as select * from dba_objects where object_id is not null;
alter session set events '10046 trace name context forever, level 12';
declare
x number;
begin
for i in 1..4
loop

[code]....

Session 2

after "starting" the above pl/sql block from Session 1, I keep on querying tab2 from Session 2 And as soon as 2 records are inserted in tab2, I create index from Session 2

select * from tab2;
select * from tab2;
select * from tab2;
N
----------
1
2
create index i on tab1(object_id);

As I have tested from a single session (just before this test) such index is used for the sql statement

select count(1) into x from tab1 where object_id=2331;

However when I checked the trace file I am not geeting results as expected

I am expecting 4 execution plans - 2 FTS and 2 Index Access scans and for this I am issuing following command

tkprof dst1_ora_7369.trc dst1_ora_7369.txt aggregate=no sys=no

But unfortunately I am getting following output

SELECT COUNT(1)
FROM
TAB1 WHERE OBJECT_ID=2331
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 4 0.00 0.00 0 2 0 0

[code]....

1) Why I am unable to see 4 execution plans - 2 with FTS and 2 with Index access when I mentioned 'aggregate=no'?

2) Whether the index i will be used for last 2 iterations after first 2 iterations of FTS?

If answer to above question 2) is 'No'

By which method I can force an ongoing sql statement in loop to take different execution path? Of course I can't hard parse sql in 'that' current session Will flushing Shared pool work in above case?

View 6 Replies View Related

Performance Tuning :: Explain Plan Analysis - Execution Ordering

Jul 21, 2010

I have two Oracle instances that are setup identically.When I run a query on one of them, it takes around 3 seconds, on the other it takes around 200 seconds.

I have looked at the explain plans, and it has shown me what I think is the problem. On one instance, it does a join on two tables, then runs the other filter/access predicates. On the other instance it runs the filter/access predicated first, then does the expensice join. The one that does the join first is the one that takes around 200 seconds. How to tell Oracle to make this join after runnning the other predicates?

View 15 Replies View Related

Performance Tuning :: Same Execution Plan For Create Table Statement When Name Changes?

May 18, 2010

Can we have same execution plan for a create table statement where the name of the table changes every time as follows:

create table test
as
select * from t1

Here table name changes from test to another table name next time

View 6 Replies View Related

Performance Tuning :: Why Elapsed Time Changed While Execution Plan And Stats Remains The Same

Jun 4, 2010

attached query giving consistent execution plan but different timings across run

SELECT /*+ INDEX (CRT CRT_CUN_FK_I)*/
DISTINCT odr.dve_id
FROM company_requirements crt, orders odr, lelo_products la_pct
WHERE crt.qtn_cun_id = 10035637--10000021--10035667
AND crt.ID = odr.crt_id_quote_implemented
AND NVL (odr.cancellation_date, '31-Dec-9999') = '31-Dec-9999'

[code]....

we have 4 databases, 2 on each servers, such that db1 and db2 on server1 and db3 and db4 on server2

refer count of the records for column of biggest table in the query, taken on all 4 databases (The column is nullable)

select count(*) from company_requirements crt WHERE crt.qtn_cun_id = 10035637
db1 = 73335
db2 = 89073
db3 = 81182
db4 = 82936

First I executed the query on db1 and db2 while there wasn't any user logged on to the system

db1
**********
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 17.47 473.39 85704 1508102 0 0

[code]...

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 85704 0.31 460.55
latch free 1 0.00 0.00
SQL*Net message from client 1 14.98 14.98

[code]...

Why the elasped time changed when data and plan hasn't changed at all? Also why the plan has different stats for round 1 and 2 on db1 and db2?

I ran it 2 times each round each database so hard parsing shall not be issue.Also why the number of rows accessed are different in db1,db2 and db3,db4 especially for step1 when count of crt.qtn_cun_id is similar?

In fact when the query was taking long I was the only user on the system Also I used hard coded value (no bind variables at all)

I checked num_rows, distinct keys as well which are quite similar across all 4 databases Also no stats where gather during the query execution

What I should have checked or monitored?

View 10 Replies View Related

Performance Tuning :: How To Tune Order By Clause Without Changing Sort Area

May 1, 2008

How to avoid sort operation by an order by clause without changing the sort area size.what hints or changes should be done in query so that order by clause work faster.

View 10 Replies View Related

Performance Tuning :: Index Usage In Order By Clause On Nullable Column

Jan 28, 2011

I came across situation where a Nullable column is not using index for 'order by' clause. I added Not Null condition in the 'where' condition but it wasn't useful. I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'

So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting

here are the steps

Initially I kept the column Nullable

SQL> create sequence s5;
Sequence created.

SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001;
Table created.

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

SQL> create index i5 on t5(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T5',cascade=>true);
PL/SQL procedure successfully completed.
exit

SQL> alter session set events '10046 trace name context forever, level 12';

select *
from
t5 where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.00 0 16 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 49.49 49.72
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 69 0.00 0.00
SQL*Net message from client 69 22.89 28.04

Now I modified the 'id' column to Not Null

SQL> alter table t5 modify id not null;

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

COLUMN_NAME N
------------------------------ -
ID N
OWNER Y
OBJECT_NAME Y
SUBOBJECT_NAME Y
OBJECT_ID Y
DATA_OBJECT_ID Y
OBJECT_TYPE Y
CREATED Y
LAST_DDL_TIME Y
TIMESTAMP Y
STATUS Y
TEMPORARY Y
GENERATED Y
SECONDARY Y

14 rows selected.

select *
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 29 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.01 0 45 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 37.74 37.91
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 8.28 8.45

select id
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 6 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 6 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us)
1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 1.88 1.89

Questions are

1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'?
2) While we got 'fast full scan' why index wasn't used for 'order by' clause?
3) Do we need the indexed column in where clause for being used in 'order by clause' too?
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?

View 5 Replies View Related

Performance Tuning :: Disable Db_block_buffers Parameters In Order To Enable Sga_target?

Jun 15, 2012

What value should i set for sga_target in my oracle 10g database?

Currently -
sga_max_size = 32GB
pga_aggregate_target = 6GB
RAM on server = 64 GB

I'll need to disable db_block_buffers parameters in order to enable sga_target. right?

View 8 Replies View Related

PL/SQL :: Execution Order In Case Of Row Num

Jul 26, 2012

I just wanted to know how the query will be executed in case of where clause and ROWNUM clause. e.g, consider below query.

SELECT * FROM emp
WHERE dept IN (20, 30, 21)
AND salary < 10000
AND rownum <2;

My question is that when rownum will be executed?

1. First all the data according to where clause will be fetched and rownum is assigned and then rownum will be executed on that data.
2. First complete data will be fetched, ROWNUM will be applied and then WHERE clause will be executed along with ROWNUM clause.

View 8 Replies View Related

Application Express :: Execution Order In 4.2.1.00.08

Feb 14, 2013

If I have Before header Application level process (with id=5) and page Before header process (with id=5), which one will execute first? Is there any relation based on id or there is other logic? I want to understand general order of execution in the same area without manual experimenting and debugging data in both process?

Question is related for Application Express 4.2.1.00.08.

View 1 Replies View Related

Forms :: Trigger Execution Order

May 1, 2010

my question is ,

i have three trigger one at form level,second on database level and third is on library level.

three are same .

which one will fire first,

give me answer.

Changed title to something meaningfull. Next time think about your title for a second rather than just putting oracle.

View 7 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

I have below tables,

describe rpthead

Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER

describe rptbody

Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE

The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)

I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.

View 5 Replies View Related

Tuning With Order By Condition

Sep 9, 2010

I have a query with order by clause, which takes 30 sec to execute with order by clause. And if i remove the order by clause it executes within 1 sec.

The column in the order by condition has index. but when i see Explain plan output. it doesn't show this index is being used. I tried to execute query with INDEX hint but still explain plan is not showing this index.

View 4 Replies View Related

PL/SQL :: Obtaining Execution Plan Of Currently Executing Plan In 9i

Jun 15, 2012

the most accurate/efficient way of obtaining the execution plan for a piece of running SQL in Oracle 9i. in 10g and 11g obviously dbms_xplan.display_cursor(sql_id) can be used,

How can this be achieved in 9i, currently I am simply obtaining the SQL_TEXT and then running an explain plan ("EXPLAIN PLAN FOR..") - I believe this is not necessarily the same explain plan that will be used for the sql that is executing though

View 7 Replies View Related

Performance Tuning :: Privilege To Run Explain Plan

May 24, 2012

what privilege is require for a user to execute explain plan? I get below error while try to execute explain plan.

SQL> explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000;
explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000
*
ERROR at line 1:
ORA-01031: insufficient privileges

View 9 Replies View Related

Performance Tuning :: Same Query With Different Explain Plan In Two Database?

Aug 17, 2012

I am facing a weird situation wherein the explain plan of same sql in SIT and PROD is different.In fact the explain plan is very costly in Prod.Also the DB version of both SIT and PROD is same.

Below is the sql and corresponding explain plan in Prod and SIT respectively.

Query:
SELECT seq,CCN,ProcessorPart,root_item,comp_path,Item,comp_item,comp_item_type,
lag(comp_item_type,1,'PART') over(PARTITION BY seq ORDER BY lvl)Nxt_comp_item_type,lvl,bom_qty,
ROUND(CASE min(abs(bom_qty)) OVER (PARTITION BY seq ORDER BY lvl)
WHEN 0 THEN 0 ELSE 1 END * EXP (SUM (LN (nullif(abs(bom_qty),0))) OVER (PARTITION BY seq ORDER BY lvl))) Ulti_qty,
'AMER'

[code]...

The tables referred in above query is small tables containing arnd 10k records.The above tables are partitioned on Region and not indexed.

Explain Plan in Prod: COST CARDINALITY BYTES

SELECT STATEMENT, GOAL = ALL_ROWS165173613539322883634804
SORT UNIQUE236360
UNION-ALL
PARTITION LIST SINGLE117240

[code]...

Explain Plan in SIT: COST CARDINALITY BYTES

SELECT STATEMENT, GOAL = ALL_ROWS3211689
SORT UNIQUE347240
UNION-ALL
PARTITION LIST SINGLE172120

[code]...

I am not able to attribute why there is a huge change in Cost between SIT and Prod.Apparently the Job is going for 3-5 hours which used to get completed within 20mins in SIT.

View 5 Replies View Related

Performance Tuning :: Explain Plan - %CPU Seems To Be Worse For JOIN Near Top?

Oct 24, 2011

however I was able to identify a poorly performing query that seemed to be maxing out our CPU. I have been trying to understand the Explain Plan. The plan below is from our test system which has considerably less information in the tables than our PROD system.

I can see there are a bunch of table scans at the end which may indicate missing indexes, but I am unclear on whether this is actually a problem as the %CPU seems to be worse for the JOIN near the top of the plan.

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1870M| 3018G| | 677M (1)|999:59:59 | | |
| 1 | SORT ORDER BY | | 1870M| 3018G| 3567G| 677M (1)|999:59:59 | | |

[code]...

View 4 Replies View Related

Performance Tuning :: XML / XPath Rewrites And Plan Baselines?

Oct 29, 2013

The types of query I refer to in the title are of this pseudo-code ilk:

select t.column_value
from table1 o, xmltable('for $co in $data
where $co/path1=$bind1
and $co/path2=$bind2
passing o.field as "data", :b1 as "bind1", :b2 as "bind2") t
where o.field = :b3

They're querying a table with a (binary) xmltype with a path/domain index over this column.As those who have had the (mis)fortune to run into these will know, the queries are extensively rewritten under the covers to access to xml via the paths supplied.

getting a baseline to work with queries like this? I was suspicious because whilst I can hint it to pick a certain access path first (leading()), the plan hashes remain the same.

I'm not sure, however, if I'm simply "doing it wrong" or it is just not possible with the level of recursive rewriting going on.NB: I consider myself reasonably competent in applying baselines to "traditional" queries...

View 7 Replies View Related

Performance Tuning :: Change In Explain Plan Due To Oracle Upgrade

Dec 14, 2010

I need to warn readers that I am not a DBA but am heavily involved in application development. Whatever I know about database tuning is whatever I've managed to pick up via self-learning, and I must admit that the sum total of my knowledge isn't a lot.

Anyway, our "DBAs" recently did an upgrade to our 10g database, going from version 10.2.0.2.0 to 10.2.0.4.0. Immediately after the upgrade, a particular query has started to under-perform. The query itself was not altered in any way during the upgrade.

We have two explain plans for the query, a before and an after plan. The two plans are similar but not identical. The plans are too massive to post here, so I hope the following synopsis of the differences will do.

The 10.2.0.2.0 plan:

shows a HASH GROUP BY
has a TempSpc column in the explain plan
shows a particular table (EMP_HISTORY) as having ~1700 rows

The 10.2.0.4.0 plan:

shows SORT GROUP BY instead of HASH GROUP BY does not show a TempSpc column in the explain plan shows the EMP_HISTORY table as having only 25 rows

Other than these points, no other discernible differences can be noted. I'm wondering what would cause HASH to change to SORT. I'm told that stats are up-to-date.

View 5 Replies View Related

Performance Tuning :: Run Explain Plan Syntax - Show Error?

Aug 3, 2010

when i runnung the explain plan syntax , show error :
running --- SELECT * FROM TABLE(dbms_xplan.display) ;
ERROR: an uncaught error in function display has happened;
please contact Oracle support
Please provide also a DMP file of the used plan table
PLAN_TABLE
ORA-00904: "OTHER_TAG": 無效的 ID

View 4 Replies View Related

Performance Tuning :: Does High Volume Of Data Can Change Plan

Jun 4, 2010

The prod stats has been implemented in development. The stats has been gathered 2 months back on dev while in production the stats has been gathered 2 weeks back.

My question shouldn't the high volume of data causes changes in plan in both the environment? My thinking is that plan can be different as the high volume of data are changing in prod it may lead to a different plan.

View 6 Replies View Related

Performance Tuning :: Difficulty In Using Bind Variable To Check Explain Plan

Dec 12, 2011

since the optimizer (during explain plan) assumes all bind variable to be of varchar type, while checking plan for SQL statement using bind variable of numeric and date type shall we convert (typecast) it as following?

variable n_sal number
variable dt_joining date
exec n_sal:= 1000
exec dt_joining := '12-dec-2005'
select first_name from emp_data where sal=to_number(n_sal) and joining=to_date(dt_joining);

View 12 Replies View Related

Performance Tuning :: Syntax To Pass Hint To Emulate Good Attached Plan?

Aug 16, 2012

I have an APP that truncates tables and loads data, which in turn makes the stats stale. I ran the query advisor (see attachment) and of course it ecommends running stats or accept a profile.I really don't want to do that as it may cause a load on my DB.

In turn, I would like to consider having my APP team change the query to pass a hint to use the best query plan.syntax to pass the hint to emulate good attached plan? Or is this a bad way to proceed?

select /* INDEX FAST FULL SCAN PK_PLACEMENT_REQUEST_QUEUE */
sum(lastshares) as "ROSEN"
from nyeo.fix_exec_reports fer, nyeo.placement_request_queue q,
nyeo.nyeo_block_control bc
where fer.clordid = q.sequence_number
and q.blockid = bc.blockid
and upper(bc.deskname) like '%ROSEN%'

View 2 Replies View Related