SQL & PL/SQL :: Query Is Using More Cost When Executing Using DB Link?

Oct 5, 2010

I am trying to insert data from 9i to 11g using db link with the below query but while doing so the select statement is going for a full table scan even though rowid is used. But when we execute the same select statement without this insert command it is using proper index. Similar issues I am facing for updates.

Query:

INSERT INTO /*+ APPEND */ emp@db_link
select *
from emp where rowid ='AAC2SmAIIAAAHQgAAZ'

View 6 Replies


ADVERTISEMENT

Executing Stored Procedure / Function From Database Link?

Feb 24, 2011

I have 2 databases:

Database A
Database B

In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.

I would like to execute my stored procedures and all associated functions in database A on my data in database B.

Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.

Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...

I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.

View 2 Replies View Related

SQL & PL/SQL :: Finding The Cost Of Query

Sep 2, 2010

I am using oracle 10g, i want to know the cost of query. In the explain plan and by auto trace am able to get some query cost, but i dont know how to calculate the query cost.

How i will know the query is costly and take more time..? But i know about the hash table and nested tables in the explain plan.
Other than this how to calculate the query cost..?

View 5 Replies View Related

Multi Table Query For A Summary Cost?

Aug 24, 2007

I'm wanting to create a query that will give me a summary of parts and labor from work orders. However there are three tables. work_order, parts, labor. Sometimes there will only be parts, sometimes just labor, or sometimes both. Well my query will only return results when they are on both. Sample query is below. Do I need to create a temp table to gather the data or is there a way to do this with a sub query.

select work_order.wono, sum(parts.cost), sum(labor.cost)
from work_order, parts, labor
where
work_order.wono = parts.wono and
work_order.wono = labor.wono
group by work_order.wono;

View 5 Replies View Related

Performance Tuning :: How To Reduce Cost Of Query

Mar 21, 2013

I have Following wjich takes some minutes to executes i want to be tune so this query Executes fast.

Query :

