DB=10.2.0.5
OS=RHEL 3
Im not sure of this, but seeing different plans for same SQL.
select sql_text from v$sqlarea where sql_id='92mb4z83fg4st'; <---TOP SQL from AWR
SELECT /*+ OPAQUE_TRANSFORM */ "ENDUSERID","LASTLOGINATTEMPTTIMESTAMP","LOGINSOURCECD","LOGINSUCCESSFLG",
"ENDUSERLOGINATTEMPTHISTORYID","VERSION_NUM","CREATEDATE"
FROM "BOMB"."ENDUSERLOGINATTEMPTHISTORY" "ENDUSERLOGINATTEMPTHISTORY";
[code]....
multiple execution plns for the sql_id from AWR,i have following questions
1.which plan is opimizer tcurrently using ?
2.how to make sure optimizer to pick right plans
SQL> select * from table(dbms_xplan.display_awr('fb0p0xv370vmb')) ;
PLAN_TABLE_OUTPUT
SQL_ID fb0p0xv370vmb
[code]....
I am executing the query below:
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,
vendor_data vd2,
vendor_data vd3,
(SELECT rownumber,
[code]....
It is taking different approaches (execution plans) while executing for same set of parameters. Due to which sometimes it executes successfully, but sometimes it fills all TEMP space and get failed. I am pasting both the execution plan (different from expalin plan) below:
I. Successfull Execution Plan:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 65612 (100)| | | |
|* 1 | HASH JOIN | | 1 | 6121 | 65612 (1)| 00:13:08 | | |
[code]....
II. Failed with TEMP space Execution Plan:
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1967 (100)| | | |
|* 1 | FILTER | | | | | | | |
| 2 | SORT GROUP BY | | 1 | 8233 | 1967 (3)| 00:00:24 | | |
|* 3 | HASH JOIN | | 1 | 8233 | 1966 (3)| 00:00:24 | | |
[code]....
We have a query which makes Oracle behave very strangely. It is a straight-forward join between four tables of about 30.000 rows each, with some simple comparisons and some NOT LIKE:s.
When we run this query, it either takes about 1 second or more than 1.000 seconds to run and return the approximately 5.000 rows of the result. If we run the same query over and over again, it fluctuates back and forth between two different execution plans, apparently at random, 3 times out of 4 selecting the 1.000 second version and 1 time out of 4 the 1 second version.
There are no other connections to the database, the schema is not modified, the data is identical, the query is identical, and the response is identical, but the execution time alternates between 1 second and 1.000 seconds.On the same database instance we have another schema which is identical, but with slightly less data, which is used for development. The 1.000 second run times did not happen in that schema, but only in the test system's database.
Therefore we would REALLY like to understand what happens and why, so that we can avoid triggering this in the future. We could try locking the 1 second execution plan, but then we're afraid of doing the same thing wrong again in the future.
Here are the two execution plans that Oracle switches between, more or less at random:
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5455 5455 5455 HASH JOIN (cr=15663 pr=10536 pw=0 time=855673 us cost=82273 size=2707430769293 card=14028138701)
79272 79272 79272 TABLE ACCESS FULL GROUPS (cr=1008 pr=0 pw=0 time=22154 us cost=277 size=10693 card=289)
[code]...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
5455 5455 5455 HASH JOIN (cr=15664 pr=0 pw=0 time=778178696 us cost=30838477 size=741611997206725 card=3842549208325)
375411 375411 375411 TABLE ACCESS FULL GROUP_GROUPS_FLAT (cr=3782 pr=0 pw=0 time=51533 us cost=1029 size=25152738 card=375414)
[code]...
The query:
select g.ucid, a.ucid
from account a, groups g, group_members gm, group_groups_flat ggf
where a.ucid = gm.ucid_member
and gm.ucid_group = ggf.ucid_member
[code]...
And excerpts from the schema:
CREATE TABLE "PDB"."GROUPS"
(
"UCID" VARCHAR2(256 BYTE),
"UNIX_GID" NUMBER(*,0),
[...]
[code]...
When we use the AUTOTRACE / EXPLAIN PLAN we can see the (estimated) best execution plan the Optimizer found for our SQL Command. Is there a way to display all alternate execution plans the Optimizer has considered ?
View 1 Replies View RelatedWhen viewing SQL statements in Top Activity in OEM, I am only able to see the Explain Plan for the most recent statement. Is there a setting to increase the number execution plans kept?
View 1 Replies View RelatedHow do I install the 10G listener on the application server (11i)
View 3 Replies View RelatedWe're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.
I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
what should I do to fix the problem with the NAFBCA schema.
Attached File(s)
NAFBCA.JPG ( 57.49K )
Number of downloads: 6
NAFBCA2.JPG ( 60.36K )
Number of downloads: 4
We're using Oracle 10g for development purposes.I have 2 same schemas with approximately same data.I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thoughtfirst that there may be missing constraints or indexes but all are the same.I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
Lately, there has been a handful of intermittent performance issues and it all boiled down to the fact that Oracle happened to choose a "bad" plan to execute them for a just a set of bind variables.
I understand Oracle must be doing it for a good reason but it just worked for us when we ensured a single plan is always used. We did it either by creating indexes or sometimes out of no other choice forcing a particular plan (manual plan).
Now, as a proactive measure, I am interested in identifying if there are more such cases. I managed to find about 23 such cases by using the query below. I am planning to regularly check the report generated by the below query.
Select Sql_Id,Count(*) From
(Select Distinct Sql_Id,Plan_Hash_Value From Dba_Hist_Sqlstat where plan_hash_value <> 0 )
Group By Sql_Id
having count(*) > 1
But before we really start addressing them I wanted to know if it is correct to consider that it is always a bad thing to happen if Oracle chooses multiple plans? I am not saying all such cases are giving problems but we did see that a handful of such problems boiled down to this case.
I have a weird optimizer behaviour on a 10.2.0.4 db.When i add "+0" or "-0" to a number predicate, the optimizer produces 2 differents plans. I dont see why. Here are the statements:
A -First statement with suboptimal plan :
SELECT /*KO*/ TFXPPRODUCT.PRODUCTID,
TFXPPRODUCT.PRODUCTTYPE,
VFUTFIX.Datech,
TFXPPRODUCT.SHORTLABEL,
VFUTFIX.TAUCPN,
VFUTFIX.Tik,
[code]....
[URL]...
I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.
The problem is that running a query:
select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.
After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...
View DDL:
create or replace view my_view as
select /*+ ORDERED FIRST_ROWS(1) */ pt.party_id
pt.party_id as id_klienta_mdm,
pt.master_reference_no as id_klienta_ref_mdm,
[code]...
we are executing a load activity every day through .NET Application, we taking a time solt for Database to ensure nobody is using at that time.But the AWR reports showing different issues on different days.
View 11 Replies View RelatedWhen i am trying to execute the below in sql. i am getting the error.
create or replace type sum_n as object (
nodes node_d,
constructor function sum_n return self as result,
member procedure do_s (m date,exd varchar)
);
/
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/9 PLS-00201: identifier 'NODE_d' must be declared
I have a trigger that is called from an update on the table, this trigger performs the procedure and this procedure update the Same record in the table That shot the trigger. this situation returns error ORA-0060 - DEADLOCK DETECTED WHILE WAITING FOR RESOURCE. Is there any way that this works?
View 1 Replies View RelatedIn our production environment some SP's are executing longer duration, but when same SP is executed from PLSQL Developer client it is executing vary quickly.
View 3 Replies View RelatedI have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function.
how can i prove it through examples. can i see the explain plan for a procedure and a function or is there any way to prove which one is faster in execution.
We have the following case: an application modifies a table in an Oracle db (10.2.0.3.0).
Unfortunately the update SQL statements from the application always use the condition "where Column1 = 'some given value'" which is wrong (never mind why).
It should be instead "where Column1 = 'some value' and Column2 = 'val for Column2'. The 'val for Column2 will be taken from the very SQL query being issued (we can make the application do an update for Column2 even if the value in it never changes).
So all the update queries from the application look at the moment like that:
"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value'".
We would like to capture them and somehow on the fly modify them to look like that:
"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value' and Column2 = 'val for Column2'".
Can a trigger "before update" do it? For some reason we cannot at the moment ask the vendor to change the hard code of the application so we are looking for a temporary workaround.
I have a very big oracle procedure. Since it's too big and calling many other procedures, I am not able to debug the exceptions thrown. Any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.
View 21 Replies View RelatedI have an understanding that the code written in Forms 6i will be executed by the oracle forms 6i run time on the client machine whether it be a sql/plsql .
e.g. i am using the following query in the procedure( of forms 6i interface ).
insert into emp_remote
select ename from emp@db1;
so where the above query would be executed , on client machine or server named db1.
Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)
I am using Oracle 9i.
I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function. Can i see the time taken by procedure and select query only time.
View 2 Replies View RelatedI want to know that is there any way to know the execution time of a sql query.
View 1 Replies View RelatedWhen the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .
View 5 Replies View RelatedI'm trying to run the following procedure and I'm getting he following message:
Warning: execution completed with warning
PROCEDURE etl_bago Compiled.
The procedure is created, the problem is that when I tried to run it with parameters I'm getting this error:
exec etl_bago ('july19.csv');
Error report:
ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.ETL_BAGO is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
[code]......
Btw, I'm using SQL Developer 1.5.3. And the procedure script is here:
[URL].....
when I am executing the below query getting diffrent count every time and not able to guess what is happening.
SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day
I have a block of code that looks something like this, I'll write it in pseudo code to avoid pasting 100's of lines of
IF <condition> THEN
FOR record in (select query here..)
LOOP
--add data
END LOOP
END IF;
IF <condition> THEN
--do soemthing
END IF;
IF <condiiton> THEN
--do soemthing
END IF;
Now, in the 1st IF statement there is a for loop which basically builds up a tab type array - this takes at least an hour to execute based on the select query which returns 1000's of records.
The strange thing is, that my log files shows that at the time the procedure was executed, the stuff in the 2nd IF statement was executed almost straight away.. is this even possible?
If the loop in the 1st IF statement takes over an hour to finish, how is it possible for the 2nd IF statement stuff to process straight away? My log files show me that the loop in the 1st IF statement was going on for a good hour.. yet the 2nd IF statement was executed straight away.
when we want to execute self join command for displaying employee names and their managers name we use the following query.
Select e1.ename"Employees",e2.ename"Managers" from emp e1,emp e2 where e1.Manager_ID=e2.Employee_ID
how it displays the above information or how it works?
[font="Lucida Console"][/font]
Issue: For this sql statement client is changing the date and this sql is running fine in development and taking time in production.So I created the sql profile and push it ot prodcution so for EFFDT <= '25-APR-2010' it was running fine as plan is same as development .....but then again client changed the EFFDT <= '28-AUG-2010' is changed then plan neglected my sql profile because of hardcoded value and so it has parsed the sql again.
How we can fix this plan ? there application is like that so they are goin to pass the hardoce value like this only.....so they can not use bind variable... they are going to fire the sql from one session can we set on the session level like cursor sharing or some hints to get the development plan *for proper formating see the attached file* Statement :
SELECT
DECODE(SUBSTR(JL.PAYGROUP,1,1),'P','P','T','P','C'), JL.DEPTID_CF, JL.OPERATING_UNIT, JL.FUND_CODE,
JL.CLASS_FLD, JL.PROGRAM_CODE, PC.EMPLID, PC.EMPL_RCD, BUGL.BUSINESS_UNIT, JL.ACCOUNT,
SUM(DECODE(JL.GL_NBR,'REGER',JL.AMOUNT,0)), 'Regular Earnings', SUM(DECODE(JL.GL_NBR,'OTERN',JL.AMOUNT,0)), 'Overtime', SUM(DECODE(JL.GL_NBR,'NRTAL',JL.AMOUNT,0)),
[code]....
I am executing below query, but optimizer generating 2 different plans for the same. I don't want to use sql profiles to fix execution plan.
Query
SELECT R.VENDOR_RECORD_SEQ_NO ,
R.VENDOR_SUBJECT_SEQ_NO ,
NVL(D.RESOLVED_VALUE, D.ORIGINAL_VALUE) VAL,
D.CONTROL_COLUMN_SEQ_NO
[code]....