In this site, Tom has been using the tools such as sqlplus autotrace to illustrate concepts many many times.

Tony, Please read Tom's new book Effective Oracle By design as well. It helped me a lot in understanding concepts related to performance tuning.

One has to wait a fortnight to get tom's new book via AMAZON in germany ...

Hi Tom,
Thanks for your views.
Actually, Cary has well acknowledged your runstats.html in relation to "Querying V$data through SQL' (page 55), which is indeed excellent, and of course, you explicitly mentioned under requirements "This test harness works best in a simple, controlled test environment".
What I was referring to - Cary's remarks about o/p of tkprof (before 9i) which simply ignored the "wait event" data (page 61). Don't you think that most of the time performance analysts are called only when there is an issue in the production system, and that too in business critical applications, and (just to make more scarier) in 24X7 applications. As such, diagnostic reliability (which Cary has given 3 marks based upon V$fixed views - page 56) is very very important. Until and unless diagnosis is reliable, how can one fix the problem, and probably I will be more relying on trial-and-error backed by my experience.
Regards,

Tom

I've read your site for some time now, have your first book, use runstats and so on but have seen you recommend Statspack to users on several occasions. Cary, on the other hand, seems to have little truck with Statspack as he doesn't believe that aggregated information is good enough ( well actually he says it makes impossible to determine the true picture and he goes on to present a nice analogy with red rocks and grey rocks...). What's your opinion on Method R without the use of Statspack but with lower level trace information ? At the end of the day, we have to parse that trace file and we cannot necessarily buy the hotsos products. Is the 9i waits information in tkprof sufficient ( I haven't got that far in Cary's book yet to see what he recommends to parse the trace files ) ?

Regards

Hi,

I´m working with bigger tables , and I need to provide performance.
The first think that, how to analsye the order to put the tables in the command from, order and where.
Some people, say to me , that Oracle read , botton to up, is really ?
I will appreciate the you can answer quickly.
Thank very much, in this moment .

Aída

Tom,

As you said without knowing what to tune, what is the most critical business unit to tune (Method-R from Cary) you will not have the target.

Usually global target to tune everything is usually not good enough. You can even tune like that but you are not sure about what will happen with your critical business unit.

From the other side you go and trace your critical part of application. And you found that for example buffer busy waits are the problem.
How are you going to tune that if you do not have the global knowledge what is going on in the database (Statspack report). Even that you need to drill down to find out all parts of applications involved in buffer busy waits or whatever.

It looks to me that everything is valuable.
Cary is not telling you that Statspack reports are bad.
He is saying to me that they are not the only source and the best source.
For him you need to start with the most critical part of the application and use extended trace file.
Good enough, but later on I think you can find many valuable information from Statspack report.

In any case you will probably not tune something to the perfection without knowing bussiness logic.

At the end you need to have global knowledge about the system and also sometimes very narrow statistics to tune to the good level.

At the end Oracle is implementing all these things inside the database with new releases.

Tom, what about x$trace? Any news in 10g.

Regards,
Zoran

Hi Tom,

Happy Holidays!

I am a DBA working with dozens of applications and architectures; from batch to OLTP;VB to Java;Oracle to SQL Server.
Recently, I went through a tuning excercise with a web reporting application moving from SQL Server to Oracle.
The application was migrated with minimal (read none) redesign of core sql. It performed horribly for end users once migrated, and was unusable.
I was able to determine the access paths that had worked in SQL Server did not work correctly for Oracle.
I used massive tracing of the application, generated lots of trace files, looked for the "low hanging fruit", and "tuned" using optimizer_index_caching, optimizer_index_cost_adj, and db_file_multiblock_read_count.
By setting these parameters in an on logon trigger for just the application login, I was able to dramatically alter the performance profile of the application. End users are now usually happy, and application developers and managers are content.
My quandary is this. Have I really addressed the issues that caused performance problems and end user complaints? No. The application continues to use literal SQL, aggregate functions that cause performance headaches (even though the end users don't see it) and is allowed to execute queries returning 100K + rows that end users never review.
I know the design is the root cause, but the performance is "good enough", and only took a few days to "fix", so management is content to leave well enough alone.
Is tuning via parameter changes valid as a tuning process? Or am I simply postponing an inevitable problem by ignoring the bind variables lack, and poor design? If no one complains, should I?

Thank you for your insight.

Excellent overview. Thank you very much!
Where can I send the holiday cards?

I am reading your Effective Oracle by Design and Cary's book on performance tuning. Both are great! Performance tuning through extended SQL Trace level 12 is unbelievable. Great job by Oracle technical folks!

Is there a way to set level 12 at an instance level? Your book as well as Cary's talks about how to set level 12 on a session level, not at instance level. I understand why session level data collection is important, but I also need to analyze SQL trace for the whole instance. Can you please help? Thanks.

Hi Tom

while reviewing this site i cam across the term "INSTRUMENTING" code many a times.I dont have experience with database programming much..But what i understood from insyrumenting code is something like we do in shell scripts.

say
some code
echo $myvar -->Instrumenting nyvar
some other code
echo $myothervar -->Instrumenting myothervar

in production i would just comment out that code..

something similar to dbms_output in ur pl/sql

is my understanding right ?If not could you please elaborate on what you mean by instrumenting.

I also had a look at your other example wherein you ask to type Y instead on N in your browser I did not understand this.

Please bear with my lack of comprehension english is not my first langauge.

regards
Austin

Is the syntax for the "alter system set events" command documented somewhere? I get the feeling it's one of those unsupported voodoo commands. It would be cool to know what else it can do, though. I've seen other words besides 'context' as well, like 'heapdump' and 'errorstack'...what's the difference? I can imagine what heapdump might look like...is errorstack similar to what you see when a PL/SQL program errors out with an unhandled exception? Also, are the various error codes Oracle uses are synonymous with the event numbers? I know you like to say a little knowledge can be a dangerous thing...

OK, fair enough. I did find a few crumbs of information on metalink.

Is there a way in 9i to trace the session created using shared server connection? Thanks.

Hi Tom,

That was helpful...Still I have ended with a view in Oracle 8.1.7,which runs for more than 8 hours in my application.
Can u please suggest some ways of improving its performance..
It involves three table - raats_cust_acct_prior_stat,
raats_cust_acct,raats_finan_actvty.
I pasted the view with this:

CREATE OR REPLACE VIEW RAATS_RPT_805_ACTUAL ( ACCT_STAT_CD,
REFER_SYS_SRC_CD, ACCT_SRV_TYPE_CD, BILL_SPEC_INSTR_CD, OCA_CD,
TOTAL_TYPE_ID, PLACEMENT_DT, TOTAL_SUM ) AS SELECT b.acct_stat_cd,
b.refer_sys_src_cd,
b.acct_srv_type_cd,
translate((nvl(b.bill_spec_instr_cd,' ')),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'*12**********************************'),
decode(b.acct_stat_cd,'H','0000',
b.oca_cd),
13,
to_char(c.payment_effect_dt,'YYYYMM'),
(sum(c.actvty_amt) * -1)
FROM RAATS_CUST_ACCT b,
RAATS_FINAN_ACTVTY c
WHERE
(to_char(c.payment_effect_dt,'YYYYMM')) <=
(to_char(add_months(sysdate,-1),'YYYYMM'))
and
(to_char(c.payment_effect_dt,'YYYYMM')) >=
(to_char(add_months(sysdate,-13),'YYYYMM'))
and
(c.actvty_amt < 0.0
or
c.rtrn_chck_ind = 'Y')
and
c.actvty_type = 'P'
and
c.send_dt > b.acct_stat_send_dt
and
b.refer_sys_src_cd = c.refer_sys_src_cd
and
b.cust_acct_nbr = c.cust_acct_nbr
and
b.acct_stat_cd in ('1','2','3','A','B','C','H')
GROUP BY
b.acct_stat_cd,
b.refer_sys_src_cd,
b.acct_srv_type_cd,
translate((nvl(b.bill_spec_instr_cd,' ')),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'*12**********************************'),
decode(b.acct_stat_cd,'H','0000',
b.oca_cd),
to_char(c.payment_effect_dt,'YYYYMM')
/* */
/* 13- Monthly Actual Pay using raats_cust_acct_prior_stat */
/* raats_finan_actvty */
/* */
UNION ALL
SELECT a.acct_stat_cd,
a.refer_sys_src_cd,
b.acct_srv_type_cd,
translate((nvl(b.bill_spec_instr_cd,' ')),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'*12**********************************'),
decode(a.acct_stat_cd,'H','0000',
a.oca_cd),
13,
to_char(c.payment_effect_dt,'YYYYMM'),
(sum(c.actvty_amt) * -1)
FROM RAATS_CUST_ACCT_PRIOR_STAT a,
RAATS_CUST_ACCT b,
RAATS_FINAN_ACTVTY c
WHERE
(b.refer_sys_src_cd = a.refer_sys_src_cd
and
b.cust_acct_nbr = a.cust_acct_nbr)
and
(to_char(c.payment_effect_dt,'YYYYMM')) <=
(to_char(add_months(sysdate,-1),'YYYYMM'))
and
(to_char(c.payment_effect_dt,'YYYYMM')) >=
(to_char(add_months(sysdate,-13),'YYYYMM'))
and
c.actvty_type = 'P'
and
(c.actvty_amt < 0.0 or c.rtrn_chck_ind = 'Y')
and
c.send_dt > a.acct_stat_send_dt
and
c.send_dt <= a.acct_stat_end_dt
and
a.refer_sys_src_cd = c.refer_sys_src_cd
and
a.cust_acct_nbr = c.cust_acct_nbr
and
a.acct_stat_cd in ('1','2','3','A','B','C','H')
GROUP BY
a.acct_stat_cd,
a.refer_sys_src_cd,
b.acct_srv_type_cd,
translate((nvl(b.bill_spec_instr_cd,' ')),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'*12**********************************'),
decode(a.acct_stat_cd,'H','0000',
a.oca_cd),
to_char(c.payment_effect_dt,'YYYYMM')


First select query takes 15 min retrieves 321 records
Second query takes 48 mins.

Also pasted table details of indexing:

Table Name RAATS_CUST_ACCT_PRIOR_STAT

Index Name Unique/Non Unique Column Position
raats_cust_accr_prior_stat_X1 Unique cust_acct_nbr 1
refer_sys_src_cd 2
acct_stat_cd 3
acct_stat_start_dt 4

raats_cust_accr_prior_stat_X2 Non Unique acct_stat_cd 1
acct_stat_send_dt 2
oca_cd 3

raats_cust_accr_prior_stat_X2A Non Unique refer_sys_seq_nbr 1
refer_sys_create_dt 2

raats_cust_accr_prior_stat_X3 Non Unique retrieve_criter_id 1

raats_cust_accr_prior_stat_X4 Non Unique acct_stat_ack_dt 1

Table Name RAATS_FINAN_ACTIVITY

Index Name Unique/Non Unique Column Position
raats_finan_actvty_X1 Unique cust_acct_nbr 1
refer_sys_src_cd 2
activity_type 3
refer_sys_seq_nbr 4
refer_sys_create_dt 5

raats_finan_actvty_X2 Non Unique send_dt 1
actvty_type 2
finan_actvty_stat 3

Table Name RAATS_CUST_ACCT

Index Name Unique/Non Unique Column Position
raats_cust_acct_uk1 Unique cust_acct_nbr 1
refer_sys_src_cd 2
acct_stat_start 3
acct_stat_cd 4

raats_cust_acct_uk8 Unique aws_acct_nbr 1

raats_cust_acct_X2 Non Unique acct_stat_send_dt 1

raats_cust_acct_X3 Non Unique refer_sys_seq_nbr 1
refer_sys_create_dt 2

raats_cust_acct_X4 Non Unique working_tel_nbr 1

raats_cust_acct_X5 Non Unique refer_sys_src_cd 1
acct_srv_type_cd 2
Bill_spec_instr_cd 3
oca_cd 4

raats_cust_acct_X6 Non Unique acct_stat_cd 1
acct_stat_ack_dt 2
oca_cd 3

raats_cust_acct_X7 Non Unique retrieve_criter_id 1
Tom please tell me if we replace "in" logic with "or"
will it show any improvement in performance..
Also arranging the indexed column in position will it help..
I checked the explain plan of the above view - it is not using the index at all instead scans the full table.

Hopefully waiting for your valuable replies

Thanks & Regards
Anusha.R
(NewbieOracle)

I cannot take a multipage query and "make it go faster"

I can say techniques like this:

                (to_char(c.payment_effect_dt,'YYYYMM')) <=
                        (to_char(add_months(sysdate,-1),'YYYYMM'))
                and
                (to_char(c.payment_effect_dt,'YYYYMM')) >=
                        (to_char(add_months(sysdate,-13),'YYYYMM'))

are bad.  

seems that 

c.payment_effect_dt between add_months(trunc(sysdate,'mm'),-13) 
                        and add_months(trunc(sysdate,'mm'),-1)


could  be done in a fashion that would be "faster" as well as permit indexes if applicable:

ops$tkyte@ORA9IR2> create table t
  2  as
  3  select trunc(sysdate)-rownum dt
  4    from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t select dt+1-1/24/60/60 from t;
 
27928 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select min(dt), max(dt), count(*) from t;
 
MIN(DT)              MAX(DT)                COUNT(*)
-------------------- -------------------- ----------
30-jun-1928 00:00:00 15-dec-2004 23:59:59      55856
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select min(dt), max(dt), count(*)
  2  from
  3  (
  4  select * from t
  5  where to_char(dt,'YYYYMM') <= (to_char(add_months(sysdate,-1),'YYYYMM')
  6    and to_char(dt,'YYYYMM') >= (to_char(add_months(sysdate,-13),'YYYYMM')
  7  )
  8  /
  and to_char(dt,'YYYYMM') >= (to_char(add_months(sysdate,-13),'YYYYMM')
  *
ERROR at line 6:
ORA-00907: missing right parenthesis
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select min(dt), max(dt), count(*)
  2  from
  3  (
  4  select *
  5    from t
  6   where dt >= add_months(trunc(sysdate,'mm'),-13)
  7     and dt < trunc(sysdate,'mm')
  8  )
  9  /
 
MIN(DT)              MAX(DT)                COUNT(*)
-------------------- -------------------- ----------
01-nov-2003 00:00:00 30-nov-2004 23:59:59        792



and perhaps you can look at replacing the union all with an outer join (when I see

select ... from A, B
union all
select ... from A, B, C

it usually means you could do it in a single SELECT without the union all -- using an outer join.  that is for you to investigate. 

Hi Tom,

Your previous ideas are helpful..
When I implemented the below

"a.acct_stat_send_dt between add_months(trunc(sysdate,'mm'),-13)
and add_months(trunc(last_day(sysdate),'dd'),-1) "

in the following query

SELECT
a.acct_stat_cd,
a.refer_sys_src_cd,
a.acct_srv_type_cd,
translate((nvl(a.bill_spec_instr_cd,' ')),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'*12**********************************'),
decode(a.acct_stat_cd,'H','0000',
a.oca_cd),
to_char(a.acct_stat_send_dt,'YYYYMM'),
to_char(a.acct_stat_send_dt,'YYYYMM'),
0,
count(a.cust_acct_nbr)
FROM raats_cust_acct a
WHERE
a.acct_stat_send_dt between add_months(trunc(sysdate,'mm'),-13)
and add_months(trunc(last_day(sysdate),'dd'),-1)
and
a.acct_stat_ack_dt is not null
and
(a.oca_cd is not null
and
a.oca_cd != '0000')
and
a.acct_stat_cd in ('1','2','3','A','B','C','H')
GROUP BY
a.acct_stat_cd,
a.refer_sys_src_cd,
a.acct_srv_type_cd,
translate((nvl(a.bill_spec_instr_cd,' ')),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'*12**********************************'),
decode(a.acct_stat_cd,'H','0000',
a.oca_cd),
to_char(a.acct_stat_send_dt,'YYYYMM'),
to_char(a.acct_stat_send_dt,'YYYYMM')

it keeps on executing & never ends....
Actually I am involved in performance tuning.
Before the query was like
SELECT
a.acct_stat_cd,
a.refer_sys_src_cd,
a.acct_srv_type_cd,
translate((nvl(a.bill_spec_instr_cd,' ')),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'*12**********************************'),
decode(a.acct_stat_cd,'H','0000',
a.oca_cd),
to_char(a.acct_stat_send_dt,'YYYYMM'),
to_char(a.acct_stat_send_dt,'YYYYMM'),
0,
count(a.cust_acct_nbr)
FROM raats_cust_acct a
WHERE
(to_char(a.acct_stat_send_dt,'YYYYMM')) <=
(to_char(add_months(sysdate,-1),'YYYYMM'))
and
(to_char(a.acct_stat_send_dt,'YYYYMM')) >=
(to_char(add_months(sysdate,-13),'YYYYMM'))
and
a.acct_stat_ack_dt is not null
and
(a.oca_cd is not null
and
a.oca_cd != '0000')
and
a.acct_stat_cd in ('1','2','3','A','B','C','H')
GROUP BY
a.acct_stat_cd,
a.refer_sys_src_cd,
a.acct_srv_type_cd,
translate((nvl(a.bill_spec_instr_cd,' ')),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
'*12**********************************'),
decode(a.acct_stat_cd,'H','0000',
a.oca_cd),
to_char(a.acct_stat_send_dt,'YYYYMM'),
to_char(a.acct_stat_send_dt,'YYYYMM')
it executed for 12 mins.

I want to reduce the time of above query to less than
2 mins
Can u please give me an idea to remove the to_char function & not NULL in above query.
To replace with efficient function to query it faster..

Please guide me

Nanda.R(newbie)

Tom

1) This is an interview question I recently faced.

You are given a large package with more than 10000 lines of code and
over 100 procedures and functions. Your task is to identify the areas
where this package suffers from performance issues and to improve it.
How would you approch this problem?

2) The following is a real time situation I had to handel a few months back
before the above interveiw take place.

I was given a large package of about 12000 lines with countless tiny, tiny
procedures and functions that handle a datawarehouse loading operation.My task-
(a) to implement a few business changes
(b) to improve the overall performance of the package (nobody knows if
there is indeed any performance issues exists - the consultant
who had written the package has no more contract with the company!)

Problem (a) was done successfully, although with a lot of pain (since
there were no technical documents, no comments in the code, even
the code alignment itself was worst).

To tackle problem no. (b), I took the following approach.

1) Created a Test schema, objects in it, and loaded a some test data.

2) Manually read through each an every procedures and function
in the order they are executed.

3) Whenever I encountered a complex query/cursor which I doubt
can cause the performance issues, I separately executed them
in sql* plus and see the explain plan output and benchmark
results (sql_trace,tkprof). Then I tried to re-write the query in a
better way.

4) In a very few cases, adding an index (index on foreign key,
fucntion-based index etc) or changing the structure of a few
code-lookup table into IOT improved the performance of that
particular query.

5) But task was such a huge one, that even after putting effort
for about 1 month, I could not complete the task to my
satisfaction and the overall performance of the loading
operation does not improved much.


Anyway, my answer to the interview question was based on my own above
experience and I couldn't support my answer with solid proofs and
confidence. That was the only single question they asked me and didn't
hear from them again.

Then I learned a lot new lessons. Tuning a single SQL query is far more
easier than tuning a whole package- it is not that there are more number
of sql statements in a package, but that it is difficult to correctly locate
those sql statements or fuctions that causes the performance problem. (i) There
may be locking issues during concurrent transactions, (ii) problems when there are
a large number of users suddenly accessing the same resource, (iii) a particular
function (not a query) is too much cpu intensive, (iv) a mysterious query,
among hundres, is not using bind variables, (v) some other query, safely hiding
inside a forest of codes and eating up all the resources, etc. etc, etc.

Could you please tell us a step-by-step, systematic approach to tackle this
problem, given that a database with hundreds of tables, lots of data and a
million codes ? How do I preciesly pin down the problematic code?

Or do you suggest Cary's book? Even then I would like to know your own
approach based on your experience.

Thanks in advance.

regards

Praveen

Tom,

Thanks for your reply.

Yes, I agree with you, 12000 lines of undocumented code!!!
And I had to consume a lot of aspirin before fixing the business logics the company demanded :)

Well, that fixes the business logic...not the performance issues. Tracing out the areas where performance hinders in a huge package, can be done only if you know how to find it. The question is "How to find it?" Setting sql_trace & timed_statistics ON helps for individual sql statements which you know you are going to tune. What about a huge package which contains hundreds of such cursors, dml/ddl statements, complex conditions and loops...? A few years back, I am sure, you too might have asked the same questions to someone, an expert or guru you admired, right?

I am disappointed that you didn't commented about the steps (1..4) I followed for the problem (b), inorder to find out where exactly any performance issues lies in a large package. Did I followed a correct method that is generally followed by everone? Step 2 is the problematic part for me. More clearly, at what step should I use sql_trace, dbms_profiler.

You will understand my concern if you think from my point of view as an aspirant "would-be-Tom-Kyte" (no pune intented...;)

Thanks tom

This question arose when I was busy instrumenting, so I thought I could use the follow-up here; if you think this should be a new question, I'll wait my turn.

In order to being able to instrument my code, I generate a shadow-package from a package spec. In this shadow-package I provide means to store the values of input/output variables and timing info. This way, I can standardize the entries and exits of my procedures; just a simple (standard) call will do.
This works fine for public procedures and functions; I can use user_arguments for that. My question is, can I somehow find the structure of private procedures/functions, or do I need a parser for that.

Frank

Whoops, forgot to mention my version: 9.2.0.6

Frank

PROCEDURE purgeCategoryTrend
( days IN NUMBER :=90,
rowsToRemove IN NUMBER :=1000)
AS
BEGIN

LOOP
delete from ESMDB10.CATEGORY_TREND
where rowid in
(select rowid from
(select rowid, audit_datetime from ESMDB10.CATEGORY_TREND
where audit_datetime < to_date( sysdate - days)
order by audit_datetime)
where rownum <= rowsToRemove);
EXIT WHEN SQL%ROWCOUNT <rowsToRemove;
COMMIT;
END LOOP;

END;

PLEASE SUGGEST

But if you insist on the procedural approach - then the original procedure fails to commit the last batch so I think it should state:

where rownum <= rowsToRemove);
COMMIT;
EXIT WHEN SQL%ROWCOUNT <rowsToRemove;

Hi Tom,

I want to know whether following statements are correct or not.

1. To ensure that partition pruning is done and other conditions are getting applied on the particular partition, the condition which is doing partition pruning, should be first.

2. Oracle internally converts BETWEEN...AND into <= and >=. So to avoid extra work done internally, it is better to write query using <= and >=.

3. More restrictive conditions should be first in where clause. If fewer rows are returned by early conditions of the query, later conditions will have to work on lesser number of rows.

What is your opinion on these statements? Please clarify the reason if you are disagreeing to any statement.

Hello Tom,

Thanks for your explaination.

I am facing a strange problem here and I am a bit confused which method should be followed.

I am trying to tune a Dataware House application. Here we have one Big table (rowsize approx. 1k) and 20+ smaller tables, having 3-4 columns (rowsize max 210, actually one column in each table is Description column having datatype varchar2(200)). These smaller tables have data ranging from 4 to 1500 records, average is 20 records. Actually only 2 tables have more than 1000 records, all other have less than 100 records.

The big table has 430000 records which is outer joined to other smaller tables . I had analyzed all these tables (using DBMS_STATS.gather_table_stats (OWNER, TABLE_NAME) command) and ran the query.

Even after 1 hr, the query kept running.

Then I deleted the stats and gathered using DBMS_STATS.gather_table_stats (OWNER, TABLE_NAME, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE). Now the same query was returning results in 11 mins (approx).

Again I deleted the stats and gathered using DBMS_STATS.gather_table_stats (OWNER, TABLE_NAME, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>DBMS_STATS.DEFAULT_DEGREE,granularity=>'ALL',cascade=>TRUE). Now the same query was returning results in less than 4 mins (approx).

I have tried it many times but every time it’s giving same result.

Now I am confused, which method I should opt for gathering statistics.

Can you please throw some light, why this is behaving like this?

(P.S. I am sorry, but I can not provide you the table scripts for this.)

Hi Tom,

I am sorry I have forgotten to provide Database version. We are using "Oracle9i Enterprise Edition Release 9.2.0.6.0".

Tom,
We worked one a project, which is developed recently and moved to production. As with every new system, we dont have much data in test beds and when the project moved to production, we have large data in the tables. Some of the queries, performing well in test beds, are taking more time in production.
Can you suggest us in these kind of scenarios, if we dont have much data in test beds,how can we be sure that the sqls we write will also work well in production?

Thanks.

Yes tom.I agree. we did generate test data. But we are not able to generate the data whatever is there in production. we could only generate around 20% of test data.
thanks for your response.

Tom,

I was hoping you could help me rewrite a query better. Someone asked me to look at it for them and it's kind of ugly and I'm not sure how to approach it.

select distinct r.r_id, r.version, d.doc_number, r.action_code, hw.identifier, r.group, r.description, m.class, nvl(r.situational,r.planned)
from maintenance m,
req r,
hardware hw,
document d
where not exists (
select null
from transactions t
where t.period = nvl(r.situational,r.planned)
and t.class = m.class
and t.doc_number = d.doc_number
and t.ind = 'N')
and d.doc_type(+) = m.doc_type
and d.doc_number(+) = m.doc_number
and hw.hardware_id = m.hardware_id
and r.version = m.version
and r.r_id = m.r_id
and ( m.doc_type = 'public'
or ( m.doc_number is null
and r.action_code like 'A%'
and r.type in ('EMP', 'MNG')))
and r.status_code = 'ACTIVE'
order by m.class, r.r_id desc;

tkprof shows:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.23 0.23 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 908 95.57 577.69 119079 3472090 0 13605
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 910 95.80 577.93 119079 3472090 0 13605

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 77

Rows Row Source Operation
------- ---------------------------------------------------
13605 SORT UNIQUE
683028 CONCATENATION
22740 FILTER
22740 NESTED LOOPS OUTER
22740 NESTED LOOPS
880 NESTED LOOPS
880 TABLE ACCESS BY INDEX ROWID REQ
1180 INDEX RANGE SCAN REQ#IE2 (object id 39533)
880 TABLE ACCESS BY INDEX ROWID HARDWARE
880 INDEX UNIQUE SCAN HARDWARE#PK (object id 40466)
22740 TABLE ACCESS BY INDEX ROWID MAINTENANCE
33330 INDEX RANGE SCAN MAINTENANCE#PK (object id 39520)
0 TABLE ACCESS BY INDEX ROWID DOCUMENT
0 INDEX UNIQUE SCAN DOCUMENT#PK (object id 39528)
2421 TABLE ACCESS FULL TRANSACTIONS
660288 FILTER
956964 HASH JOIN OUTER
956964 TABLE ACCESS BY INDEX ROWID MAINTENANCE
1573757 NESTED LOOPS
35169 HASH JOIN
3625 TABLE ACCESS FULL HARDWARE
35169 TABLE ACCESS BY INDEX ROWID REQ
35169 INDEX RANGE SCAN REQ#IE4 (object id 39535)
1538587 INDEX RANGE SCAN MAINTENANCE#PK (object id 39520)
19465 TABLE ACCESS FULL DOCUMENT
2421 TABLE ACCESS FULL TRANSACTIONS

Part of the problem is that the transactions table is not related to anything. It's more of a staging table for a batch load, but we still need to compare data in there to tables in the application. If you could just look at the query and see if you can think of a better way to write it, it would be really helpful.

Also, the maintenance table is quite large, almost 3 million rows and it's being scanned twice for 1.5 million rows. That can't be helping, but I don't know what to do about it.

Since you don't know any of the object structures, just go on what the best case scenario would be for indexes etc and i'll deal with that. Thanks very much.

m.doc_type = 'public' = 1429821 out of 2698963 total

(index on (m.doc_number, m.doc_type) that is not being used,
those two are FKs to the document table. An index on doc_type will shave about 2 minutes off, from 7 min to 5, still not great..)

and r.action_code like 'A%'
and r.type in ('EMP', 'MNG')

Is 1157 out of 54588 total. Indexes exists on both, one is being used (REQ#IE2 on r.action_code ).

I know they're not always good, I was just trying to give you an idea of what's there.

The query reuturns:

ORA-01417: a table may be outer joined to at most one other table

at line where t.period(+) = nvl(r.situational,r.planned)

I'm on 9i by the way. (And yes I know, you can't run it so you can't test it ;) Your best guess is all I ask.)

Tom,

Is this at all what you were going for?

select distinct r.r_id, r.version, d.doc_number, r.action_code, hw.identifier, r.group, r.description,
m.class, nvl (r.situational,r.planned)
from req r left outer join transactions t on t.period = nvl (r.situational,r.planned),

maintenance m left outer join transactions t on t.class = m.class,

document d left outer join transactions t on t.doc_number = d.doc_number,

document d left outer join maintenance m on d.doc_type = m.doc_type
and d.doc_number = m.doc_number,

transactions t left outer join transactions t on t.ind = 'N'
where t.period is null
and hw.hardware_id = m.hardware_id
and r.version = m.version
and r.r_id = m.r_id
and ( m.doc_type = 'public'
or ( m.doc_number is null
and r.action_code like 'A%'
and r.type in ('EMP', 'MNG')))
and r.status_code = 'ACTIVE'
order by m.class, r.r_id desc;

I've never written any ANSI sql in my life. That's probably closer to VB than it is executable sql....

It tell me
ERROR at line 1:
ORA-00918: column ambiguously defined

Which has me stumped because I used aliases everywhere.

My apologies, I assumed I did something so obviously wrong it would stick out like a sore thumb.

I can definitely give tables with only necessary columns (they would have about 50 otherwise...)

create table maintenance
(
r_id number(6),
version number(3),
class varchar2(4),
hardware_id number(8),
doc_number varchar2(32),
doc_type varchar2(8)
);
create table document(doc_number varchar2(32), doc_type varchar2(8));
create table req
(
r_id number(6),
version number(3),
action_code varchar2(4),
group_ varchar2(6),
type varchar2(6),
status_code char(2),
planned varchar2(15),
situational varchar2(15),
hardware_id number
);
create table hardware(hardware_id number, identifier varchar2(5));
create table transactions
(
doc_number varchar2(32),
class varchar2(4),
period varchar2(15),
ind char(1)
);

Thanks very much for you help.

Ok I tried this every which way, no luck.

SQL> select distinct r.r_id, r.version, d.doc_number, r.action_code, hw.identifier, r.group_, r.description,
  2                  m.class, nvl (r.situational,r.planned)
  3         from transactions t left outer join req r on (t.period = nvl (r.situational,r.planned))
  4               left outer join maintenance m on (t.class = m.class)
  5               left outer join transactions t on (t.ind = 'N')
  6               left outer join document d on (t.doc_number = d.doc_number),
  7         document d left outer join maintenance m on (d.doc_type = m.doc_type
  8               and d.doc_number = m.doc_number)
  9         where t.period is null
 10            and hw.hardware_id = m.hardware_id
 11            and r.version = m.version
 12            and r.r_id = m.r_id
 13            and (   m.doc_type = 'public'
 14              or (    m.doc_number is null
 15                     and r.action_code like 'A%'
 16                     and r.type in ('EMP', 'MNG')))
 17            and r.status_code = 'ACTIVE'
 18          order by m.class, r.r_id desc;
select distinct r.r_id, r.version, d.doc_number, r.action_code, hw.identifier, r.group_, r.description,
*
ERROR at line 1:
ORA-00918: column ambiguously defined

Hopefully you can tell by my indentation that I used one comma to separate the additional table I need to left outer join from.  You cannot ansi join in the from in addition to using Oracle standard d.doc_type(+) = m.doc_type....in your where clause so I tried to do both in ansi. 

I have since been able to get the above to run by changing the aliases on the tables that are used more than once but it doesn't return any rows so it's still definitely not right....

ops$tkyte@ORA10GR2> select data.*
  2    from (
  3  select distinct r.r_id, r.version, d.doc_number, r.action_code, hw.identifier,
  4         r.group_, m.class, nvl(r.situational,r.planned) sit_planned
  5    from maintenance m,
  6         req r,
  7         hardware hw,
  8         document d
  9   where d.doc_type(+) = m.doc_type
 10     and d.doc_number(+) = m.doc_number
 11     and hw.hardware_id = m.hardware_id
 12     and r.version = m.version
 13     and r.r_id = m.r_id
 14     and ( m.doc_type = 'public'
 15           or
 16           ( m.doc_number is null
 17             and r.action_code like 'A%'
 18             and r.type in ('EMP', 'MNG')
 19           )
 20         )
 21     and r.status_code = 'ACTIVE'
 22         ) DATA,
 23         transactions t
 24   where t.period(+) = data.sit_planned
 25     and t.class(+) = data.class
 26     and t.doc_number(+) = data.doc_number
 27     and t.ind(+) = 'N'
 28     and t.period IS NULL
 29   order by data.class, data.r_id desc;
 
no rows selected
 
 

Thanks Tom. Could you just explain the line you added

and t.period is null

That was not part of the original query I was wondering the purpose of that.

This thread was very helpful but it spurred a question. In Chapter 2 of Effective Oracle by Design you discuss using (or not) indexes. I am trying to tune a view that is used by other views and itself uses two other views that end up using 8 tables, all of which have anywhere from 1 to 8 indexes. This system was inherited by us and it has a lot of performance issues which it should not have as none of the tables are larger than 300000 records. Say I tune this view and it turns out that using less of the indexes is better, how can I then know if it will make things degrade on the myriad other views that use these same indexes without looking at all of them? Or would it be better to just create a new view that is simpler (i.e, not trying to serve so many purposes) thereby bypassing the potential impact of the index issue? Thanks in advance.

I created a new view based on the help you gave me that improved on CPU time, reduced latches and consistent I/O (percents varied from 75% to 90% reduction from original) and it performed extremely well in the SQL environment. But then I replaced the old view that was causing the performance problems with this view in our J2EE frontend. A SQL append statement is used to build the actual SQL to query this view, as the user has options of filtering and sorting the data. And it bogs down again in terms of performance although I do not know if it is as bad as before as I was never given any statistics. Where do I look to find out what is wrong and how do I look? Is there a standard approach that should be used when creating SQL queries in the Java frontend and are we doing it wrong? I have been reading the Oracle Performance Tuning guide, your site and have ordered Millsap and Lewis's books but I find it very hard to extrapolate some of this information to tuning Oracle when you have a J2EE frontend. I don't know much Java and even less J2EE so I am in the dark here. I would appreciate any help you can give me on this. Thanks in advance.

Let me make sure I understand. If the query slows done in the J2EE front end then I should just look at that particular query with whatever filter/sort caused it to slowdown and tune it again in SQLPlus just like I was doing? So there is nothing about the frontend in terms of memory management or something that I don't know to think of that could be causing the performance hit?

I was not aware of turning on tracing in the frontend and as you say the Java developers did not mention it but I will get it set up.

I talked with someone about turning on SQL trace in the Java frontend. They only way they knew to do it was to set up a logon trigger for a particular user where the trigger contains the two typical "alter session set... " statements that get used in SQLPlus. Is that the method you had in mind for turning on tracing or was there another way you had in mind?

The question was asked in good faith. Don't appreciate the sarcasm here.

I work on a batch reporting and batch load system. It is normalized. Not a datawarehouse model. There is talk that ordering data during bulk inserts may reduce index splitting and improve performance. Aparently there is documentation that his helps with DB2. I don't know about oracle.

Here is an example:
**** pseudocode *****
create table myPartitionedTable
( myPk number,
mydate date,
myChar varchar2(10))
partitioned by range (mydate)
partitioned by list (myChar);

We are using local indexes. Does inserting as follows make any sense in Oracle 10g? I know there is a cost to the order. Don't know if this offsets it.

insert into myPartionedTable
select myPk,myDate,myChar
from myStageTable
order by myDate,myChar;

We could be inserting 100,000s of rows at a time.

well, remember - you can only sort the data so many ways (well, one!) and you generally have more than one index (if the indexes are not maintained during the load - and they are b*tree indexes - it won't matter really)

There are reasons to sort the data during a load - clustering.  To have a set of rows that are frequently queried together be physically stored close together on disk.  That can make great sense (it'll make the clustering factor of one of your indexes really "nice", if you always retrieve those rows together - the range scan and table access by index rowid will benefit).

But remember, sorting (clustering) the data one way might negatively impact some other access - it is a trade off.  Something to consider.


But above I said "b*tree", it can affect bitmaps in a different way - their size is definitely affected.  Consider:


ops$tkyte@ORA10GR2> create table t
  2  as
  3  select trunc(rownum/10000) id1,
  4         mod(rownum,100) id2
  5    from (select * from dual connect by level <= 1000000)
  6  /
 
Table created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create bitmap index i1 on t(id1);
 
Index created.
 
ops$tkyte@ORA10GR2> create bitmap index i2 on t(id2);
 
Index created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec show_space('I1', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................              25
Total Blocks............................              32
Total Bytes.............................         262,144
Total MBytes............................               0
Unused Blocks...........................               2
Unused Bytes............................          16,384
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,833
Last Used Block.........................               6
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> exec show_space('I2', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             150
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              93
Unused Bytes............................         761,856
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           3,721
Last Used Block.........................              35
 
PL/SQL procedure successfully completed.


The data is sorted by ID1, ID2 is as scattered as you can get.. The index on ID2 is definitely impacted by that.

But remember - you can only sort the data in one way in general.


 

Thanks tom. Will run some more tests.

1. Would have a smaller percent free on indexes that we are ordering upon insert be useful? These columns should not be updated or deleted.

2. Can you recommend a test to measure the added cost of ordering all our inserts compared to the benefits of selection against the well clustered indexes and the reduced index maintenance? I want to do a cost benefit analysis against our data in our environment.

Thank you for the clarification. Tone or voice does not always come across correctly in the written word. Could you possibly provide me with more guidance as to the steps to follow to set this up? When I passed on your input to the Java developers the only thing they could think of was putting this in the web.xml file but they were going to have to look into it. Is this what you had in mind? If you can tell me what to read or a more detailed set of instructions on how to set things up then I can work on doing it myself but I need more guidance than "coders write code...". Thanks.

Thank you. We are using 10g.

Tom:

We have 174 batch Package Procedures do the data migration from legacy system to oracle db. All procedures we have return codes 0 -for success 8- for warning and 64 -for error.

We would like to make automation of all this 174 batch jobs. but our client not interested to buy autosys or any gui tool to automate this bath jobs using batch job scheduler like autosys

We have already identified each batch job dependences and we are planning to use dbms_job scheduler. What is your advice?
Can oracle have effective way to schedule batch jobs ? can oracle provides batch job scheduler GUI tool, like other auto batch job scheduler (autosys) tools do?

Please advice

Tom,

I have a question regarding partition pruning. We have a global temporary table (table starting with TR_), a partitioned table (hh_advance) and a normal table (HH_ACTUAL_ADVANCES). HH_ADVANCE is partitioned on settlement_date (range partition) and each partition contains data for one day. Our system has data for around 16 months and the big processes work on one settlement_date at a time.

We have a query that takes 7 minutes from sqlplus, however, when it runs from the package, it takes around an hour. I have had a look at the runtime execution plans for both the instances and they show up different plans. I have listed below both the execution plans. Could you explain what could be the reasons for the difference and how should I tune it to run quicker from within the package. Please let me know if you need any more details.

We are using 10.2.0.1 db.

HH_ADVANCE (partitioned) table has been analyzed with 10% sample using dbms_stats.gather_table_stats (global statistics).
HH_ACTUAL_ADVANCES - analyzed using dbms_stats.gather_table_stats with 10% sample data.

Table data volume
~~~~~~~~~~~~~~~~~
HH_ADVANCE = 1 billion records evenly spread across 500 partitions (500 different settlement_dates).
TR_VALID_WORK_4WEEK_DATES_T = 273012 records
HH_ACTUAL_ADVANCE = Around 200 million records

Indexes
~~~~~~~

HH_ADVANCE
~~~~~~~~~~
1. An index on mpan_setdt_uk_id, meter_period_number

HH_ACTUAL_ADVANCES
~~~~~~~~~~~~~~~~~~
1. An index on mpan, meter_register_id, meter_id_serial_number, settlement_date
2. An index on mpan_setdt_uk_id

Query
~~~~~
SELECT CASE
WHEN INSTR(REPLACE (stragg(profile2day_and_4week)
over(PARTITION BY mpan_setdt_uk_id),'T','U'),
'U') > 0 THEN
'U'
ELSE
'V'
END bsc_validation_status,
profile2day_and_4week period_validation_status,
ratio_per_cons,
comp_per_rt_tol, -- consumption period ratio tolerance
SUM(CASE
WHEN profile2day_and_4week = 'U' THEN
meter_period_consumption
ELSE
0
END) over(PARTITION BY mpan_setdt_uk_id) sum_out_tol,
meter_period_consumption,
mpan,
meter_id_serial_no,
meter_register_id,
settlement_date,
tot_cons,
period_tol,
mpan_setdt_uk_id,
meter_period_number,
prev_period_consumption,
hh_advance_id,
hh_advance_header_id,
hh_advance_header_id_det,
four_week_avg,
two_working_avg
FROM (SELECT CASE
WHEN (meter_period_consumption >=
(four_week_avg * 10 / 100) AND
meter_period_consumption <=
(four_week_avg * 200 / 100))
AND four_week_count >= 2 THEN
'V'
WHEN (meter_period_consumption >=
(two_working_avg * 10 / 100) AND
meter_period_consumption <=
(two_working_avg * 200 / 100) AND
two_working_count = 2) THEN
'V'
WHEN (four_week_count < 2 AND two_working_count < 2) THEN
'T'
ELSE
'U' -- go for yearly validation.
END profile2day_and_4week,
meter_period_consumption / tot_cons ratio_per_cons,
CASE
WHEN meter_period_consumption / tot_cons > period_tol THEN
'Y'
ELSE
'N'
END comp_per_rt_tol,
meter_period_consumption,
mpan,
meter_id_serial_no,
meter_register_id,
settlement_date,
tot_cons,
period_tol,
mpan_setdt_uk_id,
meter_period_number,
--Hh_Advance_Header_Id,
hh_advance_id,
hh_advance_header_id,
hh_advance_header_id_det,
prev_period_consumption,
four_week_avg,
two_working_avg
FROM (SELECT AVG(CASE
WHEN hah.prev_date_type = 'D' THEN
hah.meter_period_consumption
ELSE
NULL
END) two_working_avg,
AVG(CASE
WHEN hah.prev_date_type = 'W' THEN
hah.meter_period_consumption
ELSE
NULL
END) four_week_avg,
COUNT(CASE
WHEN hah.prev_date_type = 'D' THEN
hah.meter_period_consumption
ELSE
NULL
END) two_working_count,
COUNT(CASE
WHEN hah.prev_date_type = 'W' THEN
hah.meter_period_consumption
ELSE
NULL
END) four_week_count,
tvd.meter_period_consumption,
lag(tvd.meter_period_consumption,
1,
NULL) over(PARTITION BY tvd.mpan_setdt_uk_id ORDER
BY tvd.meter_period_number) prev_period_consumption,
tvd.mpan,
tvd.meter_id_serial_no,
tvd.meter_register_id,
tvd.settlement_date,
tvd.tot_cons,
tvd.mpan_setdt_uk_id,
tvd.meter_period_number,
tvd.hh_advance_id,
tvd.hh_advance_header_id,
tvd.period_tol,
tvd.hh_advance_header_id_det
FROM tr_valid_for_profile tvd,
(SELECT x.meter_id_serial_no,
x.meter_register_id,
x.settlement_date,
x.mpan_setdt_uk_id_profile,
x.mpan_setdt_uk_id,
ha.meter_period_number,
ha.meter_period_consumption,
x.mpan,
x.prev_date_type
FROM (SELECT /*+ index(haa) */ haa.meter_id_serial_no,
haa.meter_register_id,
haa.settlement_date,
tvpd.mpan_setdt_uk_id mpan_setdt_uk_id_profile,
haa.mpan_setdt_uk_id,
haa.mpan,
tvpd.prev_date_type FROM hh_actual_advances haa,
tr_valid_work_4week_dates tvpd
WHERE haa.settlement_date = tvpd.valid_prev_date
AND haa.meter_id_serial_no =tvpd.meter_id_serial_no
AND haa.mpan = tvpd.mpan
AND haa.meter_register_id =tvpd.meter_register_id
) x,
hh_advance ha
WHERE x.mpan_setdt_uk_id = ha.mpan_setdt_uk_id
AND ha.settlement_date = x.settlement_date) hah
WHERE tvd.mpan = hah.mpan(+)
AND tvd.meter_id_serial_no = hah.meter_id_serial_no(+)
AND tvd.meter_register_id = hah.meter_register_id(+)
AND tvd.mpan_setdt_uk_id =
hah.mpan_setdt_uk_id_profile(+)
AND tvd.meter_period_number =
hah.meter_period_number(+)
GROUP BY tvd.meter_period_consumption,
tvd.mpan,
tvd.meter_id_serial_no,
tvd.meter_register_id,
tvd.settlement_date,
tvd.tot_cons,
tvd.period_tol,
tvd.mpan_setdt_uk_id,
tvd.meter_period_number,
tvd.hh_advance_header_id,
tvd.hh_advance_id,
tvd.hh_advance_header_id_det) t) t1
/

EXECUTION PLAN WHEN RUN FROM SQL*PLUS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan hash value: 4274381546

------------------------------------------------------------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pst
art| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | | | | 576K(100)| |
| |
| 1 | WINDOW BUFFER | | 2197K| 423M| | 576K (1)| 02:14:26 |
| |
| 2 | VIEW | | 2197K| 423M| | 576K (1)| 02:14:26 |
| |
| 3 | WINDOW BUFFER | | 2197K| 186M| | 576K (1)| 02:14:26 |
| |
| 4 | SORT GROUP BY | | 2197K| 186M| 454M| 576K (1)| 02:14:26 |
| |
|* 5 | HASH JOIN RIGHT OUTER | | 2197K| 186M| | 547K (1)| 02:07:49 |
| |
| 6 | VIEW | | 1 | 32 | | 544K (1)| 02:07:07 |
| |
|* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| HH_ADVANCE | 1 | 20 | | 50 (0)| 00:00:01 | ROW
L | ROW L |
| 8 | NESTED LOOPS | | 1 | 85 | | 544K (1)| 02:07:07 |
| |
| 9 | NESTED LOOPS | | 1 | 65 | | 544K (1)| 02:07:06 |
| |
| 10 | TABLE ACCESS FULL | TR_VALID_WORK_4WEEK_DATES_T | 271K| 8762K| | 233 (4)| 00:00:04 |
| |
| 11 | TABLE ACCESS BY INDEX ROWID | HH_ACTUAL_ADVANCES | 1 | 32 | | 2 (0)| 00:00:01 |
| |
|* 12 | INDEX RANGE SCAN | HAA_MPMIMPMS_IX | 1 | | | 2 (0)| 00:00:01 |
| |
| 13 | PARTITION HASH SINGLE | | 48 | | | 3 (0)| 00:00:01 |
1 | 1 |
|* 14 | INDEX RANGE SCAN | HH_ADVANCE_MP_SET_PRD_NO_IX | 48 | | | 3 (0)| 00:00:01 |
1 | 1 |
| 15 | TABLE ACCESS FULL | TR_VALID_FOR_PROFILE_T | 2197K| 119M| | 2987 (4)| 00:00:42 |
| |
------------------------------------------------------------------------------------------------------------------------------------
------------

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

5 - access("TVD"."MPAN"="HAH"."MPAN" AND "TVD"."METER_ID_SERIAL_NO"="HAH"."METER_ID_SERIAL_NO" AND
"TVD"."METER_REGISTER_ID"="HAH"."METER_REGISTER_ID" AND "TVD"."MPAN_SETDT_UK_ID"="HAH"."MPAN_SETDT_UK_ID_PROFILE" AND
"TVD"."METER_PERIOD_NUMBER"="HAH"."METER_PERIOD_NUMBER")
7 - filter("HA"."SETTLEMENT_DATE"="HAA"."SETTLEMENT_DATE")
12 - access("HAA"."MPAN"="TVPD"."MPAN" AND "HAA"."METER_REGISTER_ID"="TVPD"."METER_REGISTER_ID" AND
"HAA"."METER_ID_SERIAL_NO"="TVPD"."METER_ID_SERIAL_NO" AND "HAA"."SETTLEMENT_DATE"="TVPD"."VALID_PREV_DATE")
14 - access("HAA"."MPAN_SETDT_UK_ID"="HA"."MPAN_SETDT_UK_ID")


EXECUTION PLAN WHEN RUN FROM PACKAGE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Plan hash value: 3787556004

------------------------------------------------------------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| P
stop |
------------------------------------------------------------------------------------------------------------------------------------
------
| 0 | INSERT STATEMENT | | | | | 4144K(100)| | |
|
| 1 | MULTI-TABLE INSERT | | | | | | | |
|
| 2 | VIEW | | 1 | 211 | | 4144K (3)| 16:07:05 | |
|
| 3 | WINDOW BUFFER | | 1 | 202 | | 4144K (3)| 16:07:05 | |
|
| 4 | VIEW | | 1 | 202 | | 4144K (3)| 16:07:05 | |
|
| 5 | WINDOW BUFFER | | 1 | 177 | | 4144K (3)| 16:07:05 | |
|
| 6 | SORT GROUP BY | | 1 | 177 | | 4144K (3)| 16:07:05 | |
|
|* 7 | HASH JOIN RIGHT OUTER | | 128M| 21G| 31M| 4125K (3)| 16:02:44 | |
|
| 8 | VIEW | | 633K| 24M| | 4096K (3)| 15:55:58 | |
|
|* 9 | HASH JOIN | | 633K| 61M| 56M| 4096K (3)| 15:55:58 | |
|
| 10 | TABLE ACCESS BY INDEX ROWID| HH_ACTUAL_ADVANCES | 1 | 32 | | 3 (0)| 00:00:01 | |
|
| 11 | NESTED LOOPS | | 633K| 48M| | 1273K (1)| 04:57:05 | |
|
| 12 | TABLE ACCESS FULL | TR_VALID_WORK_4WEEK_DATES | 633K| 29M| | 466 (5)| 00:00:07 | |
|
|* 13 | INDEX RANGE SCAN | HAA_MPMIMPMS_IX | 1 | | | 2 (0)| 00:00:01 | |
|
| 14 | PARTITION RANGE ALL | | 1043M| 19G| | 1435K (4)| 05:35:01 | 1 |
517 |
| 15 | TABLE ACCESS FULL | HH_ADVANCE | 1043M| 19G| | 1435K (4)| 05:35:01 | 1 |
517 |
| 16 | TABLE ACCESS FULL | TR_VALID_FOR_PROFILE | 3329K| 435M| | 5925 (3)| 00:01:23 | |
|
------------------------------------------------------------------------------------------------------------------------------------
------

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

7 - access("TVD"."MPAN"="HAH"."MPAN" AND "TVD"."METER_ID_SERIAL_NO"="HAH"."METER_ID_SERIAL_NO" AND
"TVD"."METER_REGISTER_ID"="HAH"."METER_REGISTER_ID" AND "TVD"."MPAN_SETDT_UK_ID"="HAH"."MPAN_SETDT_UK_ID_PROFILE" AND
"TVD"."METER_PERIOD_NUMBER"="HAH"."METER_PERIOD_NUMBER")
9 - access("HAA"."MPAN_SETDT_UK_ID"="HA"."MPAN_SETDT_UK_ID" AND "HA"."SETTLEMENT_DATE"="HAA"."SETTLEMENT_DATE")
13 - access("HAA"."MPAN"="TVPD"."MPAN" AND "HAA"."METER_REGISTER_ID"="TVPD"."METER_REGISTER_ID" AND
"HAA"."METER_ID_SERIAL_NO"="TVPD"."METER_ID_SERIAL_NO" AND "HAA"."SETTLEMENT_DATE"="TVPD"."VALID_PREV_DATE")

Note
-----
- dynamic sampling used for this statement

Thanks very much in advance.

Paresh

Tom,

You are right that the execution plans I sent were for different queries (SELECT and INSERT). I have listed below the actual query and the execution plan when run from SQL*Plus. The execution plan from within the package is same as sent before. Note that the query took only 7 minutes when I ran from SQL*Plus.

1. Please explain the reason for differences?

2. How to tune it and what should be the approach?

3. Why it doesn't do the partition pruning on HH_ADVANCE table? I guess Oracle doesn't know anything about the data from subquery x, hence it can't decide on partition pruning. However, I tried to use an inline view for HH_ADVANCE table (where it joins to inline query x) and it still doesn't do partition pruning. The inline view I used was select * from hh_advance a where a.settlement_date in (select distinct b.valid_prev_date from TR_VALID_WORK_4WEEK_DATES_T b). There is only one date in TR_VALID_WORK_4WEEK_DATES_T table. I am trying to get Oracle to use one or few partitions as know that the process only works on handful of partitions at a time.

Thanks very much for your time to look into this. This is driving me mad.

INSERT Query
~~~~~~~~~~~~

INSERT ALL
WHEN bsc_validation_status = 'V' THEN INTO TR_VALID_FOR_PRODUCTION
(hh_advance_id,
bsc_validation_status,
mpan_setdt_uk_id,
meter_period_consumption,
meter_period_number,
period_validation_status,
hh_advance_header_id,
hh_advance_header_id_det)
VALUES
(hh_advance_id,
bsc_validation_status,
mpan_setdt_uk_id,
meter_period_consumption,
meter_period_number,
'V',
hh_advance_header_id,
hh_advance_header_id_det)
ELSE INTO TR_VALID_PROFILE
(ratio_per_cons,
comp_per_rt_tol, -- consumption period ratio tolerance
sum_out_tol,
MPAN,
meter_id_serial_no,
meter_register_id,
settlement_date,
tot_cons,
period_tol,
hh_advance_id,
mpan_setdt_uk_id,
meter_period_consumption,
meter_period_number,
hh_advance_header_id,
hh_advance_header_id_det,
prev_period_consumption,
four_week_avg,
two_working_avg,
period_validation_status)
VALUES
(ratio_per_cons,
comp_per_rt_tol, -- consumption period ratio tolerance
sum_out_tol,
MPAN,
meter_id_serial_no,
meter_register_id,
settlement_date,
tot_cons,
period_tol,
hh_advance_id,
mpan_setdt_uk_id,
meter_period_consumption,
meter_period_number,
hh_advance_header_id,
hh_advance_header_id_det,
prev_period_consumption,
four_week_avg,
two_working_avg,
period_validation_status)
(SELECT CASE
WHEN INSTR(REPLACE (stragg(profile2day_and_4week)
over(PARTITION BY mpan_setdt_uk_id),'T','U'),
'U') > 0 THEN
'U'
ELSE
'V'
END bsc_validation_status,
profile2day_and_4week period_validation_status,
ratio_per_cons,
comp_per_rt_tol, -- consumption period ratio tolerance
SUM(CASE
WHEN profile2day_and_4week = 'U' THEN
meter_period_consumption
ELSE
0
END) over(PARTITION BY mpan_setdt_uk_id) sum_out_tol,
meter_period_consumption,
MPAN,
meter_id_serial_no,
meter_register_id,
settlement_date,
tot_cons,
period_tol,
mpan_setdt_uk_id,
meter_period_number,
prev_period_consumption,
hh_advance_id,
hh_advance_header_id,
hh_advance_header_id_det,
four_week_avg,
two_working_avg
FROM (SELECT CASE
WHEN (meter_period_consumption >=
(four_week_avg * 20 / 100) AND
meter_period_consumption <=
(four_week_avg * 200 / 100))
AND four_week_count >= 2 THEN
'V'
WHEN (meter_period_consumption >=
(two_working_avg * 20 / 100) AND
meter_period_consumption <=
(two_working_avg * 200 / 100) AND
two_working_count = 2) THEN
'V'
WHEN (four_week_count < 2 AND two_working_count < 2) THEN
'T'
ELSE
'U' -- go for yearly validation.
END profile2day_and_4week,
meter_period_consumption / tot_cons ratio_per_cons,
CASE
WHEN meter_period_consumption / tot_cons > period_tol THEN
'Y'
ELSE
'N'
END comp_per_rt_tol,
meter_period_consumption,
MPAN,
meter_id_serial_no,
meter_register_id,
settlement_date,
tot_cons,
period_tol,
mpan_setdt_uk_id,
meter_period_number,
--Hh_Advance_Header_Id,
hh_advance_id,
hh_advance_header_id,
hh_advance_header_id_det,
prev_period_consumption,
four_week_avg,
two_working_avg
FROM (SELECT AVG(CASE
WHEN hah.prev_date_type = 'D' THEN
hah.meter_period_consumption
ELSE
NULL
END) two_working_avg,
AVG(CASE
WHEN hah.prev_date_type = 'W' THEN
hah.meter_period_consumption
ELSE
NULL
END) four_week_avg,
COUNT(CASE
WHEN hah.prev_date_type = 'D' THEN
hah.meter_period_consumption
ELSE
NULL
END) two_working_count,
COUNT(CASE
WHEN hah.prev_date_type = 'W' THEN
hah.meter_period_consumption
ELSE
NULL
END) four_week_count,
tvd.meter_period_consumption,
lag(tvd.meter_period_consumption,
1,
NULL) over(PARTITION BY tvd.mpan_setdt_uk_id ORDER BY tvd.meter_period_number) prev_period_consumption,
tvd.MPAN,
tvd.meter_id_serial_no,
tvd.meter_register_id,
tvd.settlement_date,
tvd.tot_cons,
tvd.mpan_setdt_uk_id,
tvd.meter_period_number,
tvd.hh_advance_id,
tvd.hh_advance_header_id,
tvd.period_tol,
tvd.hh_advance_header_id_det
FROM TR_VALID_FOR_PROFILE_T tvd,
(SELECT x.meter_id_serial_no,
x.meter_register_id,
x.settlement_date,
x.mpan_setdt_uk_id_profile,
x.mpan_setdt_uk_id,
ha.meter_period_number,
ha.meter_period_consumption,
x.MPAN,
x.prev_date_type
FROM (SELECT /*+ index(haa) */ haa.meter_id_serial_no,
haa.meter_register_id,
haa.settlement_date,
tvpd.mpan_setdt_uk_id mpan_setdt_uk_id_profile,
haa.mpan_setdt_uk_id,
haa.MPAN,
tvpd.prev_date_type FROM HH_ACTUAL_ADVANCES haa,
TR_VALID_WORK_4WEEK_DATES_T tvpd
WHERE haa.settlement_date = tvpd.valid_prev_date
AND haa.meter_id_serial_no =tvpd.meter_id_serial_no
AND haa.MPAN = tvpd.MPAN
AND haa.meter_register_id =tvpd.meter_register_id
) x,
HH_ADVANCE ha
WHERE x.mpan_setdt_uk_id = ha.mpan_setdt_uk_id
AND ha.settlement_date = x.settlement_date) hah
WHERE tvd.MPAN = hah.MPAN(+)
AND tvd.meter_id_serial_no = hah.meter_id_serial_no(+)
AND tvd.meter_register_id = hah.meter_register_id(+)
AND tvd.mpan_setdt_uk_id =
hah.mpan_setdt_uk_id_profile(+)
AND tvd.meter_period_number =
hah.meter_period_number(+)
GROUP BY tvd.meter_period_consumption,
tvd.MPAN,
tvd.meter_id_serial_no,
tvd.meter_register_id,
tvd.settlement_date,
tvd.tot_cons,
tvd.period_tol,
tvd.mpan_setdt_uk_id,
tvd.meter_period_number,
tvd.hh_advance_header_id,
tvd.hh_advance_id,
tvd.hh_advance_header_id_det) T) t1)
/


Execution plan from SQL*Plus
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Plan hash value: 37295191

------------------------------------------------------------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | P
start| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
--------------
| 0 | INSERT STATEMENT | | | | | 576K(100)| |
| |
| 1 | MULTI-TABLE INSERT | | | | | | |
| |
| 2 | VIEW | | 2197K| 442M| | 576K (1)| 02:14:26 |
| |
| 3 | WINDOW BUFFER | | 2197K| 423M| | 576K (1)| 02:14:26 |
| |
| 4 | VIEW | | 2197K| 423M| | 576K (1)| 02:14:26 |
| |
| 5 | WINDOW BUFFER | | 2197K| 186M| | 576K (1)| 02:14:26 |
| |
| 6 | SORT GROUP BY | | 2197K| 186M| 454M| 576K (1)| 02:14:26 |
| |
|* 7 | HASH JOIN RIGHT OUTER | | 2197K| 186M| | 547K (1)| 02:07:49 |
| |
| 8 | VIEW | | 1 | 32 | | 544K (1)| 02:07:07 |
| |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| HH_ADVANCE | 1 | 20 | | 50 (0)| 00:00:01 | R
OW L | ROW L |
| 10 | NESTED LOOPS | | 1 | 85 | | 544K (1)| 02:07:07 |
| |
| 11 | NESTED LOOPS | | 1 | 65 | | 544K (1)| 02:07:06 |
| |
| 12 | TABLE ACCESS FULL | TR_VALID_WORK_4WEEK_DATES_T | 271K| 8762K| | 233 (4)| 00:00:04 |
| |
| 13 | TABLE ACCESS BY INDEX ROWID | HH_ACTUAL_ADVANCES | 1 | 32 | | 2 (0)| 00:00:01 |
| |
|* 14 | INDEX RANGE SCAN | HAA_MPMIMPMS_IX | 1 | | | 2 (0)| 00:00:01 |
| |
| 15 | PARTITION HASH SINGLE | | 48 | | | 3 (0)| 00:00:01 |
1 | 1 |
|* 16 | INDEX RANGE SCAN | HH_ADVANCE_MP_SET_PRD_NO_IX | 48 | | | 3 (0)| 00:00:01 |
1 | 1 |
| 17 | TABLE ACCESS FULL | TR_VALID_FOR_PROFILE_T | 2197K| 119M| | 2987 (4)| 00:00:42 |
| |
------------------------------------------------------------------------------------------------------------------------------------
--------------

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

7 - access("TVD"."MPAN"="HAH"."MPAN" AND "TVD"."METER_ID_SERIAL_NO"="HAH"."METER_ID_SERIAL_NO" AND
"TVD"."METER_REGISTER_ID"="HAH"."METER_REGISTER_ID" AND "TVD"."MPAN_SETDT_UK_ID"="HAH"."MPAN_SETDT_UK_ID_PROFILE" AND
"TVD"."METER_PERIOD_NUMBER"="HAH"."METER_PERIOD_NUMBER")
9 - filter("HA"."SETTLEMENT_DATE"="HAA"."SETTLEMENT_DATE")
14 - access("HAA"."MPAN"="TVPD"."MPAN" AND "HAA"."METER_REGISTER_ID"="TVPD"."METER_REGISTER_ID" AND
"HAA"."METER_ID_SERIAL_NO"="TVPD"."METER_ID_SERIAL_NO" AND "HAA"."SETTLEMENT_DATE"="TVPD"."VALID_PREV_DATE")
16 - access("HAA"."MPAN_SETDT_UK_ID"="HA"."MPAN_SETDT_UK_ID")


Best Regards

Paresh

Hi Tom,

I must have not explained you correctly.

In my previous note (second note) I gave you the correct query (Multi-INSERT query) and the corresponding execution plan when it was run from SQL*Plus. My original note (first note) had two execution plans, one for SELECT (which was wrong, so ignore it) and second one for Multi-INSERT query (even though I had listed the wrong query.

So, please consider a) the second execution plan from the first note (Multi-INSERT execution plan which was from the package) and b) the execution plan from the second note which was from SQL*Plus.

I do not want to create more confusion. I understand this was because of my mistake at first place.

1) Again reason for the difference (if I have managed to explain you to compare the correct execution plans)?

2) Again how to tune it and the approach to tune such queries?

3) Again same question as before about partition pruning. I understand that the query was using GLOBAL HASH PARTITOINED INDEX. I would prefer it to use the PARTITION TABLE as all the data from the required "few" partitions needed to be accessed (between 1 and 5 number of partitions required out of total number of 500'ish partitions). Each partition contains around 2.2 million records and index access could be very expensive.

Thanks,

Paresh

Hi Tom,

You are just genius, you have spotted things which I am not able to even after knowing that I used hard coded literals in the sqlplus. How did you spot that I used hard coded literals?

Ok, I amended my sqlplus query to use bind variables this time and got exactly the same runtime execution plan as before.

Regarding the difference in execution plans between sqlplus and plsql blocks, the main difference is that a full table scan of HH_ADVANCE table is made from plsql block (PARTITION RANGE ALL with PSTART/PSTOP showing up as first and last partition in the system). From sqlplus, it is using GLOBAL HASH PARTITIONED INDEX on HH_ADVANCE table. Full table scan on a billion record table is the main reason for bad performance of this query (I believe).

In addition to my queries listed in previous notes, I am struggling to understand how to get Oracle to do partition elimination (knowing that certain processes uses data from only few partitions at a time).

I hope I am clear this time.

Thanks,

Paresh

Yeah, partition elimination is happening but on GLOBAL HASH partitioned index. I don't know if index scan is better if it accesses around 5 million records from partitioned table.

1. Is it advisable to use index in such case?

2. I thought PARTITION ITERATOR SCAN would be better than global index partitioned scan. There must be a reason for Oracle not to do table partition elimination. I would like to understand so that I can make Oracle to use table partitions by rewriting query or any other ways that you suggest.

If you think I am missing something here, please explain in detail.

Thanks,

Paresh

Hi Tom,

1. How to configure parameter file in 10g above req. ?
2. What are all new parameters i have to add ?
3. how to configure my 10g database for best performance ?
4. Network load balancing ?
5. What are all the new features in 10g that increase performance??

Thanks,
Ravi

Hi Tom,

I was trying to find some information about alter session set events, this was the closest thread. Above there is mention of the errorstack clause, were you and a reader said most set events are undocumented voodoo. We are working with support to identify a problem from a java program and they told use to use

alter session set events '1460 trace name errorstack level 3'

So I was wondering what that does. We are getting errors that we can't track down, that it what we are working with support about. Thanks.

Tom,

Could you provide a script to check the runtime execution plan for a session running a query on 9.2.0.4 database (other than tkprof)? Like in 10g, we can use dbms_xplan. Please provide the script for 9.2.0.4 database as I want to check the runtime execution plan for a query that takes 12 odd hours to run and we killed it eventually.

Thanks

Tom,

Please could you reply to the above query on runtime explain plan for 9.2.0.4 database?

Thanks

Hi Tom,

In one of your followups above, you have said

'and perhaps you can look at replacing the union all with an outer join (when I see

select ... from A, B
union all
select ... from A, B, C

it usually means you could do it in a single SELECT without the union all -- using an outer join.
that is for you to investigate.
'

Are you saying that an outer join perfoms better than a union all query?
Thanks

Hi Mark,
Can you help me to identify performance issue in 10g while in 9i all reports are working fine with the same hardware and same machine, I tried to upgrade that setup to latest oracle 10g edition also but the problem remain same.
you can refer the sr no. 6324369.992 for details.

Hi Tom,
Is it not relative question for this forum ?
Thank You,
Pankaj

Sorry Tom I wrongly mentioned mark,

Tom

We want to write an outsourcing tuning contract, what skills do you recommend


Thanks

Hello Tom,

Can you please recommend one of yours or any other author's book for performance tuning.


Thanks

Hi Tom,

Firstly i set db_cache_size is big enough. after first executing the statement: select * from nation; i list the content of default buffer cache and got:

Number of blocks in cache: 16 total number of blocks: 32

then i execute the above statement several times, and still got the same result--
Number of blocks in cache : 16 total number of blocks: 32

the size of table nation is less than 1M, so i think this table should be entirely kept in default buffer cache.

BTW, another table still can get free block from default buffer cache. so not because of not enough buffer cache.

Best regards

Subject: Performance Tuning Instrumentation with CLIENT IDENTIFIER / MONITOR

Subject: Performance Tuning Instrumentation with CLIENT IDENTIFIER / DBMS_MONITOR

Hi Tom,

What should be a good, sound and robust practice as far as instrumentation with CLIENT_ID + DBMS_MONITOR - please see my specific questions at the bottom of this document.

Brief description of the processing environment:

¿ B2B interface receives an XML message
¿ External format of this XML is translated into an internal XML format
¿ Several web services are managed by one JAVA WS control process server
¿ The above JAVA WS control proc has many threads however each B2B request runs in a single thread end-to-end
¿ There are 5 distinct JAVA WS processes that make JDBC calls to Oracle 10g


Business response time allows 4 sec end-to-end. Current application average proc time is between 2-3 seconds. However there are spikes as high as 13 sec.

These performance problems need to be resolved as they are too frequent: 1 per minute and sometimes 2 per minute. It costs business forgone revenue.


The current code is NOT INSTRUMENTED at all.

There are 2 main environments:
1. PRE_PROD where all B2B requests are simulated by Mercury Load Runner that can submit an XML request = just one or a series of requests run back to back or a series of requests run concurrently from several sessions
2. PROD where all B2B requests come from Internet Exchange.

There is no user intervention in this application. B2B request comes in; is detected; gets processed ASAP; and finally resulting XML gets sent back to the Exchange.


First Step:

I have asked for an uninterrupted Mercury LR simulated run on PRE_PROD for a series of requests ¿ as simple back-to-back (NOT concurrent).

I have enabled Extended SQL Trace for the entire database using DBMS_MONITOR.

The SQL Trace was done in the evening yesterday ¿ after PRE_PROD was used by many testers (functional tests etc) ¿ database as well as WS were all still ¿warm¿.

Within a few minutes DBMS_MONITOR generated 623 trace files. Brief peek into those showed that there were other jobs running ¿ AWR was one of those + possibly other scheduled maintenance jobs and/or OEM etc.

Analysis of these trace files will need to serve as ¿getting a feel for B2B App.¿, rather than to derive specific execution profiles.


SECOND STEP:

THIS IS IMPORTANT.
I want to propose and ask client¿s developers to INSTRUMENT the code.
Assuming that I get a buy-in + OK and a developer assigned for a few hours ¿ I may only get ONE SHOT AT IT.


Proposed solution:

I create a TRC_CTL table and request that at every Web service an instrumented JAVA code issues an INSERT statement to this table.

I write a trigger that:
¿ Checks for CLIENT_ID whether it exists (is it possible?)
¿ I record a time-stamp for this module entry or exit point
¿ If the CLIENT_ID has not been set ¿ I generate a unique ID and record it
¿ I use DBMS_SESSION.SET_CLIENT_IDENTIFIER
¿ I execute DBMS_MONITOR specifying this CLIENT_ID


My questions are:

1. Do I need to set CONTEXT as well ¿ if so ¿ HOW?
2. Will my CLIENT_ID persist from one JAVA Web Server to the next each calling Oracle to execute different SQL ¿ however ALL of these WS constitute one single business (B2B) request that needs tuning?
3. What should I expect to happen when either Mercury Load Runner (or PROD DB) runs multiple concurrent B2B requests for quote?
4. Could each single request be identified by a new CLIENT_ID and later processed by TRCSESS?
5. Could I combine ALL these concurrent requests (with different CLIENT_ID) with TRCSESS into one trace file for analysis with TKPROF?
6. Are there any other important considerations that I may be missing?

Many thanks in advance

Kind regards
Andrew (from UK)




Follow-up June 13, 2006 - 10am US/Eastern:
In 10g, you have the dbms_monitor package as well as the trcsess utility.
Using this package (and dbms_session.set_client_indentifier), the java programmers can make it so you can actually trace an entire "session" of theirs which might cross multiple Oracle sessions (multiple trace files)

Dear Tom

I have problems with submitting review-response.

Will try in a few segments
>>
Thank you for the feedback.

I do not have any idea how Mercury Load Runner works, either ¿ so let us forget this for a moment, i.e. let us focus on PROD (without Mercury s/w).

I imagine that in the PROD it should work ¿ but I would really appreciate you confirming it or let me know what I should do to guarantee that CLIENT_ID would indeed mark all the trace files the way I want.
<<

<< seg 02 >>

Let me then rephrase how I see the instrumentation code ¿:
1. Main Control web-service JAVA program starts its work on XML message
2. I want a developer to call a SQL insert statement BEFORE any JAVA processing takes place
3. The insert into a TRC_CTL table fires a trigger that grabs a unique number id and assigns it to CLIENT ID
4. With this CLIENT_ID set all I will see later in the trace file is this INSERT followed by a time gap during which various JAVA programs do various things
5. I would like to issue an INSERT into a table that would store the formerly set CLIENT_ID + time-stamp at the entry point to every web-service JAVA program
6. Then the first (of the 5) JAVA programs is called by the main process control Web service ¿ which sends various SQL to Oracle ¿ correct me if I am wrong but I understand that CLIENT_ID is still ¿attached¿ to this suite of JAVA programs?
7. When the very last JAVA module end its execution say an UPDATE to the original row inserted into TRC_CTL triggers a DISABLE of tracing on this CLIENT_ID

<<

>> 03
>>
8. If in the meantime (another concurrent process) sends a new XML message to the main control web-service JAVA program (a new thread) the new INSERT generates a new CLIENT_ID
9. I understand that I will then have 2 trace files.
10. If I set a limit of say 10 in TRC_CTL table and check the diff in CLIENT_ID I then can limit how many such sessions will be traced, correct?

<< 04 >>

>>
11. Then I can use TRCSESS to get a combined trace to process with TKPROF ¿ and this combined trace file would have all and nothing but the sessions that I asked to be traced showing entire histograms ¿ with clearly marked entry and exit points for each JAVA module (all JAVA modules would have a SQL request - a "marker" i.e. insert into TRC_CTL and additional SQL that the App currently has in some JAVA modules) ¿ correct?
12. Now, if I can use TRCSESS to combine all trace files identified by a RANGE of CLIENT_ID ¿ then great, as I would get average processing of every SQL, but if not, I guess I could insert all these stats into a TKPROF table to examine, right?

I would very much appreciate it if you could confirm this as I am not REALLY SURE.

If a suite of JAVA modules called from one JAVA web-service control program each send SQL to Oracle that indeed the assigned CLIENT_ID is persistent and stays with all JAVA modules that belong to the same suite.

If this is the case ¿ that¿s FANTASTIC.

Many thanks in advance

Kind regards
Andrew

Dear Tom,

That's great.
I will then design instrumentation to mark entry and exit points of various JAVA modules - these that do not as well as these that do call application related SQL.

I will expect to see a full histogram of the entire set of modules that together form a BUSINESS function end-to-end.

The idea is to not only prove or... disprove that original SQL is bad, or that database had not been deployed correctly - it is to demonstrate exactly when all that processing time went.

Once this works for one business session - next is to do for several = say a 100 - and see whether there are any major variances (keep in mind the client is OK with the APP they are concerned with the response time spikes that go as high as 12-16 sec from the average of 2-3 sec).

There are potential queueing related performance problems that might be detected when Mercury Load Runner simulates sending concurrent requests to the App to process.

What I need to do know is to devise a method of compiling these results into aggregates and making it possible to drill down easily into the performance culprit.

I am aware of HOTSOS profiler.
But before I can recommend this to this client in the UK, I would need to be able to prove that the entire approach - INCLUDING this instrumentation is what they should use and be able to easily control when to switch it ON/OFF.

Any suggestions you may have as to how MOST EFFECTIVELY use the right instrumentation + TKPROF + TRCSESS and/or other?

I know how you recommend instrumentation to everyone.

I will appreciate your practical recommendations.

Thanks again

Best regards
Andrew

Dear Tom,

One more point:
While awaiting another meeting with the client (IT Dir) I had no other option (as reported earlier) but to resort to DBMS_MONITOR for the ENTIRE database. This produced 623 trc files within 8 minutes.

I wrote a script to process all of these with TKPROF in the order of the time that was recorded in trc files line starting with '*** SESSION ID:(... '

It is doing things REALLY THE HARD WAY...
1) Sort all trace files by the timestamp
2) grep for specific SQL code that App is supposed to exec
3) line up some sort of sequence

