For those that didn't know (like me) -
10046 Trace Levels
0 - no statistics
1 - basic statistics - This is the same as setting sql_trace=true.
2 - same as level 1!!!
4 - same as level 1 except adds BIND section
8 - same as level 1 except with wait events.
12 - combines levels 4 and 8.
Then use
SYS.DBMS_SYSTEM.SET_EV(&SID, &SERIAL, 10046, &LEVEL, '');
or use
SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(SID, SERIAL#, waits=>TRUE, binds=>TRUE); -- equivalent of level 12
HTH
Cardy
-----Original Message-----
From: Cirillo Joe
[mailto:oracledba-ezmlmshield-x18346626.[Email address protected]
Sent: 31 May 2005 13:38
To: LazyDBA Discussion
Subject: RE: Select Count(*) very fast yesterday but its slow today
This particular query will perform a full table scan. It will not be
making use of an index.
Statistics would not help. Your comments about statistics may have been
the reason the interviewer appeared to have had an issue with your
response. I would execute the query using a 10046 level 8 trace and
examine the resulting trace file to determine where the time was being
spent.
-----Original Message-----
From: siva
[mailto:oracledba-ezmlmshield-x42488846.[Email address protected]
Sent: Tuesday, May 31, 2005 6:31 AM
To: LazyDBA Discussion
Subject: Re: Select Count(*) very fast yesterday but its slow today
Look at the explain plan immediately
"JAIMURUGAN Shanmuga Velayutham "
<oracledba-ezmlmshield-x23705609.[Email address protected]
05/31/2005 02:17 PM
To
"LazyDBA Discussion" <[Email address protected]
cc
Subject
Select Count(*) very fast yesterday but its slow today
Hi all,
This was the question recently I encountered in an interview. I gave the
answers below but that doesn't seem to satisfy the interviewer.
QUESTION:-
SELECT COUNT (*) FROM TABLE_NAME:
The query ran fine and I got the output in less than 25 seconds. The
next day I ran the same query and I got the output after 15 minutes.
What is the reason for this? As a DBA what is that you will check
immediately?
My Answer:-
1. Mass insert or mass delete would have happened in the table,
Statistics were not gathered after that .So the performance has
degraded. 2. Statistics would have been deleted.
Are there any other reasons for the performance degradation?
Thanks in advance
JAIMURUGAN
Confidentiality Statement:
This message is intended only for the individual or entity to which it
is
addressed. It may contain privileged, confidential information which is
exempt from disclosure under applicable laws. If you are not the
intended
recipient, please note that you are strictly prohibited from
disseminating
or distributing this information (other than to the intended recipient)
or
copying this information. If you have received this communication in
error, please notify us immediately by return email.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
*****************************************************************************
This e-mail and its attachments are confidential and are intended for the above named recipient only. If this has come to you in error, please notify the sender immediately and delete this e-mail from your system. You must take no action based on this, nor must you copy or disclose it or any part of its contents to any person or organisation. Statements and opinions contained in this email may not necessarily represent those of Littlewoods Group Limited or its subsidiaries. Please note that e-mail communications may be monitored. The Registered Office of Littlewoods Group Limited and its subsidiaries is 100 Old Hall Street, Liverpool, L70 1AB. Registered number of Littlewoods Group Limited is 5059352.
*****************************************************************************
This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com
Oracle LazyDBA home page