Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kailash.

Asked: January 18, 2003 - 4:06 pm UTC

Last updated: November 11, 2015 - 1:05 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,

In one of your followup, you had mentioned that you need to look for queries that perform a large number of logical I/Os. But my understanding is that an I/O is considered as logical I/O if I/O involves a memory access and not a disk access (which constitutes a physical I/O) which will be performed much much faster compared to a disk access. Could you explain me why this is bad? (Because the performance tuning books say that the hit ratio should be high and hit ratio will be high if the query is able to find it's data in memory rather than accessing the disk)

Please explain

and Tom said...

goto www.hotsos.com and read the paper "so are you still using cache hit ratios"

Rating

  (23 ratings)

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

Comments

Goal of tuning

A reader, December 04, 2003 - 1:43 pm UTC

Tom,

I have read several of your articles. In all of them you insisted the following :

1. Use CBO and analyze the tables,indexes etc
2. Make sure that the query accesses/performs fewest logical I/Os. ie it accesses the smallest number of blocks to satisfy the query
3. Use hints only as the last resort
4. Use bind variables and bulk operations such as array processing instead of row by row process
5. Always look for Consistent Gets in the trace output and try to minimize it.

1. Are those the things you always look for when you are asked to tune the query. I mean, if you were to tune the query, are those the above ones that you focus on. If not, what other things you generally concentrate?
2. What things do you look for in EXPLAIN PLAN such as Bytes, Card, Cost etc. Which one would be of real significance when you tune the query.
3. Also, you mention that higher cost does not mean a bad performing query and lower cost mean a good performing query. ie. cost cannot be directly related to performance. Is that true?
4. As soon as you see a full table scan or an index scan, what other things do you immediately see and what steps do you follow to arrive at alternate queries to make the query efficient.
5. What collection of scripts do you personally generally use when you do a query tuning.

Tom Kyte
December 04, 2003 - 3:09 pm UTC

i like to tune QUESTIONS not queries first of all :)

but #1 is there for sure. thats a given.

The rest are really not "process". In order to tune, I want to know what the "goal" is (you've probably seen me ask "but what is it you are trying to accomplish" here more then once).

Once I know the goal, i'll look at the existing process and try to optimize it.

Take a process like


for x in ( select * from t )
loop
some trival processing of x
insert into another_t x


I'll turn that procedural code (sometimes pages of it) into a single query. Now thats tuning via re-design. that kind of tuning gets you massive payback.


Suppose I could take a query you execute 1,000,000 times in some process and cut its run time in half via "query tuning". You still have 1,000,000 * (some time) of runtime for that query. Now, I take that process that calls that query 1,000,000 times and develop a single query that runs the entire process. That -- that is tuning.

I cannot verbalize "how I tune a query" when I do tune a query. I did write in my new book "Effective Oracle By Design" what I think it takes:

<quote>
This was probably the hardest chapter of the book to write. That is not because the material is all that complex. It's because I know what people want, and I know what can be delivered. What people want is the ten-step process for tuning any query. What can be delivered is knowledge about how queries are processed, which you can use and apply as you develop queries.

If there were a ten-step, or even a million-step, process by which any query (or a large percentage of queries) could be tuned, we would write a program to do it. Sure, there are actually many programs that try to do this, such as Oracle Enterprise Manager with its tuning pack, SQL Navigator, and others. What they do is primarily recommend indexing schemes to tune a query, suggest materialized views, and offer to add hints to the query to try other access plans. They show you different query plans for the same statement and allow you to pick one. These tuning tools use a very limited set of rules that sometimes can suggest that index or set of indexes you really should have thought of during your design. They offer "rule of thumb" (what I generally call ROT, since the acronym and the word it maps to are so appropriate for each other) SQL optimizations. If these were universally applicable, the optimizer would do them routinely.

In fact, the CBO does tuning already. It rewrites our queries all of the time. If there were an N-step process to tuning a query-to writing efficient SQL- the optimizer would incorporate it all, and we would not be having a discussion on this topic. It is like the search for the Holy Grail. Maybe someday the software will be sophisticated enough to take our SQL, understand the question being asked, and process the question rather than syntax.

This chapter will provide the foundation knowledge you need in order to begin thinking about how queries could be processed. Once you understand query processing, you can make the query do what you think might be best. Writing efficient SQL is no different from writing efficient C, Java, or even English. It takes an understanding of how things work and what is available. If you don't know some technique is available, you'll never be able to optimize to use it.

What You Need to Write Efficient SQL

For every rule of thumb out there I've seen regarding writing efficient SQL, I've been able to come up with a slew of common (not esoteric) countercases to prove that rule of thumb is wrong as many times as it is right. I've talked to people who swear NOT IN is fatal, and you should never use it; instead, always use NOT EXISTS. Then I show them NOT IN running a query ten times faster than NOT EXISTS. I talk with people who feel NOT EXISTS is the worst construct on the planet; you must use NOT IN. Then I show them how NOT EXISTS can run many times faster than NOT IN.

Rather than following rules of thumb, writing efficient SQL requires the following:

o Knowledge of the physical organization of what you're querying against That is, you should know the schema. (Refer to Chapter 7 of this book for information about designing an efficient schema.)

o Knowledge of what the database is capable of doing For example, if you did not know about skip-scan indexes and what they do, you might look at a schema and say, "Aha, we are missing an index!" when, in fact, this is not true.

o Knowledge of all of the intricacies of SQL You should be familiar with SQL, from the lowly WHERE clause up to analytics and pseudo columns. You should know what using a particular construct would do to your runtime processing.

o A solid understanding of the goal-what the question is Tuning a query or process is really hard (or impossible), unless you understand the question in the first place. For example, many people use outer joins in all queries, because they are afraid of losing a row. If the objects are related in a one-to-one mandatory fashion, they don't need an outer join at all. In many cases, the question derived from the query is much more confining than the real goal of the query.

In this chapter, we'll cover the topics of what the database is capable of doing in general, looking at many of the access paths and join operations available. We'll look at what SQL is capable of doing, but not by discussing the entire language, since that is a book in itself. Rather, we'll look at a couple of things that will whet you appetite, showing you how powerful this language can be-how much more than just SELECT, FROM, WHERE, and ORDER BY there is. Then we'll close up with a look at that most important topic: why understanding the question is more important than having a query at hand to tune. I hope that when you finish this chapter, you'll want to learn more about SQL and what it can do.
</quote>



The only tools I use -- autotrace, sql_trace (10046 trace really) + tkprof, statspack sometimes but mostly -- mostly -- almost 100% -- my knowledge of what is possible with SQL and the database.

If you don't know what it can do -- you'll lose everytime.


For example -- was just talking with someone. They had a problem -- a table A with a one to many relationshop to B and a one to many relationship to C.

They wanted to get data from A, aggregated data from B and aggregated data from C.

Sounds "simple" but the A 1:M B and A 1:M C was "getting in the way". they were getting a cartesian product between B and C when just joining A to B and A to C

(eg: if the parent record in A has 2 children in B and 3 children in C, they'd get 6 records!)

Their thoughts -- we'll run two queries and join them in our application.

My thoughts -- let me show you this inline view thing where we can:

select ..
from ( select ... from a, b where ... group by ... ) T1,
( select ... from a, c where ... group by ... ) T2
where t1.... = t2.....


hmmm, very nice, very fast - but not possible unless you knew you could do it. Then we went into analytics, cubes, rollups and so on.


You cannot tune until you know what you have available to tune with!

Good show

Brett, December 04, 2003 - 3:50 pm UTC

I have been trying to convince fellow workers to use in-line views for complex joins. Not only can they run faster, but it allows you to break up your logic into little blocks. I'll keep screaming about them and hopefully, someday, they will listen.

Well said

A reader, December 04, 2003 - 5:08 pm UTC

Tom,
Many times I have been asked the same question. "Is there any tool which will automatically scan through all our packages and generate explain plans for each query?" My first response is NO. Next I ask: If such a tool was there. what do you do with the 700 explain plans? "Oh we want you to look at all explain plans and tune each query to make sure that indexes are being used". When I tell that this is not the correct approach to tuning, I get reactions from looks of disbelief "perhaps he doesn't know his job" or "he is trying to get away from it" to stern glares of "do what you are told, we don't have time for your proper approach nonsense".

You are doing a great service to the DBA and Developer communities through this forum by providing advise on technical matters and dispelling many myths about how databases work. Please advise how to handle these kind of situations.

For obvious reasons, keeping my name and location null.

Thanks...

Tools you use

A reader, December 04, 2003 - 5:28 pm UTC

Tom,

Thanks much for the response. If you use the Autotrace or TKPROF (trace 10046) :

1. What items would you first focus on such as Consistent Gets, Physical reads etc
2. What do you focus on 10046 events

Tom Kyte
December 04, 2003 - 6:11 pm UTC

10046 is just "sql_trace with options"

i look for low hanging fruit (queries with big numbers on the totals line)

then I decide if they are worth looking at.

Then I try to figure out "why, why are we doing that query" and tune the question (is there a better approach)

or I apply my knowledge of SQL and say -- you know, instead of that self join to get the current record joined with the 'next' record -- lets just use lead() instead of the join.

(logical ios are what I'm looking mostly to reduce. ignore physical, pay attention to logical and the physical will take care of themselves)

Physical IO's

A reader, December 05, 2003 - 11:07 am UTC

Tom,
am an avid reader of your site. Great work.
Can you please explain why the Physical IO's would take care of themselves when the Logical IO's are reduced ?


Tom Kyte
December 05, 2003 - 11:22 am UTC

reduce the LIOs and...

you have less blocks to buffer (probably)

you appear to have a bigger buffer cache.

you can more efficiently cache that frequently used information -- rather then bumping it out to make room for other stuff.

A block has to be read into the buffer cache eventually right. If you reduce the number of blocks you need to process to execute your query -- you'll have less blocks to potentially read and more buffer space to cache the ones you use over and over.



A reader, April 01, 2004 - 3:30 pm UTC

Hi Tom I had done a free paper, to introduce newbies, to tuning, any advice, or suggestion, will be welcome
I had done this for me like a to do list.
:)

</code> http://www.geocities.com/juancarlosreyesp/101basictunningonOracle9i.pdf <code>

happy vacations

Manoj Pradhan, April 17, 2007 - 8:51 am UTC

Tom ,
I am new into this tuning world .
1 . Whether the Inline Views will be converted ( rewrite ) into joins(if possible ) by CBO.
2. Because I came accross a term "Query Un nesting "
as per my understanding CBO will rewrite the Query , modifying Subquery and Inline Views Into Joins .

Please Clarifay .
Tom Kyte
April 17, 2007 - 10:17 am UTC

1) sure
2) sure

