Skip to main content

Explain Plan Vs Execution Plan

Explain Plan

DELETE PLAN_TABLE
EXPLAIN PLAN FOR
SELECT COUNT(*)   FROM ARADMIN80.T3293 WHERE ((((T3293.C230000009 = 'MAINCHANGE') AND 
((T3293.C301813800 = '20001-Test') OR (T3293.C1000000337 = '20001-Test')) AND (T3293.C1000003561 >= 1) AND (T3293.C1000003561 <= 9)) 
OR ((T3293.C230000009 = 'MAINHELPDESK') AND (T3293.C301813800 = '20001-Test') AND (T3293.C1000003561 >= 0) AND (T3293.C1000003561 <= 3)) 
OR ((T3293.C230000009 = 'MAINWORKORDER') AND (T3293.C301813800 = '20001-Test') AND ((T3293.C1000003561 = 0) OR (T3293.C1000003561 = 1) 
OR (T3293.C1000003561 = 2) OR (T3293.C1000003561 = 3) OR (T3293.C1000003561 = 4))) OR ((T3293.C230000009 = 'MAINKNOWLEDGEDATABASE') 
AND (T3293.C301813800 = '20001-Test') AND (T3293.C1000003561 >= 4) AND (T3293.C1000003561 <= 4)) OR ((T3293.C230000009 = 'MAINKNOWNERROR') 
AND (T3293.C301813800 = '20001-Test') AND (T3293.C1000003561 >= 0) AND (T3293.C1000003561 <= 2)) OR ((T3293.C230000009 = 'MAINPROBLEM') 
AND (T3293.C301813800 = '20001-Test') AND (T3293.C1000003561 >= 0) AND (T3293.C1000003561 <= 5))) AND (EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '1000000010;-1098;20043;13007;20042;13006;13005;-1099;13009;13008;13010;20315;20313;20214;20013;20012;20078;20055;20077;0;20218;20316;440;1000000200;1058;20032;803;804;7113;20007;20403;20302;-20000;20224;20003;20223;20221;20000;1000000006;1057;''20001-Test'';1000000201;1000000009;1000000207;500500723;1000000008;1000000007' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3293.C60901, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3293.C60901, '[^;]+')) + 1) ) OR EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '1000000010;-1098;20043;13007;20042;13006;13005;-1099;13009;13008;13010;20315;20313;20214;20013;20012;20078;20055;20077;0;20218;20316;440;1000000200;1058;20032;803;804;7113;20007;20403;20302;-20000;20224;20003;20223;20221;20000;1000000006;1057;''20001-Test'';1000000201;1000000009;1000000207;500500723;1000000008;1000000007' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3293.C112, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3293.C112, '[^;]+')) + 1) ) OR EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '1000000010;-1098;20043;13007;20042;13006;13005;-1099;13009;13008;13010;20315;20313;20214;20013;20012;20078;20055;20077;0;20218;20316;440;1000000200;1058;20032;803;804;7113;20007;20403;20302;-20000;20224;20003;20223;20221;20000;1000000006;1057;''20001-Test'';1000000201;1000000009;1000000207;500500723;1000000008;1000000007' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3293.C60900, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3293.C60900, '[^;]+')) + 1) ) OR EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '1000000010;-1098;20043;13007;20042;13006;13005;-1099;13009;13008;13010;20315;20313;20214;20013;20012;20078;20055;20077;0;20218;20316;440;1000000200;1058;20032;803;804;7113;20007;20403;20302;-20000;20224;20003;20223;20221;20000;1000000006;1057;''20001-Test'';1000000201;1000000009;1000000207;500500723;1000000008;1000000007' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs 
WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3293.C60989, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3293.C60989, '[^;]+')) + 1) )))

 select PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())

 select Count(DISTINCT C2) from ARADMIN80.T3143;
select Count(DISTINCT C230000009 || C7) from ARADMIN80.T3143;
select Count(DISTINCT C230000009 || C7) from ARADMIN80.T2310;
select C7 from ARADMIN80.T3143;
select C7 from ARADMIN80.T2310;
SELECT C7,count(*) from T2310 GROUP by C7
SELECT COUNT(*) from T2310
Select count(*) from ARADMIN80.T3143
select a.schemaid, a.fieldname,b.name from field a, arschema b where a.fieldid=230000009 and a.schemaid=b.schemaid and a.schemaid in ( 2310,3143) order by a.schemaid
select a.schemaid, a.fieldname,b.name from field a, arschema b where a.fieldid=1000000337 and a.schemaid=b.schemaid and a.schemaid in ( 2310,3143) order by a.schemaid
,Count(DISTNCT C230000009)
alter session set CURRENT_SCHEMA=ARADMIN80

 select count ( DISTINCT C2 || C7 ) FROM T2310
CREATE INDEX I2310_Test ON T2310 ( C2,C7) COMPUTE STATISTICS 
CREATE INDEX I2144_Test ON T2144 (C1000000337, C2 ,C7 ) COMPUTE STATISTICS 
CREATE INDEX I2389_Test ON T2389 ( C2,C7) COMPUTE STATISTICS 
CREATE INDEX I3143_Test ON T3143 ( C2,C7) COMPUTE STATISTICS 

 DROP INDEX I2144_Test
DROP INDEX I2310_Test

 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Execution Plan