And here is my PS to the former Review:
>>
I was hoping that these SESSION ID would not be unique - so I could possibly relate one call to the database from another.

No such luck - EVERY SINGLE ONE of the 623 trace files has a different SESSION ID.
One line above: SERVICE NAME (SYS$USERS) for all trc files.

So, my questions are =
A) What exactly is referred to as the SESSION ID - as there were nowhere near that many sessions (I checked v$session) at that time if ever...?

B) What if any relationship is there between CLIENT_ID (when I set it) and any of these SESSION ID recorded in trc files)?

C) If I wanted to instrument the code to monitor SERVICE + MODULE + ACTION - - then how should I identify SERVICE..?

+ + +

And ... one perhaps silly but perhaps not so question =

>> Is there a possible way that a very poorly written JAVA code would connect from 5 different modules to Oracle in such a way that CLIENT ID would not or COULD NOT work as common for one end-to-end business function to be tuned..?

IN case it is not clear what I mean - here is an example:

a) Suppose I connect to Oracle via SQL PLUS as FRANK;
b) I then identify myself as a UNIQUE CLIENT ID
c) I start trace with MONITOR for this CLIENT ID
d) I execute a SQL query QX
e) I issue a connect FRANK/foo - - yes, yes I know that I am already connected, but I pretend to know very little about Oracle so I want to MAKE SURE that I execute as FRANK
f) Immediately after the connect I issue another query QY
g) Now I look at the results for a while (vi spool file)
h) Here I go again - connect FRANK/foo
i) And my final query QZ
j) I am done - so now I disable my trace.

Perhaps it is silly, but I gave this as an example of some very poor code - JAVA > JDBC > ORACLE only to make sure that indeed IN SPITE OF MY EFFORTS TO DERAIL Oracle tracing
Oracle is actually smarter and somehow "knows" that I am the same guy - i.e. the same connection etc., therefore all SQL queries: QX + QY + QZ end up being identified as one session traced perhaps into 3 different trc files however they may be identified with TRCSESS and combined into one that has ALL of these queries an nothing else but these..?

I am tempted to test such a case + follow up with a test of one SQL PLUS session that is identified with CLIENT_ID and that executes jobs (dbms_job) + and also a piece of code that would use a trigger with AUTONOMOUS TRANSACTION.

These come to mind - but poorly coded JAVA does NOT come to mind because I am not a JAVA programmer.

Curious to see your answers...

Many thanks
Kindest regards
Andrew

ops$tkyte%ORA9IR2> select sid, serial# from v$session where sid = (select sid from v$mystat where rownum = 1 );

       SID    SERIAL#
---------- ----------
        12      27488

ops$tkyte%ORA9IR2> alter session set sql_trace=true;

Session altered.

ops$tkyte%ORA9IR2> @gettrace

TRACE
-------------------------------------------------------------------------------
/home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_1912.trc

ops$tkyte%ORA9IR2> !grep SESSION /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_1912.trc
*** SESSION ID:(12.27488) 2007-09-19 12:26:46.949

