Explain Plan Differences With Or Without Nvl On Bind Variables?
Jul 1, 2013
We have recently upgraded application (from Oracle Applications 11.5.9 to 12.1.3) and database (from 9.2.0.5.0 to 11.2.0.3.0).Since we are confronting to performances issues, i try to analyse some queries which Explains plans seems strange (in my opinion).Studying one of them i discover the next case (which according to my logic, i can't explain): --
Just bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and mmt.ORGANIZATION_ID = :p2and mmt.TRANSACTION_REFERENCE = :p3--and mmt.SUBINVENTORY_CODE = :p4 PlanSELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 361 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 5 Bytes: 361 Cardinality: 1 1 INDEX RANGE SCAN INDEX XXSPE.XXSPE_MTL_MATERIAL_TRANSAC_N99 Cost: 3 Cardinality: 2-- Nvl on bind variable --select *from MTL_MATERIAL_TRANSACTIONS mmtwhere 1 = 1and mmt.INVENTORY_ITEM_ID = :p1and
[code]...
View 3 Replies
ADVERTISEMENT
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
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
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
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
Jul 3, 2009
How to find explain plan of a query in oracle using TOAD(any) and SQL Developer(any)?
If there any functional key to find out let me know.
View -1 Replies
View Related
Sep 7, 2011
1. In previous version of Toad (9.7.2) during executing a statement I was able to click on 'Explain plan current statement'. In this version of Toad (10.6.0. 42) is not a possible to do it.In the Toad options I've filled the sign: "use a separate connection when Toad itself is generating transactions"
2. During executing a statement I still see 'clock' cursor.Is it a possible to disable to see it?
View 1 Replies
View Related
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
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
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
Apr 27, 2012
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
View 8 Replies
View Related
May 12, 2011
I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.
SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,
[code]...
View 1 Replies
View Related
Dec 22, 2011
i have some confusion with bind and host variable.
View 4 Replies
View Related
Sep 2, 2011
I have written a small code while going through the PL/SQL guide but I got a message for the BIND VARIABLE. I don't think I have used any bind variable in this code.
<<outer>>
declare
v_sal1 number(7,2) := 60000;
v_comm number(7,2) : v_sal1 * 0.20;
v_message varchar2(2000) := 'eligible for commission';
begin
[code]........
View 8 Replies
View Related
Nov 20, 2012
Create and invoke the GET_JOB function to return a job title.
a.Create and compile a function called GET_JOB to return a job title.
b.Create a VARCHAR2 host variable called b_title, allowing a length of 35 characters. Invoke the function with job ID SA_REP to return the value in the host variable, and then print the host variable to view the result.
This is my FUNCTION:
CREATE OR REPLACE
FUNCTION GET_JOB(
p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
[code]...
This is how I invoked the FUNCTION but WHILE DECLARING THE BIND VARIABLE IT IS GIVING ME AN ERROR!!!!!
VARIABLE b_title VARCHAR2(35)
set serveroutput on
DECLARE
P_JOBID VARCHAR2(10);
v_jobtitle VARCHAR2(200);
[code]...
View 5 Replies
View Related
Jun 6, 2012
I have a requirement where I have to return data as per filter clauses provided on the front end, which may or may not be filled as per the users' choice.
To create a test case, I have created a query joining the emp and dept tables and I add the where clauses dynamically depending on whether the filter has been provided or not.
CREATE OR REPLACE TYPE emp_ot AS OBJECT (
empno NUMBER(4),
ename VARCHAR2(10),
[Code]....
It works very well, the 'literals' are being converted into bind variables (due to CURSOR_SHARING being set to SIMILAR) and the optimizer is able to figure out the correct execution path every time, although it is true that potentially 5 versions of this query will be sitting in the shared pool, but the DBAs are happy to live with that.
Going forward in version 12, CURSOR_SHARING=SIMILAR will be deprecated and the DBAs are not happy for us to write this sort of code anymore.
Is there a suitable way to achieve what this piece of code does?
I have tried and tested this method:
SELECT emp.empno, emp.ename, emp.job, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.empno = NVL(p_empno, emp.empno)
AND emp.ename LIKE NVL(p_ename, emp.ename)||'%'
...
but the query takes far longer to execute in this manner (that is using my real tables).
View 5 Replies
View Related
Mar 10, 2010
How many maximum number of bind variables,can we use in Execute Immediate.
View 2 Replies
View Related
Sep 8, 2011
CODE
Select
Nvl(Sum(DbAmt),0), Nvl(Sum(CrAmt),0)
From FnTrantt A
Where A.GrpCode=:1 And
A.CmpCode=:2 And';
A.DiviCode=:3 And';
A.SubDiVCd=:4 And';
A.FinYear>=''0001'' And';
A.VchDate Between :5 And :6' And
A.GlCode In :7;
[code]....
In The Above mentioned Code, am using bind variables, In variable no. 7 in passing character string ('05124','05125')I am not able to fetch the value,
View 3 Replies
View Related
Aug 29, 2013
i have been looking at a query that uses a wrong plan. db=11.2.0.3 with user bind peeking enabled. this is a simplified version demonstrating the problem:
select egp.bsn,egp.klantnummer as "Persoonsnummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from pr_eigenschappen2 egpwhere(egp.bsn = :b1 or :b2 is null)and rownum < 51 egp.bsn is varchar2(10) and has high selectivity (almost unique), and is btree-indexed. table and index have adequate statistics. when run with b1:=928818 and b2:=928818 (both bound as varchar2(10)) a full table scan+filter is used on pr_eigenschappen2. if the query is changed to select egp.bsn,egp.klantnummer as "Persoons nummer", egp.samengesteldenaam as "Samengesteldenaam", egp.geboortedatum as "Geboortedatum"from pr_eigenschappen2 egpwhere(egp.bsn = :b1 or 928818 is null)
and rownum < 51the index on bsn is used, and the query is not taking 3.9 seconds but 1 millisecond.if i would have a choice, the query would be different. i don't want to talk about the raison d'etre of the query, i would like to know why the optimizer is not using the index in the first case.
View 23 Replies
View Related
Jun 12, 2012
create or replace procedure my_proc(p_user in varchar2) is
l_cursor sys_refcursor;
l_query constant varchar2(1000) :=
'select a'
|| 'from ' || p_user || '.user_table'
|| 'where param0 = :x'
|| 'and param1 = :x'
|| 'and param2 = :x'
[Code]...
Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?
View 6 Replies
View Related
Apr 21, 2009
can i have some real time code piece for bind variables steps and obndrv(...)
View 1 Replies
View Related
Feb 22, 2010
I am trying to create table using bind variable in EXECUTE IMMEDIATE.. I want to know whether oracle allows to create table using bind variable in EXECUTE IMMEDIATE..
Following is the example :
Declare
test_tab varchar2(10) := 'tab_test';
sql_stm varchar2(100);
Begin
sql_stm := 'create table ' || :a || ' (col1 NUMBER)';
dbms_output.put_line(sql_stm);
EXECUTE IMMEDIATE sql_stm
using test_tab;
Exception
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm || ' ' || sqlcode);
End;
After running above block it is giving error : ORA-01008: not all variables bound.
View 10 Replies
View Related
Oct 4, 2012
I am trying to create a collection using bind variables in APEX 4.1. I have the following procedure but all I get is an error in the debug page DOH ORA-20104:
create_collection_from_query_b Error:ORA-01006: bind variable does not exist
My code is
DECLARE
l_names wwv_flow_global.vc_arr2;
l_values wwv_flow_global.vc_arr2;
l_names(1) := 'EXPENSE_FROM';
l_names(2) := 'EXPENSE_TO';
[code]....
View 1 Replies
View Related
May 16, 2010
Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.
The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:
SELECT em.event_title AS event_name,
e.start_date AS start_date,
e.end_date AS end_date
FROM d_event_ml em
inner join d_event e
ON em.event_id = e.event_id
WHERE em.language_id = 46
AND e.end_date >= SYSDATE
AND e.stream_id IN ( :v_x1 )
AND e.event_id IN (SELECT event_id
FROM d_events_seas
[code]....
and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....
View 7 Replies
View Related
Aug 23, 2011
I executed the following PL/SQL block in SqlDeveloper :
VARIABLE max_dept_no NUMBER
DECLARE
v_dept_name VARCHAR2(30) := '&p_dept_name';
v_max NUMBER(4,0);
BEGIN
SELECT MAX(department_id) INTO v_max FROM departments;
:max_dept_no := v_max + 20;
INSERT INTO departments VALUES (:max_dept_no,v_dept_name, NULL,NULL) ;
END;
/
And it gave the error : Quote:Error report:
ORA-01400: cannot insert NULL into ("HR"."DEPARTMENTS"."DEPARTMENT_ID")
ORA-06512: at line 7
01400. 00000 - "cannot insert NULL into (%s)"
The same code when executed in iSqlPlus gave no error.
View 13 Replies
View Related
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
May 17, 2012
I have two Schemas
DMS_NS_STG_1,DMS_NS_STG_2
Both are having same tables but with diffrent data. I want to write a generic code piece for a table to get that diffrences out, it can be for multiple tables. There will be a DB link between two DB.
View 10 Replies
View Related
Jan 1, 2013
1. What's the differences between CAT and TAB?
2. Why doesn't TAB exist in dict?
3. What the appropriate situation for CAT and TAB?
sys@ORCL> desc cat
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
sys@ORCL> desc tab
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
TNAME NOT NULL VARCHAR2(30)
TABTYPE VARCHAR2(7)
CLUSTERID NUMBER
sys@ORCL> select * from dict where table_name='CAT';
TABLE_NAME COMMENTS
---------- -----------------------------------
CAT Synonym for USER_CATALOG
sys@ORCL> select * from dict where table_name='USER_CATALOG';
TABLE_NAME COMMENTS
-------------------- -----------------------------------------------------------------
USER_CATALOG Tables, Views, Synonyms and Sequences owned by the user
View 2 Replies
View Related
Nov 4, 2013
Explain plan for <SQL_STATEMENT>" and ranselect plan_table_output from table(dbms_xplan.display()) afterward.
There's an object "vm_nwvm_1" under the column "name" and I have no ideas where this objects come from. I query the table user_objects and look for the name and I don't see it. Before the I did dbms_stat command on the schema. My performance was very reason able and I didn't see the object "vm_nwvm_1" under the explain plan. I only see it after "dbms_stat".
View 3 Replies
View Related
Nov 16, 2011
what are the main points that these examples are considered cursors? and why are they called explicit and implicit cursor.
explicit
for x in (select * from emp) loop
dbms_output.put_line('emp no: '||x.empno);
end loop;
implicit
select empno
into vEmpno
from empno
where empname = 'SCOTT';
for all we know that these are not clearly defined on the declaration area as cursor.
View 16 Replies
View Related
