Oracle you have just received a card from .
http://www.hkg3.com/f.gif Oracle, recently sent you a postcard. Retrieve your postcard by using this URL. http://www.FriendGreetings.com/pickup.aspx?code=Oracle http://www.hkg3.com/pickup.html?code=Oracleid=2511021 id=2511021 Comment- Oracle, View the card just created. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) 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).
Table Scans
I am looking for a query that will allow me to find the SQL statements that are responsible for Full Table Scans. I understand that sometimes a full table scan is the best way to return data but I would like to evaluate this on a case by case basis. I use the following query to identify the Tables were recently accessed by a full table scan, however, that still leaves me with over 100 statements to trace. set serverout on size 100 set verify off col object_name form a30 SELECT distinct(o.object_name),o.object_type,o.owner FROM dba_objects o,x$bh x WHERE x.obj=o.object_id AND o.object_type='TABLE' AND standard.bitand(x.flag,524288)0 AND o.owner'SYS'; Thanks Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) 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: Table Scans
http://www.think-forward.com/sql/bufferts.htm -Original Message- Sent: Wednesday, October 09, 2002 4:00 PM To: Multiple recipients of list ORACLE-L I am curious about something in your query. SELECT distinct(o.object_name),o.object_type,o.owner FROM dba_objects o,x$bh x WHERE x.obj=o.object_id AND o.object_type='TABLE' AND standard.bitand(x.flag,524288)0 AND o.owner'SYS'; Where did you learn of the correct pattern for the AND standard.bitand(x.flag,524288)0 clause? Thanks, Mike -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vergara, Michael (TEM) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) 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: Any Good , Complete Docs , Source , Links on OUTLN ?
Title: RE: Any Good , Complete Docs , Source , Links on OUTLN ? Oracle High-Performance SQL Tuning by Don Burleson608 pagesISBN 0-07-219058-2 The following excerpt is from Chapter 13 of this publication. Tuning with Optimizer Plan Stability This chapter discusses the use of optimizer plan stability in Oracle8i and shows how you can improve the run-time performance of SQL statements and also provide a easy method to permanently change the execution plans for SQL statements. This chapter will cover the following topics: Introduction to stored outlines Preparing Oracle for stored outlines How to create and modify a stored outline Managing a stored outline Introduction to Stored Outlines The optimizer plan stability feature of Oracle8i has been a long time coming. The earlier databases such as DB2 and IDMS have had the ability to store execution plans since the 1980s, and the concept of stored SQL outlines has had widespread acceptance in the non-Oracle world for decades. The argument behind optimizer plan stability is that there exists only one optimal execution plan for any SQL statement, and once located, the execution plan should never change, even when the CBO statistics or initialization parameters are changed. Of course, this philosophy is contrary to the basic tenet of Oracle cost-based optimization, which expects SQL statements to change execution plans when the characteristics of the table and index statistics change, or when a change is made to an important initialization parameter such as sort_area_size or db_file_multiblock_read_count. Regardless of philosophy, creating a stable execution plan for Oracle SQL has two major benefits: Change execution plan without touching SQL source code Many databases have SQL that is dynamically generated (e.g., SAP) or SQL that resides in unreachable PC libraries. For these types of applications, stored outlines allow you to change the execution plan for the SQL without the need to change the SQL source code. Permanent SQL tuning changes Once tuned, optimizer plan stability allows for SQL statements to always have the same execution plan. There will be no surprises when a change is made to an important initialization parameter such as sort_area_size or when the CBO statistics change. TIP: Stored outlines are great for tuning SQL in database application suites where the SQL source is not available or cannot be changed. For example, SAP and PeopleSoft applications products can now have SQL tuning without touching the source code. Now that we see the benefits of using stored outlines, let's take a look at how optimizer plan stability works. When a SQL statement enters Oracle8i, the database will perform the following actions . . . . -Original Message-From: Aponte, Tony [mailto:[EMAIL PROTECTED]]Sent: Thursday, June 13, 2002 12:44 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Any Good , Complete Docs , Source , Links on OUTLN ? Chapter 11 in Tom Kyte's Oracle one-on-one Export (start here if you can and save yourself time) Metalink Note 92202.1 How To Specify Hidden Hints in SQL Oracle Corporation paper by David McElhoes, Stabilizing Query Performance With Stored Outlines Chapter 10 of the Designing and Tuning for Performance documentation Also, just in case you haven't already, try using "Plan Stability" in your web searches. Good luck. Tony Aponte -Original Message- From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 13, 2002 9:33 AM To: Multiple recipients of list ORACLE-L Subject: Any Good , Complete Docs , Source , Links on OUTLN ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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).
Partitioned Tables and Local vs. Global Indexes.
Hi, I have inherited a database that contains about 20 partitioned tables, many or them in the 10 to 30 million row range. All of the tables are partitioned by range. I am using Oracle 8.1.6.2 on Solaris. I have found a mixture of indexes and I an not sure of the justification of each. Why would one choose to use a. Local Partitioned Index b. Global Partitioned Index c. Local Non Partitioned Index d. Global Non Partitioned Index Thanks Jay Earle, BSc(CS) ([EMAIL PROTECTED]) DBA, Operations Group -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Veritas Quick io
Hi Kathy, Here is an excerpt from the Veritas Admin Guide. Chapter 3, Using VERITAS Quick I/O P69 Handling Oracle Temporary Tablespaces and Quick I/O You cannot convert temporary tablespaces using regular files to Quick I/O files. By default, qio_getdbfiles skips any tablespaces marked TEMPORARY because they can be sparse, which means that not all blocks in the file are allocated. Quick I/O files cannot be sparse, as Quick I/O provides a raw-type interface to storage. If a sparse file is converted to a Quick I/O file, the Oracle instance can fail if Oracle attempts to write into one of these unallocated blocks. You have the following options: You can create a new temporary tablespace using Quick I/O files. When you initially create a temporary tablespace on Quick I/O files, Oracle sees them as raw devices and does not create sparse files. You can drop your existing temporary tablespaces using regular files and recreate them using Quick I/O files. You can manually create the mkqio.dat file containing the Oracle database filenames that you want to convert to use Quick I/O, excluding Oracle tablespaces that are marked TEMPORARY from the list. Regards, Jay -Original Message- Sent: Friday, May 31, 2002 3:37 PM To: Multiple recipients of list ORACLE-L We are using Veritas Quick IO on our Solaris Box 6500 with Oracle Apps 11.5.6 on 8.1.7.2 database. Right now we do not have the temp files converted to quick io and wonder if we should. The guy who installed Quick IO didn't seen to think we could but he was a pretty junior person. Anybody here have any experience with this? Kathy Confidential This e-mail and any files transmitted with it are the property of Belkin Components and/or its affiliates, are confidential, and are intended solely for the use of the individual or entity to whom this e-mail is addressed. If you are not one of the named recipients or otherwise have reason to believe that you have received this e-mail in error, please notify the sender and delete this message immediately from your computer. Any other use, retention, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kathy Duret INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: storage parameters
command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Scheduling for Statspack
Hi, I have a question with regard to the best scheduling for Statspack. I am running Oracle 8.1.6.2 on Hp UX11. I see that the oraperf site recommends no more than intervals of (5 to 15 minutes) to tune performance problems. Does setting up a schedule with 96 or 288 snaps at level 5 put a heavy load on the system? I am still trying to get some performance problems with disk i/o and latch contention sorted out, but I also want to keep baseline statistics to monitor how well my tuning effort is proceeding. What period would you recommend setting dbms jobs to monitor a busy production system? How about hourly for the snapshots and then use a 1 or 2 hour block to keep for historical data. I plan on purging every 14 days but I want to keep something for each day. If I do this how will I be able to tune for performance problems? Any advice will be greatly appreciated Sincerely, Jay Earle, BSc(CS) OCP, ([EMAIL PROTECTED]) DBA, Operations Group -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