ops$tkyte%ORA10GR2> exec dbms_session.set_identifier( 'a_unique_thing' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @trace

Session altered.

ops$tkyte%ORA10GR2> exec dbms_session.set_identifier( 'a_differnt_unique_thing' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @gettrace

TRACE
-------------------------------------------------------------------------------
/home/ora10gr2/admin/ora10gr2/udump/ora10gr2_ora_1928.trc

ops$tkyte%ORA10GR2> !egrep /home/ora10gr2/admin/ora10gr2/udump/ora10gr2_ora_1928.trc '(SESSION|CLIENT)'
egrep: (SESSION|CLIENT): No such file or directory

ops$tkyte%ORA10GR2> !egrep '(SESSION|CLIENT)' /home/ora10gr2/admin/ora10gr2/udump/ora10gr2_ora_1928.trc
*** CLIENT ID:(a_unique_thing) 2007-09-19 12:28:11.723
*** SESSION ID:(136.1163) 2007-09-19 12:28:11.723
*** CLIENT ID:(a_differnt_unique_thing) 2007-09-19 12:28:31.619

I am seeing some behaviour on one of my queries that is too bizarre to believe.

I have a query that runs slow (2 minutes)

So what do I do? Turn on tracing (both sql_trace=true and event 10046) before running the query.

But consistently, 100% of the time, without changing anything else, when I enable tracing, the query comes back instantly!!

Any idea what might cause this sort of behaviour?

What is x?

Dear Tom,

Many thanks for your feedback.

You have explained very clearly how CLIENT_ID can tag what we want to trace.

However, your last comment ¿ that the module would need to identify itself EVERY TIME ¿ sends a clear message = i.e. the code MUST BE such that the SAME connection is preserved i.e. identification ¿ otherwise it will be a totally different trace.

Well, here I have 623 trace files ¿ all unique SID+SERIAL# which implies that each and every one has been invoked like it was the VERY FIRST CONECTION = i.e. whoever programmed these JAVA modules FAILED TO IDENTIFY ITSELF, or - - I am missing something.?

So, if this is the fact ¿ then ANY TRACING will be nearly useless ¿ UNLESS of course the code is corrected, am I correct.?

/

Unless ¿ I mark EVERY SINGLE connection from the App with the SAME Client_ID

But - -
- if I want to instrument the code so that I trace concurrent sessions ¿ then I cannot do that, right?

+

So, let me rephrase this:
1. The developers MUST identify their JAVA connections al the way through the business session ¿ and if they fail to do so, then no instrumentation will work, right?
2. If their code is OK ¿ then instrumentation can tag concurrent requests so that these can be later combined in aggregates ¿ however, also examined individually..?

So ¿ in a nutshell ¿ if you were in my position ¿ WHAT would you require developers to do ¿ so that your instrumentation would really work.???

Kind regards
Andrew

Tony said:
....I am sure others also, as Cary mentioned in his book by using Oracle's suggested method of performance tuning i.e., by using execution plan, tkprof tools etc. I am
surprised that why oracle corporation Gurus did not come up with this approach before

For those of who have not gone thru that book what exactly is "this approach" ????

Call me daft but are you (Tony) saying that "oracle corp. Gurus" do NOT do "...execution plan, tkprof tools" ???

Dear Tom,

Thank you for your follow-up.

However, I think I failed to properly communicate what this App does. Internet Exchange passes a request for a quotation that is received by my XYZ Co - their application is to process this quote and return relevant data back to Internet Exchange. Now internally (no user input or navigation at all) XML is passed on into BEA ALSB and the process starts and must complete within 4 sec max, preferably much shorter:

1. BEA ALSB passes a request for quote to WLI Web service Main Control module CTL ¿ it is from now on to the completion SINGLE JAVA THREAD
2. CTL calls a Java module (that does not require calls to Oracle) ¿ J01
3. CTL calls a Java module that requests Oracle to supply data ¿ J02
4. CTL calls a Java module that requests Oracle to supply more data ¿ J03
5. CTL calls a Java module that does not pass any requests to Oracle ¿ instead it calls a third party Calculation Engine (elapsed proc time varies between 0.7 and 1.5 sec and it is dev team estimate ¿ no measurements have been done, no instrumentation) ¿ J04
6. CTL calls a Java module that just selects SEQ number from Oracle Sequence ¿ J05
7. CTL calls a Java module that makes 2 JDBC calls to Oracle ¿ J06
8. The last Java module packages the quotation info and passes it back to the CTL which passes it to the Internet Exchange (a company that acts like a broker) ¿ J07

The Exchange (broker) passes the same request to several participating companies and those who manage their processing within 4 sec are OK but when they cannot process quickly enough their response their quote is not accepted after 4 sec.

What I want to do is to get the complete histogram that maps the entire end-to-end session as seen by the actual person who enters the relevant data that gets accepted by the Internet exchange and waits for the results (several companies ¿ XYZ amongst them ¿ return their quotations).

I hope to get a JAVA developer to issue a simple statement ¿ let¿s say an update to TRC_CTL table (instead of an Insert that I thought first):
Update TRC_CTL set JAVA_SESSION_ID = :a
, IN_OUT = 1 -- or 0 when all is complete to mark EXIT
Where TRC_ON = `Y¿

This Java module retrieved current session id into :a variable

This SQL is executed at the entry point of every Java module (J01 through J07).

I can build a trigger on this TRC_CTL table to capture Java session_id along with Oracle system time-stamp. DBMS_SESSION creates Oracle CLIENT_ID (passed Java ID) and DBMS_MONITOR starts trace when the first JAVA SESSION_ID is received and inserts the time-stamps to the TRC_CTL log records.


Would you say that this could work?

The TRC_CTL table is very simple here. It may also be updated with Module Name etc, I just wanted to demonstrate the approach.

IF instead (as you suggested in your latest response) a developer calls DBMS_SESSION to uniquely identify their current Java session, developer would need to code much more and I simply want to make as simple as it can possibly be.

Also by issuing an UPDATE statement and the start of each module + one on exit from the last module ¿ I get an instrumented code end-to-end that shows all processing times ¿ Java modules that do not call Oracle + those modules that do + the one that calls a third party complex calculation engine (processing times are significant) and such histograms will show me where the time goes and what can be tuned and what max possible improvement we can attain.

Please tell me what you think ¿ and suggest a better approach if I am on a wrong track.
Many thanks
Regards
Andrew

Dear Tom,

More uncertainty as to what is and what is NOT feasible with instrumentation on this project - post factum, that is.

After a long conversation I had with the main architect ¿ this is the brief summary:
1. It is true what we said before ¿ that there is one single JAVA thread ¿ but it is only as far as the main CTL module;
2. We are unable to obtain a UNIQUE session id from JAVA, as java modules are managed by SOAP within App server via Internet or Intranet to another server with its own SOAP within which sits another JAVA module;
3. Yes, we could accept a unique identifier from Oracle by each JAVA module, but that would require altering an interface that is the METHOD that we are using not only with modules of B2B App that need tuning but with ALL OTHERS for other Apps ¿ and that there are dozens of them ¿ HUGE EFFORT

I am not a JAVA+WEB-SERVER+SOAP expert, but I argued that if there are 5 JAVA modules that happily pass on various SQL statements back and forth with Oracle and between themselves, surely it should be feasible to also pass an identifier from and to Oracle to facilitate tracing that would activate when a CTL table says that Extended SQL Trace is required with whatever conditions specified.

Each time I suggest one way or another I am met with wall of resistance which simply put is ¿That is much too difficult and time consuming etc, etc.¿

Meanwhile ¿ over the weekend ¿ I ran some tests to see how Oracle may or may not be able to trace a session + a job configured with dbms_job.

I was NOT AT ALL surprised when SQL Trace was recording ONLY for the main session but not for sessions that were triggered by the scheduler.

So, I wrote a very simple PL/SQL proc that would accept 2 parameters =
(1) client_id
(2) counter = limit to how many rescheduled jobs would be traced

The procedure would start with DBMS_SESSION.SET_IDENTIFIER (client_id) checking with the counter if this is still required. Then whatever the code the PL/SQL had would be executed with Extended SQL Trace set on/off as required.

This worked fine.
And I feel that I need to expect a similar situation with their App.

I am not even sure whether SET_IDENTIFIER proc should run for every single SQL statement within all of their JAVA modules ¿ as I fear that each one may very well behave like my original session that cannot actually keep the CLIENT_ID.

If this is indeed the case ¿ then I would need to change their code to call PL/SQL procedures that would start with DBMS_SESSION.SET_IDENTIFIER (client_id) instead of passing just SQL Update or Insert etc.

Tom could you please give it some thought and tell me if indeed there may be a problem with the CLIENT_ID being recognized under such circumstances ¿ or if you are not sure as to what EXACTLY their APP configuration or interface or protocol really are, then perhaps you may tell me what would need to be guaranteed for CLIENT_ID based trace and conversely what App configuration would definitely render such trace impossible.

This is the main issue that I need to fully understand so that instrumentation will indeed work and be scoped into a specific end-to-end business process.

Their ¿standards and practices¿ do NOT ALLOW for any stored procedures.
Don¿t ask me why. I will need to convince management to allow me to change these practices as I will certainly need to code PL/SQL procedures that will enable and disable tracing based on set parameters in TRC_CTL table.

I will appreciate your input very much.

Many thanks ¿ Cheers
Andrew

Dear Tom,

Thank you for this.

This particular project team REFUSED TO DO ANYTHING AT ALL to facilitate tracing a business session.

They believe that they may figure out how to derive specific fixes from system averages. They made some references to a Wiley tool that is suppossed to do magic.

I have not had the opportunity to see it.
I told them basically the same - I cannot do magic. I need them to facilitate a trace. Without it it is a guess, and I am not prepared to vouch for my guesses.

Perhaps - many others who visit your site may want to consider this and adopt a firm position in such situations.

Thank you Tom

Andrew,

Outside of what Oracle can or cannot do for you, have you done a "sanity check" on how the BEA server is calling Oracle? It seems odd to generate so many sessions in the first place.

How many Oracle userids are involved? Is connection pooling being used? Ask the application folks to demonstrate that they (or the J2EE server) are "doing the right thing", meaning proper use of connection pooling and prepared statement caches.

If by chance connection pooling is not properly configured, you have all the overhead of building up and tearing down connections and sessions, plus each new session will parse every bit of SQL at least once.

To see whether connection pooling is in use, check the listener log to see how frequently the application is connecting. My log shows something like

(CONNECT_DATA=(SID=<YOUR_SID>)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=<THEIR_IP_ADDRESS>)

Dear Stew Ashton ¿ Dear Tom

Many thanks for your additional input.

You may want to go back through this particular exchange ¿ last 2 weeks.

But as far as their environment let me add a few more details ¿ which are also very important:

1. Their PROD server is configured with one large DASD that holds everything you need for their PROD DB ¿ from control files through redo + arch + temp + undo + DATA tablespace that has all their schemas and indexes.
2. Before I got engaged into this large project that has 8 (YES ¿ EIGHT) companies involved ¿ and each one is generating about a dozen opinions per hour - - they got their Oracle Data Guard configured (to a different machine + but a standby Oracle on top of this all ¿ AND ¿ on the same Sun server where their PROD is, OK?
3. Then they are running all sorts of jobs and data loads ¿ to give an example ¿ one of them is to DELETE all rows then INSERT a new batch (several hundred thousand) and then COMMIT it all ¿ just to ¿make sure¿ that if by any chance one user wanted to query the old rows between 3 and 7 AM, he/she would be able to do so ¿ and the new rows would seamlessly appear and the old disappear, OK?
4. The above blew their UNDO up.
5. Another run-away process (nobody could understand its origins) brought their PROD to a hang as their arch partition (of the same DASD as per item #1 above) got full. So someone called the shots and started deleting archived logs to free up some space. Two days later same thing happened and again nobody was any wiser
6. There is OEM and SYSMAN and AWR running at all times.
7. Before I was asked to help ¿ their infrastructure guy created indexes ¿ as he said to me on the first day (to give me a taste of what would be coming) ¿ ¿Well, I looked at all these schemas and tables and saw that we really basically read from them ¿ so I asked that we would create as many indexes as possible on all tables, as it surely would not hurt, would it?¿ ¿ when I opened my mouth to comment, the follow-up comment was: ¿Do not worry about it Andrew, we do not want you to focus on it, as the performance spikes got lower after these indexes got created ¿ but as we still have them, we want you to use your past experience and suggest what else might be wrong¿
8. Their various people communicated to their IT management enough negative issues ¿ as to how very risky it would be to do any SQL trace and any code change + interfaces etc., that IT manager agreed to keep the status quo.
9. Just to put all of that in perspective ¿ please know that although practically one could request (well I did) to run a test of all these JAVA modules from a dedicated process and connected to a dedicated Oracle session ¿ here, too, I was told = ¿NOPE, that we will not do. We have a Mercury Load Runner configured and it is the way we run all the tests here. You can examine the OEM report (that BTW is the same as to generate an AWR report with 10 min snapshot intervals configured) ¿ and tell us why we are having these spikes.
10. Then Mercury LR was re-attached to PROD. I was asked to examine a particular 16sec long spike in their response elapsed time and tell them what EXACTLY had caused it.
11. So, I asked for 2 weeks for a 5 minute time window and when eventually it had been granted, all I could do was to enable database-wide Extended SQL trace for the entire database ¿ in a slim hope that somehow, I could link various trace files to a logical end-to-end business session performance profile. However, as there are many different other components (JAVA + WS + ASLB etc., etc.) it was virtually an impossible task. I got 623 trace files out of 6 minute run and they could not even tell me how many requests for quotation their Mercury LR simulated over this period of time. They said that this should run as a series, but lacking any proof I am not even sure about that

So, just like Tom said ¿ ¿sorry, no magic from me¿ - I made virtually an identical pronouncement.

I was exposed to heavy arm twisting in a 75 minutes conference room ordeal where demands were made that I was expected to give them a very accurate account on their performance problems if it was related to Oracle ¿ or prove that it was not.

I tried to reason with them and made the following observations:
o One developer showed me a JAVA code sample that was calling SQL ¿ one statement at a time ¿ each time requesting a connection === if I was allowed to run a trace on CLIENT_ID identified session, I would be able to see that perhaps SQL*Net from client was contributing a great deal of time to the elapsed proc time, and if this was the case then call to a stored procedure to execute all SQL and return the minimum result set necessary would have a dramatic improvement as the App would not waste that much time on SQL*Net << Response: `No, we do not and will not write any PL/SQL ¿ any stored procedures¿>>
o I asked them if they would maintain the same policy regarding no code changes if it was detected that several of their modules failed to process their Oracle error conditions << Response: our focus is on spikes and not errors >>
o I asked if there was any way to identify a JAVA session and they told me that: ¿Well we believe that each request for quotation comes in and goes on a single JAVA thread, but there are many threads and we cannot identify JAVA session ID as all goes via containers into SOAP and via Intranet or Internet across to another web server via its own SOAP into JAVA and at some point to Oracle, so no we would be able to let you have a JAVA Session ID¿
o I suggested that they may obtain a unique identifier from Oracle (their first JAVA module) and as their JAVA modules must be able to recognize what bit of data goes with what when many SQL statements are executed in response to the calls for quotation, so the Oracle generated BUSINESS_SESSION_ID would also be available and could be used to trace SQL. === << Response: No ¿ ditto ¿ no change you can figure it out from the tools that we have >>

PS

I am no longer on this project ¿ left a couple of days ago ¿ but I indeed do appreciate your input Stew and Tom.

Hi,

My statspack report contains following data:
Could you please help me in analyzing it


-> "Pct Misses" should be very low

Get Pct Pin Pct Invali-
Namespace Requests Miss Requests Miss Reloads dations
--------------- ------------ ------ -------------- ------ ---------- --------
BODY 117,586 1.5 2,430,163 0.1 1,746 0
CLUSTER 473 2.3 1,026 2.7 17 0
INDEX 1,505 28.4 3,504 23.5 256 0
JAVA DATA 264 1.1 1,098 0.4 0 0
JAVA RESOURCE 14 7.1 55 30.9 6 0
PIPE 300,313 0.0 300,306 -0.0 0 0




-> ordered by Pool, Name

OUTPUT
--------------------------------------------------------------------------------
-> N/A value for Begin MB or End MB indicates the size of that Pool/Name was
insignificant, or zero in that snapshot

Pool Name Begin MB End MB % Diff
------ ------------------------------ -------------- -------------- -------
java free memory 18.7 18.7 0.00
java joxlod exec hp 12.4 12.4 0.00
java joxs heap .8 .8 0.00
large PX msg pool .6 .6 0.00
large free memory 15.4 15.4 0.00
shared ASH buffers 15.5 15.5 0.00

OUTPUT
--------------------------------------------------------------------------------
shared CCursor 15.2 17.7 16.47
shared Heap0: KGL 11.2 13.9 23.46
shared KGH: NO ACCESS 30.1 30.3 0.49
shared KGLS heap N/A 12.8 N/A
shared KQR L PO 11.7 11.2 -4.39
shared PCursor 10.7 11.5 7.15


Thanks,
Atul

After reading this i tought let me give it a try. This is what i did.

create table t as select case when rownum = 1 then 1 else 99 end id,a.* from all_objects a;

Table created.

create index t_idx on t(id);

Index created.

exec dbms_stats.gather_table_stats('sysadm','t',method_opt=> 'for all indexed columns size 254');

PL/SQL procedure successfully completed.

set autotrace traceonly explain

select * from t where id = 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=93)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=93)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)

select * from t where id = 99;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=369 Card=115937 Bytes=10782141)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=369 Card=115937 Bytes=10782141)

variable id number;

exec :id := 1;

PL/SQL procedure successfully completed.

select * from t where id = :id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=371 Card=115947 Bytes=10783071)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=371 Card=115947 Bytes=10783071)

exec :id := 99;

PL/SQL procedure successfully completed.

select * from t where id = :id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=371 Card=115947 Bytes=10783071)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=371 Card=115947 Bytes=10783071)

alter session set sql_trace = true;

Session altered.

exec :id := 1;

PL/SQL procedure successfully completed.

select * from t where id = :id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=371 Card=115947 Bytes=10783071)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=371 Card=115947 Bytes=10783071)

exec :id := 99;

PL/SQL procedure successfully completed.

select * from t where id = :id;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=371 Card=115947 Bytes=10783071)
1 0 TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=371 Card=115947 Bytes=10783071)


for the first time when i used id = 1 it used the index after that it never used the index. now again iam doing the first query.

select * from t where id = 1;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=93)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=93)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)

See i got the index there.

The version iam living in


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Hi Tom,
I am sorry as I am posting my question here, but its bit urgent. sorry again.
we have this procedure p_intranet_select_products_1
CREATE OR REPLACE procedure p_intranet_select_products_1
(p_uk_label_select in varchar2,
p_stand_cost_select in varchar2,
p_uk_prod_code in varchar2,
p_prod_title in varchar2,
p_artist_name in varchar2,
p_start_date in varchar2,
p_end_date in varchar2) AS
BEGIN
delete from GCDM_UK.intranet_product_list1;
INSERT INTO GCDM_UK.INTRANET_PRODUCT_LIST1
/*
(product_id,
product_name,
artist_name,
uk_product_code,
upc_number,
product_status,
label_name,
standard_cost_name,
creation_date)
*/
SELECT u.dw_product_id,
p.product_name,
a.artist_name,
p.uk_product_code,
p.upc_number,
p.product_status,
l.label_name,
s.standard_cost_name,
p.creation_date
FROM dwprod.product p,
dwprod.artist a,
dwprod.label l,
GCDM_UK.uk_specific u,
GCDM_UK.standard_cost s
WHERE p.artist_code = a.artist_code
AND u.dw_product_id = p.PRODUCT_ID
--and l.label_code = p.label_code
AND s.standard_cost_code = u.standard_cost_code
--and P.standard_cost_code = s.standard_cost_code
AND l.LABEL_CODE = u.uk_label_code
AND s.active_to_date IS NULL
AND NVL(u.uk_label_code,'##') = DECODE(p_uk_label_select,'Y','##',nvl(u.uk_label_code,'##'))
AND NVL(u.standard_cost_code,'##') = DECODE(p_stand_cost_select,'Y','##',NVL(u.standard_cost_code,'##'))
AND (p.uk_product_code LIKE '%'||UPPER(NVL(p_uk_prod_code,'%'))||'%'
AND p.product_name LIKE '%'|| UPPER(NVL(p_prod_title,'%'))||'%'
AND a.artist_name LIKE '%'||UPPER(NVL(p_artist_name,'%'))||'%'

AND p.creation_date BETWEEN TO_DATE(NVL(p_start_date,'01/01/1960'),'DD/MM/YYYY')
AND TO_DATE(NVL(p_end_date,'01/01/2161'),'DD/MM/YYYY'))
ORDER BY 1 ASC;
This procedure is taking ages to execute. However when we extract the Select and insert part out from this proc it runs fine within a second.

The table in which data is being inserted is a global temporary table.
There are no lock issue, no privs issue I have already checked them.
Nothing seems to be working.

I have encountered similar issue before. For me the problem was due to a oracle bug. CBQT is disabled for CTAS and Insert into select operations. A one-off patch is available to enable this. I think this is fixed in 10.2.0.3.

Hi Tom,

Could you please help in the following requirement :

-- Contains Month wise (Only one rec for month)
Insert into person_header (
header_id Number(5),
org_id Number(5),
main_begin_date Date,
main_End_Date Date
);

-- Monthwise - Person wise salary details
Insert into person_details (
header_id Number(5),
person_id Number(5),
Sal Number(16,2),
allowances Number(16,2));

Insert into person_header Values (97,251,'1-aug-2007','31-aug-2007');
Insert into person_header Values (98,251,'1-sep-2007','30-sep-2007');
Insert into person_header Values (99,251,'1-oct-2007','31-oct-2007');
Insert into person_header Values (100,251,'1-nov-2007','30-nov-2007');
Insert into person_header Values (101,251,'1-dec-2007','31-dec-2007');
Insert into person_header Values (102,251,'1-jan-2008','31-jan-2008');

Insert into person_details Values (100,1,5000,200);
Insert into person_details Values (101,1,5000,200);
Insert into person_details Values (102,1,5000,200);

Insert into person_details Values (97,2,5000,200);
Insert into person_details Values (98,2,5000,200);
Insert into person_details Values (100,2,5000,200);

Insert into person_details Values (98,3,5000,200);
Insert into person_details Values (99,3,5000,200);

Requirement:
If the report runs in Current month, It should print the previous details of persons who are not in the current month

With the above data, if I run the report in Feb 2008, It should print the following persons
Person_id Begin_Date Sal Allowances
1 1-jan-2008 5000 200
2 1-jan-2008 5000 200
3 1-jan-2008 5000 200

IF THE REPORT RUNS IN JAN, It should print 2 and 3 person (bcoz 1 is in Jan month)
Person_id Begin_Date Sal Allowances
2 1-nov-2007 5000 200
3 1-oct-2007 5000 200

Select wr.org_id, wh.person_id, wr.main_begin_Date, wh.sal, wh.allowances
From person_header wr, person_details wh
Where wr.header_id = wh.header_id
And wr.main_End_Date =
(Select max(wr1.main_End_Date)
From person_header wr1
Where wr1.main_End_Date < to_date('02/29/2008', 'mm/dd/yyyy') --
And Exists (Select 1
From person_details wh1
Where wh1.person_id = wh.person_id
And wh1.header_id = wr1.header_id))
And not exists
(Select 1
From person_header wr2, person_details wh2
Where wr2.header_id = wh2.header_id
And wh2.person_id = wh.person_id
And wr2.main_End_Date = to_date('02/29/2008', 'mm/dd/yyyy'))

Query is giving the correct result but it is taking long time to process.....(About 30 mins)

Person_header table have 5000 records and person_detail table have 20 million records

Can you please suggest whether my logic is correct or can we re-write the query to get the same result with less time.

Thanks for you help
Sanjeev Vibuthi

Assuming dates are always in the same month and the first one always the first day of the month

Try following query :

with q_dataset as
(select ph.header_id
, ph.org_id
, ph.main_begin_date begin_date
, ph.main_end_date
, max(ph.main_end_date) over (partition by pd.person_id) max_end_date
, pd.person_id
, pd.sal
, pd.allowances
from person_header ph
, person_details pd
where ph.header_id = pd.header_id)
select person_id
, begin_date
, sal
, allowances
from q_dataset
where max_end_date = main_end_date
and max_end_date < :p_date;

I don't think really for this sql we need analytics.

with person_header
as
(select 97 header_id, 251 org_id, to_date('01.08.2007','dd.mm.yyyy') main_begin_date, to_date('31.08.2007','dd.mm.yyyy') main_end_date from dual
 union all
 select 98,251,to_date('01.09.2007','dd.mm.yyyy'), to_date('30.09.2007','dd.mm.yyyy') from dual
 union all
 select 99,252,to_date('01.10.2007','dd.mm.yyyy'), to_date('31.10.2007','dd.mm.yyyy') from dual
 union all
 select 100,252,to_date('01.11.2007','dd.mm.yyyy'), to_date('30.11.2007','dd.mm.yyyy') from dual
 union all
 select 101,252,to_date('01.12.2007','dd.mm.yyyy'), to_date('31.12.2007','dd.mm.yyyy') from dual
 union all
 select 102,252,to_date('01.01.2008','dd.mm.yyyy'), to_date('31.01.2008','dd.mm.yyyy') from dual
),
person_details
as
(select 100 header_id, 1 person_id, 5000 sal, 200 allowances from dual
 union all
 select 101,1,5000,200 from dual
 union all
 select 102,1,5000, 200 from dual
 union all
 select 97,2,5000, 200 from dual
 union all
 select 98,2,5000, 200 from dual
 union all
 select 100,2,5000, 200 from dual
 union all
 select 98,3,5000, 200 from dual
 union all
 select 99,3,5000, 200 from dual
),
grouped_value as
(
select person_id, max(ph.header_id) header_id, max(main_begin_date) begin_date from person_details pd, person_header ph
where  ph.header_id = pd.header_id
group by person_id
having max(main_begin_date) < to_date('01.01.2008','dd.mm.yyyy'))
select pd.person_id, gv.begin_date, pd.sal, pd.allowances begin from person_details pd, grouped_value gv
where pd.person_id = gv.person_id and pd.header_id = gv.header_id;

When input is 01.01.2008 :

