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
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?
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
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
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
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
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).