Performance Tuning :: Getting Alternative To REPLACE Function?

Jun 12, 2010

I have a table with:

1 million rows
average row length 200 bytes
50 columns
and this update statement
UPDATE mytable SET varchar2_4000_column = replace(replace(replace...300 times)

It looks at every row in the table (no WHERE clause) and does these 300 replace operations on this column for each row. Each replace replaces with a null so effectively it is removing strings. Much of the time these strings are not in the column.

This update statement takes 25 minutes and it is 98% CPU and 2% USER_IO time.

I figure that is what is taking all the time since it is a CPU bound statement. if rows in this table are persistent over time then tag rows with a flag to show which ones have already been processed and skip these next time around.

View 2 Replies


ADVERTISEMENT

PL/SQL :: Function Retrieve Output Very Slowly - Alternative Code To Replace With Join?

Aug 6, 2012

In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join

CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS

[code]...

View 2 Replies View Related

Performance Tuning :: Not In Replace With Minus

Sep 15, 2011

I have 2 questions for this SQL Statement for tuning?

select * from a where id not in (select a_id from b)

1- How do i change this query with A MINUS OPERATOR

2- Shall i use a Hint for this query?

Explain plan is:

Execution Plan

--------------------------------------------------------------------------------
Plan hash value: 31652112322
--------------------------------------------------------------------------------
-

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-

| 0 | SELECT STATEMENT | | 299 | 21528 | 9 (12)| 00:00:01
|

|* 1 | HASH JOIN RIGHT ANTI| | 299 | 21528 | 9 (12)| 00:00:01
|

|* 2 | TABLE ACCESS FULL | b | 1 | 23 | 4 (0)| 00:00:01
|

| 3 | TABLE ACCESS FULL | a | 300 | 14700 | 4 (0)| 00:00:01
|
--------------------------------------------------------------------------------
-
How do i optimize it best?

View 2 Replies View Related

Performance Tuning :: Sub Queries With Function Call?

May 24, 2011

I am stuck with a query which is taking a lot of time to execute. Below is the pseudo code of the same:

SELECT TAB_ALIAS1.COL1,TAB_ALIAS1.COL2,TAB_ALIAS1.COL3
FROM TABLE1 TAB_ALIAS1
WHERE TAB_ALIAS1.COL4 = <INPUT PARAMETER1>
AND TRUNC(TAB_ALIAS1.ELAP_TIME) =
(
SELECT MAX(ELAP_TIME)

[code]....

View 6 Replies View Related

Performance Tuning :: Function Based Index Is Not Being Used

Feb 22, 2012

I have a table which has 4M records

This table has a query where one of the condition is
AND STATUS <> 'C'

Now the data is as following

select count(*) record_count, status from new_business group by status;

record_countstatus
4298025C
15N
13Q
122S

I want to know if following index would be useful in this case while the condition in where clause is

"AND STATUS <> 'C'"

create index nb_index_1 on new_business(case when status in('N','Q','S') then 1 else NULL end);
Or
create index nb_index_1 on new_business(case when status ='N' then 'N' when status='Q' then 'Q' when status='S' then 'S' else NULL end);

I tried it on a sample table but the index is simply not picked up even when hinted following are the db level settings

query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

I tried it 'query_rewrite_integrity' with 'trusted' as well .

View 7 Replies View Related

Performance Tuning :: How To Determine Memory Usage For A Function

Jul 24, 2013

determine if a function is worth pinning in memory? I want to come up with a percentage, implying that if the function is already im memory 80%+ of the time then it is not worth it.

View 5 Replies View Related

SQL & PL/SQL :: Alternative Of Soundex Function

Feb 16, 2010

Is there any alternative of Soundex function. I have two table of city. I want to compare city of both table. Now problem is that one city name is 'HANGO'. In table A City is feed with spelling 'HANGO' and other it is spelled 'HANGU' OR 'HUNGU'. Similarly there are many cases. I used Soundex function but it is not working perfectly.

Is there any other way to compare data by sound.

View 1 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

Performance Tuning :: Function - Insert Around 900000 Records In Name_compress Table?

Apr 12, 2013

I have created below function to remove specific words/special characters from string. This function is producing expected result. Using this function i need to insert around 900000 records in name_compress table. Insert is taking around 7 mins, how we can tune this function so that insert will be executed within 1-2 mins.

Function -

CREATE OR REPLACE FUNCTION NAME_FN(IN_STRING1 VARCHAR2)
RETURN VARCHAR2 IS
V_OUTPUT VARCHAR2(300);
V_OUTPUT1 VARCHAR2(300);
V_OUTPUT2 VARCHAR2(300);
V_OUTPUT3 VARCHAR2(300);

[code]...

View 11 Replies View Related

SQL & PL/SQL :: Alternative To Nested Group By For Performance Enhancement?

Dec 1, 2011

I need consultation on an alternative to a query that I wrote and seems very expensive.

I have the following table:

CREATE TABLE TRY_B
(TID NUMBER PRIMARY KEY,
INFO1 VARCHAR2(10),
INFO2 VARCHAR2(10),
GROUP_NO NUMBER,
TYPEID NUMBER);

[code]....

My objective is to get the data of group_no that is repeated more than once and which includes exactly one occurrence of typeid = 0.

My current query is:

SELECT * FROM TRY_B
WHERE GROUP_NO IN
(
SELECT GROUP_NO
FROM TRY_B
GROUP BY GROUP_NO

[code]....

which works but takes very long time with the real data as my real table is huge.

View 6 Replies View Related

SQL & PL/SQL :: INSTR With Replace Function

Aug 27, 2013

I am trying this function

instr(','||replace(r_code,' ') ' ' || ', ' , ',' ||r_code || ' , ' )=0

i want to hardcode 'a1', 'a2' from r_code i dont want records from a1, a2

View 1 Replies View Related

SQL & PL/SQL :: Update Using Replace Function?

May 5, 2011

I have a table called email that contains a field called email

I have a few records in the table that contain example@hotmial.com and want to change the hotmial to hotmail

I also have some that are line example@hotmail.co and I want to change those to hotmail.com

so - I want to do a substring search and replace.

can the replace do this for me in one select statement - how do I structure the sql to do this?

View 19 Replies View Related

Function To Replace All Occurrences Of A String?

Jan 9, 2007

Years ago, someone created a database in Oracle that was carried over and now sits in Oracle 10g. I am developing an application that queries this database and returns the result as XML.

Many (thousands) of rows have an item description that contains an ampersand. I want each of these pieces of data to have it written as & amp; (had to add a space so it would show up here, but you know what I mean) instead of &, but I don't feel like doing thousands of UPDATEs to change this.

Does Oracle have any global find/replace functions that I can call? I'd rather do one update statement that replaces all occurances of & with & amp; but I can't seem to find a function that will do this.

I'm thinking something that would work like:

UPDATE table SET column1=REPLACE(column1,oldstr,newstr);

View 4 Replies View Related

PL/SQL :: Difference Between Translate And Replace Function

Jan 6, 2013

Explain with the best example for difference between translate and replace function.

View 2 Replies View Related

SQL & PL/SQL :: Why Replace Function Is Not Replacing - ASCII Value Being Zero

May 16, 2013

why the REPLACE function is not replacing. I assume it has something to do with the ASCII value being zero.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST

SQL>
SQL> SELECT str,
2 e9,
3 REPLACE(str, '%E9', e9) replace,
4 regexp_replace(str, '%E9', e9) regexp_replace,
5 utl_url.unescape(str, 'UTF8') utl_url,
6 ascii(e9) ascii
7 FROM (SELECT 'Soir%E9e' str,
8 chr(to_number('E9', 'xx')) e9
9 FROM dual);

STR E9 REPLACE REGEXP_REPLACE UTL_URL ASCII
-------- --- ------- -------------- ------- -----
Soir%E9e é Soire Soirée Soirée 0

View 5 Replies View Related

PL/SQL :: Can't Compile And Execute Function (Create Or Replace)

Jun 6, 2013

I cant compile & execute this function.

create or replace
FUNCTION get_branding_testing
RETURN r_brand
IS
BEGIN
CURSOR c1
IS
SELECT b.branding_code, c.name_desc     
[code]....

View 6 Replies View Related

Performance Tuning :: Tools For Database Tuning And Instance Tuning

Jul 12, 2010

Looking to understand the difference between instance tuning and database tuning.

What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.

However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?

View 1 Replies View Related

SQL & PL/SQL :: Replace The Like Operator To Increase The Performance

Jul 16, 2012

How to replace the like operator for increase the performance. Because it is taking more time and not using the index.

SELECT *
FROM emp
WHERE ename like '%AL';

View 30 Replies View Related

Server Utilities :: SQL Loader And Replace Function Don't Work

Apr 14, 2011

I have the next SQL Loader file, and it is giving me some headches because the Replace instruction is not working. It does not replace the ' ' by NULL.

load data
CHARACTERSET UTF8
infile TABLE1.unl
BADFILE 'TABLE1.bad'
DISCARDFILE 'TABLE1.dsc'
insert into table GSCIS.table1
fields terminated by "|"
TRAILING NULLCOLS(
codl1 CHAR "REPLACE(:codl1, ' ', NULL)"
,col2 CHAR "REPLACE(:col2, ' ', NULL)"
,col3 INTEGER EXTERNAL
,col3 INTEGER EXTERNAL "DECODE(:col3, NULL, 0, :col3)"
)

View -1 Replies View Related

Performance Tuning :: Merge Statement Tuning For 100M Records In Table?

Oct 31, 2011

I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.

SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,

[code].....

View 39 Replies View Related

Performance Tuning :: How Length Of Column Width Effects Index Performance

Sep 30, 2010

How the length of column width effects index performance?

For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)

Table key consist of(id, job, time)

Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).

What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.

View 24 Replies View Related

Performance Tuning :: Fragmentation Can Reduce Performance In Query Times

Jun 16, 2010

I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..

I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.

Any useful script or query to do this, any interesting oracle system view?

View 2 Replies View Related

Performance Tuning :: Method Of Tuning Database - Row Reduction?

Oct 20, 2010

There is a simple way to increase the performance of a query by reducing the row-size of the table it hits. I used it in the past by dividing the table into smaller parts and querying respective smaller table in each query.

what is this method called ? just forgot the method and can't recall it. what this type of row-reduction optimization is called ?

View 6 Replies View Related

Performance Tuning :: Performance Standard Edition Without Partitioning?

Jun 16, 2011

How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?

300 Millions in only one table with 500K transactions / day is too much?

Simple database with simple schema.

How many records begin to be too many?

View 2 Replies View Related

Performance Tuning :: Procedure Performance On New Database Import?

Nov 15, 2010

Testing our 9i to 11g upgrade, we've imported the entire DB into the new machine.We've found that certain procedures are really suffering performance problems. BUT, we've also found, that if we check out a production copy of the procedure from our source code control, and reinstall it, the performance issue goes away. Just alter the procedure and recompiling does NOT work.

The new machine where the 11g database exists is slightly different than the source, but it's not like we have this problem with every procedure. It's only a couple.

any possible reason that we'd have to re-install a procedure to correct a performance problem?

View 13 Replies View Related

Performance Tuning :: Checking Delete Performance In Package

Apr 12, 2013

I need to check the package performance and need to improve the package performance.

1. how to check the package performance(each and every statement in the package)?
2. In the package using the delete statement to delete all records and observed that delete is taking long time to delete all the records in the table(Table records 7000000). This table is like staging table.Daily need to clean the data before inserting the data into it. what can I use instead of Delete.

View 13 Replies View Related

Performance Tuning :: Query Performance Gain Using Statistics?

Aug 9, 2010

Somewhere I read that we should not use hints in Oracle production environments, but we can use hints in the development environment and on achieving the desired execution plan we can adjust the 'statistics' to follow that plan without hints.

Q1. If it is true what statistics do we adjust for influencing the execution plan and how?

For example, I have the following simple query:

select e.empid, e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;

emp.empid, emp.deptno and dep.deptno columns have indexes and the tables have the standard structure as found in the basic oracle examples.

If I look at the execution plan of the above query then I see that the driving table is empand the driven table is dept.Also the type of join that is taking place is 'Nested Loop'.

Questions: With respect to the above query,
Q 2. If I want to make dept the driving table and emp the driven table then how can I adjust the statistics to achieve that?
Q 3. If I want to use hash join instead of a nested loop join then then how can I adjust the statistics to achieve that?

I can put the ordered and the use_hash hint to effect this but again I have heard that altering statistics is a more robust way to control an execution plan as compared to hints.

View 6 Replies View Related

Performance Tuning :: How To Improve The Performance Of Export Job (expdp)

Dec 6, 2011

I have an issue with export(expdp).

When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.

expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk

Do i need any look into any memory parameters for this?

View 1 Replies View Related

Performance Tuning :: DECODE In WHERE CLAUSE Performance?

Oct 17, 2011

The following query gets input parameter from the Front End application, which User queries to get Reports.There are many drop down boxes like LOB, FAMILY, BRAND etc., The user may or may not select values from drop down boxes.

If the user select any one or more values ( against each drop down box) it has to fetch all matching values from DB. If the user does'nt select any values it has to fetch all the records, in this case application will send a value 'DEFAULT' (which is not a value in DB ) so that the DB will fetch all the records.

For getting this I wrote a query like below using DECODE, which colleague suggested that will hamper performance.From the below query all the variables V_ are defined in procedure which gets the values selected by user as a comma separated string here V_SELLOB and LOB_DESC is column in DB.

DECODE (V_SELLOB, 'DEFAULT', V_SELLOB, LOB_DESC) IN
OPEN v_refcursor FOR
SELECT /*+ FULL(a) PARALLEL(a, 5) */
*
FROM items a
WHERE a.sku_status = 'A'

[code]...

View 9 Replies View Related

Performance Tuning :: Same Data But Different Performance Results

Sep 3, 2010

what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.

View 10 Replies View Related







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