PERSON_ID BEGIN_DAT SAL BEGIN
---------- --------- ---------- ----------
2 01-NOV-07 5000 200
3 01-OCT-07 5000 200

When input is 01.02.2008 :

PERSON_ID BEGIN_DAT SAL BEGIN
---------- --------- ---------- ----------
1 01-JAN-08 5000 200
2 01-NOV-07 5000 200
3 01-OCT-07 5000 200

Regards

Raj

Thanks guys.. really appreciate your help.. even with the new query (With option query) still it is taking 30 min..

Hi Tom,


Sorry about insufficient details...
Always the Dates are monthly. Begin Date is the first of day of the month and End Date is last day of the month.

Every month Organization posts payroll info of their employees....
Organization month wise information is storing in person header (1 record per month)
person wise details storing in person details.....

I want to find out the persons who are not in the current month with their previous payroll details (It could be previous month or any month less than current month).


Thanks
Sanjeev Vibuthi

Hi Tom,

Reason for having 2 dates is sometimes the payroll period vary... It may be Bi-weekly, quarterly and half yearly...

Each month every employer enteres about 2000 members.... one header records contains 2000 details records...

There are some other columns also in the tables but for this logic conditions invovled mainly these columns

PERSON_HEADER - Header id PK
PERSON_DETAILS - Header id and Person ID PK

Thanks
Sanjeev VIbuthi

Hi Tom,

Person Header
========================
Header_ID -- PK
Org_id
Main_Begin_Date
Main_End_Date

Also have:
Report_Type -- Regular or Adjustment (Valid values R or A) but in this query we are selecting only R
Release_Status -- Pending or Released (PE, RE) -- in this query selecting only RE

Person Details
===========================
Header_ID --PK
Person_ID --PK
Sal
Allowances

Also have:
Job Type -- we are not using this column in this criteria

No. of Records
===========================
Person Header - 5000 (Each month one record for organization, it may have 2 if Adjustments entered)
Person Details - 5000 * 2000 (Members) = 10 millions

But it can more. We are planning to load backlog data also, in that case no of records may go up.


Thanks
Sanjeev Vibuthi

select header_id, org_id
  from person_header
 where main_begin_date >= add_months( trunc(sysdate,'mm'), -1 )
having count(*) = 1   -- only one record and not this months record...
   and count(case when main_begin_date = trunc(sysdate,'mm') then 1 end) = 0 

Hi Tom,

Sorry I posted this on other thread..

Your query gives previous main begin date in all records but we need previous main begin date for
each member.
eg.
With the above data, if I run the report in Feb 2008, It should print the following persons
Person_id Begin_Date Sal Allowances
1 1-jan-2008 5000 200
2 1-jan-2008 5000 200
3 1-jan-2008 5000 200

IF THE REPORT RUNS IN JAN, It should print 2 and 3 person (bcoz 1 is in Jan month)
Person_id Begin_Date Sal Allowances
2 1-nov-2007 5000 200 (For this member prev date is Nov)
3 1-oct-2007 5000 200 (For this member prev date is Oct)

I modified the query:

Select wr.org_id, wh.person_id, wr.main_begin_Date, wh.sal, wh.allowances
From person_header wr, person_details wh
Where wr.header_id = wh.header_id
And wr.main_End_Date = (select max(main_end_date)
from person_header ph1, person_details pd1
where ph1.header_id = pd1.header_id
and pd1.person_id = pd.person_id -- to main query
and main_begin_date >= add_months( trunc(sysdate,'mm'), -1 )
group by main_end_date
having count(*) = 1 -- only one record and not this months record...
and count(case when main_begin_date = trunc(sysdate,'mm') then 1 end) = 0

But this also taking long time....

Thanks for your help
Sanjeev Vibuthi

Hi Tom,

I know full table scan is not an evil.
But this is just conceptual question...

Does outer join always lead to full table scan of the table(s) being outer joined? If so, what possible ways you suggest for 2-3 tables having 20-30 million rows being outer joined with a table having 10-20 million rows. Is this a design issue, as query runs for hours and hours..
Or there could be issue some where else?

Thanks

ops$tkyte%ORA10GR2> create table t1 ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> create table t2 ( x int, y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t1_idx on t1(y);

Index created.

ops$tkyte%ORA10GR2> create index t2_idx on t2(y);

Index created.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T1', numrows => 20000000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T2', numrows => 10000000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select *
  2    from t1 left join t2 on (t2.y = 5 and t1.x = t2.x)
  3   where t1.y = 42;

Execution Plan
----------------------------------------------------------
Plan hash value: 3371511104

------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes |TempSpc|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |   200K|     9M|       |
|*  1 |  HASH JOIN RIGHT OUTER       |        |   200K|     9M|  3712K|
|   2 |   TABLE ACCESS BY INDEX ROWID| T2     |   100K|  2539K|       |
|*  3 |    INDEX RANGE SCAN          | T2_IDX | 40000 |       |       |
|   4 |   TABLE ACCESS BY INDEX ROWID| T1     |   200K|  5078K|       |
|*  5 |    INDEX RANGE SCAN          | T1_IDX | 80000 |       |       |
------------------------------------------------------------------------

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

   1 - access("T1"."X"="T2"."X"(+))
   3 - access("T2"."Y"(+)=5)
   5 - access("T1"."Y"=42)

ops$tkyte%ORA10GR2> set autotrace off

I am getting the below error
while trying to fire a select statement

ORA-01114: IO error writing block to file string (block # string) 


Well my tablepace size of the user is 155 MB total space.4 MB is free space and used space is 150.8 mb

Also After checking in the dba_temp_files
I can see only one temp file .

SQL> /
FILE_NAME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS    RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
 ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------ ---------- ------------------------------ ---------- ---------- --------- ------------ --- ---------- ---------- ------------ ---------- -----------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
C:\ORACLE\ORADATA\ubs\TEMP01.DBF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         1 TEMP                           1.2886E+10    1572992 AVAILABLE            1 YES 3.4360E+10    4194302           80 1.2885E+10     1572864                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               Elapsed: 00:00:00.00
SQL> spool off;


While trying to retrieve the record I am getting the below error.
Please help to resolve the below issue?

$ oerr ora 1114
01114, 00000, "IO error writing block to file %s (block # %s)"
// *Cause:  The device on which the file resides is probably offline. <b>If the
//          file is a temporary file, then it is also possible that the device
//          has run out of space. This could happen because disk space of
//          temporary files is not necessarily allocated at file creation time.</b>
// *Action: Restore access to the device or remove unnecessary files to free
//          up space.

ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  41999488  29008368  60% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm
 
ops$tkyte@ORA10G> create temporary tablespace temp_huge
  2  tempfile '/d01/temp/temp_huge' size 2048m
  3  /
 
Tablespace created.
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  41999616  29008240  60% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm

ops$tkyte@ORA10G> !ls -l /d01/temp/temp_huge
-rw-rw----    1 ora10g   ora10g   2147491840 Jan  2 16:34 /d01/temp/temp_huge

ops$tkyte@ORA10G> !cp --sparse=never /d01/temp/temp_huge /d01/temp/temp_huge2
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  44099336  26908520  63% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm
 
ops$tkyte@ORA10G> drop tablespace temp_huge;
 
Tablespace dropped.
 
ops$tkyte@ORA10G> create temporary tablespace temp_huge
  2  tempfile '/d01/temp/temp_huge2' reuse;
 
Tablespace created.
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  44099396  26908460  63% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm

In case you want to know what NOT TO DO while tuning, use the following link as a benchmark ;)

http://latest360.blogspot.com/2008/04/tips-for-better-performance-and-tuning.html

BTW, never know that Oracle had parts of SQL Server embedded :D

Hi Tom,
We have a query which was running fine till few days back. It used to take 2 mins. Now it is taking more than 25 mins. I rebuilt indexes, moved some of the tables to remove fragmentation and did analyse objects. But this did not help.
Here is the query...

(SELECT /*+ORDERED*/
c.rd_code, c.fc_code, s.site_id, s.site_status, scel.research_type_id,
scel.action_category, scel.action_code,
scel.action_date AS action_date, 0 AS user_id, NULL AS user_name,
s.outorg_id AS site_outorg_id
FROM SITE_CONTRIB_ENT_LOG scel, CONTRIBUTOR c, SITE_T s, RESEARCH_TYPES rt
WHERE scel.contrib_id = c.contrib_id
AND scel.site_id = s.site_id
AND scel.entire_site = 'Y'
AND rt.contrib_id = c.contrib_id
AND rt.research_type_id = scel.research_type_id
AND scel.action_date BETWEEN TO_DATE ('12/05/2008 01/17/21',
'MM/DD/YYYY HH24/MI/SS'
)
AND TO_DATE ('12/05/2008 01/47/21',
'MM/DD/YYYY HH24/MI/SS'
)
AND EXISTS (
SELECT 1
FROM SITE_DELIVERY sd, USER_DELIVERY ud
WHERE sd.sd_id = ud.sd_id
AND sd.site_id = scel.site_id
AND ud.status IN ('A', 'T')
AND 1 = 1)
AND 1 = 1
UNION
SELECT c.rd_code, c.fc_code, s.site_id, s.site_status, ge.research_type_id,
'E' AS action_category, 4 AS action_code,
sd.action_date AS action_date, 0 AS user_id, NULL AS user_name,
s.outorg_id AS site_outorg_id
FROM SITE_DELIVERY_LOG sd,
SITE_CONTRIB_ENT sce,
GROUP_ENT ge,
CONTRIBUTOR c,
SITE_T s,
RESEARCH_TYPES rt
WHERE sd.site_id = sce.site_id
AND sce.GROUP_ID = ge.GROUP_ID
AND ge.contrib_id = c.contrib_id
AND s.site_id = sce.site_id
AND rt.contrib_id = c.contrib_id
AND rt.research_type_id = ge.research_type_id
AND sd.action_date BETWEEN TO_DATE ('12/05/2008 01/17/21',
'MM/DD/YYYY HH24/MI/SS'
)
AND TO_DATE ('12/05/2008 01/47/21',
'MM/DD/YYYY HH24/MI/SS'
)
AND ( (sd.action_category = 'I' AND sd.action_code = 4096)
OR ( sd.action_category = 'U'
AND sd.action_code = 32
AND sd.new_status = 'A'
)
)
AND 1 = 1
UNION
SELECT c.rd_code, c.fc_code, s.site_id, s.site_status, rt.research_type_id,
'D' AS action_category, 14 AS action_code,
sd.action_date AS action_date, 0 AS user_id, NULL AS user_name,
s.outorg_id AS site_outorg_id
FROM RESEARCH_TYPES rt,
SITE_DELIVERY_LOG sd,
SITE_T s,
CONTRIBUTOR c,
GROUP_ENT ge,
CLIENT_GROUP cg
WHERE c.contrib_id = rt.contrib_id
AND rt.research_type_status = 'A'
AND rt.research_type_id = ge.research_type_id
AND rt.contrib_id = ge.contrib_id
AND cg.GROUP_ID = ge.GROUP_ID
AND cg.contrib_id = ge.contrib_id
AND sd.site_id = s.site_id
AND sd.action_date BETWEEN TO_DATE ('12/05/2008 01/17/21',
'MM/DD/YYYY HH24/MI/SS'
)
AND TO_DATE ('12/05/2008 01/47/21',
'MM/DD/YYYY HH24/MI/SS'
)
AND ( (sd.action_category = 'I' AND sd.action_code = 4096)
OR ( sd.action_category = 'U'
AND sd.action_code = 32
AND sd.new_status = 'A'
)
)
AND NOT EXISTS (
SELECT 1
FROM SITE_CONTRIB_ENT xsce
WHERE xsce.site_id = s.site_id
AND xsce.GROUP_ID = ge.GROUP_ID)
AND 1 = 1)
ORDER BY action_date ASC;

Here is the execution plan..

2243 rows selected.

Elapsed: 00:22:51.99

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=3107 Card=2538 Bytes=1
42128)

1 0 SORT (UNIQUE) (Cost=3084 Card=2538 Bytes=142128)
2 1 UNION-ALL
3 2 NESTED LOOPS (Cost=2941 Card=2456 Bytes=137536)
4 3 HASH JOIN (Cost=2941 Card=44141 Bytes=2251191)
5 4 TABLE ACCESS (FULL) OF 'SITE_T' (Cost=85 Card=6168
3 Bytes=616830)

6 4 HASH JOIN (Cost=2476 Card=44141 Bytes=1809781)
7 6 TABLE ACCESS (FULL) OF 'CONTRIBUTOR' (Cost=3 Car
d=2985 Bytes=23880)

8 6 HASH JOIN (Cost=2449 Card=44141 Bytes=1456653)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'SITE_CONTRIB
_ENT_LOG' (Cost=22 Card=613 Bytes=12260)

10 9 INDEX (RANGE SCAN) OF 'IDX2_SITE_CONTRIB_ENT
_LOG' (NON-UNIQUE) (Cost=8 Card=613)

11 8 MERGE JOIN (Cost=2328 Card=472657 Bytes=614454
1)

12 11 SORT (JOIN) (Cost=208 Card=76658 Bytes=61326
4)

13 12 TABLE ACCESS (FULL) OF 'SITE_DELIVERY' (Co
st=19 Card=76658 Bytes=613264)

14 11 SORT (JOIN) (Cost=2120 Card=472657 Bytes=236
3285)

15 14 TABLE ACCESS (FULL) OF 'USER_DELIVERY' (Co
st=628 Card=472657 Bytes=2363285)

16 3 INDEX (UNIQUE SCAN) OF 'PK_RESEARCH_TYPES' (UNIQUE)
17 2 NESTED LOOPS (Cost=106 Card=81 Bytes=4536)
18 17 NESTED LOOPS (Cost=25 Card=81 Bytes=3726)
19 18 HASH JOIN (Cost=25 Card=1461 Bytes=59901)
20 19 TABLE ACCESS (FULL) OF 'CONTRIBUTOR' (Cost=3 Car
d=2985 Bytes=23880)

21 19 HASH JOIN (Cost=17 Card=1461 Bytes=48213)
22 21 NESTED LOOPS (Cost=10 Card=1461 Bytes=35064)
23 22 TABLE ACCESS (BY INDEX ROWID) OF 'SITE_DELIV
ERY_LOG' (Cost=3 Card=1 Bytes=16)

24 23 INDEX (RANGE SCAN) OF 'IDX_SITE_DELIVERY_L
OG' (NON-UNIQUE) (Cost=2 Card=1)

25 22 INDEX (RANGE SCAN) OF 'I_SITECONTENT_SIDGID'
(NON-UNIQUE) (Cost=7 Card=28158191 Bytes=225265528)

26 21 INDEX (FAST FULL SCAN) OF 'IDX_GROUP_ID' (UNIQ
UE) (Cost=1 Card=3208 Bytes=28872)

27 18 INDEX (UNIQUE SCAN) OF 'PK_RESEARCH_TYPES' (UNIQUE
)

28 17 TABLE ACCESS (BY INDEX ROWID) OF 'SITE_T' (Cost=1 Ca
rd=61683 Bytes=616830)

29 28 INDEX (UNIQUE SCAN) OF 'PK_SITE_T' (UNIQUE)
30 2 FILTER
31 30 NESTED LOOPS (Cost=9 Card=1 Bytes=56)
32 31 NESTED LOOPS (Cost=8 Card=1 Bytes=48)
33 32 HASH JOIN (Cost=7 Card=1 Bytes=42)
34 33 NESTED LOOPS (Cost=5 Card=160 Bytes=5600)
35 34 NESTED LOOPS (Cost=4 Card=1 Bytes=26)
36 35 TABLE ACCESS (BY INDEX ROWID) OF 'SITE_DEL
IVERY_LOG' (Cost=3 Card=1 Bytes=16)

37 36 INDEX (RANGE SCAN) OF 'IDX_SITE_DELIVERY
_LOG' (NON-UNIQUE) (Cost=2 Card=1)

38 35 TABLE ACCESS (BY INDEX ROWID) OF 'SITE_T'
(Cost=1 Card=61683 Bytes=616830)

39 38 INDEX (UNIQUE SCAN) OF 'PK_SITE_T' (UNIQ
UE)

40 34 INDEX (FAST FULL SCAN) OF 'IDX_GROUP_ID' (UN
IQUE) (Cost=1 Card=3208 Bytes=28872)

41 33 TABLE ACCESS (FULL) OF 'CLIENT_GROUP' (Cost=1
Card=3338 Bytes=23366)

42 32 TABLE ACCESS (BY INDEX ROWID) OF 'RESEARCH_TYPES
' (Cost=1 Card=1697 Bytes=10182)

43 42 INDEX (UNIQUE SCAN) OF 'PK_RESEARCH_TYPES' (UN
IQUE)

44 31 TABLE ACCESS (BY INDEX ROWID) OF 'CONTRIBUTOR' (Co
st=1 Card=2985 Bytes=23880)

45 44 INDEX (UNIQUE SCAN) OF 'PK_CONTRIBUTOR' (UNIQUE)
46 30 INDEX (UNIQUE SCAN) OF 'PK_SITE_CONTRIB_ENT' (UNIQUE
) (Cost=2 Card=1 Bytes=8)





Statistics
----------------------------------------------------------
0 recursive calls
129 db block gets
41921873 consistent gets
157829 physical reads
0 redo size
143033 bytes sent via SQL*Net to client
14301 bytes received via SQL*Net from client
151 SQL*Net roundtrips to/from client
1 sorts (memory)
2 sorts (disk)
2243 rows processed


I am aware of the execution plan when the query was running fine. Query was not changed and there is no change in the database as well.

I find consistent gets are more. How can we reduce them?.

Thanks for your help

Dear Sir,
I need to tune this SQL. Have worked a lot with indexing, hints and archiving off old data. It looks like the SQL itself may need to be re-written. please tell me what is the way out to optimize this so that it can return me the data at a faster
rate. Thank you.
SELECT gsob . name Book , '|' || gcc . segment1 Entity , '|' || gcc .
segment2 Location , '|' || gcc . segment3 Department , '|' || gcc .
segment4 Account , '|' || ffvv . description "Account Description" , '|' ||
gcc . segment5 Product , '|' || gcc . segment6 Project , '|' || gcc .
segment8 Intercompany , gjl . je_line_num , '|' || gjs .
user_je_source_name Source , '|' || gjc . user_je_category_name
Journal_Category , '|' || gjh . doc_sequence_value "Document Number" , '|'
|| ' ' "Vendor/Customer" , '|' || ' ' "Invoice No" , '|' || ' ' "Invoice
Date" , '|' || ' ' "Invoice Header Description" , '|' || TO_CHAR ( gjh .
default_effective_date , : P_PROFILE_OPTION_VALUE ) "Journal Effective
Date" , '|' || TO_CHAR ( gjh . posted_date , : P_PROFILE_OPTION_VALUE )
"Posted Date" , '|' || gjh . description "Journal Header Description" , '|'
|| gjl . description "Journal Line Description" , '|' || gjh .
currency_code "Currency" , '|' || TO_CHAR ( NVL ( TO_CHAR ( gjl .
entered_dr , '999G999G999D99' ) , 0 ) ) "Entered Currency Debit" , '|' ||
TO_CHAR ( NVL ( TO_CHAR ( gjl . entered_cr , '999G999G999D99' ) , 0 ) )
"Entered Currency Credit" , '|' || TO_CHAR ( NVL ( TO_CHAR ( ( NVL ( gjl .
entered_dr , 0 ) - NVL ( gjl . entered_cr , 0 ) ) , '999G999G999D99' ) , 0 )
) "Entered Net" , '|' || TO_CHAR ( NVL ( TO_CHAR ( gjl . accounted_dr ,
'999G999G999D99' ) , 0 ) ) "Functional Currency Debit" , '|' || TO_CHAR (
NVL ( TO_CHAR ( gjl . accounted_cr , '999G999G999D99' ) , 0 ) ) "Functional
Currency Credit" , '|' || TO_CHAR ( NVL ( TO_CHAR ( ( NVL ( gjl .
accounted_dr , 0 ) - NVL ( gjl . accounted_cr , 0 ) ) , '999G999G999D99' ) ,
0 ) ) "Functional Net" , gjl . reference_2 , gjl . reference_8 , gjl .
gl_sl_link_id , '|' || fu . user_name user_name
FROM
gl_je_headers gjh , gl_je_lines gjl , gl_je_sources gjs , gl_je_categories
gjc , gl_sets_of_books gsob , gl_code_combinations gcc , gl_periods gp ,
fnd_flex_values_vl ffvv , fnd_document_sequences fds , fnd_user fu where
gcc.SEGMENT1 BETWEEN '0000' AND 'ZZZZ' AND gcc.SEGMENT2 BETWEEN '0000' AND
'ZZZZ' AND gcc.SEGMENT3 BETWEEN '000' AND 'ZZZ' AND gcc.SEGMENT4 = '101101'
AND gcc.SEGMENT5 BETWEEN '0000000000' AND 'ZZZZZZZZZZ' AND gcc.SEGMENT6
BETWEEN '0000' AND 'ZZZZ' AND gcc.SEGMENT7 BETWEEN '00000' AND 'ZZZZZ' AND
gcc.SEGMENT8 BETWEEN '0000' AND 'ZZZZ' and gcc . chart_of_accounts_id = :
STRUCT_NUM and gjh . je_header_id = gjl . je_header_id and gjh . created_by
= fu . user_id and gjh . je_source = gjs . je_source_name and gjh .
actual_flag = : P_BALANCE_TYPE and gjh . je_category = gjc .
je_category_name and gjl . set_of_books_id = gsob . set_of_books_id and
gsob . set_of_books_id = : P_SET_OF_BOOKS_ID and gjl . period_name = gp .
period_name and gsob . period_set_name = gp . period_set_name and gjl .
code_combination_id = gcc . code_combination_id and gcc . segment4 = ffvv .
flex_value and ffvv . flex_value_set_id = : P_FLEX_VALUE_SET_ID and gjh .
doc_sequence_id = fds . doc_sequence_id (+) and fu . user_id = NVL ( :
p_user_id , fu . user_id ) and gjh . currency_code = UPPER ( DECODE ( :
p_currency , 'ALL' , gjh . currency_code , : p_currency ) ) and ( NVL ( gjl
. accounted_dr , 0 ) - NVL ( gjl . accounted_cr , 0 ) ) <> 0 and gjh .
je_source = DECODE ( : p_source , 'ALL' , gjh . je_source , : p_source )
and gp . start_date >= : P_PERIOD_START_DATE and gp . end_date <= :
P_PERIOD_END_DATE and to_date ( NVL ( gjh . posted_date , '31-DEC-4712' ) )
between to_date ( NVL ( : p_posted_date_from , NVL ( gjh . posted_date ,
'31-DEC-4712' ) ) ) AND to_date ( NVL ( : p_posted_date_to , NVL ( gjh .
posted_date , '31-DEC-4712' ) ) ) and gjh . status = NVL ( :
p_posted_status , gjh . status ) ORDER BY 5 , 4


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 1.12 1.32 0 0 0 0
Fetch 1194 127.96 595.51 409811 11348092 0 3581
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1196 129.09 596.85 409811 11348092 0 3581

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
3581 SORT ORDER BY (cr=11348092 pr=409811 pw=0 time=595461539 us)
3581 CONCATENATION (cr=11348092 pr=409811 pw=0 time=595300941 us)
3581 FILTER (cr=11348092 pr=409811 pw=0 time=595297309 us)
3581 NESTED LOOPS (cr=11348092 pr=409811 pw=0 time=595293724 us)
109226 NESTED LOOPS (cr=11129637 pr=409811 pw=0 time=593750711 us)
109226 MERGE JOIN CARTESIAN (cr=10911182 pr=409811 pw=0 time=592330754 us)
109226 NESTED LOOPS (cr=10911179 pr=409811 pw=0 time=591675235 us)
2797277 NESTED LOOPS (cr=1770854 pr=409764 pw=0 time=542968158 us)
75319 NESTED LOOPS (cr=304599 pr=3194 pw=0 time=10853739 us)
75319 NESTED LOOPS OUTER (cr=229252 pr=3194 pw=0 time=9949895 us)
75319 NESTED LOOPS (cr=229224 pr=3194 pw=0 time=9422648 us)
75319 NESTED LOOPS (cr=78577 pr=3194 pw=0 time=7990943 us)
75319 NESTED LOOPS (cr=3230 pr=3194 pw=0 time=6861125 us)
1 TABLE ACCESS BY INDEX ROWID GL_SETS_OF_BOOKS (cr=2 pr=0 pw=0 time=44 us)
1 INDEX UNIQUE SCAN GL_SETS_OF_BOOKS_U2 (cr=1 pr=0 pw=0 time=24 us)(object id 48849)
75319 TABLE ACCESS FULL GL_JE_HEADERS (cr=3228 pr=3194 pw=0 time=6785760 us)
75319 TABLE ACCESS BY INDEX ROWID GL_JE_SOURCES_TL (cr=75347 pr=0 pw=0 time=989265 us)
75319 INDEX UNIQUE SCAN GL_JE_SOURCES_TL_U1 (cr=28 pr=0 pw=0 time=423909 us)(object id 47604)
75319 TABLE ACCESS BY INDEX ROWID FND_USER (cr=150647 pr=0 pw=0 time=1300372 us)
75319 INDEX UNIQUE SCAN FND_USER_U1 (cr=75319 pr=0 pw=0 time=806393 us)(object id 132187)
75276 INDEX UNIQUE SCAN FND_DOCUMENT_SEQUENCES_U1 (cr=28 pr=0 pw=0 time=347503 us)(object id 39323)
75319 TABLE ACCESS BY INDEX ROWID GL_JE_CATEGORIES_TL (cr=75347 pr=0 pw=0 time=781439 us)
75319 INDEX UNIQUE SCAN GL_JE_CATEGORIES_TL_U1 (cr=28 pr=0 pw=0 time=325842 us)(object id 47508)
2797277 TABLE ACCESS BY INDEX ROWID GL_JE_LINES (cr=1466255 pr=406570 pw=0 time=528864195 us)
11897161 INDEX RANGE SCAN GL_JE_LINES_U1 (cr=263153 pr=34019 pw=0 time=67896828 us)(object id 47576)
109226 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=9140325 pr=47 pw=0 time=44546161 us)
2797277 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=5596468 pr=47 pw=0 time=20467071 us)(object id 45791)
109226 BUFFER SORT (cr=3 pr=0 pw=0 time=310558 us)
1 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES (cr=3 pr=0 pw=0 time=62 us)
1 INDEX RANGE SCAN FND_FLEX_VALUES_N1 (cr=2 pr=0 pw=0 time=20 us)(object id 255655)
109226 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES_TL (cr=218455 pr=0 pw=0 time=1319312 us)
109226 INDEX UNIQUE SCAN FND_FLEX_VALUES_TL_U1 (cr=109229 pr=0 pw=0 time=705643 us)(object id 46939)
3581 TABLE ACCESS BY INDEX ROWID GL_PERIODS (cr=218455 pr=0 pw=0 time=1372849 us)
109226 INDEX UNIQUE SCAN GL_PERIODS_U1 (cr=109229 pr=0 pw=0 time=599721 us)(object id 48037)
0 FILTER (cr=0 pr=0 pw=0 time=3 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID FND_USER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN FND_USER_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 132187)
0 TABLE ACCESS BY INDEX ROWID GL_SETS_OF_BOOKS (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN GL_SETS_OF_BOOKS_U2 (cr=0 pr=0 pw=0 time=0 us)(object id 48849)
0 TABLE ACCESS FULL GL_JE_HEADERS (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID GL_JE_SOURCES_TL (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN GL_JE_SOURCES_TL_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 47604)
0 TABLE ACCESS BY INDEX ROWID GL_JE_CATEGORIES_TL (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN GL_JE_CATEGORIES_TL_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 47508)
0 INDEX UNIQUE SCAN FND_DOCUMENT_SEQUENCES_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 39323)
0 TABLE ACCESS BY INDEX ROWID GL_JE_LINES (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN GL_JE_LINES_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 47576)
0 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 45791)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN FND_FLEX_VALUES_N1 (cr=0 pr=0 pw=0 time=0 us)(object id 255655)
0 TABLE ACCESS BY INDEX ROWID GL_PERIODS (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN GL_PERIODS_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 48037)
0 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES_TL (cr=0 pr=0 pw=0 time=0 us)
0 INDEX UNIQUE SCAN FND_FLEX_VALUES_TL_U1 (cr=0 pr=0 pw=0 time=0 us)(object id 46939)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
3581 SORT (ORDER BY)
3581 CONCATENATION
3581 FILTER
3581 NESTED LOOPS
109226 NESTED LOOPS
109226 MERGE JOIN (CARTESIAN)
109226 NESTED LOOPS
2797277 NESTED LOOPS
75319 NESTED LOOPS
75319 NESTED LOOPS
75319 NESTED LOOPS
75319 NESTED LOOPS (OUTER)
75319 NESTED LOOPS
1 TABLE ACCESS MODE: ANALYZED (BY
INDEX ROWID) OF 'GL_SETS_OF_BOOKS' (TABLE)
1 INDEX MODE: ANALYZED (UNIQUE SCAN)
OF 'GL_SETS_OF_BOOKS_U2' (INDEX (UNIQUE))
75319 TABLE ACCESS MODE: ANALYZED (FULL)
OF 'GL_JE_HEADERS' (TABLE)
75319 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'FND_DOCUMENT_SEQUENCES_U1' (INDEX (UNIQUE))
75319 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID) OF 'FND_USER' (TABLE)
75319 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'FND_USER_U1' (INDEX (UNIQUE))
75319 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID) OF 'GL_JE_CATEGORIES_TL' (TABLE)
75276 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'GL_JE_CATEGORIES_TL_U1' (INDEX (UNIQUE))
75319 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'GL_JE_SOURCES_TL' (TABLE)
75319 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'GL_JE_SOURCES_TL_U1' (INDEX (UNIQUE))
2797277 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'GL_JE_LINES' (TABLE)
11897161 INDEX MODE: ANALYZED (RANGE SCAN) OF
'GL_JE_LINES_U1' (INDEX (UNIQUE))
109226 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'GL_CODE_COMBINATIONS' (TABLE)
2797277 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'GL_CODE_COMBINATIONS_U1' (INDEX (UNIQUE))
109226 BUFFER (SORT)
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'FND_FLEX_VALUES' (TABLE)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF
'FND_FLEX_VALUES_N1' (INDEX)
109226 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'FND_FLEX_VALUES_TL' (TABLE)
109226 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'FND_FLEX_VALUES_TL_U1' (INDEX (UNIQUE))
3581 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'GL_PERIODS' (TABLE)
109226 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'GL_PERIODS_U1' (INDEX (UNIQUE))
0 FILTER
0 NESTED LOOPS
0 MERGE JOIN (CARTESIAN)
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS (OUTER)
0 NESTED LOOPS
0 NESTED LOOPS
0 HASH JOIN
0 NESTED LOOPS
0 TABLE ACCESS MODE: ANALYZED (BY
INDEX ROWID) OF 'FND_USER' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN)
OF 'FND_USER_U1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY
INDEX ROWID) OF 'GL_SETS_OF_BOOKS' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN)
OF 'GL_SETS_OF_BOOKS_U2' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'GL_JE_HEADERS' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID) OF 'GL_JE_SOURCES_TL' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'GL_JE_SOURCES_TL_U1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX
ROWID) OF 'GL_JE_CATEGORIES_TL' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'GL_JE_CATEGORIES_TL_U1' (INDEX (UNIQUE))
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'FND_DOCUMENT_SEQUENCES_U1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'GL_JE_LINES' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'GL_JE_LINES_U1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'GL_PERIODS' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'GL_PERIODS_U1' (INDEX (UNIQUE))
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'GL_CODE_COMBINATIONS' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'GL_CODE_COMBINATIONS_U1' (INDEX (UNIQUE))
0 BUFFER (SORT)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'FND_FLEX_VALUES' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'FND_FLEX_VALUES_N1' (INDEX)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'FND_FLEX_VALUES_TL' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'FND_FLEX_VALUES_TL_U1' (INDEX (UNIQUE))


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1194 0.00 0.00
gc cr multi block request 273 0.06 0.37
db file scattered read 412 0.03 2.81
gc current block 2-way 163 0.02 0.14
db file sequential read 406619 0.37 425.45
gc cr grant 2-way 17149 0.21 17.35
latch: cache buffers chains 14 0.00 0.00
gc cr block 2-way 4 0.00 0.00
gc cr grant congested 11 0.05 0.11
db file parallel read 1 0.00 0.00
latch free 2 0.00 0.00
latch: KCL gc element parent latch 3 0.00 0.00
gc cr block busy 1 0.00 0.00
SQL*Net more data to client 1 0.00 0.00
SQL*Net message from client 1194 0.00 0.40
********************************************************************************