SELECT COUNT(*) /*+ gather_plan_statistics  Vippl*/   FROM ARADMIN80.T3293 WHERE ((((T3293.C230000009 = N'MAINCHANGE') AND 
((T3293.C301813800 = N'20001-Test') OR (T3293.C1000000337 = N'20001-Test')) AND (T3293.C1000003561 >= 1) AND (T3293.C1000003561 <= 9)) 
OR ((T3293.C230000009 = N'MAINHELPDESK') AND (T3293.C301813800 = N'20001-Test') AND (T3293.C1000003561 >= 0) AND (T3293.C1000003561 <= 3)) 
OR ((T3293.C230000009 = N'MAINWORKORDER') AND (T3293.C301813800 = N'20001-Test') AND ((T3293.C1000003561 = 0) OR (T3293.C1000003561 = 1) 
OR (T3293.C1000003561 = 2) OR (T3293.C1000003561 = 3) OR (T3293.C1000003561 = 4))) OR ((T3293.C230000009 = N'MAINKNOWLEDGEDATABASE') 
AND (T3293.C301813800 = N'20001-Test') AND (T3293.C1000003561 >= 4) AND (T3293.C1000003561 <= 4)) OR ((T3293.C230000009 = N'MAINKNOWNERROR') 
AND (T3293.C301813800 = N'20001-Test') AND (T3293.C1000003561 >= 0) AND (T3293.C1000003561 <= 2)) OR ((T3293.C230000009 = N'MAINPROBLEM') 
AND (T3293.C301813800 = N'20001-Test') AND (T3293.C1000003561 >= 0) AND (T3293.C1000003561 <= 5))) AND (EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '1000000010;-1098;20043;13007;20042;13006;13005;-1099;13009;13008;13010;20315;20313;20214;20013;20012;20078;20055;20077;0;20218;20316;440;1000000200;1058;20032;803;804;7113;20007;20403;20302;-20000;20224;20003;20223;20221;20000;1000000006;1057;''20001-Test'';1000000201;1000000009;1000000207;500500723;1000000008;1000000007' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3293.C60901, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3293.C60901, '[^;]+')) + 1) ) OR EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '1000000010;-1098;20043;13007;20042;13006;13005;-1099;13009;13008;13010;20315;20313;20214;20013;20012;20078;20055;20077;0;20218;20316;440;1000000200;1058;20032;803;804;7113;20007;20403;20302;-20000;20224;20003;20223;20221;20000;1000000006;1057;''20001-Test'';1000000201;1000000009;1000000207;500500723;1000000008;1000000007' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3293.C112, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3293.C112, '[^;]+')) + 1) ) OR EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '1000000010;-1098;20043;13007;20042;13006;13005;-1099;13009;13008;13010;20315;20313;20214;20013;20012;20078;20055;20077;0;20218;20316;440;1000000200;1058;20032;803;804;7113;20007;20403;20302;-20000;20224;20003;20223;20221;20000;1000000006;1057;''20001-Test'';1000000201;1000000009;1000000207;500500723;1000000008;1000000007' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3293.C60900, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3293.C60900, '[^;]+')) + 1) ) OR EXISTS (SELECT TO_CHAR(item) FROM (SELECT regexp_substr(str, '[^;]+', 1, rownum) item FROM (SELECT '1000000010;-1098;20043;13007;20042;13006;13005;-1099;13009;13008;13010;20315;20313;20214;20013;20012;20078;20055;20077;0;20218;20316;440;1000000200;1058;20032;803;804;7113;20007;20403;20302;-20000;20224;20003;20223;20221;20000;1000000006;1057;''20001-Test'';1000000201;1000000009;1000000207;500500723;1000000008;1000000007' str FROM dual) CONNECT BY level <= length (regexp_replace (str, '[^;]+')) + 1) u_privs 
WHERE item IS NOT NULL INTERSECT ( SELECT TO_CHAR(regexp_substr (T3293.C60989, '[^;]+', 1, rownum)) item FROM dual CONNECT BY level <= length (regexp_replace (T3293.C60989, '[^;]+')) + 1) )))

 select sql_id from v$sql  where SQL_TEXT like '%Vippla%'

 output of above query supply to next

 select * from table(dbms_xplan.display_cursor('0zv29zu8qk8rz', null, 'ALLSTATS'));
cjdr5m7c1h8w2
0zv29zu8qk8rz

Comments

Post a Comment

Popular posts from this blog

Oracle Part

Process Increase due to error sometimes : SQL> connect / as sysdba ERROR: ORA-00020: maximum number of processes (300) exceeded or java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS:no appropriate service handler found Steps to increase the number for processes/sessions at database level in Oracle is as follows   1.  Log on to the database as system administrator  In SQL Plus enter:  " connect / as sysdba "   2. Set the number of processes to be desired value  In SQL Plus  " alter system set processes=  scope=spfile "   3. Unset the number of sessions to remove the default session limit In SQLPlus  " alter system reset sessions scope=spfile sid='*'  "   4. Shutdown the database In SQL Plus  " shutdown immediate "   5. Startup the database  In SQL Plus  " startup "    6. Check the changes have taken effect. In SQL Plus...
Post a Comment
Read more

Ansible - Agentless Architecture DevOps

Ansible  Ansible is an open source IT configuration management, deployment, and orchestration tool. It is unique from other management tools in many respects, aiming to provide large productivity gains to a wide variety of automation challenges. Example From YouTube - Short Videos - Quick to learn about Ansible  :) 
Post a Comment
Read more