Manoj Pradhan, April 18, 2007 - 1:51 am UTC

Tom,
1. If CBO may rewrite the Inline views and subQuery into Joins then How use of Inline view will increase Perofrmance . Pls clarify ...

Tom Kyte
April 18, 2007 - 12:05 pm UTC

who said an inline view would increase performance?

non-performance of query in procedure

Piyush, June 14, 2007 - 11:54 am UTC

Dear Tom,

I have an insert statement for Global Temporary Table like -

insert into gtt_table
select * from table_names(complex joins and subqueries , co-related subqueries)
union all
select * from table_names(complex joins and subqueries , co-related subqueries)
union all
select * from table_names(complex joins and subqueries , co-related subqueries)
union all
select * from table_names(complex joins and subqueries , co-related subqueries)
union all
select * from table_names(complex joins and subqueries , co-related subqueries)
union all
select * from table_names(complex joins and subqueries , co-related subqueries)
union all
select * from table_names(complex joins and subqueries , co-related subqueries)
union all
select * from table_names(complex joins and subqueries , co-related subqueries)
order by clause;

now this takes only a sec or less to execute from sql plus.

I have a procedure which encapsulates this. It takes 3 mins.
Yes, it has got only 2 input parameters. both are numeric. some input paramters does join with clob fields at times. even to_char() does not helps.

other statements are working fine.

SGA - 900MB
PGA - 180MB
buffer_cache - 1mb
db_cache_size about 96MB (prob 8kb blocks)
RAM -1.5MB
Automatic Memory management on.
OS - Win XP Professional
Oracle 10G Release 1

Other output parameters :- sys_refcursor and error message.
DML statements are there but not taking too much time.

about 50 java developers are working. about 100 sessions are established at any time.

Please let me know what could be the problem and what should I look for. dbms_sqltune package unable to recommend further. at times it suggest some profiles but it hardly improves the performance. As already said, outside the package and procedure, the speed is marvellous.

GTT table returns at the end about 200 rows.

Table contains about 15-40 lakh records each. Few tables are very small containing few thousand records.

I am really at pain as other DBAs are too speechless at this. Please help!

Regards,
Piyush


Tom Kyte
June 14, 2007 - 4:07 pm UTC

show us the tkprof of both - one from sqlplus and one from the procedure.

tkprof

Piyush, June 29, 2007 - 6:26 am UTC

Hi Tom !

Thanks for replying to my question.

Please find below the tkprof output of the sql query :-

insert into history(history_date,deal_seq_num,leg_seq_num,employee_num,consultant_ref,
    currency_cd,amount,operator_cd,product_type,leg_type,start_date,
    screen_changed,table_name,column_name,column_desc,before_image,
    after_image,doc_num,doc_desc,old_status,new_status,reprint_ind,
    receipt_num,pay_phase,pay_method,fulfilment_date,max_end_date,
    event_type,desc_num,event_ref,consultation_ref,chkey_value1,date_time,branch_budget_ctr,
    assoc_pay_method, editing_branch_budget_ctr, activity_type)
    select E.date_lu,E.key_value2,null,E.employee_num,null,null,null,null,null,null,null,
    null,E.table_name,ED.fieldname,null,DBMS_LOB.SUBSTR(ED.before_image,DBMS_LOB.getlength(ED.before_image),1),
    DBMS_LOB.SUBSTR(ED.after_image,DBMS_LOB.getlength(ED.after_image),1),null,null,
    null,null,null,null,null,null,null,null,E.event_type,ED.fieldname,E.event_ref,
    E.key_value1,null, sysdate, 434 ,null,null,null
    from event E, event_detail ED
    where(E.event_ref=ED.event_ref)
    and(E.key_value1=12830364)
    and(E.table_name='CONSULTATION')
    and e.branch_budget_ctr = 434
    and e.branch_budget_ctr = ED.branch_budget_ctr
        UNION ALL
       -- ** Select Consultation details for Costing, Manifest Link, Tickets and Travel Deal
    select E.date_lu,E.key_value2,null,E.employee_num,null,null,null,TD.operator_cd,
    TD.product_type,null,null,null,E.table_name,ED.fieldname,null,
    DBMS_LOB.SUBSTR(ED.before_image,DBMS_LOB.getlength(ED.before_image),1),
    DBMS_LOB.SUBSTR(ED.after_image,DBMS_LOB.getlength(ED.after_image),1),
    null,null,null,null,null,null,null,null,null,null,
    E.event_type,ED.fieldname,E.event_ref,E.key_value1,null, sysdate, 434,null,null,null
    from event E, event_detail ED, travel_deal TD
    where(E.event_ref=ED.event_ref)
    and(E.key_value1=12830364)
    and(TD.consultation_ref=12830364)
    and(E.key_value1=TD.consultation_ref)
    and(E.key_value2=TD.deal_seq_num)
    and(E.table_name in('COSTING','MANIFEST_LINK','TICKETS','TRAVEL_DEAL'))
    and E.branch_budget_ctr = 434
    and e.branch_budget_ctr = ED.branch_budget_ctr
    and e.branch_budget_ctr = TD.branch_budget_ctr
        UNION ALL
      /* Costing Lines Adjustment */
    select e.date_lu,e.key_value2,null,e.employee_num,s.consultant_ref,null,null,
    td.operator_cd,td.product_type,null,null,null,e.table_name,'Costing Added ( Deal '
    ||td.deal_seq_num||' )',null,(coalesce(c.description,cc.cost_code_desc)),
    to_char(c.quantity_involved*(c.cost_unit_val*decode(cc.trav_debit_credit_ind,'cr',-1,1)),'fm9999999999999999.00'),
    null,null,e.cash_id,null,null,null,null,null,null,null,
    e.event_type,null,e.event_ref,e.key_value1,null, sysdate, 434 ,null,null,null
    from event e,costing c,staff s,travel_deal td,cost_code cc
    where e.key_value1= 12830364
    and td.consultation_ref=e.key_value1
    and td.deal_seq_num=e.key_value2
    and td.branch_budget_ctr=e.branch_budget_ctr
    and c.consultation_ref=e.key_value1
    and c.deal_seq_num=e.key_value2
    and c.costing_seq_num=e.key_value3
    and e.employee_num=s.employee_num
    and e.table_name='COSTING'
    and c.cost_cd=cc.cost_cd
    and e.branch_budget_ctr = 434
    and e.branch_budget_ctr = c.branch_budget_ctr
    and e.branch_budget_ctr = s.branch_budget_ctr
    and c.branch_budget_ctr = cc.branch_budget_ctr
        UNION ALL
      /* Retrieve any deleted events */
    select E.date_lu,E.key_value2,null,E.employee_num,null,null,null,null,null,null,null,null,
    E.table_name,null,null,'Row deleted from '||E.table_name,null,null,null,null,
    null,null,null,null,null,null,null,E.event_type,null,E.event_ref,
    E.key_value1,null, sysdate, 434 ,null,null,null
    from event E
    where(E.key_value1=12830364)
    and (E.table_name in ('TRAVEL_LEG','MANIFEST','MANIFEST_LINK'))
    and (E.event_type='DELETE')
    and e.branch_budget_ctr = 434
        UNION ALL
      /* Select details for FE Deal */
    select e.date_lu,e.key_value2,null,e.employee_num,null,fd.currency_cd,fd.base_amt,null,null,
    null,null,null,e.table_name,ed.fieldname,null,
    DBMS_LOB.SUBSTR(ED.before_image,DBMS_LOB.getlength(ED.before_image),1),
    DBMS_LOB.SUBSTR(ED.after_image,DBMS_LOB.getlength(ED.after_image),1),
    null,null,null,null,null,null,null,null,null,null,e.event_type,ed.fieldname,
    e.event_ref,e.key_value1,null, sysdate, 434 ,null,null,null
    from event e,event_detail ed,fe_deal fd
    where(e.event_ref=ed.event_ref)
    and(e.key_value1=12830364)
    and(e.key_value1=fd.consultation_ref)
    and(e.key_value2=fd.deal_seq_num)
    and(e.table_name='FE_DEAL')
    and e.branch_budget_ctr = 434
    and e.branch_budget_ctr = ed.branch_budget_ctr
    and e.branch_budget_ctr = fd.branch_budget_ctr
        UNION ALL
      --   /* Select details for Manifest */
    select e.date_lu,e.key_value2,null,e.employee_num,null,null,null,null,null,null,null,null,
    e.table_name,ed.fieldname,null,DBMS_LOB.SUBSTR(ED.before_image,DBMS_LOB.getlength(ED.before_image),1),
    DBMS_LOB.SUBSTR(ED.after_image,DBMS_LOB.getlength(ED.after_image),1),null,null,null,
    null,null,null,null,null,null,null,e.event_type,ed.fieldname,e.event_ref,
    e.key_value1,null, sysdate, 434 ,null,null,null
    from event e, event_detail ed
    where(e.event_ref=ed.event_ref)
    and(e.key_value1 = 12830364)
    and(e.table_name='MANIFEST')
    and e.branch_budget_ctr = 434
    and e.branch_budget_ctr = ed.branch_budget_ctr
        UNION ALL
      --   /* Select details for Travel Leg */
    select /* + index pk149, pk299 */  e.date_lu,tl.deal_seq_num,tl.leg_seq_num,e.employee_num,null,null,null,
    td.operator_cd,td.product_type,tl.leg_type,to_date(tl.start_date,'YYYY-MM-DD'),null,e.table_name,
    ed.fieldname,null,DBMS_LOB.SUBSTR(ED.before_image,DBMS_LOB.getlength(ED.before_image),1),
    DBMS_LOB.SUBSTR(ED.after_image,DBMS_LOB.getlength(ED.after_image),1),null,null,null,null,null,
    null,null,null,null,null,e.event_type,ed.fieldname,e.event_ref,
    e.key_value1,null, sysdate, 434,null,null,null
    from event e,event_detail ed,travel_deal td,travel_leg tl
    where(e.event_ref=ed.event_ref)
    and(e.key_value1=12830364)
    and(tl.consultation_ref=12830364)
    and(e.key_value1=tl.consultation_ref)
    and(e.key_value2=tl.deal_seq_num)
    and(e.key_value3=tl.leg_seq_num)
    and(td.consultation_ref=tl.consultation_ref)
    and(td.deal_seq_num=tl.deal_seq_num)
    and(e.table_name='TRAVEL_LEG')
    and e.branch_budget_ctr = 434
    and e.branch_budget_ctr = ed.branch_budget_ctr
    and e.branch_budget_ctr = tl.branch_budget_ctr    
    and tl.branch_budget_ctr = td.branch_budget_ctr
        UNION ALL
      --   /* Select details for Travel Deal */
    select e.date_lu,e.key_value2,null,e.employee_num,s.consultant_ref,null,null,
    td.operator_cd,td.product_type,null,null,null,e.table_name,'Deal Created ( Deal '
    ||td.deal_seq_num||' ) Status '||ds.deal_status_desc as deal_message,
    td.bkg_ref_num,'Cash ID '||e.cash_id,'Physical Device '||e.physical_device_num,
    null,null,null,null,null,null,null,null,null,null,e.event_type,null,e.event_ref,e.key_value1,
    e.chkey_value1, sysdate, 434 ,null,null,null
    from event e,travel_deal td,deal_status ds,staff s
    where(e.key_value1=12830364)
    and(td.consultation_ref=e.key_value1)
    and(td.deal_seq_num=e.key_value2)
    and(e.status=ds.deal_status_cd)
    and(e.employee_num=s.employee_num)
    and((e.event_type='INSERT')
    and(e.table_name='TRAVEL_DEAL'))
    and e.branch_budget_ctr = 434
    and e.branch_budget_ctr = td.branch_budget_ctr
    and e.branch_budget_ctr = s.branch_budget_ctr
    UNION ALL
    -- inter-branch activity report
    SELECT e.date_lu,e.key_value2,null,e.employee_num,null,
    null,null,null,null,null,null,
    null,e.table_name,ed.fieldname,null,DBMS_LOB.SUBSTR(ED.before_image,DBMS_LOB.getlength(ED.before_image),1),
    DBMS_LOB.SUBSTR(ED.after_image,DBMS_LOB.getlength(ED.after_image),1),null,null,null,null,null,
    null,null,null,null,null,
    null,ed.fieldname,e.event_ref, e.key_value1,null, sysdate, 434,null,
    c.editing_branch_budget_ctr,decode(c.editing_branch_budget_ctr,null,null,'CHANGE OF CUSTOMER ADDRESS')
    from event e,event_detail ed ,customer c, consultation cs
    where e.event_ref=ed.event_ref
    and e.key_value1= c.cust_ref_num
    and cs.cust_ref_num = c.cust_ref_num
    and cs.cust_branch_budget_ctr = c.branch_budget_ctr
    and cs.consultation_ref = 12830364
    and e.table_name = 'CUSTOMER'
    and cs.branch_budget_ctr = 434
    and e.branch_budget_ctr = ed.branch_budget_ctr
    and c.branch_budget_ctr = e.branch_budget_ctr
    and e.branch_budget_ctr != c.editing_branch_budget_ctr
    and ed.fieldname != 'EDITING_BRANCH_BUDGET_CTR'
    UNION ALL
    select date_lu,null,null,employee_num,null,
    null,null,null,null,null,null,
    null,null,null,null,null,
    null,null,null,null,null,null,
    null,null,null,null,null,
    null,null,null,12830364, null, sysdate, 434,null,
    editing_branch_budget_ctr, 'MEMOS'
    from diary_entries 
    where branch_budget_ctr = 434
    and branch_budget_ctr != editing_branch_budget_ctr
    and consultation_ref = 12830364
    UNION ALL
    select no.date_lu,null,null,no.employee_num,null,
    null,null,null,null,null,null,
    null,null,null,null,null,
    null,null,null,null,null,null,
    null,null,null,null,null,
    null,null,null,12830364, null, sysdate, 434,null,
    no.editing_branch_budget_ctr, 'CONSULTATION NOTES'
    from consultation_notes no, customer cus, consultation  con
    where con.branch_budget_ctr = 434 and
    no.branch_budget_ctr != no.editing_branch_budget_ctr
    and no.branch_budget_ctr = con.branch_budget_ctr
    and no.consultation_ref = con.consultation_ref
    and con.cust_branch_budget_ctr = cus.branch_budget_ctr
    and con.CUST_REF_NUM = cus.CUST_REF_NUM
    and con.consultation_ref = 12830364
    UNION ALL
    select distinct e.date_lu, null,null,e.employee_num,null,
    null,null,null,null,null,null,
    null,null,null,null,null,
    null,null,null,null,null,null,
    null,null,null,null,null,
    null,null,null,12830364, null, sysdate, 434,p.assoc_pay_method,
    p.editing_branch_budget_ctr, 'PAYMENT'
    from event e, payment p, customer cus, consultation  con
    where e.table_name = 'PAYMENT'
    and e.key_value1 = p.consultation_ref
    and e.key_value2 = p.payment_seq_num
    and p.branch_budget_ctr = e.branch_budget_ctr
    and  p.branch_budget_ctr != p.editing_branch_budget_ctr
    and e.branch_budget_ctr = 434
    and e.event_type = 'INSERT'
    and con.branch_budget_ctr = e.branch_budget_ctr
    and e.key_value1 = con.consultation_ref
    and con.cust_branch_budget_ctr = cus.branch_budget_ctr
    and con.CUST_REF_NUM = cus.CUST_REF_NUM
    and con.consultation_ref = 12830364
    UNION ALL
    select E.date_lu,E.key_value2,null,E.employee_num,null,null,null,TD.operator_cd,
    TD.product_type,decode(ED.fieldname,'112','A'),null,null,null,null,null,null,null,
    ED.doc_num,ED.fieldname,null, null, null,null,null,null, null, null,
    null,ED.fieldname,E.event_ref,E.key_value1,null, sysdate, 434 ,null,null,E.event_type
    from event E,travel_deal TD,event_detail ED
    where E.table_name='DOCUMENT'
    and E.key_value1=12830364
    and E.event_type='DOCUMENT'
    and E.key_value1=TD.consultation_ref
    and E.key_value2=TD.deal_seq_num
    and E.event_ref=ED.event_ref
    and E.branch_budget_ctr = 434
    and E.branch_budget_ctr = TD.branch_budget_ctr
    and E.branch_budget_ctr = ED.branch_budget_ctr
         UNION ALL
    select E.date_lu,E.key_value2,null,E.employee_num,null,null,null,'*','*',
    decode(ED.fieldname,'112','A')
    ,null,null,null,null,null,null,null,ED.doc_num,ED.fieldname,
    null, null,null,null,null, null, null, null,
    null,ED.fieldname,E.event_ref,E.key_value1,null, sysdate, 434 ,null,null,E.event_type
    from event E,event_detail ED
    where E.table_name = 'DOCUMENT'
    and (E.key_value1=12830364)
    and (E.key_value2=-1)
    and (E.event_type='DOCUMENT')
    and (E.event_ref=ED.event_ref)
    and E.branch_budget_ctr = 434
    and E.branch_budget_ctr = ED.branch_budget_ctr

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.95       0.95          0          0          0           0
Execute      1      0.04       0.55         52        734         89          76
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.00       1.51         52        734         89          76

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 142


Also after clearing the buffer with the following 2 statements, i have executed the procedure for a new trace.

alter system flush shared_pool;
alter system flush buffer_cache;

fresh Trace tkprof for the procedure is also attached below
[fixedsys font used].


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     72.17     181.88      20506    7061316         89          76
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     72.17     181.88      20506    7061316         89          76

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 142     (recursive depth: 1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     15132        0.36         89.96
  db file scattered read                        341        0.08         15.48


Hope I will get some solution to which I am struggling so long....

Thanking you in advance,

With Warmest Regards,
Piyush
Tom Kyte
July 02, 2007 - 2:13 pm UTC

compare the plans - you should have a row source report in there, are the plans the same.

YOU compare the plans please, this is a rather large query and isn't really a followup to the original question

row source operation and waits details

Piyush, July 03, 2007 - 7:44 am UTC

Hi Tom!

I had just attached relevant part of the tkprof output as required by you.

Anyways, row source operation is not the same.

For SQL it is -


Rows     Row Source Operation
-------  ---------------------------------------------------
     76  UNION-ALL  (cr=732 pr=52 pw=0 time=1808448 us)
      5   TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: 4 4 (cr=26 pr=7 pw=0 time=149282 us)
     12    NESTED LOOPS  (cr=24 pr=5 pw=0 time=542045 us)
      6     TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=6 pr=0 pw=0 time=268 us)
      6      INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=0 pw=0 time=84 us)(object id 640073)
      5     INDEX RANGE SCAN PK149 (cr=18 pr=5 pw=0 time=60087 us)(object id 624594)
     43   NESTED LOOPS  (cr=277 pr=15 pw=0 time=28791 us)
     43    NESTED LOOPS  (cr=148 pr=13 pw=0 time=12596 us)
     34     INLIST ITERATOR  (cr=25 pr=0 pw=0 time=529 us)
     34      TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=25 pr=0 pw=0 time=621 us)
     34       INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=12 pr=0 pw=0 time=279 us)(object id 640073)
     43     TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: 4 4 (cr=123 pr=13 pw=0 time=99999 us)
     43      INDEX RANGE SCAN PK149 (cr=102 pr=6 pw=0 time=55727 us)(object id 624594)
     43    TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: 4 4 (cr=129 pr=2 pw=0 time=15701 us)
     43     INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=86 pr=2 pw=0 time=14836 us)(object id 626747)
     10   NESTED LOOPS  (cr=124 pr=15 pw=0 time=46891 us)
     10    NESTED LOOPS  (cr=84 pr=3 pw=0 time=28400 us)
     10     NESTED LOOPS  (cr=64 pr=3 pw=0 time=27951 us)
     10      NESTED LOOPS  (cr=34 pr=0 pw=0 time=1037 us)
     10       TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=4 pr=0 pw=0 time=415 us)
     10        INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=0 pw=0 time=41 us)(object id 640073)
     10       TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: 4 4 (cr=30 pr=0 pw=0 time=489 us)
     10        INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=20 pr=0 pw=0 time=284 us)(object id 626747)
     10      TABLE ACCESS BY GLOBAL INDEX ROWID COSTING PARTITION: 4 4 (cr=30 pr=3 pw=0 time=29717 us)
     10       INDEX UNIQUE SCAN PK71 (cr=20 pr=3 pw=0 time=29518 us)(object id 622110)
     10     TABLE ACCESS BY INDEX ROWID STAFF (cr=20 pr=0 pw=0 time=355 us)
     10      INDEX UNIQUE SCAN PK270 (cr=10 pr=0 pw=0 time=176 us)(object id 626291)
     10    TABLE ACCESS BY GLOBAL INDEX ROWID COST_CODE PARTITION: 4 4 (cr=40 pr=12 pw=0 time=112982 us)
     10     INDEX UNIQUE SCAN PK64 (cr=30 pr=10 pw=0 time=89403 us)(object id 622275)
      2   INLIST ITERATOR  (cr=22 pr=0 pw=0 time=95 us)
      2    TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=22 pr=0 pw=0 time=178 us)
     31     INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=10 pr=0 pw=0 time=235 us)(object id 640073)
      0   TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: 4 4 (cr=3 pr=0 pw=0 time=83 us)
      1    NESTED LOOPS  (cr=3 pr=0 pw=0 time=52 us)
      0     NESTED LOOPS  (cr=3 pr=0 pw=0 time=37 us)
      0      TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=3 pr=0 pw=0 time=29 us)
      0       INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=0 pw=0 time=18 us)(object id 640073)
      0      TABLE ACCESS BY GLOBAL INDEX ROWID FE_DEAL PARTITION: 4 4 (cr=0 pr=0 pw=0 time=0 us)
      0       INDEX UNIQUE SCAN PK153 (cr=0 pr=0 pw=0 time=0 us)(object id 624845)
      0     INDEX RANGE SCAN PK149 (cr=0 pr=0 pw=0 time=0 us)(object id 624594)
     11   TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: 4 4 (cr=53 pr=1 pw=0 time=1069 us)
     25    NESTED LOOPS  (cr=49 pr=0 pw=0 time=1200 us)
     13     TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=10 pr=0 pw=0 time=187 us)
     13      INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=4 pr=0 pw=0 time=101 us)(object id 640073)
     11     INDEX RANGE SCAN PK149 (cr=39 pr=0 pw=0 time=308 us)(object id 624594)
      1   TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: 4 4 (cr=138 pr=3 pw=0 time=31183 us)
     15    NESTED LOOPS  (cr=137 pr=3 pw=0 time=415795 us)
     13     NESTED LOOPS  (cr=98 pr=3 pw=0 time=31177 us)
     13      NESTED LOOPS  (cr=59 pr=3 pw=0 time=30624 us)
     13       TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=7 pr=0 pw=0 time=432 us)
     13        INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=0 pw=0 time=32 us)(object id 640073)
     13       TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_LEG PARTITION: 4 4 (cr=52 pr=3 pw=0 time=29980 us)
     13        INDEX UNIQUE SCAN PK299 (cr=39 pr=3 pw=0 time=29732 us)(object id 627006)
     13      TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: 4 4 (cr=39 pr=0 pw=0 time=442 us)
     13       INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=26 pr=0 pw=0 time=217 us)(object id 626747)
      1     INDEX RANGE SCAN PK149 (cr=39 pr=0 pw=0 time=241 us)(object id 624594)
      4   NESTED LOOPS  (cr=45 pr=2 pw=0 time=23128 us)
      4    NESTED LOOPS  (cr=37 pr=2 pw=0 time=22942 us)
      5     NESTED LOOPS  (cr=28 pr=0 pw=0 time=525 us)
      5      TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=13 pr=0 pw=0 time=231 us)
     19       INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=0 pw=0 time=25 us)(object id 640073)
      5      TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: 4 4 (cr=15 pr=0 pw=0 time=193 us)
      5       INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=10 pr=0 pw=0 time=103 us)(object id 626747)
      4     TABLE ACCESS BY INDEX ROWID DEAL_STATUS (cr=9 pr=2 pw=0 time=22494 us)
      4      INDEX UNIQUE SCAN PK122 (cr=5 pr=1 pw=0 time=10860 us)(object id 623533)
      4    TABLE ACCESS BY INDEX ROWID STAFF (cr=8 pr=0 pw=0 time=147 us)
      4     INDEX UNIQUE SCAN PK270 (cr=4 pr=0 pw=0 time=74 us)(object id 626291)
      0   TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: ROW LOCATION ROW LOCATION (cr=11 pr=3 pw=0 time=34459 us)
      1    NESTED LOOPS  (cr=11 pr=3 pw=0 time=34425 us)
      0     NESTED LOOPS  (cr=11 pr=3 pw=0 time=34408 us)
      1      NESTED LOOPS  (cr=6 pr=1 pw=0 time=10831 us)
      1       TABLE ACCESS BY GLOBAL INDEX ROWID CONSULTATION PARTITION: 4 4 (cr=3 pr=0 pw=0 time=45 us)
      1        INDEX UNIQUE SCAN PK55 (cr=2 pr=0 pw=0 time=22 us)(object id 621840)
      1       TABLE ACCESS BY GLOBAL INDEX ROWID CUSTOMER PARTITION: ROW LOCATION ROW LOCATION (cr=3 pr=1 pw=0 time=10770 us)
      1        INDEX UNIQUE SCAN PK_CUSTOMER (cr=2 pr=1 pw=0 time=10732 us)(object id 623092)
      0      TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=5 pr=2 pw=0 time=23557 us)
      2       INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=2 pw=0 time=23449 us)(object id 640073)
      0     INDEX RANGE SCAN PK149 (cr=0 pr=0 pw=0 time=0 us)(object id 624594)
      0   TABLE ACCESS BY INDEX ROWID DIARY_ENTRIES (cr=3 pr=2 pw=0 time=28220 us)
      1    INDEX RANGE SCAN PK_DIARY_ENTRIES (cr=2 pr=1 pw=0 time=13542 us)(object id 623559)
      0   NESTED LOOPS  (cr=8 pr=2 pw=0 time=19644 us)
      1    NESTED LOOPS  (cr=5 pr=0 pw=0 time=105 us)
      1     TABLE ACCESS BY GLOBAL INDEX ROWID CONSULTATION PARTITION: 4 4 (cr=3 pr=0 pw=0 time=66 us)
      1      INDEX UNIQUE SCAN PK55 (cr=2 pr=0 pw=0 time=36 us)(object id 621840)
      1     INDEX UNIQUE SCAN PK_CUSTOMER (cr=2 pr=0 pw=0 time=20 us)(object id 623092)
      0    TABLE ACCESS BY GLOBAL INDEX ROWID CONSULTATION_NOTES PARTITION: 4 4 (cr=3 pr=2 pw=0 time=19523 us)
      7     INDEX RANGE SCAN PK_CON_NOTES (cr=2 pr=2 pw=0 time=19485 us)(object id 621932)
      0   SORT UNIQUE (cr=16 pr=2 pw=0 time=40351 us)
      0    NESTED LOOPS  (cr=16 pr=2 pw=0 time=40276 us)
      2     NESTED LOOPS  (cr=10 pr=0 pw=0 time=180 us)
      1      NESTED LOOPS  (cr=5 pr=0 pw=0 time=95 us)
      1       TABLE ACCESS BY GLOBAL INDEX ROWID CONSULTATION PARTITION: 4 4 (cr=3 pr=0 pw=0 time=53 us)
      1        INDEX UNIQUE SCAN PK55 (cr=2 pr=0 pw=0 time=25 us)(object id 621840)
      1       INDEX UNIQUE SCAN PK_CUSTOMER (cr=2 pr=0 pw=0 time=23 us)(object id 623092)
      2      TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=5 pr=0 pw=0 time=87 us)
      2       INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=0 pw=0 time=36 us)(object id 640073)
      0     TABLE ACCESS BY GLOBAL INDEX ROWID PAYMENT PARTITION: 4 4 (cr=6 pr=2 pw=0 time=40047 us)
      2      INDEX UNIQUE SCAN PK221 (cr=4 pr=1 pw=0 time=26173 us)(object id 625804)
      0   TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: 4 4 (cr=3 pr=0 pw=0 time=98 us)
      1    NESTED LOOPS  (cr=3 pr=0 pw=0 time=67 us)
      0     NESTED LOOPS  (cr=3 pr=0 pw=0 time=50 us)
      0      TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=3 pr=0 pw=0 time=41 us)
      0       INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=0 pw=0 time=30 us)(object id 640073)
      0      TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: 4 4 (cr=0 pr=0 pw=0 time=0 us)
      0       INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=0 pr=0 pw=0 time=0 us)(object id 626747)
      0     INDEX RANGE SCAN PK149 (cr=0 pr=0 pw=0 time=0 us)(object id 624594)
      0   TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: 4 4 (cr=3 pr=0 pw=0 time=65 us)
      1    NESTED LOOPS  (cr=3 pr=0 pw=0 time=41 us)
      0     TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: 4 4 (cr=3 pr=0 pw=0 time=29 us)
      0      INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=0 pw=0 time=17 us)(object id 640073)
      0     INDEX RANGE SCAN PK149 (cr=0 pr=0 pw=0 time=0 us)(object id 624594)


Row source operation for the insert within the stored procedure is - 

Rows     Row Source Operation
-------  ---------------------------------------------------
     76  UNION-ALL  (cr=7061276 pr=20500 pw=0 time=237801018 us)
      5   NESTED LOOPS  (cr=1002517 pr=14726 pw=0 time=22878840 us)
 249969    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=2660 pw=0 time=8068715 us)
 249969     TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=2660 pw=0 time=6818842 us)
      5    TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=12066 pw=0 time=82563450 us)
 249946     INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=2979 pw=0 time=25299546 us)(object id 624182)
     43   FILTER  (cr=1002646 pr=4978 pw=0 time=9114321 us)
     43    NESTED LOOPS  (cr=1002646 pr=4978 pw=0 time=9106412 us)
     43     NESTED LOOPS  (cr=1002517 pr=4976 pw=0 time=9079056 us)
 249969      PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=2020 pw=0 time=7544911 us)
 249969       TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=2020 pw=0 time=6295053 us)
     43      TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=2956 pw=0 time=25792925 us)
 249946       INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=709 pw=0 time=9067326 us)(object id 624182)
     43     TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: ROW LOCATION ROW LOCATION (cr=129 pr=2 pw=0 time=33976 us)
     43      INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=86 pr=1 pw=0 time=17659 us)(object id 626747)
     10   NESTED LOOPS  (cr=10788 pr=95 pw=0 time=246608 us)
     10    NESTED LOOPS  (cr=10748 pr=82 pw=0 time=219904 us)
     10     NESTED LOOPS  (cr=10728 pr=81 pw=0 time=200436 us)
     10      NESTED LOOPS  (cr=10698 pr=77 pw=0 time=177374 us)
     10       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=10668 pr=77 pw=0 time=176564 us)
     10        TABLE ACCESS FULL EVENT PARTITION: KEY KEY (cr=10668 pr=77 pw=0 time=176492 us)
     10       TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: ROW LOCATION ROW LOCATION (cr=30 pr=0 pw=0 time=591 us)
     10        INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=20 pr=0 pw=0 time=297 us)(object id 626747)
     10      TABLE ACCESS BY GLOBAL INDEX ROWID COSTING PARTITION: ROW LOCATION ROW LOCATION (cr=30 pr=4 pw=0 time=39812 us)
     10       INDEX UNIQUE SCAN PK71 (cr=20 pr=3 pw=0 time=31255 us)(object id 622110)
     10     TABLE ACCESS BY INDEX ROWID STAFF (cr=20 pr=1 pw=0 time=19366 us)
     10      INDEX UNIQUE SCAN PK270 (cr=10 pr=0 pw=0 time=174 us)(object id 626291)
     10    TABLE ACCESS BY GLOBAL INDEX ROWID COST_CODE PARTITION: ROW LOCATION ROW LOCATION (cr=40 pr=13 pw=0 time=113347 us)
     10     INDEX UNIQUE SCAN PK64 (cr=30 pr=10 pw=0 time=80951 us)(object id 622275)
      2   PARTITION LIST SINGLE PARTITION: KEY KEY (cr=10668 pr=0 pw=0 time=17373 us)
      2    TABLE ACCESS FULL EVENT PARTITION: KEY KEY (cr=10668 pr=0 pw=0 time=17339 us)
      0   NESTED LOOPS  (cr=1002517 pr=17 pw=0 time=10001614 us)
      0    NESTED LOOPS  (cr=1002517 pr=17 pw=0 time=10001604 us)
 249969     PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=17 pw=0 time=2499783 us)
 249969      TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=17 pw=0 time=999946 us)
      0     TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=0 pw=0 time=8478753 us)
 249946      INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=0 pw=0 time=4377278 us)(object id 624182)
      0    TABLE ACCESS BY GLOBAL INDEX ROWID FE_DEAL PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=0 us)
      0     INDEX UNIQUE SCAN PK153 (cr=0 pr=0 pw=0 time=0 us)(object id 624845)
     11   NESTED LOOPS  (cr=1002517 pr=0 pw=0 time=1005623 us)
 249969    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=3249662 us)
 249969     TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=2249772 us)
     11    TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=0 pw=0 time=8872909 us)
 249946     INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=0 pw=0 time=4567399 us)(object id 624182)
      1   FILTER  (cr=1002524 pr=4 pw=0 time=10878659 us)
      1    NESTED LOOPS  (cr=1002524 pr=4 pw=0 time=1003154 us)
      1     NESTED LOOPS  (cr=1002521 pr=4 pw=0 time=10878529 us)
      1      NESTED LOOPS  (cr=1002517 pr=0 pw=0 time=10840941 us)
 249969       PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=2749725 us)
 249969        TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=1749836 us)
      1       TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=0 pw=0 time=9134199 us)
 249946        INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=0 pw=0 time=4748695 us)(object id 624182)
      1      TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_LEG PARTITION: ROW LOCATION ROW LOCATION (cr=4 pr=4 pw=0 time=37566 us)
      1       INDEX UNIQUE SCAN PK299 (cr=3 pr=3 pw=0 time=26462 us)(object id 627006)
      1     TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: ROW LOCATION ROW LOCATION (cr=3 pr=0 pw=0 time=78 us)
      1      INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=2 pr=0 pw=0 time=36 us)(object id 626747)
      4   NESTED LOOPS  (cr=10700 pr=2 pw=0 time=55784 us)
      4    NESTED LOOPS  (cr=10692 pr=2 pw=0 time=55596 us)
      5     NESTED LOOPS  (cr=10683 pr=0 pw=0 time=23179 us)
      5      PARTITION LIST SINGLE PARTITION: KEY KEY (cr=10668 pr=0 pw=0 time=22771 us)
      5       TABLE ACCESS FULL EVENT PARTITION: KEY KEY (cr=10668 pr=0 pw=0 time=22730 us)
      5      TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: ROW LOCATION ROW LOCATION (cr=15 pr=0 pw=0 time=333 us)
      5       INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=10 pr=0 pw=0 time=155 us)(object id 626747)
      4     TABLE ACCESS BY INDEX ROWID DEAL_STATUS (cr=9 pr=2 pw=0 time=32503 us)
      4      INDEX UNIQUE SCAN PK122 (cr=5 pr=1 pw=0 time=18316 us)(object id 623533)
      4    TABLE ACCESS BY INDEX ROWID STAFF (cr=8 pr=0 pw=0 time=160 us)
      4     INDEX UNIQUE SCAN PK270 (cr=4 pr=0 pw=0 time=81 us)(object id 626291)
      0   TABLE ACCESS BY GLOBAL INDEX ROWID EVENT_DETAIL PARTITION: ROW LOCATION ROW LOCATION (cr=11 pr=7 pw=0 time=95053 us)
      1    NESTED LOOPS  (cr=11 pr=7 pw=0 time=95012 us)
      0     NESTED LOOPS  (cr=11 pr=7 pw=0 time=94995 us)
      1      NESTED LOOPS  (cr=6 pr=4 pw=0 time=56845 us)
      1       TABLE ACCESS BY GLOBAL INDEX ROWID CONSULTATION PARTITION: ROW LOCATION ROW LOCATION (cr=3 pr=3 pw=0 time=44947 us)
      1        INDEX UNIQUE SCAN PK55 (cr=2 pr=2 pw=0 time=28039 us)(object id 621840)
      1       TABLE ACCESS BY GLOBAL INDEX ROWID CUSTOMER PARTITION: ROW LOCATION ROW LOCATION (cr=3 pr=1 pw=0 time=11875 us)
      1        INDEX UNIQUE SCAN PK_CUSTOMER (cr=2 pr=1 pw=0 time=11824 us)(object id 623092)
      0      TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=5 pr=3 pw=0 time=38132 us)
      2       INDEX RANGE SCAN EVENT_KEYVALUE1_TABLENAME (cr=3 pr=3 pw=0 time=38027 us)(object id 640073)
      0     INDEX RANGE SCAN PK149 (cr=0 pr=0 pw=0 time=0 us)(object id 624594)
      0   TABLE ACCESS BY INDEX ROWID DIARY_ENTRIES (cr=3 pr=3 pw=0 time=36633 us)
      1    INDEX RANGE SCAN PK_DIARY_ENTRIES (cr=2 pr=2 pw=0 time=21966 us)(object id 623559)
      0   NESTED LOOPS  (cr=672 pr=665 pw=0 time=1744660 us)
      1    NESTED LOOPS  (cr=5 pr=0 pw=0 time=128 us)
      1     TABLE ACCESS BY GLOBAL INDEX ROWID CONSULTATION PARTITION: ROW LOCATION ROW LOCATION (cr=3 pr=0 pw=0 time=88 us)
      1      INDEX UNIQUE SCAN PK55 (cr=2 pr=0 pw=0 time=38 us)(object id 621840)
      1     INDEX UNIQUE SCAN PK_CUSTOMER (cr=2 pr=0 pw=0 time=21 us)(object id 623092)
      0    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=667 pr=665 pw=0 time=1744515 us)
      0     TABLE ACCESS FULL CONSULTATION_NOTES PARTITION: KEY KEY (cr=667 pr=665 pw=0 time=1744490 us)
      0   SORT UNIQUE (cr=10679 pr=3 pw=0 time=184446 us)
      0    NESTED LOOPS  (cr=10679 pr=3 pw=0 time=184371 us)
      2     NESTED LOOPS  (cr=10673 pr=0 pw=0 time=23554 us)
      1      NESTED LOOPS  (cr=5 pr=0 pw=0 time=125 us)
      1       TABLE ACCESS BY GLOBAL INDEX ROWID CONSULTATION PARTITION: ROW LOCATION ROW LOCATION (cr=3 pr=0 pw=0 time=86 us)
      1        INDEX UNIQUE SCAN PK55 (cr=2 pr=0 pw=0 time=36 us)(object id 621840)
      1       INDEX UNIQUE SCAN PK_CUSTOMER (cr=2 pr=0 pw=0 time=20 us)(object id 623092)
      2      PARTITION LIST SINGLE PARTITION: KEY KEY (cr=10668 pr=0 pw=0 time=23432 us)
      2       TABLE ACCESS FULL EVENT PARTITION: KEY KEY (cr=10668 pr=0 pw=0 time=23406 us)
      0     TABLE ACCESS BY GLOBAL INDEX ROWID PAYMENT PARTITION: ROW LOCATION ROW LOCATION (cr=6 pr=3 pw=0 time=31367 us)
      2      INDEX UNIQUE SCAN PK221 (cr=4 pr=2 pw=0 time=17068 us)(object id 625804)
      0   NESTED LOOPS  (cr=1002517 pr=0 pw=0 time=11120825 us)
      0    NESTED LOOPS  (cr=1002517 pr=0 pw=0 time=11120813 us)
 249969     PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=1999832 us)
 249969      TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=999934 us)
      0     TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=0 pw=0 time=9845510 us)
 249946      INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=0 pw=0 time=5093060 us)(object id 624182)
      0    TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=0 us)
      0     INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=0 pr=0 pw=0 time=0 us)(object id 626747)
      0   NESTED LOOPS  (cr=1002517 pr=0 pw=0 time=10027137 us)
 249969    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=1999825 us)
 249969     TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=999933 us)
      0    TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=0 pw=0 time=8699771 us)
 249946     INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=0 pw=0 time=4499899 us)(object id 624182)


Please tell me what could be done to enhance the performance of the query.

Moreover, how would I reduce the waits...in the stored procedure.

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     15132        0.36         89.96
  db file scattered read                        341        0.08         15.48


There is TABLE ACCESS FULL EVENT_DETAIL within stored procedure but from sqlplus it is not so. What could be the reason ? This is really strange.

Next, how can we reduce db file sequential read and db file scattered read waits. What are the reasons for this ?

Please help me out.

Regards,
Piyush

row source operation

Piyush, July 03, 2007 - 7:48 am UTC

the remaining part
      
2      INDEX UNIQUE SCAN PK221 (cr=4 pr=2 pw=0 time=17068 us)(object id 625804)
      0   NESTED LOOPS  (cr=1002517 pr=0 pw=0 time=11120825 us)
      0    NESTED LOOPS  (cr=1002517 pr=0 pw=0 time=11120813 us)
 249969     PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=1999832 us)
 249969      TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=999934 us)
      0     TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=0 pw=0 time=9845510 us)
 249946      INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=0 pw=0 time=5093060 us)(object id 624182)
      0    TABLE ACCESS BY GLOBAL INDEX ROWID TRAVEL_DEAL PARTITION: ROW LOCATION ROW LOCATION (cr=0 pr=0 pw=0 time=0 us)
      0     INDEX UNIQUE SCAN PK_TRAV_DEAL (cr=0 pr=0 pw=0 time=0 us)(object id 626747)
      0   NESTED LOOPS  (cr=1002517 pr=0 pw=0 time=10027137 us)
 249969    PARTITION LIST SINGLE PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=1999825 us)
 249969     TABLE ACCESS FULL EVENT_DETAIL PARTITION: KEY KEY (cr=2664 pr=0 pw=0 time=999933 us)
      0    TABLE ACCESS BY GLOBAL INDEX ROWID EVENT PARTITION: ROW LOCATION ROW LOCATION (cr=999853 pr=0 pw=0 time=8699771 us)
 249946     INDEX UNIQUE SCAN PK_EVENT (cr=749907 pr=0 pw=0 time=4499899 us)(object id 624182)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                     15132        0.36         89.96
  db file scattered read                        341        0.08         15.48


moreover, i found that there is table scan for event_detail table in the stored procedure but from sqlplus it is not so. This is strange... Please help ?

Secondly, what are db file scattered read and db file sequential read waits and how to reduce them.

tune

A reader, September 16, 2009 - 5:29 pm UTC

Tom:

Would it make a diff in a ref cursor that has

select * from table where col1 in 'AB'

is it better to rewrite as

select * from table where col1 = 'AB'

I ran the stats and i get similar numbers.
shall i change the "in" to "=" if it is one value.
Tom Kyte
September 16, 2009 - 5:47 pm UTC 

ops$tkyte%ORA11GR1> select * from dual where dummy in 'X';

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

<b>   1 - filter("DUMMY"='X')
</b>


we do that already.

I would change in to = just because "in" looks wrong there, but that is all

tune

A reader, September 16, 2009 - 6:08 pm UTC

Tom:

I assume that applies to 9i too.

I use sql plus for windows and i cannot get similar output to yours. Do I need to setup soemthing to see that.


select * from dual where dummy in ('X');

D
-
X

1 row selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
213 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed




Tom Kyte
September 16, 2009 - 7:54 pm UTC 

ops$tkyte%ORA9IR2> explain plan for select * from dual where dummy in 'X';

Explained.

ops$tkyte%ORA9IR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DUAL"."DUMMY"='X')

Note: rule based optimization

14 rows selected.

tune

A reader, September 17, 2009 - 10:37 am UTC 

Tom:

I do not see any values in the table (cpu, time, rows).

SQL> explain plan for select * from dual where dummy in 'X';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  TABLE ACCESS FULL   | DUAL        |       |       |       |
--------------------------------------------------------------------

Note: rule based optimization, 'PLAN_TABLE' is old version

9 rows selected.

Tom Kyte
September 17, 2009 - 1:35 pm UTC

ummm, did you per chance read "the note"

fix your plan table

Beyond the explain plan...

Nick Hurt, January 20, 2010 - 8:24 am UTC

Hi Tom,

Thanks for all the great insights. We have a production and development database enviorments with identical setup both running 9.2. I have an insert statement that takes double as long on the production environment to run. A 10046 trace and tkprof reveals the same execution path. Only difference in index is the clustering factor. Could this be the cause? Snippet from tkprof output below.
Thanks
-----

Dev environment trace
-----
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.09 0.05 0 1200 0 0
Execute 1 239.01 959.28 1210559 8792407 413741 1975420
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 239.10 959.33 1210559 8793607 413741 1975420

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (MI)

Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=8792377 r=1210559 w=225726 time=959246015 us)
1975420 TABLE ACCESS BY INDEX ROWID WH_OBJECT_L (cr=8792008 r=984440 w=0 time=632621300 us)
3951551 NESTED LOOPS (cr=8001428 r=413161 w=0 time=296079073 us)
1976130 TABLE ACCESS FULL WH_POL_AGR (cr=54043 r=54040 w=0 time=13801286 us)
1975420 INDEX RANGE SCAN WOL_SEQ_OBJECT (cr=3956770 r=154409 w=0 time=60375485 us)(object id 189936)


Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
1 LOAD AS SELECT
1975420 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'WH_OBJECT_L'
3951551 NESTED LOOPS
1976130 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'WH_POL_AGR'
1975420 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WOL_SEQ_OBJECT'
(NON-UNIQUE)
0 SORT (AGGREGATE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WOL_MULTI_1'
(NON-UNIQUE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 8 0.00 0.00
db file sequential read 1033119 0.61 664.17
db file scattered read 1698 0.09 10.51
direct path write 34 0.00 0.00
direct path read 5247 0.10 27.96
log file switch completion 12 0.06 0.28
free buffer waits 38 0.11 2.24
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 1202.39 1202.39
************************************************************
Prod environment trace
-----
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.05 0 1035 0 0
Execute 1 288.46 259.06 1481108 9236602 1526668 1975426
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 288.50 259.12 1481108 9237637 1526668 1975426

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61 (MI)

Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=9236572 r=1481108 w=225846 time=259063226 us)
1975426 TABLE ACCESS BY INDEX ROWID WH_OBJECT_L (cr=9109219 r=1305616 w=0 time=2069994718 us)
3951563 NESTED LOOPS (cr=8000512 r=529858 w=0 time=619294159 us)
1976136 TABLE ACCESS FULL WH_POL_AGR (cr=54059 r=54056 w=0 time=18228260 us)
1975426 INDEX RANGE SCAN WOL_SEQ_OBJECT (cr=3956782 r=179172 w=0 time=260168489 us)(object id 182476)


Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE
1 LOAD AS SELECT
1975426 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'WH_OBJECT_L'
3951563 NESTED LOOPS
1976136 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'WH_POL_AGR'
1975426 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WOL_SEQ_OBJECT'
(NON-UNIQUE)
0 SORT (AGGREGATE)
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'WOL_MULTI_1'
(NON-UNIQUE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 32 0.01 0.08
db file sequential read 1303648 0.58 1984.78
db file scattered read 1699 0.12 14.78
direct path write 34 0.00 0.00
direct path read 5642 0.68 33.31
undo segment extension 202 0.00 0.00
buffer busy waits 1 0.00 0.00
log file sync 77 1.02 8.81
async disk IO 254 0.00 0.00
db file single write 2 0.00 0.00
control file parallel write 4 0.00 0.00
rdbms ipc reply 2 0.08 0.15
log file switch completion 32 0.23 1.44
free buffer waits 61 0.09 3.64
log buffer space 18 0.14 0.88
write complete waits 2 0.01 0.01
latch free 3 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2040.26 2040.26
********************************************************************************

Tom Kyte
January 20, 2010 - 11:44 am UTC

sorry, but my reading of this shows production is more then 3 times FASTER than development????


Dev environment trace
-----
call     count       cpu    elapsed       disk      query    current        
rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    239.10     <b>959.33</b>    1210559    8793607     413741     1975420


Prod environment trace
-----
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    288.50     <b>259.12</b>    1481108    9237637    1526668     1975426



so, the insert is 3 times faster, but I do see a big difference in sql net message from client.

which means - your client is taking twice as long - not the database. sql net message from client means the DATABASE waited that long for the CLIENT to tell it to do something. So, your client code is running much slower in prod than development. I cannot fix that - you have to.


A reader, January 16, 2012 - 12:20 pm UTC

Hi Tom,

From last 2 meeting with DBA always ask to reduce DB sequential read for batch process.How can we achieve that ??If it will not do the sequential read then how it is going to get the data .My assumption was that it is the best in batch process.Can you please explain on this ?Sorry if i asked a silly question.
Tom Kyte
January 17, 2012 - 3:16 pm UTC

How can we achieve that ??

drop all indexes :)


db file sequential read is a read from an index to a table in general. What your DBA is trying to say is:

.....
please stop it with the slow by slow (row by row) processing. Please BULK UP your sql - do not do billions of teeny tiny little SQLs - do fewer really big sqls that take advantage of full scans, hash joins and the like.
.....

tons of db file sequential reads in a batch process indicates to me that the developers do lots of slow by slow processing - read a record, read another record, read another record - process tiny bit of data - update a record, repeat - over and over and OVER again.

read the first review/followup above, it says thing as well...

A reader, January 18, 2012 - 11:10 am UTC

Hi Tom,

Thanks alot for your response.

Let say i have 60000 account which need to process in batch.Presently we initialize a class inside which some select query will execute for each account and then do some processing and then save the class which will update the table for each account in loop.

Now you mean to say the select inside class need to be done in bulk rather than account by account and then store in some collection .Based on this collection process and finally bulk update.

Is this correct way tom ??
Tom Kyte
January 18, 2012 - 12:31 pm UTC

I'm saying that

Presently we
initialize a class inside which some select query will execute for each account
and then do some processing and then save the class which will update the
table for each account in loop.

is very very very - the epitome of - procedural slow by slow processing.


a) "a" class
b) "query will execute for each account"
c) do some processing
d) then update the table for an (i changed, same meaning) account
e) goto (a)