Tom, I follow your methodologies because you are able to prove them and nobody can argue against proof. I am an advocate of a blend between Steven Feuerstein “PL SQL Object methodology but at the Business Transaction level” as a wrapper around your proven idea of do everything in SQL if you can. One thing that has caught my attention when looking at the Oracle documentation around PL SQL performance tuning is: why does the documentation not state that you should try to do everything you can in SQL to have a robust scaleable solution? If I did not read your books and visit this site I would think that PL SQL and bulk collect…. are the best way to get my code to scale.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#BCGCIFGH

Did I miss something?

Hi Tom,

Thanks in advance for your help.

I am having a strange situation here.

When I see the out of a Trace file (from a java session).. for example as below

SELECT NVL(MAX(CHANGE_SEQ_NO), 0)
FROM
CSTBS_CONTRACT_CHANGE_LOG WHERE CONTRACT_REF_NO = :B2 AND EVENT_SEQ_NO = :B1



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 1.49 1.71 0 40636 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10 1.49 1.71 0 40636 0 4

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

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=10159 pr=0 pw=0 time=0 us)
0 FIRST ROW (cr=10159 pr=0 pw=0 time=0 us cost=2 size=1575 card=63)
0 INDEX RANGE SCAN (MIN/MAX) PK01_CSTB_CONTRACT_CHANGE_LOG (cr=10159 pr=0 pw=0 time=0 us cost=2 size=1575 card=63)(object id 671118)


You can see that the elapsed time is 1.71 seconds.

But if i run the query with data in sql*plus

SELECT NVL(MAX(CHANGE_SEQ_NO), 0)
2 FROM
3 CSTBS_CONTRACT_CHANGE_LOG WHERE CONTRACT_REF_NO = 'BBSSDAX092100038' AND EVENT_SEQ_NO = 1
4 /

NVL(MAX(CHANGE_SEQ_NO),0)
-------------------------
64

1 row selected.

Elapsed: 00:00:00.04

As you can see , I get the result in 4 milli seconds.

I am using oracle 11g .

You can please answer this abnormal situation.

Thanks
SKL

The query should change, as needed.
I've seen queries go 10X longer/shorter depending on production workloads.
When I trace in production, the thing I look for is "work done"; ie logical+physical i/o, and almost always the i/o is the same even if the elapsed is much different. The few times the i/o changes, it is almost always the result of PGA and higher/lower usage of memory for sorts.

Oracle really does a very good job of optimizing to workload.

HI TOM ,
In one of the answers up, you mentioned ,
The logon trigger - almost a ZERO percent change of success with that in a 3 tier J2EE application.
Can you please tell us why so? I thought becuase either with or without coneection pooling , we would know which the user ( Oracle user ) on which create the logon trigger
and just enable tre trace and at the logoff disable it

Hi Tom,

In a company - their database performance is very poor and slow - they dont know exactly where the issue is -
If the company consults to you and asks you to 'tune' their database-

What is the first thing you do ?
Where do you start ?

(I mean do you run the statspack reports - ? )

How to approach the problem ?

Can you please explain in few standarad steps which we we have to follow INTIALLY to find out the issue with the database (assume that we dont know any thing about the database and data?)

Hi Tom,

i would like to know a better apporach for the problem:

There is a table with more than 5 million rows, of which there iis a STATUS column with different value. I am concerned with Status = 100, 101. Which does not have partition, no primary key as well. Now I have to access that table multiple times(may be 10 times with different criterias) in my CFL(Cursor For Loop)..

Which makes the life difficult as I have to wait for atleast 6 hours for the package to complete. Can I select the rows that are with status = 100 and put them :

1. In a TABLE or VARRAY COLLECTION
2. Create a table in database to keep the intermediate selected rows and use this new table in my Cursor.
3. Or a Global temporary table

I have some issues as 3-4 million of 6-7 million records can satisfy the criteria I have.

Please suggest. I am not able to provide you any Create Insert script as it is with million records.

Hi Tom,

Thanks for your reply.. Yes we are accessing the selected rows in side the cursor and in side cursor as well I have to do a select from that table for some other property existence.. The whole package needs at least 6 hours to be completed..

Reasons.. Table having million of records.. Appended weekly.. So am not very sure if I could make some other logic except those three solutions..

In such a case, what should I do... Any futher guidance will be appreciated.

Tom, you mentioned some time ago about how tracing the activity in an entire service is not a good idea for a number of reasons. With web systems it's not so easy to pick up a particular session to monitor it's performance through session level tracing. What is the alternative to instance level tracing to spot bottlenecks or poorly performing SQL. I know Statspack will pick out 'heavy' sql running during the window that's being monitored but that doesn't give you session information. I can also run ad hoc queries to check various counters but again these may not give the stats required.

For the quick response re ASH. Isn't this part of the Diagnostics pack, along with AWR, which means it generates an additional licence cost ?

For the quick response. Have to see whether I can justify the extra cost with the people who can spend the money. Hope you're enjoying your visit to 'Brum'.

It's hard to get licensing for a lot of things. I think Real Application Tracing and Diagnostics and Tuning are so high value they would pay several times over the cost of licensing. The drawback in a larger shop I've seen is we use just a fraction of what we license already.
No XML, No analytic (or scarce), no SQL beyond 10 year old SQL, because over 99% of the infrastructure was developed on 10 year old platforms. I've put in code I detest years ago that I'd really like to rip out and do over. Everyone says "No problems, no rebuilds". And I usually have my hands full with the use bind variables and bulk inserts in new projects.

Hi Tom,

DB version is 10.2.0.4 below is the sql which is running now in production for more than 28 hours now..why its taking long time..? 