SELECT a.CHDR_EXCH_CD ,TMHR_EXCH_TM_CD,'S' Sec_type,
round(SUM (Decode(csdt_Depo_Typ,'I',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-

[Code]...

Explain Plan Result :

Plan
SELECT STATEMENT ALL_ROWS
Cost: 1,669 Bytes: 67 Cardinality: 1
15 HASH GROUP BY
Bytes: 67 Cardinality: 1
14 CONCATENATION

[Code].....

After i see result , no 4 in explain plan result gives TABLE ACCESS FULL . i want to be indexing on that how to do this..

This table MG_COLL_SCP_DTL have index like this

CREATE UNIQUE INDEX CSDT_PK ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_TRAN_SR_NO, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD,
CSDT_STSC_CD, CSDT_CHDR_CLNT_CD, CSDT_CHDR_CLNT_TM_CD)

[Code]....

How to Reduce cost ???

View 4 Replies View Related

Performance Tuning :: Cost Of Query Is Brought Down But Taking Same Time

Sep 1, 2010

For an query, cost was 16Lakhs and was taking 30min, I brought down the cost to 1.5lakhs, but still it is taking 30min.

There were many outer joins and same table has been Used(FROM clause) 5 times in the query. I have introduced WITH clause, and brought down the cost.

View 7 Replies View Related

Forms :: Executing Query When Display Box Value Changes

Aug 24, 2010

In my form i have 2 datablocks, the first contains only a display item which is populated from a lov when a user presses a button. The second datablock shows a list of items which should be queried dependant on the value of the above LOV. What i thought i could do is on a post-change trigger for the display item is:

go_block('block_name');
do_key('execute_query');

However, i am told i cannot do these in a post-change trigger, how to acheive what I am looking. Would i perhaps have to create my own trigger somehow or is their a simplier way.

View 2 Replies View Related

SQL & PL/SQL :: How To Get Which Query Is Taking Time While Executing

Mar 2, 2013

How to find time log for query or any procedure like start time and end time and total time.

So that I can tune that queries properly.

Also how can we find estimated query running time.?

View -1 Replies View Related

SQL & PL/SQL :: How To Get Count On Executing Dynamic Query

Sep 16, 2010

l_query := 'SELECT sedol ' ||
'FROM integration.tmp_attributed_sedol ' ||
'WHERE ' || p_field || '=''' || p_ref_number || ''' ';

by using the execute immediate or any other command, how can i check whether the query returned any rows or not?

View 2 Replies View Related

Spatial :: Error While Executing Oracle Query From C#

Apr 18, 2013

I am trying to execute an inline spatial query from c# using ODP.NET. This is a select query and I am passing 2 parameters to this query, one is string and other one is SDO_GEOMETRY. I am able to send SDO_Geometry parameter from c# using Oracle UDT custom designed classes.

Query is working as intended but occasionally giving the following errors:

ORA-29902: error in executing ODCIIndexStart() routine
ORA-13033: Invalid data in the SDO_ELEM_INFO_ARRAY in SDO_GEOMETRY object
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333
ORA-29902: error in executing ODCIIndexStart() routine
ORA-13031: Invalid Gtype in the SDO_GEOMETRY object for point object
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333

I could not figure out the cause of this issue (as it is not failing frequently). Am i getting this issue with the actual query or with Oracle UDT conversion.

View 5 Replies View Related

Performance Tuning :: Removing Nvl Function Then Query Executing In 2 Min Condition

Aug 10, 2011

when am trying to use nvl for one condition it is taking lot of time to execute but when am removing nvl function then the query executing in 2 min. condition is given below

(HOI2.ORG_INFORMATION1)=nvl(TO_CHAR(:p_set_of_books_id) , HOI2.ORG_INFORMATION1)

but when am using the same condition as below the querry executing in 2 min

(HOI2.ORG_INFORMATION1)=TO_CHAR(:p_set_of_books_id)

my query given below

(SELECT cust.customer_number cust_no, cust.customer_name customer,
cnv.item_no, SUM(wd.shipped_quantity) shp_qty_nos,
0 rtn_qty_nos,
ROUND(SUM(cnv.cnf * wd.shipped_quantity), 3) shp_qty_tons,
0 rtn_qty_tons, 0 net_shp_qty_nos, 0 net_shp_qty_tons
[code]...

View 30 Replies View Related

PL/SQL :: Incorrect Query Result Over Database Link

Jul 2, 2012

My problem is I have 3 tables (TEST_TBL1, TEST_TBL2, TEST_TBL3). TEST_TBL2 and TEST_TBL3 are in remote database and I use database link to join them. The following query returns incorrect result (I seems that it ignore the where clause)

SELECT * FROM TEST_TBL1 JOIN TEST_TBL2@db_remote USING (KEY1) JOIN TEST_TBL3@db_remote USING (KEY2) WHERE KEY1=XXX OR KEY2=YYY;

I am on 11R1 (11.1.0.7)

FOR EXAMPLE:

Local database:
CREATE TABLE TEST_TBL1
(
KEY1 NUMBER(5) NOT NULL,

[Code].....

View 8 Replies View Related

SQL & PL/SQL :: Query Over Database Link Ambiguous Column Error Not Raised

Apr 1, 2011

why this query is not raising an error:

select memberid, mi.typeid, emailaddress
from memberdata@prod_db m
join memberitems@prod_db mi on m.memberid = mi.memberid;

These are the tables:

SQL> desc memberdata@prod_db
Name Null? Type
----------------------------------------- -------- ----------------------------
MEMBERID NUMBER
EMAILADDRESS VARCHAR2(50)
ADDRESS1 VARCHAR2(50)

SQL> desc memberitems
Name Null? Type
----------------------------------------- -------- ----------------------------
MEMBERID NUMBER
ITEMID NUMBER

Shouldn't we raise an 'ambiguous column' error on memberid?

View 9 Replies View Related

SQL & PL/SQL :: Units Of Cost?

Sep 28, 2010

I was questioned in an interview what is the unit of cost and based on what was it calculated.

View 1 Replies View Related

Application Express :: Separate Link For Each Series On Multiple Series Bar Chart From One Query

May 13, 2013

I have a query that selects multiple series for a bar chart (I have defined only one chart series, and have 3 columns selected to represent 3 series). The series display on Bar Chart is fine. Now I want to create a separate link target depending on which series is clicked on the chart. I am not able to do this.

View 1 Replies View Related

Application Express :: Column Link Where Column Number Changes With Query

Feb 11, 2013

We are using Oracle 11g with Apex 3.2 on AIX. We are reporting data from customer satisfaction surveys. I'm using the following sql to create my report

<code>select * from(
select month,'Overall Satisfaction' as q_group, 1 as srt,Overall Satisfaction,site,
case when count(*) < 31 then '*' else
round((sum(ttos)/count(*))*100,0)||'/'|round((sum(bfos)/count(*))*100,0)||'/'||count(*) end ospct
from v_XXX_report a,(select distinct month_dt month from v_XXX_report) b
where Overall_Satisfaction is not null and year_dt = 2012

[Code]....

The problem is that site is not allways present and sometimes I have other variables in addition to site. This creates a situation where the month columns will not allways appear at the same column number. For example, When I run this query as is then the "JAN" column is Col3 (first column is a break, col2 is not shown). When I run this query without site then "JAN" is the second column. I would like to create column links for the "JAN" - "DEC" columns but not for any other columns.

Another issue - in the column link creation screen I can create up to 3 variables that I can pass to the next page. Since my query is a pivot I'm uncertain how to pass the column heading or the row value (for col2)

ie
Overall Satisfaction JAN FEB MAR APR MAY ...
Overall Satisfaction 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
Recommend 12/12/200 12/12/210 12/12/220 12/12/230 12/12/240...
etc.

So if I clicked on the values at Recommend:FEB how can I get "Recommend" and "FEB" into variables that I can use on the next page? I've tried #column_name#, #month#, #q_name# and #APEX_APPLICATION.G_F10# but no luck.

View 0 Replies View Related

SQL & PL/SQL :: Sum (cost) Basing On ID Entered

Oct 1, 2012

my data :

id cost
aa_aa 100
ab_aa 100
bb_bb 100
ba_bb 100
ab_aa 100

I need to get my data as
if id like 'aa_%' then it should sum cost for ids for aa_aa and ab_aa and print only 1 row with putput as

id cost
aa_aa 300
If id is like 'ab_%' then it should sum up
ids only for ab_aa..
Output as:
id cost
ab_aa 200

View 7 Replies View Related

PL/SQL :: Lag Function Cost Almost All Of CPU Resource

May 24, 2013

select *from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

prepare data:
CREATE TABLE a AS SELECT LEVEL ID ,decode(mod(LEVEL,10),1,'CODE'||LEVEL) CODE from dual CONNECT BY LEVEL<20001;

sql1:
CREATE TABLE TEST AS
SELECT ID,
NVL(CODE,LAG(CODE ignore nulls) OVER(ORDER BY ID )) CODE
FROM a;

the sql is slow,most of the waits on session is resmgr:cpu quantum and almost all of the cpu resource is occupied

sql2:
CREATE TABLE TEST AS
SELECT ID,
NVL(CODE,last_value(CODE ignore nulls) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) CODE
FROM a;

this sql is fast

How lag function is implemented if the offset is not deterministic?

View 10 Replies View Related

Performance Tuning :: Low Cost And High Cpu?

Jul 23, 2010

I am facing one performance issue, in which the query cost is very low compare to cpu cost and as a result the cpu always show the high graph.I am also attaching the gv$sql and gv$sql_plan data of this query.

This is the query:

SELECT PTLS.ITEMTYPE , PTLS.ITEMID , PTLS.STAGEID, TS.USERID, SUM(PREVIOUSHOURS) AS PREVIOUSHOURS, MIN(STARTDATE) AS STARTDATE, MAX(STARTDATE) AS ENDDATE FROM PROJECTTIMELOGSSTAGE PTLS, PROJECTTIMESHEETITEM PTSI, TIMESHEET TS WHERE PTLS.PROJECTID = :B2 AND TS.TIMESHEETID = PTSI.TIMESHEETID AND TS.USERID = :B1 AND PTSI.TIMESHEETID = PTLS.TIMESHEETID AND PTSI.ITEMTYPE = PTLS.ITEMTYPE AND PTSI.ITEMID = PTLS.ITEMID AND (PTSI.ISPWFITEM = 'N' OR PTSI.ISPWFITEM IS NULL) AND PTLS.ITEMTYPE NOT IN ('OtherTsk','NewTsk','Loc','Glb') AND (PTLS.ITEMTYPE, PTLS.ITEMID ) IN (SELECT ITEMTYPE, ITEMID FROM PROJECTTIMELOGSSTAGE PTLS1 WHERE PTLS1.PROJECTID = :B2 AND PTLS1.TIMESHEETID = :B3 ) GROUP BY PTLS.ITEMTYPE, PTLS.ITEMID, PTLS.STAGEID, TS.USERID

View 17 Replies View Related

What Is Oracle Cost-Based Optimizer

Jun 3, 2013

what is oracle Cost-Based Optimizer? Any material easy to follow?

View 3 Replies View Related

SQL & PL/SQL :: Strange Cost - Summary Lower Than Ingredients?

Aug 1, 2012

I've been experiencing strange Oracle behavior from time to time when using "explain plan". what could be the reason? (I'm going to create an Oracle ticket when I could find the cause...)

The total cost of a query was low (ex. ~500) while sub-parts of the query has high cost (ex. ~10000).I don't want to provide the query itself as the issue is observed for huge queries (like 2M characters!).The execution plan looks like this:

+- SELECT STATEMENT , ALL_ROWS, Cost 736
|
+--+- WINDOW SORT , Cost 736
|

[code]...

View 5 Replies View Related

Performance Tuning :: Difference Between Cost And Bytes

Aug 4, 2010

I am working on Tuning a Materialized view. I was getting an error saying ORA-12008: error in materialized view refresh path

ORA-12801: error signaled in parallel query server P002, instance hrms-stg-db01:HRSTG1 (1)
ORA-01652: unable to extend temp segment by 16 in tablespace TEMP1.

so i increased the temp size. I am trying out various hints to get it rectified. But i am not sure if i should go for less cost or less bytes.

View 1 Replies View Related

Performance Tuning :: High CPU Cost And Low Optimizer?

Jan 22, 2009

how to reduce the cpu cost for a query at query level.

View 10 Replies View Related

Server Administration :: To Find Out The Database Is Using Cost Based Analyzer

May 23, 2012

How to find out the database is using Cost based analyzer?

Currently database is set to ALL_ROWS.

SQL> show parameter optimizer_mode

NAME TYPE VALUE
------------------------------------ ----------- -----------
optimizer_mode string ALL_ROWS
SQL>
SQL>

View 3 Replies View Related

Performance Tuning :: Cost Calculation For Nested Loop Join

Mar 27, 2012

Following is the query on TPC-H schema.

explain plan for select
count(*)
from
orders,
lineitem
where
o_orderkey= l_orderkey.

The trace 10053 (as shown below) for this query shows nested loop join with Lineitem as outer table and Orders as inner table. It is effectively join on composite index (pk_lineitem) of Lineitem and unique index(Pk_orderkey) of Orders table. The cost calculation formula as given in the book as "outer table cost + cardinality of outer table * inner table cost " fails here. I am not able to understand this.

BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: LINEITEM Alias: LINEITEM
#Rows: 6001215 #Blks: 109048 AvgRowLen: 124.00
Column (#1): L_ORDERKEY(NUMBER)
AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 1 Max: 6000000
[code]....

how the cost has been calculated. This does not follow the traditional nested loop cost formula as mentioned in the book.

View 7 Replies View Related

SQL & PL/SQL :: Executing SP Using Jobs

Sep 3, 2010

how to execute one stored procedure for every 10 minutes using oracle jobs

sp name: abc

using anonymous block with sample code.

View 10 Replies View Related

Executing WHOLE Package (of Procedures) At Once?

Aug 30, 2007

I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??

Its not possible but i could call each procedure from ONE procedure

View 2 Replies View Related

Executing Function From Ksh Script?

Mar 5, 2009

I don't know how to control if a call to a PL function return errors.

I have this ksh:

executeFunction () {
echo "[executeFunction ]-> Phoebus DB started"
sqlplus -s /nolog <<!EOF!
CONNECT $USER/$PWD@$DB
@/sql/ejecutaFuncion.sql
EXIT;
!EOF!
}
executeFunction

And the code of "ejecutaFuncion.sql" is:

executeusers.startProcessing();
EXIT

How i can controle if the "startProcessing" function has any problems from the ksh?

View 1 Replies View Related

Getting Errors While Executing Procedure

Oct 4, 2010

I am getting errors while executing the following block.

create TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
DECLARE
-- TYPE c_Rec as object(a VARCHAR2(1), b NUMBER);
TYPE c_collection IS TABLE OF c_Rec;
l_coll c_collection := c_collection();
BEGIN
[code]........

error

06530. 00000 - "Reference to uninitialized composite"

View 1 Replies View Related

Executing Function In A Package?

Mar 24, 2008

I have created one function in the package.

function : Tier_wh
package : Order_DESC

Function defined as

function TIER_WH( message in out Xorder_desc)
...
...
end;

Xorder_desc is defined as

create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);

how to execute this function which is defined in the package .

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved