Re: Suggestions on MV Implementation !!!!!!!
Hi, I got the following info on MViews from metalink. It is very good info and hope it is useful to you. Best regards, Problem Description --- You are trying to create a materialized view which refreshes automatically when the underlying table is updated. However, the create command fails with an ora-12051 or an ora-12054. The select statement that you are using could be any of the following: == create materialized view log on emp with rowid(empno, ename, job, mgr, hiredate, sal, comm, deptno) including new values / = create materialized view mv_emp_1 build immediate refresh fast on commit as select deptno, sum(sal), count(sal) from emp group by deptno / from emp * ERROR at line 5: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view = or = create materialized view mv_emp_1 build immediate refresh fast on commit as select deptno, sum(sal) from emp group by deptno / = from emp * ERROR at line 5: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view Solution Description The correct script for creation of this materialized view is as follows: == create materialized view log on emp with rowid(empno, ename, job, mgr, hiredate, sal, comm, deptno) including new values / create materialized view mv_emp_1 build immediate refresh fast on commit as select count(*), deptno, sum(sal), count(sal) from emp group by deptno / Explanation --- One of the mandatory requirements for creation of an on-commit materialized view has not been satisfied. ON-COMMIT : Refresh occurs automatically when a transaction that modified one of the materialized view's fact tables commits. Can be used with materialized views on single table aggregates and with materialized views containing joins only. As can be seen from above, an ON-COMMIT can be used only under specific cases: These are: 1. The M.V. should have a single table aggregate or 2. the M.V. should have a join only. 3. count(*) must be present for Single-Table Aggregates (see example above). 4. count() should be present. Here, stands for the column which is being aggregated. Note: the only time that count(col)is not required is when the aggregate itself is a count(col). 5. It should be possible perform a fast refresh on the materialized view. Fast refresh by itself has a few restrictions. These are as follows: a)The FROM list must contain base tables only (that is, no views). b)It cannot contain references to non-repeating expressions like SYSDATE and ROWNUM. c)It cannot contain references to RAW or LONG RAW data types. d)It cannot contain HAVING or CONNECT BY clauses. e)The WHERE clause can contain only joins and they must be equi-joins (inner or outer) and all join predicates must be connected with ANDs. No selection predicates on individual tables are allowed f)It cannot have subqueries, inline views, or set functions like UNION or MINUS. In addition for M.V.'s with Single-Table Aggregates and Materialized Views with Joins and Aggregates, there are some more conditions on refresh to the ones mentioned above: Single Table Aggregates: === i) They can only have a single table. ii) The SELECT list must contain all GROUP BY columns. iii) Expressions are allowed in the GROUP BY and SELECT clauses provided they are the same. iv) They cannot have a WHERE clause. v) They cannot have a MIN or MAX function. vi) A materialized view log must exist on the table and must contain all columns referenced in the materialized view. The log must have been created with the INCLUDING NEW VALUES clause. vii) If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr). viii) If VARIANCE(expr) or STDDEV(expr) is specified, you must have COUNT(expr) and SUM(expr). Joins and Aggregates : = i)The WHERE clause can contain inner equi-joins only (that is, no outer joins) ii)Materialized views from this category are FAST refreshable after Direct Load to the base tables; they are not FAST refreshable after conventional DML to the base tables. iii)Materialized views from this category can have only the
Re: RMAN Incremental backups.
Thank you ALL for your responses. Rachel, I quoted exactly what the documentation says. It is in oracle 8i backup and recovery training doc by oracle. It is on page 43 of 12th chapter. That is why I was confused and seeking some suggestions. Since our system is data warehouse system, we decided to run in noarchive log mode. All we care is, restoring the last backup. From the business point of view, It is okay to see the data as of last backup (that is previous day) and repopulate current day's data. We do not want to take the burden of running in archive log mode. Having said this (please see my earlier mail down below for the questions I asked), 1. Are the incremental backups are valid which are done when the database is in mount state? 2. Since I want to keep the db open while doing rman backups, I wanted to open the db in archive log mode during backups(backup including archive logs) just for the sake of backups. Do you think the backups are not good for restore. Thanks in advance for your help. Best regards, Prasad Rachel Carmichael To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: RMAN Incremental backups. Sent by: root@fatcity. com 02/06/2002 01:08 PM Please respond to ORACLE-L the documentation told you: no they are not just because the incremental backup completed does not mean that it is usable. Either leave the database in archivelog mode to do incremental backups or keep it in noarchivelog mode (and why you would not want to do that is a whole other discussion) and do full backups --- [EMAIL PROTECTED] wrote: > > > > > Hi All, > > I am new to RMAN. I am reading RMAN documentation and doing some > testing on > our development box. > > I have couple of questions. > > 1. Our database is in NOARCHIVE LOG mode and I would like to do > Incremental > backups. > Since db is in noarchive log mode, I put the database in mount > state > and did the base level > and incremental backups. Looks like it worked fine. > > In one of the training documentation, it says 'Because the > database is > in noarchive log mode, the incrementals are > not applicable, so use the full backup option'. > > It is contrary to what I did. Am I missing something here. > please > clarify me. Are the backups valid? > > > 2. Since the database is in noarchive log mode, db is in mount state > during > full/incremental backup. > I want to keep the database open if possible and would like to > know if > the following procedure works to > keep the database open during the backup. > >1. Shutdown the db before backup. >2. Bring back the db in ARCHIVE LOG mode. >3. Perform full/incremental backups (including archive log files) > while > the database is open. >4. Shutdown the db after backup is done. >5. Bring back the db in NOARCHIVE LOG mode. > >I am not sure whether it makes sense or not. Please pass your > suggestions. > > Thanks in advance for your suggestions. > Best regards, > Prasad > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing > Lists > ---
RMAN Incremental backups.
Hi All, I am new to RMAN. I am reading RMAN documentation and doing some testing on our development box. I have couple of questions. 1. Our database is in NOARCHIVE LOG mode and I would like to do Incremental backups. Since db is in noarchive log mode, I put the database in mount state and did the base level and incremental backups. Looks like it worked fine. In one of the training documentation, it says 'Because the database is in noarchive log mode, the incrementals are not applicable, so use the full backup option'. It is contrary to what I did. Am I missing something here. please clarify me. Are the backups valid? 2. Since the database is in noarchive log mode, db is in mount state during full/incremental backup. I want to keep the database open if possible and would like to know if the following procedure works to keep the database open during the backup. 1. Shutdown the db before backup. 2. Bring back the db in ARCHIVE LOG mode. 3. Perform full/incremental backups (including archive log files) while the database is open. 4. Shutdown the db after backup is done. 5. Bring back the db in NOARCHIVE LOG mode. I am not sure whether it makes sense or not. Please pass your suggestions. Thanks in advance for your suggestions. Best regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Question on dba_ts_quotas
Thanks Jared and others who replied. Best regards, Prasad Jared.Still@r adisys.com To: [EMAIL PROTECTED] cc: [EMAIL PROTECTED] 12/03/2001 Subject: Re: Question on dba_ts_quotas 07:18 PM If you check DBA_SYS_PRIVS you will likely find that the user has 'UNLIMITED TABLESPACE'. Probably due to granting RESOURCE to the user. Jared Prasada.Gunda1@hartfo rdlife.com To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by:cc: [EMAIL PROTECTED]Subject: Question on dba_ts_quotas 12/03/01 02:56 PM Please respond to ORACLE-L Hi All, I have a question on dba_ts_quotas. User called QUOTE has objects in TS_QUOTE_DATA and TS_QUOTE_INDEX tablespaces. And, I expect that the relation/quota would show up in dba_ts_quotas. I don't see any records in dba_ts_quotas for this schema. I checked for other schemas and they showed up. Just for testing, I created a dummy table to test it and it got created without any problems. Why is it not showing up in dba_ts_quotas. Am I missing something here? BTW, I am using SYSTEM to query these views. Thanks in advance for your help. SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER LIKE 'QUOTE'; TABLESPACE_NAME -- TS_QUOTE_DATA TS_QUOTE_INDEX SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME LIKE 'QUOTE'; no rows selected CREATE TABLE TEST1 (DUMMY NUMBER) TABLESPACE TS_QUOTE_DATA; Table created. Best regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: 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).
Question on dba_ts_quotas
Hi All, I have a question on dba_ts_quotas. User called QUOTE has objects in TS_QUOTE_DATA and TS_QUOTE_INDEX tablespaces. And, I expect that the relation/quota would show up in dba_ts_quotas. I don't see any records in dba_ts_quotas for this schema. I checked for other schemas and they showed up. Just for testing, I created a dummy table to test it and it got created without any problems. Why is it not showing up in dba_ts_quotas. Am I missing something here? BTW, I am using SYSTEM to query these views. Thanks in advance for your help. SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER LIKE 'QUOTE'; TABLESPACE_NAME -- TS_QUOTE_DATA TS_QUOTE_INDEX SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME LIKE 'QUOTE'; no rows selected CREATE TABLE TEST1 (DUMMY NUMBER) TABLESPACE TS_QUOTE_DATA; Table created. Best regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: 8i or 9i for Data Warehouse
Very good white papers/technical articles related to DW enhancements/new features in 9i are available on Oracle Technet. They are pretty good. Please take a look at those and see whether you can use those in your DW project. Hope this helps. Regards YTTRI Lisa nh.com> cc: Sent by: Subject: 8i or 9i for Data Warehouse root@fatcity. com 10/30/2001 03:05 PM Please respond to ORACLE-L Hi everyone - I have been asked to do the database planning for a fairly large data warehouse. The warehouse is currently in Sybase and is about 160 GB. They are planning to move to Oracle very soon. My questions - does it make more sense to spin it up on 8.1.7 or 9? (This will be a pretty good size Solaris machine). Are there newer features in 9i that would really benefit them? I am looking at partitioning tables and using some bit-mapped indexes - something they don't have today. I was originally looking at 8.1.7, but now that 9 has been around for a little bit, I'm wondering if any of you have some advice (good or bad) regarding version 9. Any and all comments are appreciated. Thanks - lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa 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: 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: Re: Export Hanging
I did find it in SQLab (It is by Quest). There was sql running for hours on this table. Then, I did whatever I mentioned in my earlier mail. Just to let you know, the table that we had problem wasn't the same table other people reported in metalink. Hope this helps. Prasad "Mandar Shete" times.com> cc: Sent by: Subject: Re: Re: Export Hanging [EMAIL PROTECTED] 10/22/2001 03:22 PM Please respond to ORACLE-L --=_MAILER_ATTACH_BOUNDARY1_2001102212345401867107722 Content-Type: text/plain; charset=us-ascii Thanks Prasad. I have looked in Metalink but could not find anything that relates exactly to my situation - as I mentioned before, I can't see any non-idle wait in either v$session_wait or v$session_event. But I'll try tracing the session to see if that helps (though I still can't figure out why I can't see the session waiting on anything). If, as you said, the session was hanging while trying to read a table, wouldn't I see at least some db file reads ? In your case how did did you find that the session was waiting on that particular event ? Mandar. [EMAIL PROTECTED] wrote: Hi Mandar, We had similar problem when we did full db export. What we found was, export process was hanging when it was reading one of the system table (in our case, it is SYS.EXU8REFIC). When we saw the execution plan, it is using ALL_ROWS (there are no stats on sys objects but still it is doing ALL_ROWS). We recreated the view with RULE hint and export worked without any problems. I also did research in metalink and I found that the same solution was given to similar questions by oracle tech support. If you have access to metalink, please search with 'export hung' and you will get related information. hope this helps. Prasad "Mandar Shete" times.com> cc: Sent by: Subject: Export Hanging [EMAIL PROTECTED] 10/22/2001 09:50 AM Please respond to ORACLE-L --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/plain; charset=us-ascii Hi All, Oracle 8.1.6.1 NT 4 SP5 I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS 63 latch free 2 2 63 db file sequential read 178 0 63 db file scattered read 6195 0 63 file open 3 0 63 SQL*Net message to client 244859 0 63 SQL*Net more data to client 149282 0 63 SQL*Net message from client 244858 0 I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriously since we don't take regular exports. I'm about to raise a TAR on this one, but any pointers before that would be useful. TIA, Mandar. Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/html; charset=us-ascii Hi All, Oracle 8.1.6.1 NT 4 SP5 I'm facing a problem with all exports hanging on our production database. Tried it several times, but the e
Re: Export Hanging
Hi Mandar, We had similar problem when we did full db export. What we found was, export process was hanging when it was reading one of the system table (in our case, it is SYS.EXU8REFIC). When we saw the execution plan, it is using ALL_ROWS (there are no stats on sys objects but still it is doing ALL_ROWS). We recreated the view with RULE hint and export worked without any problems. I also did research in metalink and I found that the same solution was given to similar questions by oracle tech support. If you have access to metalink, please search with 'export hung' and you will get related information. hope this helps. Prasad "Mandar Shete" times.com> cc: Sent by: Subject: Export Hanging [EMAIL PROTECTED] 10/22/2001 09:50 AM Please respond to ORACLE-L --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/plain; charset=us-ascii Hi All, Oracle 8.1.6.1 NT 4 SP5 I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS 63 latch free2 2 63 db file sequential read 178 0 63 db file scattered read 6195 0 63 file open 3 0 63 SQL*Net message to client 244859 0 63 SQL*Net more data to client149282 0 63 SQL*Net message from client 244858 0 I'm running this on the server itself, so the network's not an issue. Tried different export modes and parameters (full, owner, tables, buffer, direct, etc.) but with the same result. I'm pretty new to this site, but I'm told that this problem's been there for some time now but was never really taken seriously since we don't take regular exports. I'm about to raise a TAR on this one, but any pointers before that would be useful. TIA, Mandar. Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com Buy Music, Video, CD-ROM, Audio-Books and Music Accessories from http://www.planetm.co.in --=_MAILER_ATTACH_BOUNDARY1_200110221181548701943705 Content-Type: text/html; charset=us-ascii Hi All, Oracle 8.1.6.1 NT 4 SP5 I'm facing a problem with all exports hanging on our production database. Tried it several times, but the export seems to get stuck at different points each time. The strange part is that at this point v$session_event shows absolutely nothing happening - all the wait numbers just freeze indefinitely and v$session_wait shows a 'SQL*Net message from client' wait - the session seems to be doing nothing at all. This is the output from v$session_event for my latest try SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS63 latch free 2 263 db file sequential read 178 063 &nb! ! ! ! sp;db file scattered read 6195 0 63 file open 3 063 SQL*Net message to client 244859 0 63 SQL*Net m
Re: Renaming GLOBAL_NAME
Thank you all for your replies. I bounced the database and still it has the same problem. Regards, Prasad "Rachel Carmichael" To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: Renaming GLOBAL_NAME Sent by: root@fatcity. com 09/17/2001 03:30 PM Please respond to ORACLE-L you need to reboot your database, changing the init.ora parameter to global_name to false: SQL> select value from v$parameter where name like 'global_name%'; VALUE -- FALSE SQL> select * from global_name; GLOBAL_NAME -- UREGPROD >From: Paul Baumgartel <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: Renaming GLOBAL_NAME >Date: Mon, 17 Sep 2001 10:05:20 -0800 > >I don't think you can do this. GLOBAL_NAME by definition consists of >the database name and database domain, which defaults to WORLD. > >Paul Baumgartel > >--- [EMAIL PROTECTED] wrote: > > > > Hi All, > > > > I am trying to rename the global_name from SIMSNT7B.WORLD to > > SIMSNT7B. > > > > I issued the following command to change the global_name. > > > > ALTER DATABASE RENAME GLOBAL_NAME TO SIMSNT7B; > > > > Still it is showing SIMSNT7B.WORLD when I query the global_name view. > > SQL> select * from global_name; > > > > GLOBAL_NAME > > -- > > SIMSNT7B.WORLD > > > > Values for the following parameters in v$parameter view, > > GLOBAL_NAMES parameter value is FALSE and DB_DOMAIN parameter value > > is > > NULL. > > We are not using Oracle Names server. I renamed SQLNET.ORA file in > > network\admin location. > > Init.ora file has global_names set to false. > > > > How do I change to SIMSNT7B? This database is on Windows NT and it > > is > > 8.1.6. > > > > Thanks in advance for your help. > > > > Best regards > > Prasad > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > 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). > > >__ >Terrorist Attacks on U.S. - How can you help? >Donate cash, emergency relief information >http://dailynews.yahoo.com/fc/US/Emergency_Information/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Paul Baumgartel > 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') an
Renaming GLOBAL_NAME
Hi All, I am trying to rename the global_name from SIMSNT7B.WORLD to SIMSNT7B. I issued the following command to change the global_name. ALTER DATABASE RENAME GLOBAL_NAME TO SIMSNT7B; Still it is showing SIMSNT7B.WORLD when I query the global_name view. SQL> select * from global_name; GLOBAL_NAME -- SIMSNT7B.WORLD Values for the following parameters in v$parameter view, GLOBAL_NAMES parameter value is FALSE and DB_DOMAIN parameter value is NULL. We are not using Oracle Names server. I renamed SQLNET.ORA file in network\admin location. Init.ora file has global_names set to false. How do I change to SIMSNT7B? This database is on Windows NT and it is 8.1.6. Thanks in advance for your help. Best regards Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: bitmap indexes and rule based optimizer
rbo does not support bitmap indexes. Alternate way (in case if you don't want to use alter session to change the optimizer) to make use of the bitmap index is, If you provide index hint with bitmap index in the SQL statement, It invokes cbo and the bitmap index would be used for that particular query execution. We had the same requirement when we were using v7.3 hth prasad "Hillman, Alex" treas.gov> cc: Sent by: Subject: RE: bitmap indexes and rule based optimizer [EMAIL PROTECTED] 09/12/2001 04:15 PM Please respond to ORACLE-L Thanks. However bitmap indexes are 7.3+ feature - why I asked. Alex Hillman -Original Message- Sent: Wednesday, September 12, 2001 1:50 PM To: Multiple recipients of list ORACLE-L Alex: No, the rbo does not support bitmap indexes or any other Oracle8 or greater feature (such as partitioning, etc.). To use these features, you must set optimizer_mode to CHOOSE, FIRST_ROWS or ALL_ROWS, which will invoke the cbo. Jon Walthour -Original Message- Sent: Wednesday, September 12, 2001 1:05 PM To: Multiple recipients of list ORACLE-L Anybody knows whether rule based optimizer can use bitmap indexes? Alex Hillman -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex 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: Walthour, Jon (GEAE, Compaq) 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: Hillman, Alex 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: 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 li
Re: PL/SQL Question after migrating from 7.3.4 to 8.1.7
Use %ROWTYPE. TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%ROWTYPE INDEX BY BINARY_INTEGER; hth, prasad "Deen Dayal" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L ate.nj.us><[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED]Subject: PL/SQL Question after migrating from om7.3.4 to 8.1.7 07/27/2001 01:45 PM Please respond to ORACLE-L Hi, I just migrated from 734 to 817, I used migration utility. Migration went fine. Some of my package specification have become invalid. When I tried to recompile, I got the following error 30/41PLS-00206: %TYPE must be applied to a variable, column, field or attribute, not to "UC9_CLAIM_AMOUNT" The source is listed below. uc9_correspondence is table in my schema. I do not know why this is throwing up an error where as 734 was happy with it. TYPE tb_uc9_corres_type IS TABLE OF uc9_correspondence%TYPE INDEX BY BINARY_INTEGER; Any help is appreciated Thanks in ADvance deen -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deen Dayal 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: 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).
Strange behavior: Update with select
Strange behavior : Update with select stt. select stt in update 1 is NOT working correct and select stt in update 2 is working correct though the select statements are logically same. Explanation: select stt in update 1 doesn't return any data even though it suppose to return one record. To confirm that the select stt returns one record, I prepared select stt by joining the table(claim) that is updated with the tables used in select stt(pls see select 1). It is returning one record. Then, I modified the select stt (pls see update 2) in update stt and it is working fine. The difference between these two select statements is, In update 1 select stt, table r is joining with p and r joining with p615. In update 2 select stt, table r is joing with p and p is joining with p615. The way the data is: join with r and p yields 1 record and join with r and p615 yields no record. explain plan on update 1: sort join and then merge join cartesian. explain plan on update 2: nested loops outer join. explain plan on select 1: nested loops outer join. I belive, update 1 is not working correct because the second join (r and p615) resulting no data hence the merge join cartesian resulting with no data. Even though optimizer generates different plans for update 1 and 2, final result should be the same. FYI, In the select statement, same table is being used with different aliases (p and p615). Your thoughts are appreciated. It is very interesting, at least for me. Thanks prasad Update 1: UPDATE claim r SET disability_definition = (SELECT p.option_description || ' ' || RTRIM(p.description) || DECODE(p.duration_code, 'Y',' YEAR(S)', 'M',' MONTH(S)', 'D',' DAY(S)', 'C',' CAL.YEAR', 'L',' LIFE TIME', 'A',' YEARS OF AGE', 'W',' WEEK(S)',NULL) || ' ' || p615.option_description || ' ' || RTRIM(p615.description) || DECODE(p615.duration_code, 'Y',' YEAR(S)', 'M',' MONTH(S)', 'D',' DAY(S)', 'C',' CAL.YEAR', 'L',' LIFE TIME', 'A',' YEARS OF AGE', 'W',' WEEK(S)',NULL) FROM coverage_provision p, coverage_provision p615 WHERE p.provision_id = '614' AND r.case_id = p.case_id AND r.coverage_category_code = p.coverage_category_code AND r.coverage_type_code = p.coverage_type_code AND r.coverage_plan_number = p.coverage_plan_number AND r.class_code = p.class_code AND r.disability_date >= p.eff_date AND r.disability_date < NVL(p.term_date, r.disability_date+1) AND r.case_id = p615.case_id(+) AND r.coverage_category_code = p615.coverage_category_code(+) AND r.coverage_type_code = p615.coverage_type_code(+) AND r.coverage_plan_number = p615.coverage_plan_number(+) AND r.class_code = p615.class_code(+) AND r.disability_date >= p615.eff_date(+) AND r.disability_date < NVL(p615.term_date(+), r.disability_date+1) AND p615.provision_id(+) = '615' ) WHERE r.coverage_category_code = 'LTD' AND r.coverage_type_code = 'ABIL' AND NVL(r.coverage_plan_number,'UNK') <> 'UNK' AND NVL(r.class_code,'UNK') <> 'UNK' AND r.claim_event_id='999' ; Select 1: SELECT p.option_description || ' ' || RTRIM(p.description) || DECODE(p.duration_code, 'Y',' YEAR(S)', 'M',' MONTH(S)', 'D',' DAY(S)', 'C',' CAL.YEAR', 'L',' LIFE TIME', 'A',' YEARS OF AGE', 'W',' WEEK(S)',NULL) || ' ' || p615.option_description || ' ' || RTRIM(p615.description) || DECODE(p615.duration_code, 'Y',' YEAR(S)', 'M',' MONTH(S)', 'D',' DAY(S)', 'C',' CAL.YEAR', 'L',' LIFE TIME', 'A',' YEARS OF AGE', 'W',' WEEK(S)',NULL) FROM claim r, coverage_provision p, coverage_provision p615 WHERE p.provision_id = '614' AND p615.provision_id(+) = '615' AND r.case_id = p.case_id AND r.coverage
Re: any way to stop rollback for dml?/8.1.7.1/Solaris 8
Steve, Even we do have the same kind of requirement. AFAIK, there is no way except INSERT with APPEND hint (Direct Load Insert). See Direct-load Insert chapter in concepts manual. Unfortunately Direct load insert works with Insert .. select, not Insert .. values syntax. hth, prasad "Austin, Steve S" To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]> cc: Sent by: Subject: any way to stop rollback for root@fatcity.dml?/8.1.7.1/Solaris 8 com 07/27/2001 03:02 PM Please respond to ORACLE-L Does anyone know of a way to inhibit rollback from being generated for DML? We've got a data warehouse load process that we're trying to speed up (involving sqlldr and then some DML afterwards.) The staging tables it uses are entirely for this process -- there's no need to rollback if it fails; we'd truncate them and start again with that set of data. Aside from sqlldr direct mode and import direct mode, can anyone think of ways to do this? Thanks, Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Austin, Steve S 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: 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: both dbms_stats & dbms_utility are gathering stats on sys objects
Hi Jon,
Thanks for your reply. I am aware of this option and I am currently doing
the same way(i.e.,analyze database and delete SYS schema stats).
But, I was wondering which release of 8.1.7 fixed this problem since Oracle
claims that it is fixed in 8.1.7.
Excerpt from oracle tech support mail:
filed bug 969814 against DBMS_UTILITY.ANALYZE_DATABASE which was not
excluding these tables. This bug is fixed in 8.1.7.
Thanks
Prasad
"Jon
Walthour"To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]<[EMAIL PROTECTED]>
t> cc:
Sent by: Subject: Re: both dbms_stats &
dbms_utility are
root@fatcity.gathering stats on sys objects
com
07/24/2001
08:50 PM
Please
respond to
ORACLE-L
Well, first of all, you could use dbms_utility.analyze_schema() and analyze
all the schemas except SYS ... or couldn't you run
dbms_utility.analyze_database() and then
dbms_utility.analyze_schema('SYS','DELETE') to remove SYS's stats. What
about one of those?
Jon Walthour
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, July 24, 2001 3:55 PM
>
> Both DBMS_STATS.GATHER_DATABASE_STATS and DBMS_UTILITY.ANALYZE_DATABASE
are
> gathering statistics on sys objects. As per oracle, we shouldn't analyze
> the objects owned by sys.
>
> When I searched on metalink, I found the following information provided
by
> oracle tech support.
>
> filed bug 969814 against DBMS_UTILITY.ANALYZE_DATABASE which was not
> excluding these tables.
> This bug is fixed in 8.1.7.
>
> I did my tests on 8.1.7 database on hp-ux. Apparently it is not fixed on
> 8.1.7.0.0.
>
> Is it fixed in later releases? I appreciate your comments.
>
> Best regards,
> Prasad
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> 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: Jon Walthour
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:
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 f
both dbms_stats & dbms_utility are gathering stats on sys objects
Both DBMS_STATS.GATHER_DATABASE_STATS and DBMS_UTILITY.ANALYZE_DATABASE are gathering statistics on sys objects. As per oracle, we shouldn't analyze the objects owned by sys. When I searched on metalink, I found the following information provided by oracle tech support. filed bug 969814 against DBMS_UTILITY.ANALYZE_DATABASE which was not excluding these tables. This bug is fixed in 8.1.7. I did my tests on 8.1.7 database on hp-ux. Apparently it is not fixed on 8.1.7.0.0. Is it fixed in later releases? I appreciate your comments. Best regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
oracle-l@fatcity.com
We noticed a different behavior with too_many_rows exception in v7 and v8i.
Is this a bug or that's the way it should work in 8i?
I really appreciate your feedback on this.
Thanks & Regards,
Prasad
declare
v_table_nameuser_tables.table_name%TYPE;
begin
v_table_name := null;
select table_name
intov_table_name
fromuser_tables;
dbms_output.put_line('v_table_name : '||nvl(v_table_name,'null'));
exception
when too_many_rows then
dbms_output.put_line('too_many_rows exception, v_table_name : '
||nvl(v_table_name,'null'));
when others then
dbms_output.put_line(sqlerrm);
end;
/
In v7.3, It keeps value remains null in v_table_name column and v8i it is
assigning the first received value.
In both cases, It is raising too_many_rows exception.
Output in v7.3:
too_many_rows exception, v_table_name : null
PL/SQL procedure successfully completed.
Output in v8.1.7, v8.1.6, v8.1.5
too_many_rows exception, v_table_name : CHAMPION_LIFE_DGNSS_CATEGORY
PL/SQL procedure successfully completed.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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: OEM Book Recommendation
Technical libraries section under metalink has white papers, manuals etc on OEM packs. hth, Prasad Christopher Spence <[EMAIL PROTECTED]>@fatcity.com on 05/30/2001 04:21:20 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Technet if i remember has some good things on OEM. I generally never use it. Although it does have some nice features. But I remember seeing some good docs on it. "Walking on water and developing software from a specification are easy if both are frozen." Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Wednesday, May 30, 2001 2:16 PM To: Multiple recipients of list ORACLE-L DBAs, As a keyboard lover, hate to do this. But I need to study OEM. Any good Books / URLs / DOCs / PDFs Thanks in advance, -Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul Dandekar 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: Christopher Spence 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: 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: looking for export with dynamic compress script on VMS
Thanks to all of you who responded to my question. Best Regards, Prasad "Jesse, Rich" <[EMAIL PROTECTED]> on 05/08/2001 03:56:49 PM To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> cc: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Prasad, If you are on v7+ of OpenVMS (or is it v7.1+?), you can use the DCL PIPE command to emulate Unix-style pipes. Although I haven't tried it, it may produce something similar. For example, instead of the traditional DCL: $ DEFINE/USER SYS$OUTPUT T.T $ SHOW SYSTEM $ SEARCH T.T ORA ...which is similar to the unix "ps -ef|grep ORA" command, you can shorten it up to: $ PIPE SHOW SYSTEM | SEARCH SYS$PIPE ORA You may also want to investigate GNUZip for OpenVMS. http://www.openvms.digital.com/freeware/FREEWARE50/000TOOLS/ should contain a directory called "ALPHA_IMAGES" (8i will not run on VAXen, just Alphas) which will contain a GZIP.EXE file for ya. Yes, I'm aware that the link will forward you to Compaq's site. Just in case Ken Olsen is watching from Somewhere Above... :) HTH! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, May 08, 2001 14:56 > To: Multiple recipients of list ORACLE-L > Subject: looking for export with dynamic compress script on VMS > > > Hi VMS & DBA gurus, > > As part of migration process from Oracle v7 to v8i, we are > trying to do the > complete export of our database. Due to the lack of disk > space, we can not > land such a big export file. > > Our v7 database is on VMS platform and I am looking for the > VMS script, > which does dynamic compression (like the way pipes work on > Unix systems) > while export is running. > > If any one has such script and would like share it, please > post it on the > list. > > I really appreciate for your help. > > Thanks in advance, > > Best Regards, > Prasad > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
looking for export with dynamic compress script on VMS
Hi VMS & DBA gurus, As part of migration process from Oracle v7 to v8i, we are trying to do the complete export of our database. Due to the lack of disk space, we can not land such a big export file. Our v7 database is on VMS platform and I am looking for the VMS script, which does dynamic compression (like the way pipes work on Unix systems) while export is running. If any one has such script and would like share it, please post it on the list. I really appreciate for your help. Thanks in advance, Best Regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Response times with different multiblock read count
Hi All, I am running a test query with different db_file_multiblock_read_count to test the overall throughput. environment: oracle 8.1.6 on hp-ux v11 db_block_size : 16k system is using LVM and file system is using buffered I/O and disk is mirrored but not stripped. I was expecting that having larger multiblock count would result in better performance. But, when I was testing the query with smaller multiblock count,the response time is better. Here is the testing result. Multiblock time in Secs -- 1 12.5 better 2 12.5 better 4 12.6 better 8 27 bad 16 27 bad 32 18.6 okay 64 19.0 okay I don't understand why 8,16 are taking longer time. I did make sure that oracle is issuing proper multiblock read count with the help of multiblock_read_test.sql which is available in Steve Adams's site. Thank you steve for providing valuable information & scripts on your web site. select statement that I am using is, select /*+ full(t) noparallel(t) nocache(t) */ count(*) from &Table t; I am clueless why it is behaving like this. Please pass your suggestions. If you need more info like oracle/OS settings etc, please let me know. When I was doing some reading on steve adam's site, he suggests the following. Date: 29-Dec-2000 20:55 Nevertheless, it remains best to allow Oracle to use large multiblock reads, as long as the optimizer is not doing inappropriate full scans. The explanation relates to the system call and context switch overheads associated with I/O. It is cheaper in CPU usage terms to request a 256K I/O and allow the operating system to service it in 64K chunks, than to request 4 I/O's of 64K each. As is mentioned in the tip on Why Large Multiblock Reads, the increased latency is not an issue because multiblock reads should not be used in cases where "first rows" response time is critical. Thanks in advance. Best Regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Max phy I/O size on Hp-UX
This question regarding max physical I/O size for hp-ux v11. When we were talking to unix sys admin, she searched in HP knowledge base and found that 256k is the maximum physical I/O for hp-ux. When I was looking at Q&A on Steve Adams's web site, the answer is 1M for max physical i/o size for hp-ux. Steve, Could you please let me know if I am missing some thing here. >From Steve Adam's Web site: Maximum I/O size 4 January 2000 What is the maximum I/O size (MAXPHYS) nowadays on HP-UX and Solaris? MAXPHYS has long been fixed at 64K on Solaris, and 256K on HP-UX. However, from Solaris 2.6 it is defined in /etc/system and defaults to 128K. On HP-UX 11 it now defaults to 1M. However, I think the LVM layer still constrains I/O operations to a single logical track group, which is 256K, so the higher MAXPHYS only applies if you are not using LVM, which is most unusual. Of course, these large physical I/O sizes are only possible if you are using raw or direct I/O. Perhaps more importantly, there is an internal Oracle kernel constant (SSTIOMAX) that limits I/O operations to 512K. >From Knowledge base on HP Web site What is MAXPHYS for HP-UX systems? DocId: KBRC3216 Updated: 7/31/00 7:37:00 AM PROBLEM What is the the maximum size of a single I/O (or MAXPHYS) that can be issued to a device on an HP-UX system? RESOLUTION MAXPHYS is not a tunable on HP-UX systems but it defaults to 256KB. This can be seen in one of the system header files: 1. vi /usr/include/machine/param.h 2. Search for MAXPHYS This should return: #define MAXPHYS (256 * 1024)/* Maximum size of physical I/O transfer */ So MAXPHYS is 256KB. This is not tunable to larger value due to driver restrictions. Thanks in advance for all your comments. Regards, Prasad -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
