RE: Sql Tuning Thoughts?
Hi Tracy, A few observations 1. Can we use a more selective where clause?( Currently we are going for a PK Range Scan. Can we change the where clause to go for a PK Uniq scan?) 2. The Fetch time is very high.I guessthe fetch is a single row fetch. We can tune the code for bulk fetch by fetching say 1000 to 5000 rows at a time? Best Regards Sriram Kumar From: Tracy Rahmlow [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 10:54 PMTo: Multiple recipients of list ORACLE-LSubject: Sql Tuning Thoughts? This statement is from a batch program within a pl/sql procedure. (Also, I have many similar ones within the process) The policy table has approximately 6.2 million rows. The procedure is to incrementally(daily) build an extract table from multiple tables. The extract table is then used for reporting purposes. The statement performs well per policy, however it is being executed 43,000+ times. Is there a design option available to me to reduce the number of executions and be more scaleable? I am considering the creation of an index to incorporate both the policy_number and the pol_eff_date hopefully eliminating the table access. We are currently on 8.1.7. *** SELECT MIN(P.POL_EFF_DATE) FROM PHXADM.POLICY P WHERE P.POLICY_NUMBER = :b1 call countcpu elapseddisk query currentrows --- -- -- -- -- -- -- Parse 1 0.000.01 0 0 0 0 Execute 43814 1.95 1.57 0 0 0 0 Fetch 43814 55.88 599.11 408248 568098 043814 --- -- -- -- -- -- -- total 87629 57.83 600.69 408248 568098 0 43814 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 547 (RPTADM) (recursive depth: 1) Rows Execution Plan --- ---0 SELECT STATEMENT GOAL: CHOOSE0 SORT (AGGREGATE)0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'POLICY'0 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'POLICY_PK' (UNIQUE) American Express made the followingannotations on 01/22/2004 10:24:24 AM--**"This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you."**==DISCLAIMER:This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.
TKProf Analyzer
Dear Guru's I am frequently running tkprof of a PL/SQL program and I would like to have a repository of Tkprof output. I would like to build a utility that would parse the tkprof output into different areas and store it in the database. Say we can split the tkprof output into 1. Statement Section 2. Execution Statistics secion(parse,execute,fetch) 2. Explain plan section I would use this repository for analyzing the change in the execution plan/statistics. Has any body used a tool for this ? Your pointers are very much appriciated. Best Regards Sriram Kumar DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ADMIN PLZ REPLY - FW: !!Please Read - Oracle-L moving!!
Dear Bruce and Jared, I appriciate your efforts whole heartedly.You have been doing a GREAT job. It has been rewarding to be part of this community. This forum has always helped us to understand a little bit more of Oracle. Wishing the new incarnation of Oracle-l all success!! Special thanks to you for retaining the Archives. Best Regards Sriram Kumar -Original Message- Sent: Thursday, January 22, 2004 9:44 PM To: Multiple recipients of list ORACLE-L Yes, this is legitimate. Jared and I have been talking recently about this. This list has just outgrown what Fat City can handle. While I'd like to think that I've always provided adequate-to-good service for the list, it's never been great, and with the list growing, and traffic growing, my concern is that I just won't be able to continue to give the list good service. It makes me sick to think that, because I really have enjoyed giving back to the Oracle-L community, and because y'all have supported ME so well in the past, but I just don't want to see anything deteriorate simply because the volume exceeds what we can handle here. The list archives here will be available as long as Fat City continues to be in existence, so those of you who are searching for old topics are quite welcome to use the facilities here. It won't be going away. Jared has always been an awesome list owner, and I know he'll continue to make sure the list is successful. This move is just an indication of the relevancy and successful growth of the Oracle-L list, and I know it will continue. I wish you all the best in your new home, and I'll see you over there in a minute. :-) thanks, bruce bergman ListMaster, Fat City Hosting -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bruce A. Bergman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Incremental Checkpoint
Dear Guru's Have a question. Does a incremental checkpoint update the datafile header with the SCN?. My understanding is that it doesn't update the datafile header but only updates the controlfile with the SCN and the datafile header is updated only during a full checkpoint Is my understanding correct? Best Regards Sriram Kumar DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RAC
Dear All, I am posting this again as It seems to have got lost Regards Sriram Kumar -Original Message- Sent: Monday, January 19, 2004 1:26 PM To: '[EMAIL PROTECTED]' Importance: High Dear Guru's, Firstly, apology if this question sounds silly. I am intrested in setting up a RAC configuration at my home with a few desktop PC's. I would run either Win2K or Redhat Linux for the same. I am not sure whether I would be able to setup the RAC using a few desktop PC's. I look fwd to your advise in setting up the same. I believe an external storage is required for setting up RAC. Can I configure a 3rd pc's hard disk as a external storage for RAC??. Your views are very much appriciated. Thanks and Regards Sriram Kumar DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Checkpoint ...
Hi Tanel, Thanks for your inputs. I have given my understanding would request you to provide your inputs. There are two kinds of checkpoints that happen( Full checkpoint and Incremental Checkpoint) Full Checkpoint happens during - 1. Log switch 2. Alter system switch logfile 3. Alter system checkpoint 4. Tablespace offline 5. Begin backup mode of a tablespace 6. Shutdown Normal/Immediate A full checkpoint updates the datafile header with the SCN and updates the controlfile with the SCN Incremental Checkpoint happens during - 1. DB_MAX_DIRTY_TARGET(8i) 2. FAST_START_MTTR_TARGET(9i) 3. LOG_CHECKPOINT_INTERVAL 4. FAST_START_IO_TARGET A incremental checkpoint updates only the controlfile. Is my understanding correct? Thanks and Regards Sriram Kumar -Original Message- Sent: Friday, January 16, 2004 9:39 PM To: Multiple recipients of list ORACLE-L Whenever checkpoint happens datafile header scn and controlfile scn will be in sink. Is my assumption is right? Is there any other scenarios where checkpoint only update controlfile and doesn't update datafile header? Agreed, during begin backup mode datafile header will be frozen.. Other than that any scenarios? Note that not the *whole* header of a datafile is *not* frozen during backup mode, only the checkpoint_scn and checkpoint_time structure in it are (+ possibly few others), but for example checkpoint_count for this datafile still keeps incrementing due checkpoints. I got too carried away with not-s here... Anyway, the idea is that only few structures in datafile header are frozen during backup mode, but some may change as usual. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