so you probably have code that looks - if you unwrapped your "objects" and just put it out linearly - like this:

for x in (select acct-id from somewhere)
loop
     for y in (select something about this account from somewhere else)
     loop
          select something into something else from somewhere for this account;

          select something into something else from somewhere for this account;

          select something into something else from somewhere for this account;

          select something into something else from somewhere for this account;

          select something into something else from somewhere for this account;

          some processing
   
          update a row
     end loop
end loop



there should be lots of joining going on - fewer individual SQLs, one bigger sql that knocks out all but ONE of those loops. The sql should be bulk fetched from - get 100 or 500 accounts at a time. Those N accounts should be processed AND THEN batch updated back into the database

that is, if the process cannot actually be done in sql.

IO Tuning

Ahmad Al-Sallal, March 19, 2012 - 9:52 am UTC

Hi Tom,
1) I've issued the following "simple" query:
select * from dba_objects;
which needed 13.438 Seconds to return the 319,581 Records.
My question is, how can i tune it !!! Network ! Storage ! Server !

2) In any application, i need to return only subset of the results (say 25) and when hit the next page bring the next 25..., how can i implement this? currently we are bounding it through RW (which presents a rownum in the inner view).
And i beleive this is wrong, Am i right ? if yes, what is the best solution :) ?
Tom Kyte
March 19, 2012 - 10:39 am UTC

1) depends, start by tracing it and finding out where it spends its time.


2) http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Query execution time calculation

aliyar, November 08, 2013 - 12:56 pm UTC

Dear Tom,

Thanks a lot for helping us to understand oracle.

could you please clarify the below question.


select * from xxx where c1=10 and c2=20;

lets say xxx contains 1 million rows and above one returns 1000 rows.

i am running above select query at 10.00 AM directly from DB server using SQLPLUS as SYS and it started showing result at 10:10 am. it keeps showing all the results till last row ( in our case 1000th row is last row) and completed at 10:20 am.

so how can calculate the exact time taken by my query to complete. is that 10 minutes since i started getting rows or 20 minutes till getting last row ??

Thanks tom in advance
aliyar
Tom Kyte
November 11, 2013 - 9:34 am UTC

do NOT do things as SYS, stop it. stop it now. sys is special, sys is ours, things work differently for sys. just don't.


it you want to know "from the perspective of the end user, how long did the query run", that would obviously be "20 minutes of elapsed time"

if you want to know how much time was spent in the database running your query - get an ASH report for it and that'll show you db-time. elapsed time minus db-time si the amount of time spent processing the query outside of the database in the application.


you have to define what you mean by "time taken by my query to complete"

A reader, December 26, 2013 - 9:09 pm UTC

good

Query Performance

Waheed Ahmed, January 04, 2014 - 11:04 am UTC 

Hi Tom,

We had fight with our team recently reg an issue between count(*) and count(1) which one gives the quick output.

We see the explain plan and performance are same for both count(*) and count(1) but we see there is difference in output time between count(*) and count(1). Please find the below example.

I have read through all your previuos blogs for the same scenario but we see the time difference. In such case I will come conclusion as count(1) is faster than count(*). 

04:33:08 SQL> select count(*) from S_EMP_CON;

  COUNT(*)
----------
 107586717
Elapsed: 00:13:22.79

04:46:40 SQL> 04:46:40 SQL>
04:55:03 SQL>
04:55:03 SQL> select count(1) from S_EMP_CON;
  COUNT(1)
----------
 107586717
Elapsed: 00:00:07.02

Please help me.

Help in understanding results of DBMS_SQLTUNE

Shimmy, November 10, 2015 - 7:40 pm UTC 

Hi,

I  have an anonymous PL/SQL block, that reads from an external table and processes one record at a time. When it loops through the cursor, one at a time, it does some select, few PL/SQL activities etc....

When I run the following SQL, I am seeing a big "Other Waits". Can you please let me know what "Other Waits" consist of?
 
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id       => '73s3y27wcfstf',
                                       report_level=>'ALL') as report
FROM DUAL;


Global Stats
===========================================================================================
| Elapsed |   Cpu   |    IO    | Concurrency | PL/SQL  |  Other   | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Time(s) | Waits(s) |  Gets  | Reqs | Bytes |
===========================================================================================
|     886 |     873 |     0.07 |        0.00 |    5.10 |       14 |    54M |    2 | 32768 |
===========================================================================================

I know I can improve the performance if I rewrite the SQL to use bulk/change the way it call different SQLs inside the PL/SQL(one at a time).At this time I am not allowed to modify  the SQLs. I just want to analyse
what the most time consuming part of this process is.Also, want to better understand the numbers I am seeing above.

Thank you

Chris Saxon
November 11, 2015 - 1:05 am UTC 

SQL> select name
  2  from   v$event_name
  3  where  wait_class = 'Other'
  4  order by 1;

NAME
----------------------------------------------------------------
ADR block file read
ADR block file write
ADR file lock
AQ Background Master: slave start
AQ master shutdown
AQ master: time mgmt/task cleanup
AQ propagation connection
AQ reload SO release
AQ slave: time mgmt/task cleanup
AQ spill debug idle
AQ:non durable subscriber add or drop
AQPC: new master
ARCH Remote Write
ARCH wait for process death 1
ARCH wait for process start 1
ARCH wait for process start 3
ARCH wait on c/f tx acquire 1
ASM Instance startup
...
...


But before you dive into that...look at the report

886 seconds total time, 873 of which was CPU time. I wouldnt even look at the "Other" section, because its only worth a tiny portion. Tackle the big items first - and that is CPU time.

Looking along the report - there's a very good chance that all that CPU is due to the 54M buffer gets. Shrink that, and your CPU should drop as well.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.