Dear Tom,

Thanks for the answer. Agreed. Performance Tuning requires a lots of experience. Each problem might have different way of handle it. You have brought up a very good example (Client Program issue or could be client resources). Performance can be affected not only by Database but System resources, Network congested, Client Hardware resources and "The way the program being developed and used". Since we are DBA and should always start to look at the DATABASE layer and System resources layer. I wonder if it is the good approach to collect the timing for a particular complex SQL statement at the Database Console. For instance, When user complaints system slow, I will run the complex SQL at the console and collect the timing. I will then compare the collected timing against the timing that I collected when system running smooth. With this performance indicator, I would be able to conclude that the DATABASE is having a problem. I am not sure if this is workable or not. Need your view. Thanks.

Rgds,
SHGoh

Letme give a quote from my experience

I think to get the best tunning, you only get it with experience.

1.
You must know what is good, what is acceptable and what is wrong. First study.

To know it you must have the experience with the hardware, Oracle version and size of tables and type of query you do. But first you must have knowledge. (read)

I am never going to know how much faster would have been my softaware in 7.x because in that time I do in the way I knew Oracle.

So the first thing is or you get some book (including Tom Kyte book , not because he answers the question, else because it´s really a good book ) and try to get an OCP.

When you create the database with all best conditions, for example automanage, uniformsize, automatic undo management. and know the tools and features you have to know if you are doing something wrong or losing some feature that can increase your performance, for example materialized views in dataware housing.

Then only then you can have an idea of what should be the performance.
For example I solved a problem in time of backing up,moving old data to a read only tablespace, If you don´t know that this exists, you can use it.

2. Experience

You too need some experience, because if you don´t know how Oracle forms locks records in the database, or other specific conditions ( people, software, hardware, network, etc. )only you can has in your work.
For example recently we got a problem in a oracle forms client, the reason was the router that disconnected the client, Word don´t has problem, but Oracle forms yes, we demostrated it, disconnecting the network cable and reconnecting, and the same error message appeared. Then they fixed it.

3. Understand what is important

One thing Tom helpme to understand is how you can incrase the performance, at least in Oracle, he stopped my ideas to set exactly the size of the variable for example NUMBER(8,3), and showme what was really important, where I should have to look for performance problems.

4. Practice.

Even I got my OCP, I have oceans of doubts, not because I don´t understand, else because until you don´t use a feature or see something, you don´t understand how important it is or it is not.

Until using the hint /*+ NO_MERGE */ in a query that used views, and reduced the time from 30:00 to 9 seconds. I didn´t understand how important was the hints, I understand what the hints does, but I didn´t understand how useful they could be.

5. Ask tom
If you has a problem, you can make it understandable, ask tom,
For example the explanation Tom gave me about parameters
OPTIMIZER_INDEX_COST_ADJ = 5
OPTIMIZER_INDEX_CACHING = 90
saved me days of work, not only hours.
And solved a very important problem in performance.

:)

Tom, the online doc describes the subject statistic as how many times a server process had to wait for space in the online redo log, not for space in the redo log buffer. Could you please help me in understanding this in what situations in oracle the server process has to wait for space in online redo log files? LGWR is the one that is writing to online redo log files from log buffer. When does server process access online redo logs? Thanks.

Hi Tom, my question is regarding logons current. Does it impact performance and resources. i have the following query which i run frequently everyday:

select class, name, value from v$sysstat
where name like 'session cu%'
or name like 'open%'
or name like 'parse c%'
or name like 'logon%'
order by 1;

It generated the following query:
CLASS NAME VALUE
---------- ---------------------------------------- ----------
1 logons cumulative 2142278
1 logons current 1.8447E+19
1 opened cursors cumulative 23518633
1 opened cursors current 1.8447E+19
8 opens requiring cache replacement 0
8 opens of replaced files 0
64 session cursor cache hits 2388840
64 session cursor cache count 1597
64 parse count (total) 28092107
64 parse count (hard) 33597
64 parse count (failures) 10673


As u can see the number of users are HIGH as are the open cursors current. I want to know the reason why? The open cursor parameter is set to 500 and sessions is set to 170. So how is it possible for it to reach such a high number.
Also could u please suggest a way to resolve this issue as the number of sessions from the v$sessions are less than 30 at maximum.
Thanks in advance.

Tom,

In our production database, the process of making the ODS data factory involves several pro *c, pl/sql programs that extracts data from the text files, cleans it and loads it into Oracle tables.

Last month, we moved the database to a different Unix box which has more number of CPU's and more RAM than the previous one.

We were expecting the runtime of the production jobs will come down (the amount of data this month is almost same as last month) because of the better machine. However, it took almost double the amount of time than last month.

I don't know from where to start investigating this matter.

Can you help me out in this?

Thanks,

I did not understood completely about your first 4 lines. Can you plesae elaborate.

Other than IO, do you think any other hurdle could cause the performance bottle neck?

My manager took the report of CPU consumption for the entire month of May and found out that the CPU was pretty high during the production jobs.
I am pretty sure, the production jobs that does not do any kind of sorting (just ETL)use very less CPU. But he has a proof in his hand.

Unfortunately, I do not have the statspack report on the previous machine. May be I have to consider your option B.
Have you covered the method of application tuning in any of your books? I own a copy of both of your books.

Thanks a lot.

Excellent example!

Wow!!!! Your logic is just great. It makes sense and explains clearly.

Hi. I want to take a class on performance optimization/tunning. I'm thinking about taking either hotsos class (Diagnosing Oracle Performance Problems - </code> http://www.hotsos.com/courses/PD101.php

 or Orapub class (Advanced Reactive Performance Management for Oracle Based Systems 

http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=pm_course <code>. Does anybody have any experience with them, maybe even both? Which one would you recommend?

Tom,

Actually I have a copy of Oracle Performance book too. Which particular chapter explains about application tuning, bottom up?

Thanks

Ok, more CPU more RAM could have caused IO bottleneck. Does it mean less CPU less memory is ideal for a job that is more IO oriented?

For class on performance optimization/tunning I prefer hotsos .

Hi Tom,

I've been using the highway analogy quite a lot (maybe because I tend to spend quite some time on the Autobahn driving to various customers' offices :-), and it strikes me that it is so close to what really happens in an Oracle database (or any multitasking system, for that matter) that I begin to seriously consider applying the results of scientific research in the road traffic management area to computer system performance.
I have very little knowledge in road traffic management, but I know, for example, that they use hydrodynamics to model traffic flows - something I have never seen applied to computer performance.
Do you know of any author that has published any results in this direction? I assume computer scientists have tried this approach already, haven't they?

By the way, I recently tried to explain the significance of the log buffer size to a friend, and I came up with this analogy:
The LGWR is your friend driving in front of you, leading the way. You are the user session(s) producing redo. You will not overtake LGWR, because you don't know the way. LGWR will always slow down as necessary to keep a maximum distance of log_buffer meters, so that you don't lose him/her in the other traffic. The amount of safety distance is obviously irrelevant for the total performance (time needed to get to your friend's place).

Further observations:
If you get too close to LGWR, you must slow down ('log buffer space' wait event). If you fail to slow down in time (because of a bug), you crash (ORA-600). Slowing down and accelerating frequently is known to consume more petrol (context switching) and therefore should be avoided, so you should not reduce the safety distance too much.

Regards,
Flado

What is the use of ACTIVE_SESSION_HISTORY view? How as a DBA I could use the view to tune the database? Do you have an example to illustrate the usefullness of active session history info?

I see the value of v$session_wait. However, I would like to understand more about how can I use the new V$ACTIVE_SESSION_HISTORY view. Thanks.

I'm currently reading (and weeping at my inadequate Oracle tuning abilities, compared to those of the author) *Optimizing Oracle Performance*, by Cary Millsap.

In that (superb) book, he effectively tramples TKPROF and StatsPack underfoot, as they are, apparently, not nearly detailed enough for *proper* evaluation of Oracle/System problems.

Fighting talk, I'd have thought - what say you?

Is your own method of *looking at a trace in more detail using SQL* in an existing book, or might it be in your next (Tolkien-esque*) dual-tome publication?


* TK = Gandalf; DKB = Saraman; M$ SQL Server = Sauron! :)

In the strange world of UML, patterns (templates, really) are widely used (GOF, etc.) to identify and solve problems. The Perl Cookbook is, in a way, based upon the same idea.

Could such a patterns-based approach to Oracle problem solving ever be sane? I suppose I'm trying to say that whilst any individual Oracle system is unique, might it be right to document scenarios with "... so if you see this, it MIGHT well be worth considering xyz"? Or is this just another step into the wilderness of *Silver Bullets* ?

I have worked for three companies over ten years; only one of them uses Oracle. But, the common factor for all three was: tuning the systems(s) was simply not done in any meaningful way... apart from my attempts, which, I fear, were looked upon as *meddling*. "More, better and faster hardware, that's what we need" was the usual cry. They usually got it, too.

Am I right to infer, from your previous reply, that most sites you help out with DO tune their system(s) (or at least attempt to)? If so, do you suspect that this is the norm? Or could there be an *underclass* of Oracle/SQL Server/Whatever installations that are, at best, tinkered with from time-to-time?

Sometimes, I do have my doubts.

The dominant motive for what we do is that a performance diagnostic Method needs a single entry point. Yes, tkprof will help you well to solve several types of problems. In my experience, people who use tkprof have far fewer performance problems than people who don't. But for some types of problems, tkprof won't help you at all. The cases in the final chapter of the book illustrate what I mean by this.

Furthermore, sometimes, tkprof will show you that you've implemented a poor practice, but it's terrible at explaining the total cost (in response time, of course) of that mistake. For example, tkprof might alert you that you're "parsing too much," (e.g., parse count == exec count), but it's possible that though this is happening, it's not a significant enough contributor to response time to really matter, and therefore it's not the where you should be applying your investment.

The real issue, to my mind, is that I want a deterministic problem-solving method that works all the time. Maybe tkprof helps company X solve 90% of their problems but only 10% of the problems that company Y has. What do you do about the remainder? You have to choose another "method." How do you do that? In my opinion, the answer is to begin the analysis by looking at the right data: a SPANNING, NON-OVERLAPPING account of exactly your problem response time. Tkprof doesn't do that.

Two tkprof deficiencies led to our more thorough investigation of raw trace data: (1) There are some problems that tkprof can't help you solve, and (2) tkprof doesn't tell you whether your problem is one that it can solve versus one that it cannot. This second point is what sometimes leads to those performance improvement projects where analysts try everything they can think of for months without ever really getting their finger on the real root cause.

As you said in your post, this (long, failed performance improvement projects) is generally the state of the world by the time I enter a situation. I believe that the benefit of avoiding these situations far exceeds the small amount of pain required to actually do performance improvement projects in the Method R way from the very beginning.

Hi Tom,

a. Can you please outline some steps/strategies that you would suggest if there has been a degradation of performance on a production box. One fine day the database drags and the users need some quick fixes. How does one go about? I know that these should have been anticipated during dev stage itself but such things do happen.

b. Let's say a particular segment of the application is clearly showing up performance problems and we know that a particular package is being called at that time. Given this information, would there be any change in the strategies that you have outlined for (a) above?

Thanks Tom for all that you do.

Hi Tom could you please add a very short example about each step in the tunning process, because I'm confusing a bit.
Thank you.

Step 1: Tune the Business Rules
Step 2: Tune the Data Design
Step 3: Tune the Application Design
Step 4: Tune the Logical Structure of the Database
Step 5: Tune Database Operations
Step 6: Tune the Access Paths
Step 7: Tune Memory Allocation
Step 8: Tune I/O and Physical Structure
Step 9: Tune Resource Contention
Step 10: Tune the Underlying Platform(s)

Hi Tom you didn't any comment to the previous process, I expect some comment, because this appears up to 8i documentation startion on 10g I don't see that tuning process in the documentation, I was curious if you had some comment about it
:)
thank you

Thank you

Hi Tom,

I understand the general principal in this discussion is to put more capacity where the bottleneck is - and I totally agree.

I was reading Douglas Burns's paper on "How Many PX Slaves?".

The paper quotes the 10.2 documentation with regards to PARALLEL_THREADS_PER_CPU:

"PARALLEL_THREADS_PER_CPU enables you to adjust for hardware configurations with I/O subsystems that are slow relative to the CPU speed and for application workloads that perform few computations relative to the amount of data involved. If the system is neither CPU-bound nor I/O-bound, then the PARALLEL_THREADS_PER_CPU value should be increased. This increases the default DOP and allow better utilization of hardware resources. The default for PARALLEL_THREADS_PER_CPU on most platforms is two. However, the default for machines with relatively slow I/O subsystems can be as high as eight. "

The idea in the documentation seems to be to increase processes using the CPU when the IO subsystem is slow. How does it fit in with the "add resources where there is a bottleneck" approach. It seems opposite to it - enabling more threads would ultimately only mean MORE requests going to the IO system.

What am I missing here?

Thanks,
Naresh

Hi Tom,

if I do a select statement as "SELECT * FROM v$active_session_history" it is showing as "table or view does not exist". Is there any way by which I can get the results. Can I know who is the owner for the above view
Thanks for the same.

Hi Tom,

I've been given the enviable task of trying to tune a database / application that I'm not allowed to connect to. I can get someone else to run commands for me but the timezone difference is 9 hours so going back and forth like that is going to be a nightmare.

I will be allowed to connect to a UAT system but that obviously doesn't have the same data (at the moment we don't even know if it has the same code, schema etc.).

I've asked a bunch of questions but the answers are all VERY vague. It's pretty much 'sometimes the system runs slow'.

The db is version 8.1.7

Here's what I've suggested, have I missed anything glaringly obvious - or would you suggest a different route?

1. What I've asked the onsite guy to do is to run statspack for a 5 minute snap over the slow period, and also for a 5 minute snap during 'normal' operation.
2. Provide me with details of stats collection (this is a rapidly growing db so I'm hoping stale stats may be part of the problem)
3. Provide the onsite guy with some sql worst offenders code so that he can send me back the output.
4. Get the UAT system up to date code and schemawise.
5. Export the statistics from production and import them to the uat system (I know you don't like this idea but copying the data is not an option)
6. Using the UAT system attempt to tune the worst offending code using the cost from the explain plans (again I know it's not a great option but I don't see what other choices I have).

Thanks
Graham

In all fairness to the company in question, they did offer to fly me there the next day - my other half decided this was not an option, so I'm stuck with it.

Looks like I'd best get hypothesizing!

Hi Tom,

We have an three tire application. Oracle 8i is used for the database. Now we experience timeout issues ( from the applciation log file) almost everyday. Most of the time, the system is very slow.

I get the infomation from the V$system_event as following:
I am very new to the system tuning stuff. Does this database response ok with such kind of statistics? if not, I would like you can give me suggetion in what should I do about the database?

Many thanks,


hu Dec 14
System-wide Wait Analysis
for current wait events

vent Total Total
ame Waits Timeouts
----------------------------- ----------- -----------
ull event 1 1
atch free 13,484 13,478
mon timer 273,516 272,088
dbms ipc reply 4,390 0
dbms ipc message 1,421,940 1,145,272
nqueue 4,302 6
eliable message 1 0
ontrol file sequential read 176,526 0
ontrol file parallel write 324,022 0
efresh controlfile command 17 0
ocal write wait 558 0
uffer busy waits 32,807 4
og file sequential read 56,750 0
og file single write 8,384 0
og file parallel write 148,912 0
GWR wait for redo copy 59 7
og buffer space 70,206 1
og file switch (checkpoint in 1,071 545
omplete)

witch logfile command 1,410 0
og file switch completion 3,097 443
og file sync 59,280 64
b file sequential read 6,830,195 0
b file scattered read 9,829,549 0
b file parallel write 7,847 0
irect path read 7,408,664 0
irect path write 2,619,359 0
irect path read (lob) 4,030,001 0
irect path write (lob) 1,275 0
nstance state change 1 0
mon timer 2,739 2,732
ibrary cache pin 3 0
ile identify 21,002 0
ile open 167,006 0
irtual circuit status 27,313 27,313
ispatcher timer 13,661 13,661
QL*Net message to client 82,049,522 0
QL*Net more data to client 28,064,378 0
QL*Net message from client 82,049,497 0
QL*Net more data from client 6,594 0
QL*Net break/reset to client 52,752 0

Hi Tom
Wish you a happy new year. I am in the process of tuning a batch operation program. The program was taking about 1 hour 15 minutes to complete. I re wrote the program in PL/SQL, pretty much all the calculations are done thru SQL statements and few Global temporary tables.
1.In development and testing it was taking about 30 minutes
2.Installed the program in production. It is taking about 1 hour 45 minutes to complete.There are other CPU intensive programs running at the same time.

I would like to trouble shoot. I am wondering where should I start and what should I look for . (Tracing 10046 events slows down the program execution drastically almost three times slower and row source operation for some cursors are not there in the raw trace file).

I have read three of your books and am in process of reading Oracle performance tuning by Mr.Millsap.

Your guidance would be highly helpful.

Thank you very much
Velu

Thanks very lot for the valuable information we can find here. I have all your books. I am reading and reading very often your book "expert one on one" and I enjoy it each time.
I am working in an application where our BOSS has decided that there will be no direct select/update on tables in this application. He instead decided that if you want to select some information then you need to call an oracle package having an object type as input and will give you your output in form of object type also. Then it's up to you to get your needed information from this output object type. All needed information are mapped in this output object type.
He said all request on our data base will pass through this service.
However I've realised when starting the development that I am writing more and more of plsql code to do what a simple direct select/update from table t will do it for me very quickly. We started then having performance problem when selecting/updating a huge number of information in the data base.
You could not imagine my reaction when I re-read again this from your book
"The same 'keep in simple' argument applies at the broader architecture level. I would urge people to think carefully before adopting very complex implementations. The more moving parts you have in your system, the more things you have that can go wrong and tracking down exactly where that error is occurring in an overly complex architecture is not easy. It may be really 'cool' to implement using umpteen tiers, but it is not the right choice if a simple stored procedure can do it better, faster and with less resources"
Its exactly my actual situation.
Could you please give me few words about your opinion? Do the data bases have been made to do not directly select from them??
Thanks

Tom,

Today's (and specially tomorrow's) data centers are increasingly using Virtualization techniques for lower costs and all the other advantages virtualization (supposedly) brings.

The question is: how virtualization affects the performance tuning process, if at all? Isn't it insane to try and tune a product (database or otherwise) without knowing beforehand where it will run? You can move a server image from one box to another with a few clicks, but what will happen to your performance then? Are your performance tuning efforts lost?

What are your thoughts on the subject? Do you really think virtualization is this panacea some vendors are trying to sell?

Hi Tom

ASH is very cool but I wonder how the time are measured, in seconds?

TIA

Malone

Tom,
I agree that collecting and comparing statspack reports during normal and worse timing will give us an idea about where we have constraints in the application.But in production if i need to do tune-what are the possible methods to fix where we dont have an option to touch sql code?is it limited to adding/removing indexes(scenario 1)/add memory(Scenario 2)/changing cursor sharing parametr(Scenario 3) etc
My question simply put:Yes,I have a statspack report where i can find lot of bottlenecks.But what next when iam not touching sql code?

Hi Tom,
This this is a performance tuning thread, hope I'm not wrong in posting this here.
We have a problem that in one test database server an import takes 4 hours but in the other it takes 24 hours.
The hardware, according to the sysadmin's is exactly same.
We tried to trace 910042, level 12) and have taken tkprof output and it is as below. Please can you shed some light to what low hanging fruit you can see in it please.



$ less avanp1_import.txt

TKPROF: Release 10.2.0.3.0 - Production on Wed Apr 23 13:51:10 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Trace file: avanp1_ora_22056.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "WORKTASKSAFETY_B" ("OWNER_OI",
"BLOBID", "BLOBLEN", "BLOB")
VALUES
(:1, :2, :3, :4)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 549 0.86 0.89 0 6 1702 549
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 549 0.86 0.89 0 6 1702 549

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 549 0.00 0.00
SQL*Net message from client 549 0.00 0.08
********************************************************************************

SELECT /*+NESTED_TABLE_GET_REFS+*/ "BLOB"
FROM
"WORKTASKSAFETY_B" WHERE ROWID = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 549 0.26 0.21 0 0 0 0
Fetch 549 0.09 0.08 0 549 0 549
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1098 0.35 0.30 0 549 0 549

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 549 0.00 0.00
SQL*Net message from client 549 0.00 0.18



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1098 1.12 1.11 0 6 1702 549
Fetch 549 0.09 0.08 0 549 0 549
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1647 1.21 1.20 0 555 1702 1098

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log file sync 550 0.19 3.45
SQL*Net message to client 2745 0.00 0.01
SQL*Net message from client 2745 0.00 0.74
SQL*Net more data from client 117452 0.03 2.59
direct path write 1121 0.00 0.06
db file sequential read 1 0.00 0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 9 0.00 0.00 0 0 0 0
Execute 9 0.02 0.02 0 26 6 6
Fetch 6 0.00 0.00 0 12 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 24 0.02 0.02 0 38 6 9

Misses in library cache during parse: 3
Misses in library cache during execute: 3

2 user SQL statements in session.
9 internal SQL statements in session.
11 SQL statements in session.
********************************************************************************
Trace file: avanp1_ora_22056.trc
Trace file compatibility: 10.01.00
Sort options: default

1 session in tracefile.
2 user SQL statements in trace file.
9 internal SQL statements in trace file.
11 SQL statements in trace file.
5 unique SQL statements in trace file.
150912 lines in trace file.
3459 elapsed seconds in trace file.


Thanks Tom,
Regards,
RahulD.

Hi Tom,
How should I go about trying to analyze the above issue.
What else can I check if even the tkprok does not help. Should I trace it for the whole duration of 24 hours?
Initially I thought that there would be something different about the server hardware,OS and storage but am told no.
Please help...
Regardes,
ARU

Hi Tom,
I would like to ask how relevant are the details provided in v$active_session_history?
I have been using it extensively for sometime now to gather historic data for the database users and the time they spent on each wait time. I am assuming that the value for time_waited is in seconds, if not milliseconds, right?

But what I would like to know is how good is the value provided by the columns wait_time and time_waited?
Although the sessions had executed for close to 2 hours and had a lot of wait time on db sequential read, gc buffer waits etc. The wait is always 0 yet the time_waited column shows some high value. If I do a sum(time_waited) and group by name I get some value which far execeeds the time the session has lasted.

Any thoughts are welcome always.

Thanks.

Database 10.2.0.3 and 6 Node RAC Cluster.

Hi Tom,
Thanks for the update. Sorry to have missed out on that vital piece of information.....

Here is the SQL which I use:

Set pages 999 lin 250
Col sql_text for a50 word_wrapped
SELECT SQ.SQL_TEXT,
ET.NAME Event_name,
COUNT(*),
SUM(ASH.TIME_WAITED) Total_time_waited
FROM V$ACTIVE_SESSION_HISTORY ASH,
V$EVENT_NAME ET,
V$SQLAREA SQ
WHERE to_char(ash.sample_time,'DD-MON-YYYY') > '15-MAY-08 09:00:00' AND
ASH.EVENT# = ET.EVENT#
and ash.session_id = 3236
and ash.session_serial# = 49139
AND ASH.SQL_ID = SQ.SQL_ID
GROUP BY SQ.SQL_TEXT, ET.NAME
order by 4 desc;

Cheers.

ops$tkyte%ORA10GR2> select * from dual where '16-APR-08' > '15-MAY-08 09:00:00';

D
-
X

Sorry, I had realised that long time back, but forgot to delete that one. I had modified the query but pasted the wrong one. Thanks for pointing that out :)

Based on your comments, I should go for DBA_HIST_ACTIVE_SESS_HISTORY rather than v$ACTIVE_SESSION_HISTORY, correct?

Thanks.

1 SELECT to_char(max(sample_time),'DD-MM-YYYY HH24:MI') end_sample_time , C.SQL_TEXT,
2 EVENT,
3 seq#,
4 SUM(TIME_WAITED/60000000) minute
5 FROM V$ACTIVE_SESSION_HISTORY A,
6 V$SQLAREA C
7 WHERE A.SAMPLE_TIME BETWEEN to_date('30-07-2008 19:00','DD-MM-YYYY HH24:MI') AND
8 to_date('30-07-2008 23:00','DD-MM-YYYY HH24:MI') AND
9 SESSION_STATE='WAITING'
10 and A.SQL_ID = C.SQL_ID
11* GROUP BY C.SQL_TEXT,EVENT, seq#
LUCOTUS at orcl> /

END_SAMPLE_TIME SQL_TEXT EVENT SEQ# MINUTE
---------------- ------------------------------ ------------------------------ ---------- ----------
30-07-2008 19:55 LOCK TABLE LUCOTUS.TEST IN EXC enq: TM - contention 64 .16565335
LUSIVE MODE

30-07-2008 20:11 update test set object_id = 0 enq: TM - contention 71 2.86739578
30-07-2008 19:52 update test set object_id = 0 enq: TM - contention 47 1.84333278
30-07-2008 19:45 update test set object_id = 0 enq: TM - contention 37 1.92530068
30-07-2008 20:02 update test set object_id = 0 enq: TM - contention 61 1.87855473

---

1 select to_char(maxsample, 'DD-MM-YYYY HH24:MI') end_sample_time, sql_text , event, seq#, m from dba_hist_sqltext ,
2 ( select sql_id ,max(sample_time) maxsample , event,seq#, sum(time_waited/60000000) m
3 from dba_hist_active_sess_history ash
4 where
5 SAMPLE_TIME BETWEEN to_date('30-07-2008 19:00','DD-MM-YYYY HH24:MI') AND
6 to_date('30-07-2008 23:00','DD-MM-YYYY HH24:MI') AND
7 sql_id is not null
8 and session_state='WAITING'
9 group by sql_id, event, seq#
10 ) wait
11* where wait.sql_id = dba_hist_sqltext.sql_id
12 /

END_SAMPLE_TIME SQL_TEXT EVENT SEQ# M
---------------- --------------------------------------------- -------------------- ---------- ----------
30-07-2008 20:11 update test set object_id = 0 enq: TM - contention 71 .292966983
30-07-2008 19:52 update test set object_id = 0 enq: TM - contention 47 .195311267
30-07-2008 19:45 update test set object_id = 0 enq: TM - contention 37 .195320667
30-07-2008 20:02 update test set object_id = 0 enq: TM - contention 61 .1953105
30-07-2008 19:55 LOCK TABLE LUCOTUS.TEST IN EXCLUSIVE MODE enq: TM - contention 64 0


Seems the result (time_waited) from both v$active_session_history and dba_hist_active_sess_history not the same. Am I missing someting here ?

Hi Tom,

What wait event does IMP/IMPDP wait on when it is reading from the (i.e. flat) import file?
My Statspack data shows main wait event is "db file sequential read" but that doesn't seem to make sense since it is reading from a plain old flat file on the server.

Would you please explain this?

Thank you,

Robert.

Tom,

Can we identify stats for these O/S reads anywhere in AWR, STATSPACK, etc. ?

Thanks,

Robert.

Is it a good practice to use a stand-alone server for tuning a query? there are no other sessions except only one session that we use to run the query to produce a trace file for tuning? by the way, production server is on Unix platform and the server that is used for tuning purpose is on Windows platform. However, oracle database version is 9.2.0.7.0. on both.

OR do you recommend the query to be tuned on the production database server itself?

Thanks

Dear Tom,

In the process of Query Tuning, can you please throw some light on how to handle scenario where child table can have unexpected huge number of child records.

This is the scenario from DataMart. We have a child table which is suppose to have max 4000 child records, but we got a strange case wherein this table is carrying 67000 child records for single parent. When I am including this record in query, it is running forever.

What stats gathering mechanism we need to adopt to handle such scenarios? Or do we have any other method to handle this.

Please help.

Tom:

I have a mystery I need your help on. I think you can guide me to pinpoint the issue here.

I have a small package with a few procdures that are called via mod_plsql.
A visual baic app calls the oracle SP which delivers result in XML format. users complain that database seems to sleep sometimes or app is slow in getting data. I ran a few tests in the database and found it to be true sometimes.

It seems to happen first thing in the morning when you first call the procedure. IT does happen sometimes during the day. When i run autotrace and I test the XML query it happens in 0.1 second with excellent stats(see below).



Could it be one of the following:

1) hard parse since execution plan is no in shared pool
2) time it takes oracle to start a unix process to handle query.
3) calling a procedure in a package takes more time first time becaus it is loading all procedures into memory
4) database needs tuning
5) some big job runs in the morning.

Any suggestions for troubleshooting(i.e statspack report in the morning, explain plan/autotrace, TKPROF) any others.






MESSAGE_NO PACKAGE_NAME PROCEDURE_NAME DATE_IN TIME
---------- ------------------------------ ------------------------------ -------------------- ------
2178 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:00;31 42.3 seconds.
2179 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:01;10 17.34 seconds.
2180 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:01;31 1.6 seconds.
2181 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:01;37 1.52 seconds.
2182 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:01;42 1.24 seconds.
2183 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:01;47 1.12 seconds.
2184 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:01;53 .91 seconds.
2185 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:01;57 .06 seconds.
2186 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:02;02 1.41 seconds.
2187 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:02;07 .47 seconds.
2188 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:02;11 .09 seconds.
2189 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:02;16 .95 seconds.
2190 MAGAAZINES MAGAZINES_QUERY_BYMAGANO 20-nov-2009 06:02;21 1.55 seconds.


When i run the SQL in MAGAZINES_QUERY_BYMAGANO

Elapsed: 00:00:00.04


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

Tom:

Thanks for your reply.

I mostly read Chapter 2 of your book over the weekend on performce tuning. Great substance!.


I do not think they startup the test database evey night. Would not this confirm it

SELECT statup_time from V$instance

19-OCT-2009



When you say trace the procedure, Shall I do it at a session level any time (not just at 6:00 AM) or turn sql tracing for the whole database.

Here is my plan using sql*plus for windows.

1. Initialize mod_plsql
declare
    nm      owa.vc_arr;
    vl      owa.vc_arr;
begin
    nm(1) := 'WEB_AUTHENT_PREFIX';
    vl(1) := 'WEB$';
    owa.init_cgi_env( nm.count, nm, vl );
end; /

2. SQL> SET Long 10000
3. SQL> SET serveroutput on
4. SQL> SET AUTOTRACE on

5.  SQL> alater session set sql_trace=true;

6.  SQL> execure pkg.my_procedure;

7.  SQL> exec owa_util.showpage();

7. Go to the server disk and check the trace file created for my session. Run TKPROF and print the report.
   Check the report to see how long it took


Would not auttorace give similar result. It if is the procedure I would think it will always be slow though.
Would statspack report in the morning be of any help in this case too or that is fater we determine it is not the procedure.

Tom:

How do you set this selective trace?

Do you do it in the procedure itself or you change the database parameter SQL_TRACE=ON for a day or how?

I noticed it happens sometimes during the day too.

Is there a way in oracle to capture the CPU saturation stats to determin if it is CPU issue or would the trace report tell you that.

When i run the sql in sql*plus 1st time it shows me
recursive calls = 18
redo size = 212
consistent gets = 18

then on subsequuence calls it resets to 0 (except consistent gets = 3).

Kind of strange to get redo size for a SELECT statement using DB links? I wonder if those DB links have to do with this issue. Query is using tables in another database on same machine though.

Tom:

it is 9i and 7 years old. I cant use apex too until we go 11g in a few months.

Now, selective tracing you mention, i already did that.
I basically inserted a record into debug table and measure the number of seconds it takes for each step in the code.
That would *not* should me the execution plan, stats, etc.

It did not answer the main question though. Most time consume is opening the cursor.

The remote database is sitting right next to this database. Does oracle handle things differntly with DB links. Would the other database cache the execution plan or it has to be hard parse many times with db links.
I have to do it this way because this database has a DAD and open for the web while the other that has the data is not. I do not think we want to open the other database for ther web (security issue).


I can turn SQL_TRACE on this database for a day and then TKPROP the report. Do you think that would help me figure out what is going on? or do i need SQL_TRACE for ther otehr database that i query from using a link.

Hi Tom

apart from all the tuning approaches mentioned above,
I've read that the default SDU size in oracle is 2048 bytes.
On some databases setting it to a value of 1461 has actually resulted in around 40% improvement for me.
I had a rman backup which completed in almost half its regular time after setting SDU to 1461.

can you elaborate more on how this SDU functions and how to judge as to what value to set for SDU on a database server.

Hi Tom,

The rman backup example I gave, I was taking Rman backup from a Windows Machine with Oracle client. The database server was on Redhat Linux. Database was in no archive log mode. I had set the sdu to 1461 on both the client as well as the database server.

Regards.

Tom:

apex is not supported in 9.2.0.2. Oracle supports it in 9.2.0.4 and later. But they also told me to wait to 11g. it is not under my control.

I did sql trace at session level and TKPROF. I found high level of recursive sql and memroy sorts on 1st call.

My query was selecting from a VIEW which had a join.
I changed that so it selects from tables (instead of view) and it seems things became much better. Does oracle optimizer deal differently with a VIEW than a table. DO you think that might cause a large number of sorts and recursive sql? What normally causes that in your opinion.

Statistics
----------------------------------------------------------
1055 recursive calls
1 db block gets
236 consistent gets
11 physical reads
212 redo size
3638 bytes sent via SQL*Net to client
2972 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
46 sorts (memory)
0 sorts (disk)
1 rows processed

Elapsed: 00:00:00.06


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

Tom:

Perforamnce is good (subsecond) except the first call in the morning.

DBA tells me it is normal for database to be slow on first request in the morning as they ahve not warmed up yet. I guess they need to get that morning coffee and stretch up a but before they start servicing requests!

He mentioned that they do a nighly export everynight that might be flushing the cache as it uses it.

Can this be accurate?

I did flush the cache and ran it took a few more seconds ( nothing compared to the 50 seconds i get on 1st request).

But i read the mod_plsql manual and i found interesting thing. The connection pool is at the apache process level and if the process does not service any requests in 15 minutes, mod_plsql will kill the connection pool.

So could it be creating new connections taking a lot of time (I doubt it). would TKPROF show you if it is actually creating a new connection (instead of suing a pooled one).

Mr Tom:

Merry Christmas! 

Can you tell me what is possibly be going on here.
I Have a simple verify login function that takes a user input password and runs a java stored procedure to compute the sha1 string and compare that to the value in a small 100 user table.

The java class is this

http://www.anyexample.com/programming/java/java_simple_class_to_compute_sha_1_hash.xml


I run it over a db link usually where remote database is next to local one. But then i tested it in the local database to see if it is the link and it takes also 30 seconds on first request and then it runs very fast afterwards. Then next day or after some idle time it takes 30-40 seconds and then a snap. The function uses p_password as a bind variable.

1. Why does it take that long and should not oracle be caching it all the time that it should be a subsecond response always. The fetch here is taking 30 seconds. is it fetching the whole ticuser table which is very tiny.

2. how would TKPROF show you if a db link is the issue usually.

SQL> edi
Wrote file afiedt.buf

  1  SELECT COUNT (*)
  2          FROM ticuser
  3          WHERE userid = 'mike'
  4*         AND password = SHA1_PKG.F_SHA1('secret')
SQL> /

  COUNT(*)
----------
         1

1 row selected.

Elapsed: 00:00:28.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TICUSER' (Cost=1 Card
          =1 Bytes=18)

   3    2       INDEX (UNIQUE SCAN) OF 'PKTICUSER' (UNIQUE)




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


SELECT COUNT (*)
        FROM ticuser
        WHERE userid = 'mike'
        AND new_passwrd = SHA1_PKG.F_SHA1('secret')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       1.30          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.38      25.40          1          2          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.39      26.70          1          2          0           2

SQL> /

  COUNT(*)
----------
         1

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TICUSER' (Cost=1 Card
          =1 Bytes=18)

   3    2       INDEX (UNIQUE SCAN) OF 'PKTICUSER' (UNIQUE)




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

Hi Tom,

I would like to ask for the usefulness of Automatic SQL Tuning in Oracle Database 10g.. How effective and reliable is it when compared to human analysis..like we do..

open c for select * from t;
loop
   fetch c bulk collect into array limit 1000;
   forall i in 1..array.count insert into t2 values array(i);
   exit when c%notfound;
end loop;
close c;

Tom:

Thanks, I did not see your response until today.

Would this trace report show you that the time is wasted in fetching one record from this very small user table OR the sqlnet wait.

When i check the Cache (V$DB_OBJECT_CACHE) the package.sql is there but still it should not take oracle to load a simple statement 30 seconds to load it, If it is a caching issue, would you check why it is getting flushed OR schedule a morning job (3:00 AM) to load all of these into CACHE every day.


TKPROF: Release 9.2.0.2.0 - Production on Mon Jan 4 08:55:58 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: tic_ora_13742342.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

The following statement encountered a error during parse:

alter session set_sql_trace=false

Error encountered: ORA-00922
********************************************************************************

ALTER SESSION set events '10046 trace name context forever, level 12'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.04 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.04 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 419

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 21.69 21.69
********************************************************************************

SELECT COUNT (*)
FROM ticuser
WHERE userid = 'mike'
AND password = SHA1_PKG.F_SHA1('Secret')

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 1.75 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.41 24.13 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.43 25.89 0 2 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 419

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=135 r=0 w=0 time=24741940 us)
1 TABLE ACCESS BY INDEX ROWID TICUSER (cr=135 r=0 w=0 time=24741917 us)
1 INDEX UNIQUE SCAN PKTICUSER (cr=1 r=0 w=0 time=42883 us)(object id 234051)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 18.06 30.97
SQL*Net break/reset to client 2 0.01 0.01
********************************************************************************

select longname
from
javasnm$ where short = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.10 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 0.00 0.29 0 40 0 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.00 0.39 0 40 0 10

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

select longdbcs
from
javasnm$ where short = :1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.04 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.04 0 0 0 0

Misses in library cache during parse: 0
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

SELECT max(version)
FROM
"SYS"."JAVA$POLICY$SHARED$TABLE"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.04 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.05 0 6 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.09 0 6 0 2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=3 r=0 w=0 time=51547 us)
2 TABLE ACCESS FULL JAVA$POLICY$SHARED$TABLE (cr=3 r=0 w=0 time=51509 us)

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

select privilege#,level
from
sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with
(grantee#=:1 or grantee#=1) and privilege#>0


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.01 0 0 0 0
Execute 3 0.00 0.07 0 0 0 0
Fetch 180 0.01 0.07 0 87 0 177
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 186 0.01 0.16 0 87 0 177

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
59 CONNECT BY WITH FILTERING (cr=29 r=0 w=0 time=50778 us)
24 NESTED LOOPS (cr=26 r=0 w=0 time=19745 us)
24 CONCATENATION (cr=2 r=0 w=0 time=122 us)
0 INDEX RANGE SCAN I_SYSAUTH1 (cr=1 r=0 w=0 time=57 us)(object id 110)
24 INDEX RANGE SCAN I_SYSAUTH1 (cr=1 r=0 w=0 time=46 us)(object id 110)
24 TABLE ACCESS BY USER ROWID SYSAUTH$ (cr=24 r=0 w=0 time=19586 us)
31 NESTED LOOPS (cr=3 r=0 w=0 time=314 us)
45 BUFFER SORT (cr=0 r=0 w=0 time=140 us)
45 CONNECT BY PUMP (cr=0 r=0 w=0 time=50 us)
31 INDEX RANGE SCAN I_SYSAUTH1 (cr=3 r=0 w=0 time=91 us)(object id 110)

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

alter session set sql_trace=false


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.10 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.10 0 0 0 0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 419



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

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.02 1.85 0 0 0 0
Execute 3 0.00 0.04 0 0 0 0
Fetch 2 0.41 24.13 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.43 26.03 0 2 0 1

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4 0.00 0.00
SQL*Net message from client 4 21.69 52.66
SQL*Net break/reset to client 2 0.01 0.01


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7 0.01 0.21 0 0 0 0
Execute 15 0.00 0.07 0 0 0 0
Fetch 192 0.01 0.41 0 133 0 189
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 214 0.02 0.70 0 133 0 189

Misses in library cache during parse: 0

3 user SQL statements in session.
7 internal SQL statements in session.
10 SQL statements in session.
********************************************************************************
Trace file: tic_ora_13742342.trc
Trace file compatibility: 9.00.01
Sort options: default

1 session in tracefile.
3 user SQL statements in trace file.
7 internal SQL statements in trace file.
10 SQL statements in trace file.
7 unique SQL statements in trace file.
391 lines in trace file.

SELECT COUNT (*)
FROM ticuser
WHERE userid = 'mike'
AND new_passwrd = SHA1_PKG.F_SHA1('secret')

In this above statement the SHA1_PKG.F_SHA1('secret') call is executed for atleast rows where userid = 'mike' or worse for all the rows in the table. Oracle may be spending lot of time doing context switching between SQL and PL/SQL engine. There are couple of ways of reducing it.
1. Get the value for SHA1_PKG.F_SHA1('secret') in a variable and use variable in the SQL.
2. Define the F_SHA1 function as deterministic in the package. This will reduce the number of executions to at most two.

Hope this helps.

ops$tkyte%ORA10GR2> @test
ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function foo(x in number) return number
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> begin
ops$tkyte%ORA10GR2>         dbms_application_info.set_client_info( to_number(userenv('client_info'))+1 );
ops$tkyte%ORA10GR2>         return x;
ops$tkyte%ORA10GR2> end;
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2> drop table t;
ops$tkyte%ORA10GR2> create table t as select * from all_users where rownum <= 5;
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select * from t where foo(user_id)=user_id;


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

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
5

Tom

Database: 10gR2

Is there anyway to find out the past execution plans for the specific query ? I want to see how the actual execution plan was 3 days back at specific time. I have AWR snapshot data for 14 days configured.
Thanks!

Tom:

When I check for worst performing SQL that uses a lot of resources in V$SQL I get this which shows high disk reads and buffer gets.

I do not have this SQL in my application. This must be sent by MOD_PLSQL. Is there some bad SQL there or is there anything I can do for this.

EXECUTIONS BUFFER_GETS DISK_READS FIRST_LOAD_TIME
---------- ----------- ---------- ------------------- SQL_TEXT
-----------------------------------------------------------------------------------------------------------------
675 1040227 423033 2011-01-30/13:03:13
declare rc__ number; simple_list__ owa_util.vc_arr; complex_list__ owa_util.vc_arr; function match_pattern ( p_string in varchar2, p_simple_pattern in owa_util.vc_arr, p_complex_pattern in owa_util.vc_arr, p_use_special_chars in boolean default true ) return boolean is special_chars constant varchar2(27) := ' @*()+-/=\<>;:"|&?{}[]''' || chr(9) || chr(10) || chr(12) || chr(13); trans_chars constant varchar2(27) := '***************************'; l_string varchar2(512) := upper(p_string); l_count integer := p_simple_pattern.count; begin if (p_use_special_chars) then if (instr(translate(l_string, special_chars, trans_chars),'*') > 0) then return true; end if; end if; for i in 1..l_count loop if (l_string like upper(p_simple_pattern(i)) escape '\') then return true; end if; end loop; l_count := p_complex_pattern.count; for i in 1..l_count loop if (owa_pattern.match(line => l_string, pat =>
upper(p_complex_pattern(i)),flag

431 405998 327606 2011-01-30/08:00:14

Tom,
quick question about the optimizer choice. I'm running 10.2.4 on Sun. Don't have system stats on. tables in question are loaded and stats are collected. These are regular tables. nothing "fancy" or special about them.

Take 2 tables and define ref integrity. The only one note here- the FK on child is on PK column.
If I issue the follwoing statement:

select c.a from parent p, child c
where p.pk= :1 and p.pk=c.pk

in the plan it looks up the parent table pk before proceeding with access to child.
But if i think about what the sql is doing than it's seemed to be a redundent operation - since all information is presented in the child to begin with.
Am i missing smth?

Hi Tom,

We have a system where our researches constantly enter and query data in our database as they find information,
throughout the day. It is a multi-user environment, OLTP if you will.

Our architecture is such that all the application logic resides in xml/java layer. Believe it or not,the application has "built-in" concurrency control, constraint management, cascade updates,cascade deletes, dynamic SQL generation, pagination and all that goes with an application. No stored procedures, packages or any procedural objects except tables, views and indexes.
Everything is meta-data driven. The database (oracle 10g) is used primarily as a bucket which holds data.
"yikes" is an understatement :(

I was brought in to address performance issues for this system.

I did some preliminary checks with explain plan/tkprof and found that the "sql generator engine" is generating some "abusive" sqls which is very difficult to fix. I fix one thing and it breaks another. It also does things like count(*) on big tables to get total no.of pages for pagination queries etc. The application developer/DBA who worked on this before me tried various partition schemas,
indexes etc but it seems dint work out.

For example. We have a big table about 7148354 rows (about 1.5 gb) which has about 37 columns. Researches can bring up the information on the UI (big table, joining several tables) and query on any combination of these 37 columns to narrow their search and delete/update information
as appropriate. This search is always a wild card search. The SQL Engine always does a LIKE operation.

Also, the query is always on "enddate is null" meaning, all the rows which are active. Inactive rows are "end dated" with sysdate. I tried several combination of concatenated indexes, function based indexes,bit-map indexes etc; nothing worked as optimizer always wants to do
a FTS/Hash join, understandably so, because that would be less expensive than index search. The distribution on the table is something like

total rows in the table: about 7148354
rows with enddate is null 6967034
rows with endate is not null 181320

I tried intermedia text but dint workout because we had to rebuild the indexes each time to make the search current.

The performance is painfully slow, i mean very very slow.

Please let me know if I can do anything else before giving it up! Please do let me know if you need more information.

Rewriting this application is ruled out in near future.

Thanks in advance!

Well, as a FTE i cant run away so quick :) I would have definitely done that if i were a contractor. Anyways, I always feel good when I validate my thoughts with you. Asktom never let me down so far. Thanks a million for your service!

Tom:

I have a web application with a slow main menu (3-5 seconds) page. The menu design is such that the page is about 20 KB in size because it builds dynamically list of vendors to view/edit data for and list of contacts, etc. Also, all the application links are listed on the main menu.
It also has a few derived numbers that are calculated real time (i.e open orders). so every time user navigates to main menu we run the sql again.

I did a SQL trace and checked the tkprof report and found that two SQL commands that use a dblink to lookup an organzation and contact table cause the delay sometimes. It could be a network or machine issue.

The tables in the remote db which is on same machine do not get updated that often. I am also looking up a few columns in those tables.

i am thinking of copying those remote tables locally to speed things up and refresh the tables nightly from remote tables.

Do you agree? and how would you implement this.

WOuld you

a) create a local table with same structure and procedure and job to DELETE data and the INSERT new data from remote table.
b Run DDL (CREATE TABLE AS SELECT * FROM TABLE@LINK) in local database to create the local table from remote table every night..
c) WOuld you create Materialized view for local table and schedule to refresh nightly from remote table..

Can you advise?


Thanks,

Tom:

1) How often do you refresh your MV? Are the stats updated real time (i.e if someone closed an order and he goes back to main menu)?

2) What about looking up the data from remote table. I only need a few columns (i.e last name, first name, organization type).

Would you create a local table or MV for this too?

If i ran DDL to create the table I would need to create the index and update the STATS. Even if i delete data and reinsert I would think i would need to update stats so optimizer can use the index.

om:

To answer your question first, "it depends".

If the source table has 100 ROWS and remote table has 100 ROWS then you dont need to update stats, but if your remote table has 10000 and you loaded those into local table or MV then yes you would need to update it..



You know what I concluded yesterday. Using DB links real-time in an application is as dangerous as some nasty virus sleeping in a human body. You never know when it wakes up and does damage. If you think triggers are bad, DB links are worse.


DB links are very tricky. When i run the SQL in sql*plus to test i get excellent performance time and logical/physical statistics but when I analyzed many TKPROF reports to see what is causing all these delays it is all related to the DB link. It is very unpredictable.

Even a simple SQL like "select fname,lname from contact@hr_link where contactid='john' " would take sometimes 1-2 seconds.

I should have listened to you before but sometimes you dont believe a thing until you see it live.
For all remote tables I created READ-ONLY MATERIALIZED VIEWS and got rid of all the DB links and the application runs faster than a high speed train. What a difference that made!

My main menu was taking sometimes 5 seconds, now it runs in 0.05 seconds.

The people who originally architected this thing were creating a database/instance per application. And now we have applications and links all over the place creating all kinds of chokes.

1) Why do you think DB links are sometimes slow? Is it a networking bottleneck or it is a connection overhead or the other instance is tied up. I noticed also it always PARSES the SQL with every call when using a link.



2) This is what i did.

On the Local/master database:

CREATE MATERIALIZED VIEW LOG ON <tab> ;

On the remote site:

CREATE MATERIALIZED VIEW <mv_name>
BUILD IMMEDIATE
REFRESH FAST START WITH SYSDATE NEXT TRUNC(SYSDATE) + (25/24)
AS SELECT * FROM <tab>@<dblink>;

That should build a read-only materialized view that is refreshed daily at 01:00 AM.


This solution works well for all REMOTE SELECTS/READS. Do you see any issues I did wrong?



3) It is not as easy to get rid of the REMOTE WRITES that use a link that are done real time too.
How do you handle those usually. Do you write locally to a table or MV and push the records later somehow.
Can you explain how best handle UPDATES/DELETES/INSERTS that use a db link.

Would you create a local table and save the data there and have a hourly or nighly job that checks the table and copies the data to remote and then delete it from local.


I think the best thing for the long term is to consolidate the databases into one and create different schemas but that requires more work and management approvals and funding.

ops$tkyte%ORA11GR2> @mystat 'parse count (total)'
ops$tkyte%ORA11GR2> set echo off

NAME                        VALUE
---------------------- ----------
parse count (total)            85

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          cursor c is select * from dual@loopback@ora11gr2;
  3          l_rec c%rowtype;
  4  begin
  5          for i in 1 .. 100
  6          loop
  7                  open c;
  8                  fetch c into l_rec;
  9                  close c;
 10          end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> @mystat2
ops$tkyte%ORA11GR2> set echo off

NAME                        VALUE DIFF
---------------------- ---------- ------------------
parse count (total)            92                7

Tom:

I guess I was wrong on thinking that if the remote database runs on the same machine you would not have much network issues and connection issues.


But you did not explain to me how I would handle WRITES with MVs.

For example, I have this situation now where the local user create new VENDORS and CONTACTS before he can place an order.

The VENDORS and CONTACTS tables are in a remote database so i INSERT the data using a db link.

The issue here is that READ ONLY MVs i created for VENDORS and CONTACTS table in the local database will not have this new vendor so the until the MV is refreshed at night. he user may need to select this vendor on the order form.

How do you handle this issue. WOuld you

a) Schedule a JOB to fast refresh the MV in the transaction that inserts the record into the remote DB.
b) Make the local MV Writeable and insert the record in remote and local database.
c) Save the vendor and contact data locally into the local MV and push it to the remote DB via job.

What do you advise to do?

Hi Tom,

My database is experincing lot of Emq-Tm contentions. PPls say that we need to create an index on foreign key on child tables. Whys is it required.. Whats is data flow between parent and child tables when DML's are fired.

It would be a great help,if u can explain me with an example

Tom,

The batch process which runs for 9 hours is now taking 14 hours to complete with the server which has higher configuration in terms of CPU and memory. This is affecting business as we are unable to give the report.

Could you please confirm why does it takes time in new server which has better capacity high end server ? Ideally it shound at least give the same performance as old server, but it gives poor performance.

The things i tried :

I had looked at AWR report and got few problematic queries and if i use specific index hint in few queries which has problem the total job run coming close to 12 hours.

gathered stats with auto_sample_size across database.

if i disable parallelism on few tables the query runs better, why is this on new server, better resources availability.

and many more..

My questions are:

1. If the sql's have to be tuned why the same runs well in old server, though i copied the stats from old to new?
2. the new machine has double the capacity of old server in terms of CPU and memory, why does the sql takes poor plan?
3. If I gather stats with estimated of 100% with dbms_stats.gather_database_stats, it should run well, but teh job never completes even after 15 hours and I had to copy the stats from old to new server to bring close to 14 hours, why is this case?
4. Do you suspect any other problem ?
5. Do i really need to do SQL tuning of all the problmatic queries to fix? Again doubt raises the same running well if i point the connect string back to old sever.

Your expert advise is really appreciated.

Tom,
I just need your advice on the partitioning way of improving the things.
I read your books chapter covering the partitioning bits few years back.

we have a real example/tasks on paritioning on hand and just wanted to share with you for your expert opinion. ;)


facts :
1. table T is range partitioned on column (c1 number)
2. c1 column is not unique.
3. composite ( c1, c2, c3) forms a unique combination.
3. partition p1 has range upto 1000000000, p2 has upto 2000000000,.....partition p10 has upto 10000000000 and rest is maxvalue. --- I didnt changed 1000000000 and likewise to 1,000,000,000 to make it more readable as in one of the post you mentioned 1,000,000,000 is not the number, sorry about that :)
4. we seen all the data is going to p1 partition - as of today , max(c1) is far below from the ceiling figure 1000000000, table is being used since last 02 years or so.
5. sudenly one day it is found - partitionining is not proper ( we see pruning would not be happening ).
6. We are in the process of assessing if the paritioning is proper or not - because someone did the partition few years ago..
7. We are at 10gR2 (10.2.0.4)



Next immediate step:
a) Decided to split partition the p1 ( making it smaller)leaving the other p2... p10 as it is - thus inviting the split partiton again again :( ...- also the million dollar question would be - how to choose the magic figure of splitted parition size..

My approach :
a) Understand the question, whether partitioning is really required or not ? ( this would lead to first looking into what we want to achive from partitioning...
I see in our case it is only the Performance ( one of the corner of the tringle you mentioned in your book) to be achived.
b) If the queries happens to have the predicate on columns c1 , then choose c1 as the candidate column for partitioning else chose other column/s.
c) re-visit the paritioning scheme , may be hash(c1) would be more appropriate here...as Oracle would do equal distribution of the data across the paritions
d) Keep the parition across the different tablespaces.. ( availablity corner of the triangle), But this is really rarely required in 21st century databases .. we have redudndancy, robustness built at every level ( disks spindles/ controller/ IO borads etc) so things usually wont fail...it is something like all will fail ( disaster ) or nothing would fail :)
e) I would see if Administration ( last corner of the triangle ) is required to achived in this case.. may be if someone getting rid of inactive data frequently.. or someone doing exchange paritition etc using this table..


Question/s
q1) your comments on the give facts avove, next immediate step and my approach of analysing the same?
q2) how does hash paritioning internally works? .. some pseduo code would give more insight.. ( i have read many psuedo code from you , which makes the things crystal clear.. the one which always runs of top of my mind is pseudo of 'rollback' which starts with commit statement ;) )
q3) Do the inserts would get benifitted from partitioning ( may be parallel inserts .. ?) , I feel in 20th Century this might have been, but now a days it hardly matters.. as LMT, ASSMS are there today.
q4) If performance gain is the only aim...then most of the queries which involves the c1 column , would defintely get benifitted from paritioning pruning c1 column ( in case Full table scan .. it would be small table now to full table scan .. and in case of index range scan .. it would be local index range scan..).?
q5) querris which involves predicate other then the partition key column (c1) , we can safely create the global indexes and for those querries it would be as if table is not paritioned.. ?
q6) How to estimate the time taken for - changing the existing paritioning scheme or creating partitons from the existing partition and say this would take some <n> hrs.. I would do benchmark to reach on such figures .... ? what do you say ...I read some article " Partition 4TB tbale in 20 mins " - is there something we can exploit within Oracle to reduce th outage during partioning implementation or we have to go for DIY ( customization approach ) to really make the outage to minimal during partioning implementation.?
q7) I see paritioning implementation requires good understaning of performance tuning? which in turn requires basic understanding on everything how Oracle works ;)

Many thanks for your time and happy weekend ahead.

Hi Tom,

I came across this blog which teaches how to use the dba_hist_active_sess_history to point to RAC DB issue.

The author after his analysis points the problem to the fact that segment space management of the Tablespace was set to Manual.

My Question to you is: Oracle recommeds auto - segment management to be set to automatic all the time. From your experience can you explain to me any scenario where we would need the auto-segment to be set to Manual?

Forgot to mention the link in my previous post

http://www.ardentperf.com/2007/09/12/gc-buffer-busy-waits-in-rac-finding-hot-blocks/

Hi Tom,

We have a big table in production which is used extensively in online .And in batch we insert many rows as well.The problem is while in batch processing we see lot of index contention(because parallel processing in batch) but we need index for online otherwise it will be very slow.So every day before batch we drop this index and at morning re create index.Is there any solution to handle this kind of case.

Tom,
we have the query of the folowing ..

MERGE /*+ PARALLEL(b,2) NOLOGGING */
INTO t_load b
USING
( SELECT a.SERVICE_KEY SKEY ,a.ACCOUNT_KEY AKEY
,a.PERIOD_KEY PKEY ,a.USAGE_TYPE_KEY UKEY ,COUNT(*) CNT FROM
t1 a WHERE a.Key = :B1 GROUP BY
a.SERVICE_KEY, a.ACCOUNT_KEY, a.PERIOD_KEY,
a.USAGE_TYPE_KEY ) A
ON ( b.SERVICE_KEY = A.SKEY AND b.ACCOUNT_KEY = A.AKEY AND b.PERIOD_KEY = A.PKEY AND
b.USAGE_TYPE_KEY = A.UKEY AND b.Key = :B1 )
WHEN MATCHED THEN
UPDATE SET b.TOTAL_CALL_CNT = A.CNT

and the plan is( -- sorry Tom, I tried to format the below to more readable .. but not succeeded)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                       | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                                 |                             |       |       |       |   637 (100)|          |       |       |        |      |          |
|   1 |  PX COORDINATOR                                 |                             |       |       |       |            |          |       |       |        |      |          |
|   2 |   PX SEND QC (RANDOM)                           | :TQ10002                    |   585 | 54405 |       |   637   (1)| 00:00:09 |       |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    INDEX MAINTENANCE                            | t_load           |       |       |       |            |          |       |       |  Q1,02 | PCWP |          |
|   4 |     PX RECEIVE                                  |                             |   585 | 54405 |       |   637   (1)| 00:00:09 |       |       |  Q1,02 | PCWP |          |
|   5 |      PX SEND RANGE                              | :TQ10001                    |   585 | 54405 |       |   637   (1)| 00:00:09 |       |       |  Q1,01 | P->P | RANGE    |
|   6 |       MERGE                                     | t_load                      |       |       |       |            |          |       |       |  Q1,01 | PCWP |          |
|   7 |        BUFFER SORT                              |                             |       |       |       |            |          |       |       |  Q1,01 | PCWC |          |
|   8 |         PX RECEIVE                              |                             |   585 | 54405 |       |   637   (1)| 00:00:09 |       |       |  Q1,01 | PCWP |          |
|   9 |          PX SEND HYBRID (ROWID PKEY)            | :TQ10000                    |   585 | 54405 |       |   637   (1)| 00:00:09 |       |       |        | S->P | HYBRID (ROW|
|  10 |           VIEW                                  |                             |       |       |       |            |          |       |       |        |      |          |
|  11 |            SORT GROUP BY                        |                             |   585 | 54405 | 82000 |   637   (1)| 00:00:09 |       |       |        |      |          |
|  12 |             PARTITION RANGE ALL                 |                             |   585 | 54405 |       |   627   (1)| 00:00:09 |     1 |    12 |        |      |          |
|* 13 |              HASH JOIN                          |                             |   585 | 54405 |       |   627   (1)| 00:00:09 |       |       |        |      |          |
|  14 |               PARTITION LIST SINGLE             |                             |   585 | 40950 |       |    36   (0)| 00:00:01 |       |       |        |      |          |
|  15 |                TABLE ACCESS BY LOCAL INDEX ROWID| t_load                      |   585 | 40950 |       |    36   (0)| 00:00:01 |   KEY |   KEY |        |      |          |
|  16 |                 BITMAP CONVERSION TO ROWIDS     |                             |       |       |       |            |          |       |       |        |      |          |
|* 17 |                  BITMAP INDEX SINGLE VALUE      | bitmap_ndx1                 |       |       |       |            |          |   KEY |   KEY |        |      |          |
|  18 |               PARTITION LIST SINGLE             |                             | 10475 |   235K|       |   591   (1)| 00:00:09 |       |       |        |      |          |
|  19 |                TABLE ACCESS BY LOCAL INDEX ROWID| t1                          | 10475 |   235K|       |   591   (1)| 00:00:09 |   KEY |   KEY |        |      |          |
|  20 |                 BITMAP CONVERSION TO ROWIDS     |                             |       |       |       |            |          |       |       |        |      |          |
|* 21 |                  BITMAP INDEX SINGLE VALUE      | bitmap_ndx                  |       |       |       |            |          |   KEY |   KEY |        |      |          |

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  13 - access("b"."PERIOD_KEY"="a"."PERIOD_KEY" AND "b"."SERVICE_KEY"="a"."SERVICE_KEY" AND "b"."ACCOUNT_KEY"="a"."ACCOUNT_KEY" AND
              "b"."USAGE_TYPE_KEY"="a"."USAGE_TYPE_KEY")
  17 - access("b"."Key"=:B1)
  21 - access("a"."Key"=:B1)

facts:
a) we seen during long ops hash doing a lot of work

  ID   SID SERIAL# USERNAME START_TI SQL_ID        MESSAGE                                   Minutes
---- ----- ------- -------- -------- ------------- ---------------------------------------- --------

   2  2054     753 OLAP     06.07.12 apvkedjeg2tbb Hash Join:  : 2778396 out of 17174512 Bl   187.57

ocks done
17174512 blocks = 17174512 * 16*1024 Bytes -- 16k block size
= 262 GB !!!
Its is too much
we have t_load just 28 GB
an
t1 just 2 GB

b) we saw most of the work done here hence most wait time.

c) There is spill to disk ... here
currently we have work_area size policy manual for this session, but sort_area is just set to 64k and no hash_area set
we will bump these 02 _area to 2GB

d) we see merge takeing hrs to complete.

e) we are at 11.2.0


Question
a) Why Hash join doing this huge work here ? - bug?
b) We seen somewhere "INDEX MANAGEMENT" ? But we are not updating the index columns which are used in the plan..? would Oracle be waiting here?
c) update would actually happen when the 1st rows flows out from hash join?
d) any other suggestion?

Tom
further,

e) Would "INDEX MAINTENANCE" be happening when hash operation is in progress? -- its when 1st set of candidate rows would keep flowing out from hash join and those would be taken in next operation ..So would "INDEX MAINTENANCE" be adding to the has join long operation stats which are shown above?

regards

Tom,
Sorry I asked the same question by direct mailing to you sometime ago .
I know that is not scalable solution. sorry about that .
:-(


I am working on performance analysis of a database. I have identified the areas and these are below at high level.

1. Concurrency issue
parse/execution =1 - many parse and many execution of the same SQL - a soft parse ) - due to this sometime top 5 wait event in AWR for 1 hr are showing 60% concurrency waits on latch: library cache

2. Sweeper indexes : Index height 5 in many cases, index size > table size for a single number column index; table has 6 columns.

3. Index contention enq wait sometime popping up in AWR accounting 10% concurrency waits

4. Stale statistics in all schema objects - statistics not gathered since many years. There are around 8 billion DMLs on the table ( now total 9 billion).

5. Improper partitioning.
Range based partitioning for sequnce populated columns; 1 table has all the data in single ( 1st partition - very high value of partition range) and other have started seeing most of the data in the maxVal partition ( low value of partition range).

6. Improper Indexing scheme on partitioned table
All Non partition key are created as LOCAL indexes. More IO.

7. Some of the tables( say t1 , and t2) have primary key not enabled. Reason - these tables are partitioned table and primary key column has LOCAL partitioned index created. Index on primary Column is not composite index ( not including the partition key column) . issue leading to logical corruption of data.



I have evidences of above issues and recommended the fixes.


Now the question/s:

a) If we fix all above how much % performance gain would be there?

b) By seeing the issues listed above , when the system breakdown would occur ( future date/time) ?

c) if we do all above execercise how long the we can push the system breakdown date? - I mean if on one hand we dont not do above fixes system would break on day/time -date_1_time_1 . However on other hand if we do the fixes for the above issues today how long we can push the date_1_time_1 to another date date_2_time_2 ( here date_1_time_2 > date_1_time_1 )

d) if really answer exists for above questions what is the methodology we should use.?

thanks a lot Tom.

I am also of the same view.

Hi Tom,

hope you are doing great.

In your book expert one-one-one on page 541,
you have written the below sentences.
############
There wasnʹt an index in the world that could help us here – so we built our own. Every
night as we refreshed the data from our HR system (a complete refresh of data), we would
also issue:
CREATE TABLE FAST_EMPS
PCTFREE 0
CACHE
AS
SELECT upper(last_name)||ʹ/ʹ||upper(first_name)||ʹ/ʹ …. || ʹ/ʹ || ...................
.
.
.
.
.
and on page number 543 you had written below sentences.
######
Before settling on this design for the above application, we tried a couple of alternative
approaches. We tried using a fast full scan on a function‐based index (close, but not as fast),
we tried interMedia Text (not useful due to the %ABC% requirement),
#######

First of all I want to say very nice book.
I have a doubt regarding above excerpt .
##############
In page 541 you have written database had no index concept
that time and in page 543 you have written that you tried
a fast full scan on a function based index( referring to the texts on 541) .

Could you please clarify whether function based index concept was there but no normal index concept was there ,or
You have written wrongly on page 543 that you tried full scan function based index.

Thanks as always.

Hi Tom,

You dont know how much we love you.
I may know( only know :) ) fraction of you know about oracle ,even less than that, but I always believe in you.

But here your Bob ,Sam and Mary example made me upset a bit.
You wrote ,"We tried using a fast full scan on a function‐based index (close, but not as fast)".

How come it is close,but not as fast ,if it is not at all exist?

think of it this way:

there was no one that could help Bob. He tried Mary, she couldn't help. He tried Sam - Sam couldn't help him either. So Bob did it himself.

I didnt think like that, only thought like below.

There was no one that could help Bob. He even cant try Mary,coz mary doesnt exist .He cant try Sam ,coz Sam does not exist too.

Sorry ,if it hurts you.

Thanks as always.
Long live

Hi tom,
below is the query i tuned from old one.but still it is taking 15 minutes.is there any way to tune better.or it seems the better one.(tables contain rows in lacs nearly in millions).can you tell me...please please...

SELECT /*+ leading(r.run r.rsk )*/
R.Uprice,
R.Aprice,
R.Pprice,
R.exeID,
R.exeDATE,
R.SID,
R.PVprice,
R.PV,
R.PREMIUMPV,
R.BOOK,
R.Makeup,
R.MAkeupprice,
T.businessTYPE,
T.REASON,
T.businessTABLE,
T.businessID,
'20 Dec 2013' AS ARC_date,
'LDN' AS ARC_place,
'2013-12-23 04.52.56' AS ARC_DATETIME
FROM
OWNER.totalRISK R,
OWNER.TRADE T,
(SELECT Name
FROM OWNER.BOOK
WHERE BookGroupYN = 'N'
START WITH Parent IN ('FX ACE ' , 'harward ' , 'harvard_LTFX' , 'FX Utility' , 'GCS FX' , 'HRE London' , 'MGT London' , 'proper London' , 'Prop Strategy' , 'Spot ' , 'SYSTEMATIC')
CONNECT BY PRIOR Name=Parent
) b
WHERE
R.SID = T.SID
AND T.VERSION = R.VERSION
AND R.Book = b.name
AND R.Department='FX Cash Controlling'
AND R.Location = 'London'
AND R.RunType = 'EOD'
AND R.RUNDATE = '20 Dec 2013'