SQL> explain plan for INSERT INTO JSW_SALEM_RECEIVING_TEMP_T  SELECT 'Inventory',
  2           'Inventory_Tax1', OOD.ORGANIZATION_CODE, OOD.ORGANIZATION_ID,
  3           RSH.RECEIPT_NUM, RT.TRANSACTION_ID, RT.TRANSACTION_DATE,
  4           RT.TRANSACTION_TYPE, MSI.SEGMENT1, MSI.DESCRIPTION, 0 RECEVIED_QTY, 0
  5           RECV_BASIC_VAL, 0 REC_ED, 0 DEL_QTY, 0 DEL_BASIC_VAL, GL.ACCOUNTED_CR
  6           DEL_ED, 0 REJ_QTY, 0 REJ_BASIC_VAL, 0 REJ_ED, 0 RETURN_RECEIVING_QTY,
  7           0 RETURN_TO_RECEVING_BASIC, GL.ACCOUNTED_DR RETURN_TO_RECEIVING_ED, 0
  8           RETURN_TO_VENDOR_QTY, 0 RETURN_TO_VENDOR_BASIC, 0
  9           RETURN_TO_VENDOR_ED, MMT.TRANSACTION_ID MTL_TRANSACTION_ID,
 10           PH.SEGMENT1, PV.SEGMENT1, PV.VENDOR_NAME, PVS.VENDOR_SITE_CODE,
 11           GCC.SEGMENT1, GCC.SEGMENT3, GH.JE_SOURCE, GB.NAME, GB.DESCRIPTION,
 12           GP.PERIOD_NAME, GL.ACCOUNTED_DR, GL.ACCOUNTED_CR, ROWNUM,0
 13           CORRECTION_QTY,0 CORRECTION_VALUE FROM GL_JE_LINES GL, GL_JE_HEADERS
 14           GH, GL_JE_BATCHES GB, GL_PERIODS GP, GL_CODE_COMBINATIONS GCC,
 15           MTL_TRANSACTION_ACCOUNTS MTA, MTL_SYSTEM_ITEMS MSI,
 16           ORG_ORGANIZATION_DEFINITIONS OOD, MTL_MATERIAL_TRANSACTIONS MMT,
 17           RCV_TRANSACTIONS RT, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES
 18           RSL, PO_HEADERS_ALL PH, PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS WHERE
 19           GP.ATTRIBUTE1 BETWEEN 257 AND 272 AND GL.PERIOD_NAME = GP.PERIOD_NAME
 20           AND GL.CODE_COMBINATION_ID IN ( SELECT CODE_COMBINATION_ID FROM
 21           GL_CODE_COMBINATIONS WHERE SEGMENT1 = '11' AND SEGMENT3 = 1601091 ) AND
 22           GL.SET_OF_BOOKS_ID = 13013 AND GL.STATUS = 'P' AND GL.JE_HEADER_ID =
 23           GH.JE_HEADER_ID AND GH.JE_BATCH_ID = GB.JE_BATCH_ID AND
 24           GL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GB.ACTUAL_FLAG =
 25           'A' AND GH.JE_SOURCE = 'Inventory' AND GL.REFERENCE_3 = TO_CHAR
 26           (MTA.TRANSACTION_ID) AND GL.CODE_COMBINATION_ID =
 27           MTA.REFERENCE_ACCOUNT AND MTA.INVENTORY_ITEM_ID =
 28           MSI.INVENTORY_ITEM_ID AND MTA.ORGANIZATION_ID = MSI.ORGANIZATION_ID
 29           AND OOD.OPERATING_UNIT = 1541 AND MTA.ORGANIZATION_ID =
 30           OOD.ORGANIZATION_ID AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID AND
 31           MMT.TRANSACTION_TYPE_ID = 80 AND MMT.TRANSACTION_REFERENCE =
 32           RT.TRANSACTION_ID AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
 33           AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND RT.PO_HEADER_ID =
 34           PH.PO_HEADER_ID AND PH.VENDOR_ID = PV.VENDOR_ID AND PH.VENDOR_SITE_ID
 35           = PVS.VENDOR_SITE_ID;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 377390074

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                          | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                                   |                                |     1 |   511 |   873   (1)| 00:00:11 |
|   1 |  COUNT                                             |                                |       |       |            |          |
|   2 |   NESTED LOOPS                                     |                                |     1 |   511 |   873   (1)| 00:00:11 |
|   3 |    NESTED LOOPS                                    |                                |     1 |   506 |   872   (1)| 00:00:11 |
|   4 |     NESTED LOOPS                                   |                                |     1 |   504 |   870   (1)| 00:00:11 |
|   5 |      NESTED LOOPS                                  |                                |     1 |   478 |   869   (1)| 00:00:11 |
|   6 |       NESTED LOOPS                                 |                                |     1 |   451 |   867   (1)| 00:00:11 |
|   7 |        NESTED LOOPS                                |                                |     1 |   369 |   865   (1)| 00:00:11 |
|   8 |         NESTED LOOPS                               |                                |     1 |   363 |   864   (1)| 00:00:11 |
|   9 |          NESTED LOOPS                              |                                |     1 |   350 |   862   (1)| 00:00:11 |
|  10 |           NESTED LOOPS                             |                                |     1 |   336 |   861   (1)| 00:00:11 |
|  11 |            NESTED LOOPS                            |                                |     1 |   298 |   860   (1)| 00:00:11 |
|  12 |             NESTED LOOPS                           |                                |     1 |   279 |   858   (1)| 00:00:11 |
|  13 |              NESTED LOOPS                          |                                |     1 |   241 |   856   (1)| 00:00:11 |
|  14 |               NESTED LOOPS                         |                                |     1 |   226 |   854   (1)| 00:00:11 |
|  15 |                NESTED LOOPS                        |                                |     1 |   218 |   853   (1)| 00:00:11 |
|  16 |                 NESTED LOOPS                       |                                |     1 |   211 |   853   (1)| 00:00:11 |
|  17 |                  NESTED LOOPS                      |                                |     1 |   204 |   852   (1)| 00:00:11 |
|  18 |                   NESTED LOOPS                     |                                |     1 |   183 |   169   (1)| 00:00:03 |
|  19 |                    NESTED LOOPS                    |                                |     1 |   107 |   167   (1)| 00:00:03 |
|  20 |                     NESTED LOOPS                   |                                |     1 |    91 |   166   (1)| 00:00:03 |
|* 21 |                      HASH JOIN                     |                                |     1 |    69 |   164   (1)| 00:00:03 |
|* 22 |                       TABLE ACCESS BY INDEX ROWID  | GL_JE_LINES                    |    12 |   504 |   123   (0)| 00:00:02 |
|  23 |                        NESTED LOOPS                |                                |    32 |  1856 |   159   (0)| 00:00:02 |
|  24 |                         TABLE ACCESS BY INDEX ROWID| GL_CODE_COMBINATIONS           |     3 |    48 |    24   (0)| 00:00:01 |
|* 25 |                          INDEX RANGE SCAN          | GL_CODE_COMBINATIONS_N2        |     3 |       |    22   (0)| 00:00:01 |
|* 26 |                         INDEX RANGE SCAN           | GL_JE_LINES_N1                 |   933 |       |     6   (0)| 00:00:01 |
|* 27 |                       TABLE ACCESS FULL            | GL_PERIODS                     |     1 |    11 |     4   (0)| 00:00:01 |
|* 28 |                      TABLE ACCESS BY INDEX ROWID   | GL_JE_HEADERS                  |     1 |    22 |     2   (0)| 00:00:01 |
|* 29 |                       INDEX UNIQUE SCAN            | GL_JE_HEADERS_U1               |     1 |       |     1   (0)| 00:00:01 |
|  30 |                     TABLE ACCESS BY INDEX ROWID    | GL_CODE_COMBINATIONS           |     1 |    16 |     1   (0)| 00:00:01 |
|* 31 |                      INDEX UNIQUE SCAN             | GL_CODE_COMBINATIONS_U1        |     1 |       |     0   (0)| 00:00:01 |
|* 32 |                    TABLE ACCESS BY INDEX ROWID     | GL_JE_BATCHES                  |     1 |    76 |     2   (0)| 00:00:01 |
|* 33 |                     INDEX UNIQUE SCAN              | GL_JE_BATCHES_U1               |     1 |       |     1   (0)| 00:00:01 |
|* 34 |                   TABLE ACCESS BY INDEX ROWID      | MTL_TRANSACTION_ACCOUNTS       |     1 |    21 |   683   (0)| 00:00:09 |
|* 35 |                    INDEX RANGE SCAN                | MTL_TRANSACTION_ACCOUNTS_N3    |  4045 |       |    25   (0)| 00:00:01 |
|* 36 |                  TABLE ACCESS BY INDEX ROWID       | HR_ALL_ORGANIZATION_UNITS      |     1 |     7 |     1   (0)| 00:00:01 |
|* 37 |                   INDEX UNIQUE SCAN                | HR_ORGANIZATION_UNITS_PK       |     1 |       |     0   (0)| 00:00:01 |
|* 38 |                 INDEX UNIQUE SCAN                  | HR_ALL_ORGANIZATION_UNTS_TL_PK |     1 |     7 |     0   (0)| 00:00:01 |
|  39 |                TABLE ACCESS BY INDEX ROWID         | MTL_PARAMETERS                 |     1 |     8 |     1   (0)| 00:00:01 |
|* 40 |                 INDEX UNIQUE SCAN                  | MTL_PARAMETERS_U1              |     1 |       |     0   (0)| 00:00:01 |
|* 41 |               TABLE ACCESS BY INDEX ROWID          | MTL_MATERIAL_TRANSACTIONS      |     1 |    15 |     2   (0)| 00:00:01 |
|* 42 |                INDEX UNIQUE SCAN                   | MTL_MATERIAL_TRANSACTIONS_U1   |     1 |       |     1   (0)| 00:00:01 |
|* 43 |              TABLE ACCESS BY INDEX ROWID           | RCV_TRANSACTIONS               |     1 |    38 |     2   (0)| 00:00:01 |
|* 44 |               INDEX UNIQUE SCAN                    | RCV_TRANSACTIONS_U1            |     1 |       |     1   (0)| 00:00:01 |
|* 45 |             TABLE ACCESS BY INDEX ROWID            | PO_HEADERS_ALL                 |     1 |    19 |     2   (0)| 00:00:01 |
|* 46 |              INDEX UNIQUE SCAN                     | PO_HEADERS_U1                  |     1 |       |     1   (0)| 00:00:01 |
|  47 |            TABLE ACCESS BY INDEX ROWID             | PO_VENDORS                     |     1 |    38 |     1   (0)| 00:00:01 |
|* 48 |             INDEX UNIQUE SCAN                      | PO_VENDORS_U1                  |     1 |       |     0   (0)| 00:00:01 |
|  49 |           TABLE ACCESS BY INDEX ROWID              | PO_VENDOR_SITES_ALL            |     1 |    14 |     1   (0)| 00:00:01 |
|* 50 |            INDEX UNIQUE SCAN                       | PO_VENDOR_SITES_U1             |     1 |       |     0   (0)| 00:00:01 |
|  51 |          TABLE ACCESS BY INDEX ROWID               | RCV_SHIPMENT_HEADERS           |     1 |    13 |     2   (0)| 00:00:01 |
|* 52 |           INDEX UNIQUE SCAN                        | RCV_SHIPMENT_HEADERS_U1        |     1 |       |     1   (0)| 00:00:01 |
|* 53 |         INDEX UNIQUE SCAN                          | RCV_SHIPMENT_LINES_U1          |     1 |     6 |     1   (0)| 00:00:01 |
|  54 |        TABLE ACCESS BY INDEX ROWID                 | MTL_SYSTEM_ITEMS_B             |     1 |    82 |     2   (0)| 00:00:01 |
|* 55 |         INDEX UNIQUE SCAN                          | MTL_SYSTEM_ITEMS_B_U1          |     1 |       |     1   (0)| 00:00:01 |
|* 56 |       TABLE ACCESS BY INDEX ROWID                  | HR_ORGANIZATION_INFORMATION    |     1 |    27 |     2   (0)| 00:00:01 |
|* 57 |        INDEX RANGE SCAN                            | HR_ORGANIZATION_INFORMATIO_FK2 |     1 |       |     1   (0)| 00:00:01 |
|  58 |      TABLE ACCESS BY INDEX ROWID                   | HR_ORGANIZATION_INFORMATION    |     1 |    26 |     1   (0)| 00:00:01 |
|* 59 |       INDEX RANGE SCAN                             | HR_ORGANIZATION_INFORMATIO_FK2 |     1 |       |     1   (0)| 00:00:01 |
|* 60 |     TABLE ACCESS FULL                              | FND_PRODUCT_GROUPS             |     1 |     2 |     2   (0)| 00:00:01 |
|* 61 |    INDEX FULL SCAN                                 | GL_SETS_OF_BOOKS_U2            |     1 |     5 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

thanks,
baskar.l

Hi Tom,

I have the below three approaches for inserting data.

a) insert into table1 select * from table2

b) loop
fetch some_cur bulk collect into some_tab limit 500;
--some_cur is 'select * from table2'
forall i in 1..some_tab.count
insert into table1 values some_tab(i);
commit;
end loop

c) fetch some_cur bulk collect into some_tab
forall i in 1..some_tab.count
insert into table1 values some_tab(i);

table2 contains millions of rows

1) which of the above approaches is the better approach

2) If the rollback segment is not sufficient, will approach (a) cause a performance issue.

Thank you

Tom -

I have been asked by an application support team to diagnose percieved poor performance of a particular transaction. This is from a third party application and the performance is poor relative to what the vendor's support team reports for the same transaction.

I have traced and TKPROF'ed the statement. Here is the output from that (I have added an LN# column to the row source section to facilitate discussion):

********************************************************************************

delete from MTMAIN.T126_TS_MWB_PO 
where
 run_id=:1 and sec_id NOT IN(SELECT DISTINCT r.sec_id FROM 
  MTMAIN.T126_TS_MWB_SECURITY r JOIN csm_security s2 ON(s2.sec_id=r.sec_id)
  JOIN csm_sec_typ_inv_class stic ON(stic.sec_typ_cd=s2.sec_typ_cd)WHERE 
  stic.inv_class_cd='CURR')AND(sec_id NOT IN(SELECT r.sec_id FROM 
  MTMAIN.T126_TS_MWB_SECURITY r JOIN csm_security s2 ON(s2.sec_id=r.sec_id)
  WHERE s2.sec_typ_cd IN(SELECT sec_typ_cd from csm_sec_typ_inv_class WHERE 
  inv_class_cd IN(:2)))OR sec_id NOT IN(SELECT r.sec_id FROM 
  MTMAIN.T126_TS_MWB_SECURITY r JOIN csm_security_industry secind 
  ON(secind.sec_id=r.sec_id)WHERE secind.indust_class_cd=:3 AND 
  secind.indust_cd NOT IN(:4))OR sec_id NOT IN(SELECT r.sec_id FROM 
  MTMAIN.T126_TS_MWB_SECURITY r JOIN csm_security_industry secind 
  ON(secind.sec_id=r.sec_id)WHERE secind.indust_class_cd=:5 AND 
  secind.indust_cd NOT IN(:6)))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      2     57.11      55.78          0     697677      17231       14162
Fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     57.11      55.78          0     697677      17231       14163

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

LN#  Rows     Row Source Operation
---  -------  ---------------------------------------------------
  1        0  DELETE  T126_TS_MWB_PO (cr=697678 pr=0 pw=0 time=55754796 us)
  2    14162   FILTER  (cr=697671 pr=0 pw=0 time=54637822 us)
  3    32850    INDEX RANGE SCAN T126_TS_MWB_PO_N1 (cr=322406 pr=0 pw=0 time=25165585 us)(object id 16273)
  4      119     NESTED LOOPS  (cr=322143 pr=0 pw=0 time=24887532 us)
  5    17956      NESTED LOOPS  (cr=286231 pr=0 pw=0 time=24543134 us)
  6    17956       TABLE ACCESS FULL T126_TS_MWB_SECURITY (cr=214407 pr=0 pw=0 time=23721471 us)
  7    17956       TABLE ACCESS BY INDEX ROWID CSM_SECURITY (cr=71824 pr=0 pw=0 time=727871 us)
  8    17956        INDEX UNIQUE SCAN CSM_SECURITY_PK (cr=53868 pr=0 pw=0 time=454663 us)(object id 12664)
  9      119      INDEX UNIQUE SCAN CSM_SEC_TYP_INV_CLASS_PK (cr=35912 pr=0 pw=0 time=271912 us)(object id 12706)
 10     7809    NESTED LOOPS  (cr=285695 pr=0 pw=0 time=21231844 us)
 11    17746     NESTED LOOPS  (cr=250203 pr=0 pw=0 time=20893456 us)
 12    17746      TABLE ACCESS FULL T126_TS_MWB_SECURITY (cr=179219 pr=0 pw=0 time=20194563 us)
 13    17746      TABLE ACCESS BY INDEX ROWID CSM_SECURITY (cr=70984 pr=0 pw=0 time=627818 us)
 14    17746       INDEX UNIQUE SCAN CSM_SECURITY_PK (cr=53238 pr=0 pw=0 time=410233 us)(object id 12664)
 15     7809     INDEX UNIQUE SCAN CSM_SEC_TYP_INV_CLASS_PK (cr=35492 pr=0 pw=0 time=271323 us)(object id 12706)
 16     4027    NESTED LOOPS  (cr=46591 pr=0 pw=0 time=4100272 us)
 17     4252     TABLE ACCESS FULL T126_TS_MWB_SECURITY (cr=33834 pr=0 pw=0 time=3942256 us)
 18     4027     INDEX RANGE SCAN CSM_SECURITY_INDUSTRY_PK (cr=12757 pr=0 pw=0 time=141336 us)(object id 12690)
 19     4003    NESTED LOOPS  (cr=42979 pr=0 pw=0 time=3740233 us)
 20     4003     TABLE ACCESS FULL T126_TS_MWB_SECURITY (cr=30970 pr=0 pw=0 time=3613925 us)
 21     4003     INDEX RANGE SCAN CSM_SECURITY_INDUSTRY_PK (cr=12009 pr=0 pw=0 time=110177 us)(object id 12690)

********************************************************************************

Observations:

A. There are four seperate full table scans on T126_TS_MWB_SECURITY. These are on lines 6, 12, 17 and 20.

B. T126_TS_MWB_SECURITY is a global temporary table.

C. Together these four table scans account for approximately 51.5 seconds of the 55.78 seconds of elapsed execution time.

D. The table scans are all in memory (as are all of the other steps).

E. An AWR report for the period when this was run confirms no I/O on the user's temporary tablespace.

Questions:

1. Does the lack of any physical reads for the FTS of T126_TS_MWB_SECURITY indicate that the blocks are already in the buffer cache or simply that the tables temporary segments are all in memory?

2. Why are the CR block counts different for each of the four table scans? If Oracle is scanning the entire table shouldn't the block count be the same each time?

3. I think that 51.5 seconds is a long time for reading memory and that much of this time is probably due to blocks being copied from the temporary segments to the LRU end of the buffer cache. Do you think that this is the case? If so, how would I go about verifying that? If not, what do you believe the explanation is?

4. What do you recommend as a next step in diagnosing and tuning this? (I've been told that I can't modify the SQL, only the instance.)


Thank you in advance for any insight that you care to offer.

ops$tkyte%ORA11GR2> select 57.11/697678  from dual;

57.11/697678
------------
  .000081857

I should have also stated that this is Oracle 10.2.0.5. Also, we see indications that the overall process that this is part of is CPU bound. The Oracle server process consistently uses ~12.5% of an eight CPU system (or 100% of one core).

Tom:

Would you advise us on purchasing the diagnostics and tuning packs with 11gR2?

do you use them? are they useful somehow or you use scripts and OEM mostly.

You basically can't live with out them. It's so lame they charge extra for it. Especially since it's gathering all that good information for you right out of the box....it's entrapment :)

Hi Tom,

SQL Trace is very effective, but I don't think it can really help in case of parallel queries. I found SQL Monitor very useful. Infact I found it effective enough to replace SQL Trace. What are views about it?

Hi Tom,

While trying to diagnose a problem installing Grid Control, I came across a curious problem relating to the speed with which TNS resolves names.

When I performed a standard ping to the localhost, the response time was <1ms which is naturally expected. When I performed a TNSPING to a listener, ORCL, the response time was averaging out at 30ms. My HOSTS file has the following entries:

127.0.0.1 localhost
192.168.0.1 myserver.home.local

LISTENER.ORA looks like this:

# listener.ora Network Configuration File: c:\programme\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = c:\programme\oracle\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:c:\programme\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver.home.local)(PORT = 1521))
)
)

ORCL =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
)

ADR_BASE_LISTENER = c:\programme\oracle

# tnsnames.ora Network Configuration File: c:\programme\oracle\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = myserver.home.local)
)
)

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

And this is SQLNET.ORA:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

The installation is a fairly simple 11.2.0.2 database, WebLogic Server 10.3.2 and Grid Control 11.1.0.1. I had installed the software in the following order:

1. WebLogic Server
2. Database
3. Grid Control

I would be grateful for any information you might be able to share. MyOracleSupport has a lot of entries relating to this issue but nothing seems to work up until now.

Many thanks in anticipation.

Regards,

Kev

Hi Tom,

The issue that I was trying to represent above was based on the significant difference in timings between the PING utility and TNSPING.

Why this is significant is that in the error logs generated during the failed installation of Grid Control, it was suggested that a connection time-out had occured and it was that that was (in part at least) responsible for the installation failure. Coupled with the fact that both utilities were executed on the localhost I did think that these timings were different enough to be significant.

Interestingly, (from my point at least) is that I have now rearranged the entries in TNSNAMES and LISTENER files so that the most commonly used are at the top of the list and now I get a response time of <0ms instead of <30ms. Although I am aware that TNSPING does a great deal more than simply throw an ICMP packet out and hope it comes back, I was still concerned that resolution takes such a "long" time to do its work in comparison.

Rearranging the entries in the two files has resulted in success as far as the afore-mentioned issue was concerned although the grid control installation is still proving troublesome for another reason now!!!

Many thanks for your constructive comments!

Regards,

Kev

Tom,
We are using Oracle10g in windows 64 bit OS , 32 GB RAM.
What I observed , one bad sql (Causing lots of IO due to full table scan on big tables , and running for a while) slows down the entire database , and have a ripple effect on the system. Is there anyway so that I can set the priority of the bad sql , either at the time of submitting the sql or afterwords (I know the application or session generating the bad sql) , so that the bad sql runs with lowest priority , and didn't impact others.

Thank you very much Tom for letting me know about resource manager. I have gone through this , but it does not really help much in addressing the issue.
As I understood and observed in the test , that with the resource manager we can control CPU usage. But in our database box CPU usage is not an issue at all. Total CPU usage is less that 10% even when a process running with lots of IO , slowing down the others. What we have observed in the machine , there is a disk queing. And with the long running process and much IO disk queing is more and it slows down others as well.
Is there any solution , so that we can change the priority of the long running process so that it make less disk queing , and let the others to go , and will proceed only if it see is free , or change the priority.

Can you please tell whether your book "expert oracle database architecture 9i 10g book" is available in India???

I was seeing its content and few default pages and found
very interesting.

regards,
Biswaranjan

Hi Tom,

In two different session I ran the below query and got confused by its output(specially consistent get and physical 
read things).

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SESSION1> alter system flush shared_pool;

System altered.

SESSION1> alter session set optimizer_dynamic_sampling=0;

Session altered.

SESSION1> create table a(a number);

Table created.

SESSION1> set autotrace traceonly
SESSION1> select * from a;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

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


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


SESSION2> alter session set optimizer_dynamic_sampling=0;
session altered
SESSION2> set autotrace traceonly
SESSION2> select * from a;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

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


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

SESSION1> insert into a select level from dual connect by level<2000000;

1999999 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | INSERT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|   1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       1780  recursive calls
      36588  db block gets
       6440  consistent gets
          0  physical reads
   32581304  redo size
        674  bytes sent via SQL*Net to client
        593  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
    1999999  rows processed

SESSION1> select * from a;

1999999 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

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


Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
     136475  consistent gets
         29  physical reads
        176  redo size
   38303417  bytes sent via SQL*Net to client
    1467044  bytes received via SQL*Net from client
     133335  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1999999  rows processed

SESSION2> select * from a;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      16811  consistent gets
        115  physical reads
     194072  redo size
        270  bytes sent via SQL*Net to client
        370  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SESSION1> rollback;

Rollback complete.

SESSION1> select * from a;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

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


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

SESSION2> select * from a;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2248738933

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


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

In above outputs when for the session2 no records are there (in session one 1999999 records are yet to be commit) why it
showed too many consistent gets and physical reads.
I was thinking the explain plans  results are session
specific but not sure.

And another big confusion even after rollback done in session1 why in both session still so many consistent gets and physical reads.

thanks in advance,
Biswaranjan.

Hi Tom,

Hope your weekend was good.:)

first of all thank you for providing the "book link" ,I just downloaded it .

And thanks a lot for the nice explanation(now I understood why so many consistent and physical gets).I am glad that I am learning something new.

regards,
Biswaranja.