Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Chethan.

Asked: November 05, 2007 - 12:07 pm UTC

Last updated: November 19, 2007 - 5:34 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,

We have made use of NVL2 functions at lot of places in pl/sql coding, esp for building a dynamic query like
----------------------------
stmt := 'SELECT event_key,name_key,addr_id,stip_type_code,stip_catg_type,event_date,event_oper FROM address_book_event '||
'WHERE 1=1 '||
NVL2(pn_name_key,' AND name_key = ' || pn_name_key ,NULL)||
NVL2(pv_addr_id, ' AND addr_id = ''' || pv_addr_id || '''',NULL)
INTO lv_sql_stmt
FROM DUAL;

OPEN c1 for stmt;
--------------------------------
Can you let me know if we have any side affects of using NVL2 (most of the users are not using NVL2)
This we could have coded using normal IF .. THEN... ELSE

Thanks,
Chethan




and Tom said...

this stuff makes me shudder. honestly, it does.

Use this approach please:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279

You will no longer be subject to massive SQL injection flaws (please, google sql injection and read what you find, understand what you find)

You will not be hard parsing like mad, it will be "bind friendly"

You will even be able to get a query plan that works best for your query (you stand a chance of having some degree of performance)

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Physical vs logical io and dirty blocks

krishan, November 10, 2007 - 6:24 am UTC

Hi Tom
please give me some information about logical vs physical io,how to reduce logical io
and
Dirty block in oracle

Tom Kyte
November 10, 2007 - 11:45 am UTC

logical = read from buffer cache. logical io might be preceded by a physical io to get the block into the buffer cache.

to reduce logical io's, use 'common sense'. Know what data structures (Index organized tables, heap tables, table compression, index key compression, b*tree clusters, hash clusters, partitioning, b*tree indexes, bitmap indexes, domain indexes) you have available, understand how they work and apply them when appropriate.

eg:

select count(*) from t where x = :x;

suppose T has 1,000,000 rows and on average "where x = :x" returns 100 rows. What could you do to reduce logical IO? You might index the column x - probably.

The "how to reduce" question is unanswerable without writing a book - for there are as many problems/cases to consider as you can think.

a dirty block is not clean :) You modified it, it is dirty, it needs to be check pointed sometime in the future.

Index scan is slower than a Full Table scan

A reader, November 12, 2007 - 1:10 pm UTC

Hello Tom,
Could you help me understand the below scenario please:

Case:1 Below query returns 2300 rows in 12 secs
select * from wells

Case:2 This query returns 490 rows in 37 secs
select * from wells where day='1/7/1978';

I built an index on day column.

I could not understand why is it taking longer time when I use a WHERE clause.In fact I thought it would be faster with a WHERE clause.

Could you throw some light on this behaviour please?How do I make the Index scan faster than a Full table scan?

Tom Kyte
November 16, 2007 - 1:05 pm UTC

well, i think 2300 rows in 12 seconds is horribly slow.

but you would need to trace this and using tkprof see what it is doing.

(and getting 490 out of 2,300 records - 21% of the records - via an index might just be a really slow way of doing it. think about it. index does NOT mean "fast=true")

but there is something wrong if it takes 12 seconds to return 2300 records

Answer to above post

A reader, November 12, 2007 - 3:29 pm UTC

I believe, answer to your question lies on the execution plan for both statements. You should provide the execution plan for both, then only Tom or anybody can see what is going on. Otherwise It is all guess work (what is going on in your database?).

Regards

CPU 100% utilization

mahendra, November 13, 2007 - 8:20 am UTC

the following query makes the CPU utilization to 100%.... pls let me know ASAP what is the problem in this query.




select

v_cvdpl_no,Hrs1 from (select jm.v_cvdpl_no v_cvdpl_no ,min( TimeDiff(D_MAX_RET_PERIOD , SYSDATE)) Hrs1
from

job_ticket_mst jm,job_work_flow_dtl jd where jd .N_LINENUMB = jm .N_LINENUMB
and

jd.V_CVDPL_NO = jm. V_CVDPL_NO
and

jd.D_PROD_IN_TIME is null
and

jd.V_PROCESS_CODE = jm. V_STATUS
and

V_SCHEDULE_TO = '10281'
and

jm.V_STATUS = 'production'
group

by jd.V_SCHEDULE_TO, jm.v_cvdpl_no
order

by Hrs1,jm. v_cvdpl_no)
where

rownum <=1
Tom Kyte
November 16, 2007 - 2:22 pm UTC

what query doesn't take the cpu to 100%? It is not like you can put cpu into the bank and use it later.


I will guess, you have a query in the form:

select *
  from (select c1, c2, min( PLSQL_FUNCTION( c3, sysdate ) )
          from t1, t2
         where join_condition
           and predicates
         group by c1, c2
         order by min(plsql_function(c3,sysdate), c2  )
where rownum <= 1


You are invoking that plsql function thousands of times (or more) and it is "code" - code takes time to run.


suggestion:

inline the function - do NOT call the function, take the code that is in the function and put it right into the query itself (eg: write it using just sql)





A reader, November 13, 2007 - 12:47 pm UTC

I too have a similar issue.But in my case it is a view and not a table.

1.This will take 10 secs to return 3000 rows.
select * from accounts_view;

2. This query will take 37 secs to return 400 rows.
select * from accounts_view where account_mth='TO_DATE('01-jul-2007','dd-Mon-yyyy')

Accounts_view is based on "accounts" table which has an index on "account_mth" column.

3. create index acct_mth on accounts(account_mth);

Execution plan shows that the index on "account_mth" is used and still the response time is high than compared to a "select * from accounts_view".

Any thoughts?

Appreciate any suggestions.
Thanks



Tom Kyte
November 16, 2007 - 2:34 pm UTC

hahaha

it is so similar, it comes from the same IP address :)

just repeat to yourself:

a) full scans are not evil
b) indexes are not all goodness
c) goto a until you believe both a and b fully


and then realize that no one could say anything useful to you when you say "I have an arbitrary view"

Sending the database round the houses

Richard Smith, November 14, 2007 - 7:51 am UTC

>1.This will take 10 secs to return 3000 rows.
> select * from accounts_view;
The query will be full scanning the table, doing multi-block reads, getting multiple records per block read.

>2. This query will take 37 secs to return 400 rows.
> select * from accounts_view where >account_mth='TO_DATE('01-jul-2007','dd-Mon-yyyy')
>
>Accounts_view is based on "accounts" table which has an >index on "account_mth" column.
Sound like the index is being used, requiring the database to traverse the index, and then visit a table block for every record where the index reveals the account_mth is 1/7/7.

For this particular query, using the index is the wrong thing to do.

My rule of thumb is
- if the query will return less than 5% of the rows in the table, use of index will probably help
- if the query will return more that 15% of the rows in the table, the index will almost certainly hurt
Tom Kyte
November 19, 2007 - 5:34 pm UTC

problem really is.....

accounts_view is a horribly complex view, we have no clue what is happening under the covers. No one can say anything sensible about this.

It is unlikely that select * from accounts_view is a simple single table full table scan. If it were and it took 10 seconds - to return that tiny bit of data - I'd be really upset.

"select * from accounts_view;" RBO or CBO!? ROT indexes!

Duke Ganote, November 16, 2007 - 2:17 pm UTC

@reader: "select * from accounts_view;" is this an old version of Oracle or using the rule-based optimizer?

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8764517459743#63610540110983

Perfect example for a design flaw to ruin performance

Carsten, November 16, 2007 - 2:37 pm UTC

> select * from accounts_view where >account_mth='TO_DATE('01-jul-2007','dd-Mon-yyyy')

You are storing a date as string. You are hiding valuable information about your data from the database.

With the proper data type AND automatic statistc collection (on by default) oracle should make better guesses about the number of rows to retrieve and if it is faster via an index or full table scan (which would be optimal for you obviously).