Tom also has been saying the same ....
October 5, 2003 - 10am Central time zone
Reviewer: Reader from Houston, TX
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.
lucky man ...
October 5, 2003 - 3pm Central time zone
Reviewer: j.
One has to wait a fortnight to get tom's new book via AMAZON in germany ...
Is your book available in India also?
October 5, 2003 - 6pm Central time zone
Reviewer: Dilbert from San Jose, ca, USA
Followup October 5, 2003 - 6pm Central time zone:
it will be
More specifically....
October 5, 2003 - 9pm Central time zone
Reviewer: Tony
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,
Followup October 6, 2003 - 7am Central time zone:
yes -- but it is something many people have been exspousing for years
you need to INSTRUMENT your system
you need to be able to TRACE your system
you need to gather a history of PAST performance
yadda yadda yadda.
yes, most people do the "trial and error" (i've a couple of examples of that in my new book --
people not bothering to find out what is really wrong, just coming up with N things to do "that
couldn't hurt" -- leading to disaster)
What about statspack?
October 6, 2003 - 8am Central time zone
Reviewer: Adrian from UK
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
Followup October 6, 2003 - 8am Central time zone:
the analogy is useful to understand why you need to use small windows on a statspack report -- why
for example a report that spans HOURS is useless.
I find a 15 minute report to be very useful at a high level.
say I see a really low (under 99%) soft parse ratio -- and high latch frees. Can I say anything
about that? Yes, yes I can (you are NOT using bind variables and parse lots and it is hurting you)
I see large direct path read/write waits. Can I say anything about that? Yes, yes I can -- you
are doing lots of sorts to temp -- maybe we should look at that.
I see the top sql was executed 10,000 times and did 1,000 LIOs each time. Might I want to look at
that query?
It is useful for tuning in the aggregate. sort of "instance" or "database" tuning. It finds the
painfully OBVIOUS. It is also a great way to find out "what changed". Say the system was going
just dandy. Now, this morning, it is not. A simple comparision of today with yesterday can be
very very telling -- yesterday you generated 5k of redo per transaction. Today -- you are
generating 50k of redo per transaction -- that might trigger you to look in a specific area now to
find out "why" (oh yeah, we rebuilt our indexes last night -- log miner is showing lots of extra
"internal operations" on the indexes -- the indexes are splitting back to their steady state ----
for example)
The advisors or of use as well -- what should the pga target be? how is my shared pool doing? if
I were to increase the buffer cache -- would it have any measurable effect on physical IO?
To tune a PROCESS -- statspack is useless. tkprof rocks for that.
Peformance and Tuninng
October 8, 2003 - 8am Central time zone
Reviewer: Aída Esther Assayag from Oracle Site.
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
Followup October 8, 2003 - 10am Central time zone:
with the CBO, you do it in any order you want. Don't worry about it.
that advice was from the legacy RBO that you won't be using.
What is good?
October 13, 2003 - 5pm Central time zone
Reviewer: Zoran Martic from Dublin, Ireland
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
Tuning from a DBA perspective
December 23, 2003 - 8am Central time zone
Reviewer: Dana Day from Phoenix, AZ USA
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.
Followup December 23, 2003 - 11am Central time zone:
ll, well, if the system is "done", will not grow, nothing else added to the machine ever -- perhaps
you are done.
You have lots more hardware then you really need.
You could fix the application and regain that capacity allowing for future growth and new
applications.
Or, you can buy yet more hardware.
tuning via parameter changes is valid -- definitely.
But -- removing work that need never be done in the first place -- that is the real low hanging
fruit.
It is funny how the developers cared so little as to inflict this on THEIR end users without
actually testing it first. (not funny "ha ha", bad funny). Don't they care what people think
about their work?
You should complain the next time anyone asks you to up the load on this machine a notch. tell
them "well, we could 10x the capacity -- if the developers fixed the bugs in their code. or you
can buy me a brand new really big machine"
Insightful and concise
December 23, 2003 - 12pm Central time zone
Reviewer: Dana Day from Phoenix, AZ USA
Excellent overview. Thank you very much!
Where can I send the holiday cards?
Followup December 23, 2003 - 4pm Central time zone:
no need, you just did :)
How to set level 12 sql trace at instance level?
February 20, 2004 - 3pm Central time zone
Reviewer: cosmos from CA, USA
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.
Followup February 20, 2004 - 3pm Central time zone:
You do not want to do that!
It would kill you. Really. You would only trace at the session level, trust me -- it would be
killer.
if you really want to, you can either set the event in the init.ora or
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT OFF';
which turns on tracing for all NEW sessions. Existing sessions and
background processes are unaltered. dbms_system can be used to turn it on in existing ones.
instrumenting
February 21, 2004 - 2am Central time zone
Reviewer: Austin from Pa
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
Followup February 21, 2004 - 11am Central time zone:
Here is a short excerpt from my book "Effective Oracle by Design" on this topic (with the
disucssion of the tools available to instrument in the book as it gets rather long)
Instrument the system
Many of you might be looking at the title of this section and asking yourself "what the heck is
that, what does he mean by 'Instrument the system'". It is really quite simple - what I mean is
that you will liberally and in great volume spread debug code in your code. To instrument the code
means to embed in your applications, in your systems, the ability to generate copious amounts of
trace information at will.
This will provide you the ability to do two things:
* Debug the code without a debugger: just like Oracle development does. When you run into a problem
with the database - support has you enable "trace events" that generate TRC files containing tons
of diagnostic information. This enables support and development to debug your problems - without
ever touching your system. Imagine for a moment Oracle not having SQL_TRACE or the EVENT system
used by support. Imagine what impact that would have.
* Identify where bad performance is in your system: If you are building N-Tier applications with
many moving pieces, this is extremely vital. When the support desk calls you and says "it is going
slow", how will you find out where your system is slowing down without the ability to have the code
itself tell you what is taking a long time?
There are those that will say "but this will add overhead" or "this is something I really don't
need to do". To that I say you are wrong on both counts. Overhead is something you can remove
without losing much benefit. Instrumentation is something that if you do not have or remove - takes
away considerable functionality. As for the "this is something I don't need to do", that would only
be true if your systems never break, never needs diagnostics, never suffers from performance
issues. If that is true - then sure, you don't need to do that (and - send me your email address, I
have a job offer for you).
As anecdotal evidence of the need to instrument your code, we'll take a look at a couple of real
world examples. First and foremost is SQL_TRACE in the database. Hopefully everyone is aware of
this powerful tool (if not - see the Oracle Performance guide). Here is an instrumentation we
should all be using to tune our applications, to identify performance bottlenecks. Every piece of
code that executes SQL in the database is already on its way to being partially instrumented.
Another example is asktom.oracle.com. Just go there and click on any article from the home page.
You'll see a URL similar to:
http://asktom.oracle.com/pls/ask/f?p=...::NO:: <code>
If you simply type over that URL and replace the word NO with YES - you'll see the same page but with lots and lots of state/timing information dumped into it.
NOTE: the preceding URL is just a "for example one". It is not a functional URL, you may goto the asktom website to get a "real" one to test this on
All of the applications I work on - every single one - has this feature in some shape or another (this tracing ability). If someone reports a slow page - I can easily and quickly determine what exact region of the page is causing the issue. If a certain query that paints part of the page is taking an inordinate amount of time, it will be obvious where that is due to this instrumentation. In addition to this instrumentation inside of the page, we audit each and every page access as well. That means - for every single page accessed in my application -every page, every time, we insert an audit trail record containing whatever information we desire. On asktom, I nominally capture the IP Adress, Browser, Time of request, duration of the request, what page in what application was requested among other bits of data. This allows me to rapidly respond to questions like "how many people use asktom, how many pages (versus images and such) do you serve up everyday". This allowed me for example to respond immediately to this question that was posted:
From last 24 hours, I have observed that accessing your site, clicking on 'ask question', 'read question','Review Question' etc., all gone slow (taking around 2-3 minutes instead of couple of seconds before). Are others facing the same problem?
I just went to my statistics page, generated right from my audit trail as part of my system (clicked a button) and
viewed a report.
Now, that instantly told me it was not the database and in fact, it probably wasn't a widespread problem at all (that is, this person was having an isolated issue). The reason I could tell it wasn't the database - the Avg Elap/Page. It fell right into the norm, what I was expecting. The database was generating pages as per normal. The reason I could tell it wasn't a widespread problem? The number of page views fell well within my normal range (I have another report I look at from time to time that tells me page views per day/week and so on). Basically I know that during the week I get between 25,000 and 35,000 page views a day on average from 2,000 to 3,000 different IP addresses. The last 24 hours on asktom.oracle.com fell well within the norm. If pages had been taking 2 to 3 minutes to generate instead of well under a second, well, people would have stopped clicking on them and the hit counts would have been way down.
So, within a couple of seconds, I was able to rule out the database, the webserver and my front end network at least. As it turned out - I asked people coming to asktom.oracle.com to tell me "how is the performance" and without exception it was "normal" - further confirming that this was an isolated incident, somewhat outside of my control. Fortunately, the poster of this question came back to say "it was resolved". As they were somewhere deep inside of Australia and I'm on the East Coast of the US - I can only speculate that there was some nasty network issue between us and them that got corrected.
The last example I'll use is a piece of code I wrote a long time ago - when this "internet" thing was just catching on. Many of you a
SET EVENTS
March 5, 2004 - 3pm Central time zone
Reviewer: James Blanding from Kingston, RI USA
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...
Followup March 5, 2004 - 3pm Central time zone:
the 10046 trace event (and 10053) are two very "safe".
In general, yes, events are voodoo. These two however are "good magic".
They are the only two I talk about.
SET EVENTS
March 5, 2004 - 4pm Central time zone
Reviewer: James Blanding from Kingston, RI USA
OK, fair enough. I did find a few crumbs of information on metalink.
tracing shared servers
May 14, 2004 - 10pm Central time zone
Reviewer: Reader
Is there a way in 9i to trace the session created using shared server connection? Thanks.
Followup May 15, 2004 - 12pm Central time zone:
sure, but the trace data goes all over the place.
so while you can trace...
it is not very practical to trace....
Performance Tuning
December 16, 2004 - 10am Central time zone
Reviewer: Anusha.R from India
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)
Followup December 16, 2004 - 10am Central time zone:
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.
Performance Tuning
December 25, 2004 - 7am Central time zone
Reviewer: Nanda from India
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)
How to pin down where the performance issues lies in a database application?
March 24, 2005 - 2am Central time zone
Reviewer: Praveen from Bangalore
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
Followup March 24, 2005 - 8am Central time zone:
1) someone starts by telling me what the heck it does and why.
Then I might trace it with sql_trace=true
Then I might profile it with dbms_profiler
2) (b) -- hopefully this contract consultant was told to fully document their code as part of their
task. If not, someone needs to go to contract negotiation school :)
apparently reading the rest of the stuff -- they did not. you have 12,000 lines of undocument
"stuff"
Ref: How to pin down where the performance issues lies in a database application?
March 29, 2005 - 1am Central time zone
Reviewer: Praveen from praveen.vijayan@gmail.com
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
Followup March 29, 2005 - 8am Central time zone:
The only way I know to do it is to
a) understand the question (what is it supposed to do)
b) create an optimal algorithm to do it.
fact is most code (12,000 lines of it most likely fits this generalization) is written to be slow
by slow and only by understand the question can you make it go faster.
After sql_trace=true to fix SQL.
After dbms_profiler to find low hanging fruit in PLSQL.
There is fixing the implementation.
I don't think you need to cut queries out, just instrument the code so it can enable sql tracing.
dbms_profiler likewise is non-intrusive.
tkprof will show grievous issues like sql that runs for ever. sql is that is repeatedly run
thousands or millions of times.
source code profiling will get you that last little bit.
After that, architectural changes in the fundemental way the application executes would be called
for.
Thanks Tom.. :)
March 30, 2005 - 9am Central time zone
Reviewer: A reader
Generate instrumentation
April 7, 2005 - 3am Central time zone
Reviewer: Frank from NL
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
Generate instrumentation
April 7, 2005 - 3am Central time zone
Reviewer: Frank from NL
Whoops, forgot to mention my version: 9.2.0.6
Frank
proce tune
June 7, 2005 - 8pm Central time zone
Reviewer: abc
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
Followup June 7, 2005 - 8pm Central time zone:
delete from t
where audit_datetime < sydate-days;
nothing more, nothing less.
And make a big undo segment
June 8, 2005 - 2am Central time zone
Reviewer: Lars Stampe Villadsen from Copenhagen, Denmark
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;
Followup June 8, 2005 - 8am Central time zone:
good eye, yes.
regarding where clause of Select statement
January 5, 2006 - 2am Central time zone
Reviewer: Archana from India
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.
Followup January 5, 2006 - 10am Central time zone:
1) false, the CBO is always used with partitioned tables, the CBO is free to reorder the predicate.
2) I would not accept the "to avoid extra work", the query will undergo thousands of
transformations - trying to "super suboptimize" at this level is futile.
Write your SQL to be understandable, do not avoid between.
3) false, see #1
Regarding performance on various options of DBMS_STATS
January 6, 2006 - 1am Central time zone
Reviewer: Archana from India, Mumbai
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',c
ascade=>TRUE). Now the same query was returning results in less than 4 mins (approx).
I have tried it many times but every time its 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.)
Followup January 6, 2006 - 1pm Central time zone:
did you look at the statistics that where gathered - how they differed.
you see, the problem is the defaults for dbms_stats change - and you have left no clues as to what
version you might be using, so it is rather impossible to say.
But - don't just tell me the version - do a little investigation yourself. Look at what each
command did (find out what DBMS_STATS.gather_table_stats (OWNER,
TABLE_NAME) DOES in your version exactly, compare that to what the others did)
and most importantly, compare the plans - how do the estimated cardinalities look in each - are the
plans the same? different?
Version
January 6, 2006 - 3am Central time zone
Reviewer: Archana from India, Mumbai
Hi Tom,
I am sorry I have forgotten to provide Database version. We are using "Oracle9i Enterprise Edition
Release 9.2.0.6.0".
Followup January 6, 2006 - 1pm Central time zone:
see above, even with the version - you can do a little bit of the research first..
Need your suggestion
February 25, 2006 - 4am Central time zone
Reviewer: A reader
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.
Followup February 25, 2006 - 11am Central time zone:
... As with every new system,...
well, I disagree with that generalization. Vehemently.
Generate test data. We all can do that, many of us do.
February 26, 2006 - 9am Central time zone
Reviewer: A reader
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.
Followup February 26, 2006 - 10am Central time zone:
if you can generate 20%, you can generate 200%
It is not that you could not.
It is that you DID NOT.
May 3, 2006 - 10am Central time zone
Reviewer: Alexander
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.
Followup May 3, 2006 - 1pm Central time zone:
and ( m.doc_type = 'public'
or ( m.doc_number is null
and r.action_code like 'A%'
and r.type in ('EMP', 'MNG')))
how many public?
how many like 'A%' and in (emp,mng)?
May 3, 2006 - 1pm Central time zone
Reviewer: Alexander
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 ).
Followup May 3, 2006 - 2pm Central time zone:
indexes are not "goodness"
but lets try an "anti join" first - no promises, this is really really hard without knowing the
schema, the question really being asked and so forth.
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,
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 t.period IS NULL
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;
May 3, 2006 - 3pm Central time zone
Reviewer: Alexander
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.)
Followup May 3, 2006 - 4pm Central time zone:
without having the schema, i cannot test anything.
try the new ansi join syntax - basically want to do it without the subquery.
May 4, 2006 - 10am Central time zone
Reviewer: Alexander
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.
Followup May 4, 2006 - 4pm Central time zone:
like I said, without the schema - I'm not going to be very useful here - I'm not a sql compiler
myself.
supply minimal create tables (remove all unnecessary columns and storage clauses) so we can play
with it for the syntax and we'll see what happens.
May 5, 2006 - 9am Central time zone
Reviewer: Alexnader
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.
Followup May 5, 2006 - 2pm Central time zone:
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'
you don't use commas here - you use a list of tables in order with "joins" between them
from a left join b on (...) left join c on ( .. )
and so on - no commas, it is not an old style join.
May 5, 2006 - 3pm Central time zone
Reviewer: Alexander
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.
May 8, 2006 - 9pm Central time zone
Reviewer: Alexander
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....
Followup May 9, 2006 - 10am Central time zone:
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
May 9, 2006 - 1pm Central time zone
Reviewer: Alexander
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.
Followup May 9, 2006 - 1pm Central time zone:
it was part of MY original query.
We only want to keep the "not in T" rows - t.period will be NULL when we outer join to T and "make
up a row" (the row was not in T)
Tuning Views
May 30, 2006 - 8am Central time zone
Reviewer: A reader from Arlington, VA
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.
Followup May 30, 2006 - 9am Central time zone:
I prefer the last option - create a new view.
I consider views to be stored queries - one layer deep is as deep as I normally want to go. They
are special purpose stored queries, there were designed to answer a specific question. When you
start getting into views of views of views - you probably have defeated the purpose of them. How
could a special purpose answer be so generic (and good for performance) that is can be repurposed
50 different ways.
Performance Tuning
June 7, 2006 - 7am Central time zone
Reviewer: A reader from Arlington, VA
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.
Followup June 7, 2006 - 2pm Central time zone:
why is J2EE "special".
it is no more special than you typing into SQL Plus.
Have the j2ee developers made it possible for you to enable tracing (sql trace) of their
applications (odds are "no, they don't even know what that means", but I have to ask anyway)
Performance Tuning
June 8, 2006 - 6am Central time zone
Reviewer: A reader from Arlington, VA
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.
Followup June 8, 2006 - 9am Central time zone:
the tracing needs to be turned on BY THE CLIENT, you are not "tracing the frontend", you are
enabling tracing in the database.
Performance Tuning
June 8, 2006 - 8am Central time zone
Reviewer: A reader from Arlington, VA
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?
Followup June 8, 2006 - 9am Central time zone:
umm, no.
coders write code.
If coders can write SELECT * FROM EMP;
then coders can definitely write "ALTER SESSION SET SQL_TRACE=TRUE/FALSE"
and coders make this parameter driven, so that testers and tunes (who are the coders themselves!)
can do something like "update a config file", "pass an extra parameter"
Good coders know how to build applications that are debuggable, maintainable, tunable.
Performance Tuning
June 8, 2006 - 11am Central time zone
Reviewer: A reader from Arlington, VA
The question was asked in good faith. Don't appreciate the sarcasm here.
Followup June 8, 2006 - 11am Central time zone:
Wasn't being sarcastic.
Was being honest.
Coders write code.
Coders write SQL
Coders can build applications that can be tuned, debugged, and maintained.
Coders can make it so these things can happen.
Coders can also make it virtually impossible to do this.
http://tkyte.blogspot.com/2005/06/instrumentation.html exhibits some of my frustration that coders do not actually
a) know how
b) do it
c) believe in it.
The logon trigger - almost a ZERO percent change of success with that in a 3 tier J2EE application.
So, no sarcasm really - just statement of fact. Coders should actually be able to figure out how
to do this? I mean, they write code - it is what they do for a living.
ordering data to improve performance of inserts
June 8, 2006 - 1pm Central time zone
Reviewer: Ryan from Virginia
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.
Followup June 8, 2006 - 1pm Central time zone:
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.
followup ordering question
June 9, 2006 - 3pm Central time zone
Reviewer: Ryan from Virginia
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.
Followup June 9, 2006 - 5pm Central time zone:
1) if the indexed data is not modified, pctfree=0 would be appropriate regardless of the sort order
2) depends on the question being asked of the data. We need a question you want to optimize first.
Consider a table based on ALL_OBJECTS
There is this "key" of
(owner,object_type,object_name)
People frequently query this report table with these questions:
show me scotts employee table.
show me all of scotts tables.
show me all of scotts stuff.
I would really love that table to be physically clustered by "scott", and even better by
"scott/object_type".
You could achieve that in different ways.
Hash clusters
B*tree clusters
IOT
partition (list by owner maybe)
Or - create table as select * from external_table order by owner,object_type,object_name;
but, if we just "ad-hoc'ed" that table all day and there was no discernable pattern to the access -
I wouldn't pay the price of clustering the data upon load.
Unless of course I wanted to COMPRESS it - they definitely, order it by something really
repetitive.
Or, I was bitmap indexing a specific column - might order by that.
and so on...
Followup Performance Tuning
June 12, 2006 - 6am Central time zone
Reviewer: A reader from Arlington, VA
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.
Followup June 12, 2006 - 9am Central time zone:
Ok, the java guys grab connections from a connection pool, They also can read parameter files -
obtain inputs (from anywhere - eg: if I add "&p_trace=YES" to the end of an asktom URL, guess what
happens!! that "page" issues an alter session to enable trace, runs, and then turns off trace).
They should
a) grab connection
b) determine if THEY are to trace
c) enable tracing if so
d) continue as normal
e) if they enabled trace, when they ungrab the connection, turn it off
are they using 10g or 9i here? 10g we can be "even more cool" with dbms_monitor and tracing across
sessions (connects from the pool)
Performance Tuning followup
June 12, 2006 - 10am Central time zone
Reviewer: A reader from Arlington, VA
Thank you. We are using 10g.
Followup June 13, 2006 - 10am Central time zone:
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)
June 13, 2006 - 5am Central time zone
Reviewer: Murali from Singapore
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
Followup June 13, 2006 - 12pm Central time zone:
Enterprise Manager has a job scheduler, yes.
Performance tuning
July 7, 2006 - 6am Central time zone
Reviewer: Paresh from UK
<code>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
Followup July 8, 2006 - 10am Central time zone:
can you compare apples to apples here????
you are comparing a select to a multi-table insert?????
Performance tuning
July 10, 2006 - 6am Central time zone
Reviewer: Paresh from UK
<code>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
Followup July 10, 2006 - 7am Central time zone:
the difference? You are comparing an apple (a query, a select) to a toaster oven (an insert, a
write, a modification). You cannot compare apples to toaster ovens except to say "it doesn't make
sense to try to compare an apple to a toaster oven"
how can you compare a simple SELECT to a MULTI-TABLE insert.
it IS doing partition pruning:
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| HH_ADVANCE | 1 | 20 |
| 50 (0)| 00:00:01 | R OW L | ROW L
you have pstart/pstops in there.
Performance Tuning
July 10, 2006 - 9am Central time zone
Reviewer: Paresh from UK
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
Followup July 10, 2006 - 9am Central time zone:
plans not formatted in a fashion I can easily read - you compare them, you tell us what is
different.
and in plsql are you using "binds" but in sqlplus using "hard coded literalls"? eg: are we really
comparing the same query.
Performance Tuning
July 10, 2006 - 10am Central time zone
Reviewer: Paresh from UK
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
Followup July 10, 2006 - 12pm Central time zone:
... How did you spot that I
used hard coded literals?...
I didn't, I guessed - #1 cause of "I get plan a in sqlplus and plan b in plsql - why".
partition elimination *is happening*. you would not have pstart/stops without it.
Performance Tuning
July 11, 2006 - 4am Central time zone
Reviewer: Paresh from UK
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
10g database configuration for better performance.
August 20, 2006 - 11pm Central time zone
Reviewer: Ravi from India
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
January 15, 2007 - 11am Central time zone
Reviewer: Alexander the ok
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.
Runtime execution plan
January 23, 2007 - 6am Central time zone
Reviewer: a reader from england
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
runtime explan plan
January 24, 2007 - 6am Central time zone
Reviewer: a reader from england
Tom,
Please could you reply to the above query on runtime explain plan for 9.2.0.4 database?
Thanks
Outer join better than union-all
May 21, 2007 - 11am Central time zone
Reviewer: A reader from UK
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
Followup May 21, 2007 - 12pm Central time zone:
if you can make one pass on data, that is typically better than making two passes.
If wanted the output of:
a joined to b
union all with
a joined to b joined to c
that typically means you could have:
(a joined to b) OUTER JOINED to c
hitting a and b once, instead of twice.
outer joins are NOT EVIL
outer joins are NOT EVIL
outer joins are not better than union all
outer joins are not worse than union all
outer joins are a technique that you might be able to use to not have to process and REPROCESS data over and over.
Slow performace oracle 10g
July 3, 2007 - 8am Central time zone
Reviewer: Pankaj Kshirsagar from India
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.
Is it not relative question
July 6, 2007 - 12am Central time zone
Reviewer: Pankaj Kshirsagar from India
Hi Tom,
Is it not relative question for this forum ?
Thank You,
Pankaj
Followup July 6, 2007 - 12pm Central time zone:
i don't know, you addressed it to "mark", so I didn't read it
Sorry
July 18, 2007 - 3am Central time zone
Reviewer: Pankaj from India
Sorry Tom I wrongly mentioned mark,
Outsourcing contract
July 18, 2007 - 8am Central time zone
Reviewer: A reader
Tom
We want to write an outsourcing tuning contract, what skills do you recommend
Thanks
Performance Tuning Book
July 31, 2007 - 5pm Central time zone
Reviewer: A reader
Hello Tom,
Can you please recommend one of yours or any other author's book for performance tuning.
Thanks
default buffer cache
August 14, 2007 - 11am Central time zone
Reviewer: David from AUS
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
Followup August 20, 2007 - 11am Central time zone:
you give no numbers here. what is "big enough"
and what is total number of blocks? so there are 32 of them, but I don't know what that represents?
instrumentation - dbms_session
September 14, 2007 - 7am Central time zone
Reviewer: Andrew from the UK
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)
Followup September 15, 2007 - 9pm Central time zone:
1) no, not unless YOU need a context
2) you would need to set the client id immediately after grabbing a connection
3) depends on how load runner is programmed and what it does....
4) well, they would all appear to be separate clients then...
5) how would you tie them all back together?
Instrumentation - segment 01
September 17, 2007 - 5am Central time zone
Reviewer: Andrew from UK
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.
<<
Followup September 18, 2007 - 2pm Central time zone:
not sure what you are asking...
Instrumentation - segment 02
September 17, 2007 - 5am Central time zone
Reviewer: Andrew from UK
<< 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
<<
Followup September 18, 2007 - 2pm Central time zone:
the client id is a very simple sort of concept.
a) you set it, it is set in that session.
b) it stays set in that ORACLE SESSION (forget java altogether, not relevant) until you unset or reset it.
that is all.
It isn't tied to a program, java typically uses a connection pool, after each "grab" from the pool the java program should say "this is me" and identify itself.
Instrumentation - segment 03
September 17, 2007 - 5am Central time zone
Reviewer: Andrew from UK
>> 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?
Instrumentation - segment 04
September 17, 2007 - 5am Central time zone
Reviewer: Andrew from UK
<< 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
Instrumentation - practical & effective impl
September 19, 2007 - 1am Central time zone
Reviewer: Andrew from UK
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
Followup September 19, 2007 - 12pm Central time zone:
not sure what you mean by "most effectively" here - you are instrumented so as to enable trace data so as to permit a tkprof output (that is the output).
You can use the 10g grid control tools as well to "drill down" into expensive SQL and have it identify that sql and such - but tkprof is what you would use to look into an application.
I would say that if you wanted to track business transactions - then the application should have an audit trail builtin (all of my pages do for example, every single one).
when they start, then insert a row into their audit trail with whatever makes sense to audit plus the start time - returning that rowid into some local variable. When they are done, they update this entry with the end time.
You now have a complete record of every transaction type, the avg, min, max response times (more than you can hope to obtain from a tkprof which only shows database time) - how many utter failures you had (start time logged, no end time).
I have a feeling this is along the lines of what you really want - and the tkprof would let you take a business transaction that is taking "too long" and gain insight into whether the time is spent in the database or outside the database.
Instrumentation (see above) - SESSION_ID?
September 19, 2007 - 5am Central time zone
Reviewer: Andrew from UK
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
Followup September 19, 2007 - 12pm Central time zone:
a) *** SESSION ID:(11.25800) 2007-09-19 12:10:26.581
that is the sid.serial#
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
b)
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
they are just tags you'll see on different lines of the tracefile, there is no relationship really - other than you set the client id in some session identified by that sid.serial#
c) the service name is in the trace file as well.
for the last bit, you would have to identify yourself each and every time
Tracing makes it go faster!
September 19, 2007 - 1pm Central time zone
Reviewer: A reader
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?
September 19, 2007 - 3pm Central time zone
Reviewer: A reader
What is x?
Followup September 20, 2007 - 9pm Central time zone:
ahh, i wanted to update the question, so put x in there and came back to update it with the url and didn't remove the x :)
Instrumentation - will it work if JAVA guys ..?
September 19, 2007 - 4pm Central time zone
Reviewer: Andrew from UK
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
Followup September 20, 2007 - 9pm Central time zone:
you have a connection pool, a bunch of connections to oracle
java apps ask for a connection, they get a random one from the pool. they return it, it goes back, the next time they ask they might get a totally different one.
every time a java app grabs a connection, it must identify itself. the sid and serial# is meaningless in a connection pool environment.
1) pretty much.
I would have the developers call
dbms_session to uniquely identify their java session (java sessions have a unique id, you see the sessionid for the JAVA session in the url many times)
dbms_application_info to set the action, module and optionally the client_info
Don't quite understand the original question
September 20, 2007 - 6pm Central time zone
Reviewer: miley from NJ
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" ???
Instrumentation - is this approach OK?
September 21, 2007 - 3am Central time zone
Reviewer: Andrew from UK
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
Instrumentation efforts meet more obstacles
September 24, 2007 - 2pm Central time zone
Reviewer: Andrew from UK
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
Followup September 26, 2007 - 8pm Central time zone:
Andrew,
i can only tell you what we do.
we trace by some ID
you have to be able to figure out what that ID means.
that is up to you, we cannot do much more than "you tell us WHO you are, we give you ability to trace by that"
I can only tell you what we do - I do not understand all of the nuances of your architecture, I can tell you what dbms_stats does. Your needs need to take that into consideration - and what the java application can do.
that is all I can say.. sorry, no magic from me, I cannot make an existing application do things it is not willing, ready or able to do.
tracing ID - instrumentation
September 27, 2007 - 2am Central time zone
Reviewer: Andrew from UK
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
To: Andrew on instrumentation
September 27, 2007 - 2am Central time zone
Reviewer: Stew Ashton from Paris, France
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 Andrew: simple first check of connection frequency
September 27, 2007 - 11am Central time zone
Reviewer: Stew Ashton from Paris, France
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>)
to Tom & Stew Ashton on instrumentation
September 28, 2007 - 7am Central time zone
Reviewer: Andrew from back in France
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.
AWR Report
October 23, 2007 - 6am Central time zone
Reviewer: Atul from India
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
Confusing
December 5, 2007 - 8am Central time zone
Reviewer: karthick from india
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
January 11, 2008 - 7am Central time zone
Reviewer: pranay singh
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.
Followup January 11, 2008 - 9am Central time zone:
compare the plans.
I'll bet when you extract it - you use literals and are not really comparing the same queries at all..
Information
January 13, 2008 - 4am Central time zone
Reviewer: Santanu from India
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.
Performance Tuning
February 28, 2008 - 6pm Central time zone
Reviewer: Sanjeev Vibuthi from USA
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
Followup February 29, 2008 - 2am Central time zone:
you know, an example with everyone having the same data values is a really bad example. How can you tell what the heck you are looking at to verify anything.
tell us more about the dates, do they EVERY cross months in a single row, or are they always in the same month and is the first one always the first day of the month - details, details, details.
To : Sanjiv
February 29, 2008 - 5am Central time zone
Reviewer: A reader from UK
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;
Analytics or Aggregate
February 29, 2008 - 9am Central time zone
Reviewer: Raj from Watford, UK
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
Performance Turning
February 29, 2008 - 11am Central time zone
Reviewer: Sanjeev Vibuthi from USA
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
Followup March 1, 2008 - 11am Central time zone:
... Always the Dates are monthly. Begin Date is the first of day of the month and
End Date is last day of the month. ...
hah, one wonders why then you have two attributes... oh well.
you wrote:
Person_header table have 5000 records and person_detail table have 20 million records
is that true, or do you have those numbers backwards?
what are the primary keys of these tables.
Performance Tuning
March 1, 2008 - 9pm Central time zone
Reviewer: Sanjeev Vibuthi from USA
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
Followup March 1, 2008 - 10pm Central time zone:
... Reason for having 2 dates is sometimes the payroll period vary... It may be
Bi-weekly, quarterly and half yearly...
...
then you did not speak the truth above. You said very distinctly:
... Always the Dates are monthly. Begin Date is the first of day of the month and End Date is last day
of the month.
...
Please tell me real record counts - of header and detail, they seem backwards. In fact, your header table looks more like a detail table - if it has pay periods over time for 2000 members - how could it have just 5000 records.
Performance Turning
March 2, 2008 - 1pm Central time zone
Reviewer: Sanjeev Vibuthi from US
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
Followup March 2, 2008 - 2pm Central time zone:
so, would it be true that
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
is the set of people you want, use that and join that to the details.
Performance Tuning
March 4, 2008 - 9am Central time zone
Reviewer: Sanjeev Vibuthi from US
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
Outer Join
March 28, 2008 - 5pm Central time zone
Reviewer: A reader
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
Followup March 31, 2008 - 8am Central time zone:
it depends on the amount of data to be returned from each.
In general, if you have:
select * from t1, t2 where t1.key = t2.key(+);
it ONLY MAKES SENSE to full scan and hash join (unless you are optimizing for first rows - then a full scan and nested look MIGHT apply - maybe, but in general the full scans and hash outer join will return the last row MUCH FASTER)
On the other hand, if you have:
select * from t1, (select * from t2 where col='x')
where t1.col = 'y'
and t1.key = t2.key(+)
there is no reason we couldn't use an index on t2.col (presuming "where col='x'" returns FEW records) and an index on t1.col='y'...
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
Thanks a lot!
April 7, 2008 - 4pm Central time zone
Reviewer: A reader
Thanks a lot!
April 7, 2008 - 4pm Central time zone
Reviewer: A reader
ORA-01114
August 15, 2008 - 7am Central time zone
Reviewer: Manoj Thakur from India
<code>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
Followup August 20, 2008 - 8am Central time zone:
$ 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. 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.
// *Action: Restore access to the device or remove unnecessary files to free
// up space.
From Expert Oracle Database Architecture:
Temp Files
Temporary data files (temp files) in Oracle are a special type of data file. Oracle will use temporary files to store the intermediate results of a large sort operation and hash operations, as well as to store global temporary table data, or result set data, when there is insufficient memory to hold it all in RAM. Permanent data objects, such as a table or an index, will never be stored in a temp file, but the contents of a temporary table and its indexes would be. So, you¿ll never create your application tables in a temp file, but you might store data there when you use a temporary table.
Temp files are treated in a special way by Oracle. Normally, each and every change you make to an object will be recorded in the redo logs; these transaction logs can be replayed at a later date in order to ¿redo a transaction,¿ which you might do during recovery from failure, for example. Temp files are excluded from this process. Temp files never have redo generated for them, although they can have UNDO generated. Thus, there will be REDO generated working with temporary tables since UNDO is always protected by REDO, as you will see in detail in Chapter 9. The UNDO generated for global temporary tables is in order to support rolling back some work you have done in your session, either due to an error processing data or because of some general transaction failure. A DBA never needs to back up a temporary data file, and in fact to attempt to do so would be a waste of time, as you can never restore a temporary data file.
It is recommended that your database be configured with locally-managed temporary tablespaces. You¿ll want to make sure that as a DBA, you use a CREATE TEMPORARY TABLESPACE command. You do not want to just alter a permanent tablespace to a temporary one, as you do not get the benefits of temp files that way.
One of the nuances of true temp files is that if the OS permits it, the temporary files will be created sparse¿that is, they will not actually consume disk storage until they need to. You can see that easily in this example (on Red Hat Linux in this case):
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
Note df is a Unix command to show ¿disk free.¿ This command showed that I have 29,008,368KB free in the file system containing /d01/temp before I added a 2GB temp file to the database. After I added that file, I had 29,008,240KB free in the file system.
Apparently it took only 128KB of storage to hold that file. But if we ls it
ops$tkyte@ORA10G> !ls -l /d01/temp/temp_huge
-rw-rw---- 1 ora10g ora10g 2147491840 Jan 2 16:34 /d01/temp/temp_huge
it appears to be a normal 2GB file, but it is in fact only consuming some 128KB of storage. The reason I point this out is because we would be able to actually create hundreds of these 2GB temporary files, even though we have roughly 29GB of disk space free. Sounds great¿free storage for all! The problem is as we start to use these temp files and they start expanding out, we would rapidly hit errors stating ¿no more space.¿ Since the space is allocated or physically assigned to the file as needed by the OS, we stand a definite chance of running out of room (especially if after we create the temp files someone else fills up the file system with other stuff).
How to solve this differs from OS to OS. On Linux, some of the options are to use dd to fill the file with data, causing the OS to physically assign disk storage to the file, or use cp to create a nonsparse file, for example:
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
After copying the sparse 2GB file to /d01/temp/temp_huge2 and creating the temporary tablespace using that temp file with the REUSE option, we are assured that temp file has allocated all of its file system space and our database actually has 2GB of temporary space to work with.
On a lighter note
November 8, 2008 - 1am Central time zone
Reviewer: A reader
In case you want to know what <b>NOT TO DO</b> 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
Followup November 11, 2008 - 2pm Central time zone:
You know, the day that came out - I had just finished my worst practices presentation... We were on a break... I read that...
And started the next bit of the day with it - as it contained many of my worst practices :)
Good query became bad
December 9, 2008 - 12pm Central time zone
Reviewer: Kulkarni from India
<code>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 'CONTRIBUTO
Followup December 9, 2008 - 2pm Central time zone:
.... I rebuilt indexes, moved some of the
tables to remove fragmentation and did analyse objects. ....
and one seriously wonders why one would start with that. That would be the last thing to do after determining
a) indexes needed rebuilding
b) tables needed reorganizing.
what a waste. sigh. I'll never understand. That is like taking a gun and shooting into a room with good guys and bad guys in it and the lights are out. You are hoping to shoot a bad guy - but you might shoot a good guy or you might miss everyone. But everyone gets a good scare no matter the outcome.
I'll bet you that a few days back - you were not using that date where you? that probably changed.
also, lose the hint, why is the hint there.
you say you are aware of the old plan - what was it.
and can we get a tkprof here. with the row source execution plan attached to it. So we can compare the estimated row counts from above with the ACTUAL row counts.
tuning issue in PROD
January 30, 2009 - 10am Central time zone
Reviewer: Manu from India
<code>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 t
|