Re: [Q] wait time /lob def
Tanel, Oracle 9-2 SQL Ref manual. Lob storage clause. The options for lobs on creation are: CACHE NO CACHE CACHE READS I don't think the last one appeared until 9.2 I was wrong about caching only writes, though - one of the joys of trying to quote everything from memory. The CACHE READS option means that the LOB goes into the buffer cache for reads, but not for writes. Pity, really, because I'd quite like to see it the other way around. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 30, 2004 12:19 AM Jonathan, about which version are you talking here? (You do also have the option in more recent versions of refining the caching properties so the LOB can be readcache only, writecache only or read/write cache or nocache, I believe). I haven't found a way to explicitly set read or write caching for LOBs in system level, although I've done some research on them lately (on 10g as well). Or are you talking about OCI LOB caching here? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: [Q] wait time /lob def
It's just one example of my general suggestion that messing about with block sizes rarely has any direct performance benefit. But if you can put something out of the way where it can't do so much damage then the performance of everything else might benefit. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 11:04 PM That is an interesting use of an alternate block size Jonathan. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: [Q] wait time on stat
Do you have any LOBs defined with the nocache nologging attribute ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 8:09 PM WE have ORACLE 9.2.0.3 database run under Win2000. I run statspack and found Top 5 timed event. Look like control file parallel write and redo file parallel write take lots time. How to fix? Top 5 Timed Events ~~ % Total Event WaitsTime (s) Ela Time --- control file parallel write 5,499 1,14190.37 CPU time 97 7.70 log file parallel write 1,305 12 .95 db file parallel write 162 6 .44 log file switch completion 10 2 .16 - Wait Events for DB: 9IDEV Instance: 9idev Snaps: 5 -6 - s - second - cs - centisecond - 100th of a second - ms - millisecond -1000th of a second - us - microsecond - 100th of a second - ordered by wait time desc, waits desc (idle events last) Avg Total Wait waitWaits Event Waits Timeouts Time (s) (ms) /txn -- -- -- control file parallel write 5,499 0 1,141207211.5 log file parallel write 1,305 1,299 12 9 50.2 db file parallel write162 0 6 34 6.2 log file switch completion 10 0 2204 0.4 control file sequential read3,827 0 2 0147.2 db file sequential read 176 0 1 7 6.8 direct path write 92 0 1 6 3.5 log file sync 14 0 0 33 0.5 log file single write 20 0 0 13 0.8 log file sequential read 35 0 0 6 1.3 direct path read 92 0 0 2 3.5 SQL*Net break/reset to clien 44 0 0 0 1.7 SQL*Net more data to client 7 0 0 0 0.3 async disk IO 4 0 0 0 0.2 virtual circuit status 6,826496 19,650 2879262.5 wakeup time manager 530530 19,179 36187 20.4 SQL*Net message from client 6,457 0 12,084 1871248.3 jobq slave wait 402381 1,227 3051 15.5 SQL*Net message to client 6,458 0 0 0248.4 - Background Wait Events for DB: 9IDEV Instance: 9idev Snaps: 5 -6 - ordered by wait time desc, waits desc (idle events last) __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: dba1 mcc 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
Re: [Q] wait time /lob def
There is a note in one of the manuals about nologging lobs (or nocache lob, I forget which) that points out that the unrecoverable SCN for file that holds the LOB has to be updated in the control file whenever the LOB is updated. If you actually have a performance problem because of this - i.e. if lots of people/processes are running slowly because they are waiting on control file writes - then you might want to make the LOB cache/loggong. But control file writes are not inherently a bad thing to be blocked. Of course, if the LOBs are quite large, then the time taken to write the LOB may be far greater than the time taken to update the controlfile - which would make any concerns about the controlfile update irrelevant. So there is no 'obvious' correct answer to your question, without examing your current activity. The note (which I think Steve Adams' also has on his website) mentions an event that can be set to stop the controlfile update when the LOB is updated. This may not be a good idea, though, as it may affect some aspects of recoverability. If you do make the LOB 'cached', then remember that all reads and writes go through the db_block_buffer, which could affect all the other I/O activity adversely, so you might want to consider putting the LOBs into a tablespace with a non-standard block size so that the LOB activity doesn't affect the rest of the cache. (You do also have the option in more recent versions of refining the caching properties so the LOB can be readcache only, writecache only or read/write cache or nocache, I believe). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 5:04 PM Jonathan / Tanel: I, however, AM having this problem. Didn't know where to look till I saw this message. (I love this list!) I've yet to capture the sid (and therefore track back to the table) where the 'direct path write' occurs. Definitions for the tables were supplied by the vendor. When I look at at the lobs, the definitions are mixed. Most are nocache, logging yes. some are no/no I don't see much on metalink about this -- just a couple of generic articles on lob storage. Should I change the lobs to cache/logging across the board? Thanks for any insight. Barb -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: merge command ???
Can't do it (until version 10) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February UK___June The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 4:54 PM Hi, for Merge command, how to specify if matched, not to do anything, I tried NULL, not working. Thank you! MERGE INTO caption c3 USING [EMAIL PROTECTED] c1 ON (c3.caption_id = c1.caption_id) WHEN MATCHED THEN NULL -- don't need to do anything when matched! WHEN NOT MATCHED THEN INSERT (c3.CAPTION_ID, c3.CAPTION_NAME, c3.VISIBILITY_ID, c3.MOD_DATE, c3.MOD_USER) VALUES (c1.CAPTION_ID, c1.CAPTION_NAME, c1.VISIBILITY_ID, c1.MOD_DATE, c1.MOD_USER); __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy 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.net -- Author: Jonathan Lewis 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: Unusable partition index -- working funny
I would check which index is being reported as unusable, and check the access path for the query when all indexes are useable. Since you have a statement level trigger, I suspect Oracle is producing an execution plan that dictate the use of index X. The plan executes, which means the trigger fires, but the execution engine is committed to using index X - which happens to be unusable, so the statement fails. On the second call, the session parameters have changed, so Oracle re-parses the update, and ignores the unusable index, choosing a different plan. Consequently the update can work. In the case where the index being used to access the data is useable (i.e. where only the index on the updated column is unusable), I would assume that Oracle makes the decision about updating indexes only after columns have been updated, therefore it can notice the effect of session switch in mid-statement. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 12:19 AM Thanks for your reply Jonathan..Here is an update.. The update that i sent you yesterday is updating a column on which there is a local bitmap index. There are also other local bitmsap indexes on that partitions. Yesterday i made all the local indexes pertaining to that partition UNUSABLE and we got the results that i posted yesterday..Today i went and made all the indexes usable and then made only the local index on the column which we are updating unusable while the rest of the local bitmap indexes were usable. and then the update stmt was run. There was NO problem at all. It ran the first time without giving the error of index being in the unusable state. That nmeans the trigger has fired. So what would be the explanation in this case. If i make only that local bitmap index unusable, it works ok but if i make all the local bitmap indexes unusable in that partition, we get the situation that i posted yesterday.. Thanks, Sathish. SQL connect [EMAIL PROTECTED] Enter password: ** Connected. SQL UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1 where 2 mortgage_loan_key = 1 and period_key = '30-JUN-03'; UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1166444 where * ERROR at line 1: ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of such index is in unusable state SQL / 1 row updated. - On Wed, 21 Jan 2004 00:24:25 -0800, Jonathan Lewis [EMAIL PROTECTED] said: It's probably the case that the trigger fires the first time - but at parse/optimise time Oracle had already determined the sequence of actions needed to execute the statement based on the then session state, so that sequence is played out, irrespective of the fact that you changed the session state in the middle of the sequence. By analogy, consider an update to an updatable join view which defaults to using a hash join. If you create a before row update trigger to disable hash joins, would you expect Oracle to not do a hash join the first time the statement executes ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 12:19 AM Hello All, I have a strange problem... I have a table on which i am doing an update. Its a partition table and the local index on the column which is being updated is in an unusable state. I have a database trigger at statement level (before update of col_a for ) where i do an execute immediate ' alter session set skip_unusable_indexes = true
Re: fast commit
Note in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 6:29 AM Hi list, 1)Why fast commit generate no redo ? It's called a fast commit BECAUSE it doesn't generate redo (except for a tiny bit that describes the change to the transaction table entry in the segment header block that marked the transaction as active). It doesn't need to generate redo because it's going to leave (most of) the lock and change information on the blocks that have been changed, and let some other visiter to the blocks clean up the mess. 2)Is delayed cleanout generate redo? Delayed block cleanout - where a later operation simply READS a messy block and cleans it up (by referring back to the transaction table to get the necessary commit details) will generate redo. Delayed-logging block cleanout - which occurs when the first transactions cleans out a few of the blocks it has dirtied but does not log the cleanout - is effectively not going to generate redo, as the next transaction to MODIFY the date will generate some undo which looks as if it started from a clean block, rather than the partly dirty block that is really there - so the cleanout is effectively free. 3)In a block dump even after transactions commit why it shows lock 1 in ITL? Because Oracle doesn't clean the block out properly, it will either not revisit it at all (1), or just revisit the ITL and a couple of header bytes (2). Thanks in advance. Syed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Oracle 8.1.7 can only use the first 15th indexes?
Notes in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - From: Kaing, Leng [EMAIL PROTECTED] Brilliant example. Thanks very much for this. (And yes, I meant the first 15th for each index, not the first 15th for the entire database!) I had to ask, as once upon a time people used to say that Oracle was limited to using a maximum of 5 indexes in any one query. (Misunderstanding the manuals comments about the maximum number of indexes that could be used in the AND-EQUAL path of a single table, I believe). And yes, I do think that 15 indexes is a bit excessive but I can't help it at the moment (3rd party, packaged application...) It's not necessarily wrong - just something to be suspicious of when you start from cold at a site. Does this mean that I'm reading another myth? Couldn't confirm it on metalink. I've never seen anything like it claimed before. Maybe it's something to do with the fact that the manuals list 15 rankings in the RBO ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Automatic or Uniform allocation
I think http://www.jlcomp.demon.co.uk/08_lmt.doc is pretty good, but I'm biased. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - Brad - I agree with Ron. I think it is critical to read How to quit defragmenting . . . before making the change to ensure you clearly understand the concepts and receive the benefits. If someone on the list knows of a more recent paper, please post it. With uniform extents, you eliminate tablespace fragmentation, in addition to Ron's points. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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 over head with DBMS_STATS.ALTER_DATABASE_TABLE_MONITORING
The number of rows affected by an SQL statement is something that has been available to Oracle for a long time. Monitoring just records that number in a memory structure. I'd guess the memory structure is a hash table, and there are no latches protecting it (so I've heard, and I can't see any in x$ksllt) so the memory update is (a) rapid (b) subject to lost data. At regular intervals (3 hours for older versions, 15 minutes for newer) smon copies the data from memory to the mon_mod$ table. The overhead is small. But: a)The results are not corrected on rollback b)Concurrent changes to the count get lost c)There was at least one bug relating to partitioned tables with large numbers of partitions reported on metalink some time ago. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 1:44 PM Especially on high transaction tables? I dont have any numbers for transactions/second since we are not live. Any known issues? how does the monitoring work? Does it use an internal trigger and then use SQL to write the data? or does the monitoring data bypass the SQL layer and get written directly to the block? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: RE: Has anyone done any scalability work on dbms_lock?
I came across a very nice example a while ago where there were 4 concurrent sessions feeding data into a holding table, and one session consuming from the table. The rules said that the consumer could not run while the producers were loading the table, but multiple producers were allowed to run. It was easy to implement using dbms_lock - the producers ran a pl/sql loop that requested a share lock (mode 4) on a named resource, and committed at the end of each loop; the consumer also ran a loop but requested an exclusive lock (mode 6) on the same named resource and committed at the end of each loop. Both programs had a little sleep time built in to the loops after the commit. When the consumer got its lock, the producers waited. When the consumer committed, the producers got in, and the the consumer queued on them when it next asked for its lock. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearances: Jan 29th 2004 UKOUG Unix SIG - v$ and x$ March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 22, 2004 4:39 PM Well, I don't know about anyone else, but I make use of it to synchronize data upload programs for our testers. Can't have two instances of the upload program processing the same tester, they'd duplicate data. Anyhow, we normally run 4 instances of this program the dbms_lock package works absolutely fantastically. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Oracle 8.1.7 can only use the first 15th indexes?
drop table t1; create table t1 nologging pctfree 50 pctused 50 as select 1 n01, 1 n02, 1 n03, 1 n04, 1 n05, 1 n06, 1 n07, 1 n08, 1 n09, 1 n10, 1 n11, 1 n12, 1 n13, 1 n14, 1 n15, rownum n16, lpad(rownum,10) v1 from all_objects ; create index i01 on t1(n01); create index i02 on t1(n02); create index i03 on t1(n03); create index i04 on t1(n04); create index i05 on t1(n05); create index i06 on t1(n06); create index i07 on t1(n07); create index i08 on t1(n08); create index i09 on t1(n09); create index i10 on t1(n10); create index i11 on t1(n11); create index i12 on t1(n12); create index i13 on t1(n13); create index i14 on t1(n14); create index i15 on t1(n15); create index i16 on t1(n16); analyze table t1 estimate statistics; set autotrace traceonly explain; select v1 from t1 where n16 = 99; set autotrace off The execution path uses I16 on my system. It would be possible to produced test cases that failed to use the 16th index, of course, and some of them could look quite convincingly as if the 16th index should be used. But it only takes one counter-example ... (I assume the report intended to say the first 15 indexes on a specific table, 'cos the data dictionary alone has rather more than 15 indexes). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 6:19 AM Hello again, I've just been reading a report for one of our systems and it says that Oracle 8.1.7 will only use the first 15 indexes created. Any index created after the 15th will be ignored unless specified via a hint. Is this correct? I haven't heard of this before. TIA, Leng. -- Leng Kaing Email: [EMAIL PROTECTED] Phone: +61-3-9203-7589 Mobile: +61-417-371-348 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kaing, Leng 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.net -- Author: Jonathan Lewis 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: Unusable partition index -- working funny
It's probably the case that the trigger fires the first time - but at parse/optimise time Oracle had already determined the sequence of actions needed to execute the statement based on the then session state, so that sequence is played out, irrespective of the fact that you changed the session state in the middle of the sequence. By analogy, consider an update to an updatable join view which defaults to using a hash join. If you create a before row update trigger to disable hash joins, would you expect Oracle to not do a hash join the first time the statement executes ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 12:19 AM Hello All, I have a strange problem... I have a table on which i am doing an update. Its a partition table and the local index on the column which is being updated is in an unusable state. I have a database trigger at statement level (before update of col_a for ) where i do an execute immediate ' alter session set skip_unusable_indexes = true'; i log into sqlplus as the owner of the table and do the following SQL connect [EMAIL PROTECTED] Enter password: ** Connected. SQL UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1 where 2 mortgage_loan_key = 1 and period_key = '30-JUN-03'; UPDATE nevadmin.DM_MORTGAGE_LOAN_HIST SET ORIGINATION_SOURCE_KEY = 1166444 where * ERROR at line 1: ORA-01502: index 'NEVADMIN.DM_MORTGAGE_LOAN_HIST_BK13' or partition of such index is in unusable state SQL / 1 row updated. -- - My question is why does the trigger not fire for the first time... When i do the / i am able to update the table which means the trigger is firing the 2nd time. Any help would be greatly appriciated.. thanks, sathish. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Oracle 8.1.7 can only use the first 15th indexes?
Data warehouse with bitmap indexes ? But in OLTP I would assume guilty until proven innocent. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 9:44 AM Let's be realistic: any table with 15 indexes PROBABLY needs a little bit of a re-design exercise? ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Has anyone done any scalability work on dbms_lock?
On a light-weight test on 8.1.7.4 at 700MHz on W2000 - About 15,000 request/release per second if you are using an ID About 8,000 request/release per second if you are using a pre-allocated lock handle About 800 request/release per second if you have to allocate_unique on every request. Bear in mind that each request or release will hit the enqueue latch a couple of times, so you could get contention for the latch in the two high-speed options. (Forget the low-speed option, allocate_unique does a commit in mid-stream, which you might be able to hide with a recursive transaction - but the overheads are extreme). Bottom line - for high-speed OLTP type of work, I don't think you will get away with more than a dozen request/release cycles per transaction. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 12:49 PM As in: does it present an inherent or hidden performance problem when a lot of sessions try to lock/release the same lock? Or how many lock/release per second. Or some other idea of how efficient it is? Need to use it in a design, but not sure of any potential performance hits or scalability issues. Any ideas? TIA. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Has anyone done any scalability work on dbms_lock?
Note in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - Not a worry, it's a workflow app. A few users but not much load, a little volume. I just need to make sure a given sequence of operations is not undertaken by more than one user per group (one lock/group) and a table lock is way too heavy to do this. Sounds like you just need each user to call allocate_unique on startup to get a group-specific handle, then do a request in exclusive mode before doing the job and a release on completion. Users will then naturally queue and resume with minimum lost time. You could probably do the thing just as easily by issuing a select for update against a group-id row in a table - but dbms_lock makes it easier because it can bypass the normal commit activity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Renumber a set of grupped rows?
Although you've had a row-at-a-time version from someone, you might try something like the following if you can't do a create as select to rebuild the original data. Create table temp pctfree 0 nologging as select rowide_rowid, row_number() over (PARTITION BY DEPTNO order by deptno) x from emp; create unique index t_i on temp(e_rowid); alter table temp modify e_rowid not null; update emp e set req_id = ( select x from temp t where t.e_rowid = e.rowid ); (Just making it up as I go along, so I won't guarantee that it works). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 3:04 PM Just out of curiocity, and while I am trying to learn about Row_NUMBER(), how would you code the following to do an update on the 2nd column? select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) x from emp thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: pga_aggregate_target and a memory leak
A comment I picked up from Tom Kyte's Masterclass in Copenhagen last week was that there is an effective limit of 1GB to P_A_T - and although a single session is supposed to be allowed 5% of the P_A_T, you could get about 90MB. So there are some funny things going on in that area which still need fixing. It's a bit tough for big systems, as I've found that the optimizer seems to be much smarter about memory user and access paths when P_A_T and W_S_P are set. What's the book about ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 21, 2004 7:44 PM Replies in line... - Kirti --- [EMAIL PROTECTED] wrote: Kirti, you're back! Thanks. Found some slack time from routine DBA work! Must have finished the book. :) Not yet.. Its tough.. Re the PGA problems, what was the value for 'over allocation count' in v$pgastat? Actually, I never bothered to look at v$pgastat. Should have.. and will, when we do some more testing next week.. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: pga workarea and ora-04030
If you want to work out how much difference there is in different code paths, then you have to do some very patient testing. Run your test program for lots of different array sizes, say 1, 2, 3, and so on up to 100M. On each run, disconnect and reconnect your session, and check v$sesstat for pga and uga memory usage before and after each run, as well as the memory reported from the O/S (I think ps -al and look at the RSS figure for your shadow process is the HP-UX option - but someone may have a better idea). You then need to run a second set of tests where the size of an array element is significantly different from the first test - e.g. test1 uses a varchar2(32) test2 uses varchar2(1000) (and the third test uses varchar2(8000) ). Then you may be able to figure out the significant differences in handling It is quite likely that there is a different code path for allocating and freeing memory as you change versions of Oracle, or change parameters within a version; and it is quite possible that a piece of code for handling arrays changed from version to version - and any change could have introduced an unreasonable error. In passing, I thought the 'array is a fully pre-allocated' was a version 6 thing that got fixed in version 7. I would be amazed if arrays had gone backwards a step - it's easy enough to check: change your test to populate just element 1 and element 1 and see if your session still crashes. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, January 12, 2004 11:44 AM Jonathan, Thanks for your answer this clarifies a bit more But it still bothers me that this program can swallow 4Gb of physical memory and 4 Gb of swap and it is still not enough. You explain that the memory of pl/sql tables is not in the sga so that's clear now. What still bothers me is that my original program works fine with pga_target = 0 and wa-size-policy=manual When I try this with this test-program it fails (see below) VU_2exec testarray(1); begin testarray(1); end; * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-04030: out of process memory when trying to allocate 8144 bytes (cursor work he,qesaQBInit:buffer) ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at SYS.DBMS_OUTPUT, line 127 ORA-06512: at VRIJ_UIT.TESTARRAY, line 23 ORA-06500: PL/SQL: storage error ORA-06512: at line 1 Somehow these setting influence the way the pl/sql program works. This testprogram is clearly not enough to explain this behaviour. Because we Use quite some pl/sql I would like to know more because it could happen Maybe with other programs. Oracle 7 the same code runs fine also. I read a post that the difference for pl/sql tables is that they are now implemented as fully allocated arrays in memory whether they were implemented in oracle 7 and chained linked lists. Obviously this takes more memory but why do these 2 settings play such a role? Is the memory involved differently when using these settings? Can I monitor specific memory usage with these setting and how should this be done on HPUX? Regards, Jeroen -Oorspronkelijk bericht- Van: Jonathan Lewis [mailto:[EMAIL PROTECTED] Verzonden: Saturday, January 10, 2004 6:54 PM Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: pga workarea and ora-04030 I think what you've demonstrated is that pl/sql tables are not limited by pga-aggregate target, and that a pl/sql table can grow until it has taken up all the available memory on your machine. I'd guess that each element in your table takes about the same space - with a little error round the edges - so you can have 17.6M rows before you are out of memory - either as two tables of 8.8M or one table of 17.6M. The sleep time is probably because you start going to SWAP and your session spends time dumping real memory to disc. When the SGA is 1.5G smaller, that frees up an extra 1.5G of memory for you to use as PGA - so you get lots more entries in the table before you run out of memory. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC
Re: (Non)Unique Index Vs Unique Constraint
Depends what you want to achieve. A non-unique index enforcing a unique constraint allows the constraint to be deferrable - so you could load some 'nearly unique' data against it and find the duplicates efficiently. However, a non-unique index requires one byte per entry more than the equivalent unique index - and some people are very fussy about making indexes as small as possible. As far as the optimizer is concerned, the unique constraint guarantees uniqueness of data - which allows the 'single row' optimisation to be used, and also results in an equality on the index to be costed at the 'unique index' cost, rather than the 'non unique index' cost. (But the cost thing changes again if the constraint is deferrable) Bottom line - if you know that you never need to play silly games with the constraint, then a unique index is more efficient, and helps the optimizer more than a non-unique index. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 17, 2004 3:14 AM All, Please enlighten this Junior DBA. Which method is more efficient? When should I go for option (1)? 1)NON-UNIQUE index Vs Unique Constraint drop table index_test; create table index_test(c1 number,c2 varchar2(20)); create index i1 on index_test(c1); alter table index_test add constraint index_test_uk1 UNIQUE(c1); 2)UNIQUE index Vs Unique Constraint drop table index_test; create table index_test(c1 number,c2 varchar2(20)); create UNIQUE index i1 on index_test(c1); alter table index_test add constraint index_test_uk1 UNIQUE(c1); Thanks in advance, Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay 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.net -- Author: Jonathan Lewis 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: Reset sequence at midnight
Note in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 14, 2004 7:09 PM Interesting, I've actually had to do this before. Be forewarned that this is not a good method to use for a very busy app, as it does introduce some level of serialization. control access to the sequence through a package Within the package use a function that sets a lock via dbms_lock.request and then immediately release the lock. Did you request the lock in share mode to request next val ? This would help to reduce the contention. Normal users would then only queue on the exclusive lock that you would take for the fix-up. lock(shared) increment sequence release The purpose of this will become clear in a moment. Create a procedure within the package that will be used to reset the sequence to 0. It is not necessary to drop the sequence to do this. eg. drop sequence s; create sequence s start with 100; select s.nextval from dual; declare vs integer; inc integer; junk integer; begin lock(exclusive) select s.nextval into vs from dual; inc := 0 - vs; execute immediate 'alter sequence s minvalue ' || inc; execute immediate 'alter sequence s increment by '||inc; select s.nextval into junk from dual; execute immediate 'alter sequence s increment by 1'; release() end; / select s.nextval from dual; The procedure that does this just needs to take the same dbms_lock.request that the function mentioned earlier takes. The difference is that it does not release the lock until the modification of the sequence is completed. This forces any requests for new sequence numbers to wait for the modification to the sequence to complete. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: DB_WRITER_PROCESSES vs DBWR_IO_SLAVES
There could be some interaction. If DBWR needs to write a block for which the most recent changes are in the log buffer but not in the log file, then DBWR posts LGWR to write - and in earlier versions of Oracle DBWR would then wait for LGWR to sync, in later versions DBWR links the buffer to a holding list carries on down it's main write list and comes back later to pick up the spares (I think). It is possible that this type of cross-over could produce some redo latch pressure in the right circumstances. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 8:29 AM Since when is redo log writing performance handled by DB_WRITERS or DBWR_IO_SLAVES? Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Now, the Sr DBA here is screaming about the performance since I made the change, in particular, he says he's seeing high redo latch contention and redo log buffer waits which he says validates his contention that we need to get back to using DBWR_IO_SLAVES. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Suggestions Needed: Latch free - library cache
It's always a little hard to tell from a low-concurrency experiment how bad things can be at high concurrency. (If it were easy, Cary wouldn't have had to have written his book). I have an example where a collision rate of 0.25% results in an increase in response time of 8% at relatively low concurrency. One of the problems of contention is that the back-off time after a collision may be unsuitably large. (Which is one reason why there was an argument for changing the spin_count on latches - the backoff of 1/100 sec has not changed since 'fast' CPUs clocked 100MHz.) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 09, 2004 5:29 PM I remember readign that article and I thought the results that the contention was very minor? Steve, are you monitoring? It seemed like one of those things that its so minor its not really something to worry about? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Suggestions Needed: Latch free - library cache
Note in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 09, 2004 5:09 PM I'm not sure that's right. If everyone uses a public synonym, then you get one sql text, and one cursor. I think the contention appears because everyone has to have a 'non-existent' reference in memory to say that they don't own an object with the same name as the public synonym - consequently if you have lots of users who have to check long chains of 'non-existent' then the latches get held for longer periods of time. Hi Jonathan, I don't see how your statement contradicts the claim that heavy use of public synonyms causes contention for not only library cache latches but also row cache objects latches. What I had in mind is Steve Adams' test. Here's the URL http://www.ixora.com.au/newsletter/2001_05.htm#synonyms. If I understand right, the additional row cache objects latch gets are for synonym translations, particularly public synonym translations. Given that Steve has replied, I don't suppose there is much need to say anything more. However, you will note that Steve's experiment covers the authentication call to an existing cursor. Subsequent uses result in no further access to the rowcache - hence my comment that it is not the rowcache, just the length of what Steve has pointed out is a 'segmented array' that lurks in the library cache that causes the problem by pushing up library cache latch hold times. However, if parsing is so extreme that lots of statements never get beyond the slightly softer authentication only parse call, then the presence of public synonyms makes a bad problem worse, and a big chunk of the latch impact is in the rowcache objects latch. (I haven't check v9 closely yet to see how much this has changed, but there are extra latches for the library cache the row cache which may mean a different strategy now exists). Yong Huang __ Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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.net -- Author: Jonathan Lewis 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: pga workarea and ora-04030
I think what you've demonstrated is
that pl/sql tables are not limited by
pga-aggregate target, and that a pl/sql
table can grow until it has taken up all
the available memory on your machine.
I'd guess that each element in your table
takes about the same space - with a little
error round the edges - so you can have
17.6M rows before you are out of memory -
either as two tables of 8.8M or one table
of 17.6M.
The sleep time is probably because you start
going to SWAP and your session spends time
dumping real memory to disc.
When the SGA is 1.5G smaller, that frees up
an extra 1.5G of memory for you to use as
PGA - so you get lots more entries in the
table before you run out of memory.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearance2:
March 2004 Hotsos Symposium - Keynote
March 2004 Charlotte NC - OUG Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___February
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, January 09, 2004 10:34 PM
Hi,
I followed you advice and made small testprogram see below:
I only get the ora-06500 which I have had before in the original program
as
A followup error so to me it seems to be reproducible.
In manuals I only find that the index
Of a pl/sql table cannot be more than 2**31, which is something like
2.000.000.000
I found on metalink some posts which suggested this might be functioning
better enlarging shared pool and also max user data (ulimit of oracle)
We increased maxdseg in the ux-kernel parameters to 4Gb to increase this
limit (was 2Gb). There is 4Gb available of physical memory in the box.
Using a shared pool 0f 500Mb, pga_aggregate_target 100Mb
setting 100.000.000 elements - 22 minutes and it fails
Exception raised insert i= 68102540
Using 1 table, shared pool 2Gb
setting 1.000.000 elements - 14 seconds
setting 10.000.000 elements - 282 seconds
setting 100.000.000 elements - 12 min 24 seconds fails
21:54:37 VU_2exec testarray( 1 );
Exception raised insert i= 17613935
Running with a second table involved: after 17 minutes 29 seconds
22:40:20 VU_2exec testarray( 1 );
Exception raised insert i= 8806960
So it is reduced by 50%. But why is the result with a smaller sga
Giving me more elements set?
Watching the oracle serverprocess with top utility
I see the memory resident part
Most of the time around 2600M but more interesting the process is
Most of the time sleeping, what the heck is it doing all the time before
Going into an error?
1 ?4728 oracle 128 20 4116M 2626M sleep7:49 1.20 1.20
oracleVU_2
I cannot find any other restriction then 2**31 limit on the index.
I don't know how to calculate how much memory this is taking because
watching sqlworkarea of pgastat doesn't show any useful info in this case.
But it looks to I'm hitting a limit somehow.
Can somebody explain which limit this is and how is it composed or
influenced (temp, sga ?) ?
Is this reproducible on other systems / versions ?( Metalink post reports
This also on early 8.1.x versions , I couldn't find this on 9.x versions)
create or replace procedure testarray( psize number ) as
begin
declare
TYPE nAllotment_tabtypIS TABLE OF number
INDEX BY BINARY_INTEGER;
assarray nAllotment_tabtyp;
assarray2 nAllotment_tabtyp;
assarray3 nAllotment_tabtyp;
uitleg varchar2(100);
begin
uitleg := 'start loop';
for i in 1..psize loop
uitleg := 'insert i= ' || i;
assarray(i) := i;
/*
uitleg := 'insert i2= ' || i;
assarray2(i) := i;
*/
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.enable(2);
dbms_output.put_line(' Exception raised ' || uitleg );
end;
end;
-Oorspronkelijk bericht-
Van: Jonathan Lewis [mailto:[EMAIL PROTECTED]
Verzonden: dinsdag 6 januari 2004 16:49
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Re: pga workarea and ora-04030
The workarea_policy stuff does not apply
to things like pl/sql tables, only to tuneable
memory. Given that you don't have the
problem when you disable p_a_t and w_p,
it may be that there is some buggy event
occurring where the workarea_policy code
is being infringed by an abuse of pga memory.
You could try setting up test cases where
you use a pl/sql loop to build a pl/sql table.
Make it a procedure with an input parameter
that is the table size, and see how big the table
has to before the procedure crashes. Fiddle
with the p_a_t, and w_p (they can be set
separately) to see if the crash point moves.
This may give you (or Oracle Corp
Re: pga workarea and ora-04030
In the UGA, I should think (which also means the SGA if you are running MTS). It can't be in the PGA (ignoring the fact that the UGA is in the PGA for non-MTS) or you couldn't have global pl/sql tables that persist across database calls. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 6:04 PM Where does oracle store pl/sql tables? I have run into problems with developers doing massive bulk collects and I have to bounce the entire server... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 10, 2004 12:54 PM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Should we stop analyzing?
Note in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 09, 2004 1:19 PM - Original Message - Wouldn't it be nice if dbms_stats could do an incremental refresh, tracking ONLY stats changes that might make a difference to execution plan: I'd settle for a flag I could turn on and off, saying: do/do not change stats for this object. I know which of them need to be analyzed and which don't. Better than Oracle will ever, deltas or no deltas, workload managers or not. Available in Oracle 10g - lock stats. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: DB_WRITER_PROCESSES vs DBWR_IO_SLAVES
One interpretation of increased red latch contention and log buffer space waits is that more work is being done more quickly - so the log writer can't keep up. This could mean: a)the log writer has slowed down b)the database writer(s) have speeded up, so there is less time lost on write complete waits, free space waits, and (side effect) buffer busy waits. So the symptoms could be positive or negative, and we don't have enough relevant information to say which. Depending on the nature of the application, and the number and duration of sync waits, you could address the log buffer space waits by increasing the size of the log buffer. Are the redo latch waits allocation latch or copy latch ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - Now, the Sr DBA here is screaming about the performance since I made the change, in particular, he says he's seeing high redo latch contention and redo log buffer waits which he says validates his contention that we need to get back to using DBWR_IO_SLAVES. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Should we stop analyzing?
Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 1:14 AM When we consider that re-analyzing stats can cause huge changes to data access patterns I'm continuously amazed at the number of shops that re-analyze on a schedule and have the Monday Morning syndrome. The issue here is that very few people understand how the CBO works, or what the statistics do, or how to use them properly. If someone came to me and said: I'd like to inject some random numbers into the database every Monday morning I'd insist on proof of concept and rigid change control for evey set of random numbers Is some came to me and said: I need to keep the meta-data synchronised with the data, and install a routine to adjust certain components of the meta-data that the database cannot derive automatically I'd ask for one proof of concept, and a one-off change control. I have worked for shops where they must certify every change, no matter how trivial. Mostly banks and medical systems. So they have a difficult choice to make when the data changes sufficiently to make the out of date statistics a disaster and NEED to correct the statistics. Do they clone the production database, change the statistics, prove that the system can complete it's batch job in 8 hours, then install ? I doubt it. These certified shops are stuck. On one hand, they are obligated to follow the best-practices of their vendor, yet obligated not to make any untested changes in production. Even Oracle is schizophrenic on the issue; my contacts in the real-world performance group are zealously in favor of the take one deep sample approach, while the 10g developers are pissed that the CBO has been getting a bum-rap because of crappy statistics. Can you ask them what their approach is towards monotonic increasing values in columns, and the side-effects of the low/high basis for selectivity ? Personally, I love the automatic histogram generation skewonly and the auto option in dbms_stats, and use it for all my 9ir2 clients. However, I remain skeptical about the benefits of dynamic sampling and workload analysis automation tools for most shops. These two statements aren't entirely compatible. the skewonly and auto options are driven by built-in dynamic sampling and workload analysis automation tools. There's no very good reason why Oracle can build the only such tools that make sense - and in fact, it is arguable that a 3rd party may have a more general view of how these types of tools need to work because Oracle Corp tends to focus at two extremes - the very tiny (lab experiment) or the huge (big companies and TPC). In my experience, the vast majority of shops DO NOT benefit from re-analysis, and I've got shops where re-analysis NEVER results in CBO changes. But sometimes the re-analysis NEVER results in CBO changes because failure to re-analyze WOULD result in a detrimental CBO change. (Actually, re-analysis almost always results in CBO changes if the data has changed, but hardly anyone looks at the actual stats stored in user_table, user_indexes, user_tab_columns etc.) Regards, Donald K. Burleson www.dba-oracle.com www.remote-dba.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Suggestions Needed: Latch free - library cache
There's a failure in the statistics at this point. As far as I can tell, there are at least four different reasons why the stats can report a parse count (total) without recording a parse count (hard) and it would be nice if we could see them as four different statistics. Code that explicit holds a cursor open need not issue a parse call at all. Code that issues a parse call may: Invoke the whole parse/optimize cycle Invoke a permissions cycle on an existing statement Invoke a search and execute cycle on an existing statement with valid permission Invoke a 'this is where it is and I know I've got permission, so just do it' cycle The last option can appear when you set up session_cached_cursors != 0. The front-end code is still issuing an explicit parse call, which is why you see the parse count go up, but the work done is kept to near minimum. NOTE: This description is probably not complete and I'd welcome any corrections and refinements that anyone can supply. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 1:34 AM if your caching the cursors, why does soft parsing still happen? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 7:24 PM -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Problem with understanding Optimization methods.
Conversely, the CBO is a lot smarter with this scenario that people realise. How many people knew that Oracle can resolve a query of the type: where colX is null using a b-tree index ? Try this -- drop table t1; create table t1 (n1 number, n2 number not null, n3 number); create index i1 on t1 (n1, n2); set autotrace traceonly explain select /*+ first_rows */ * from t1 where n1 is null; set autotrace off Execution Plan -- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=4 Bytes=156) 10 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card=4 Bytes=156) 21 INDEX (RANGE SCAN) OF 'I1' (INDEX) (Cost=4 Card=4) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 11:54 AM Bambi, Yes it is expected behaviour, but only when it is guaranteed that no rows will be missed because of unindexed null entries. I wanted to point out that RBO is too dumb to realize that even though it ordered by column A which could be null, the column B in composite index was not null, thus causing every row to be indexed and RBO didn't use the index. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Problem with understanding Optimization methods.
I don't know, but it's been in Steve Adams' seminar material for a long time. It doesn't apply to a single-column b-tree index - you need at least one column available to be the mandatory column. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 1:59 PM Hi Jonathan, What release did this NULL_CHECK start with? I used to see: Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6) 1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=1 Card=1 Bytes=6) As I recall, I used-to need an FBI on column with NULL values create index i1 on t1 (nvl(n1,'null')) ; Regards, Donald K. Burleson www.dba-oracle.com www.remote-dba.net - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 8:14 AM Conversely, the CBO is a lot smarter with this scenario that people realise. How many people knew that Oracle can resolve a query of the type: where colX is null using a b-tree index ? Try this -- drop table t1; create table t1 (n1 number, n2 number not null, n3 number); create index i1 on t1 (n1, n2); set autotrace traceonly explain select /*+ first_rows */ * from t1 where n1 is null; set autotrace off Execution Plan -- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=4 Bytes=156) 10 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (TABLE) (Cost=4 Card=4 Bytes=156) 21 INDEX (RANGE SCAN) OF 'I1' (INDEX) (Cost=4 Card=4) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 11:54 AM Bambi, Yes it is expected behaviour, but only when it is guaranteed that no rows will be missed because of unindexed null entries. I wanted to point out that RBO is too dumb to realize that even though it ordered by column A which could be null, the column B in composite index was not null, thus causing every row to be indexed and RBO didn't use the index. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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.net -- Author: Don Burleson 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.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Quest....
The number of sites I go to that use spotlight to keep a check on the top 10 SQL statements - it's weird, but it always seems to be this query against v$sqlarea. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 4:29 PM I'm TOADing and Spotlighting right now... :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Suggestions Needed: Latch free - library cache
Excellent !! I've been demonstrating in the past using v$latch that the latch costs of parsing are different on the first, second, and third parse - and I've assumed that that's why the cursor goes into the cache on the third parse. I've never thought that the 'cursor authentication' statistic might be relevant. If you go to the other session a) Where the user is the same - do you see a session cache cursor hit on the second execution, or does it still not appear until the fourth Rationale - maybe the cursor is put into the cache on the first hit after full authentication. b)Where the user is different - do you see a session cache cursor hit on the THIRD execution, or does it still not appear until the fourth. Rationale - the first execution generates the in-memory permissions; the second execution finds the cursor authenticated, therefore causes a cache load. The manual says the cursor is cached on the third execution - but maybe that's the obvious result from the simplest test. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 4:34 PM Ok, I did a little experiment. Here are my results: In session A, I do: I did 'select sid from v$mystat where rownum=1;' I did 'alter session set session_cached_cursors=100;' I did 'alter system flush shared_pool;' In session B, I ran the following: select my.statistic#, sn.name, my.value from v$sesstat my, v$statname sn where sn.statistic#=my.statistic# and sn.statistic# in(179,180,191,193) and my.sid=62; Which yielded the baseline stats: STATISTIC# NAMEVALUE -- -- -- 179 parse count (total)60 180 parse count (hard) 9 191 session cursor cache hits 6 193 cursor authentications 6 Now, session A: Select /* this is my unique sql */ * from dual; Session B shows: STATISTIC# NAMEVALUE -- -- -- 179 parse count (total)62 180 parse count (hard) 10 191 session cursor cache hits 6 193 cursor authentications 6 Two more total parses, one hard. (The extra soft parse due to recursive sql?) Now, session A: / (Re-execute query) Session B: STATISTIC# NAMEVALUE -- -- -- 179 parse count (total)63 180 parse count (hard) 10 191 session cursor cache hits 6 193 cursor authentications 7 Hmm...no hard parse, soft parse and 'cursor authentication'. Session A: / (execute a third time) Session B: STATISTIC# NAMEVALUE -- -- -- 179 parse count (total)64 180 parse count (hard) 10 191 session cursor cache hits 6 193 cursor authentications 7 Hmm...soft parse, NO cursor authentication. This is just the third exec, so no session cursor cache hit, but we should be in the session cursor cache now. Session A: / (Fourth execution) Session B: STATISTIC# NAMEVALUE -- -- -- 179 parse count (total)65 180 parse count (hard) 10 191 session cursor cache hits 7 193 cursor authentications
Re: Suggestions Needed: Latch free - library cache
Thanks, Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 5:19 PM Jonathan, Second session, same user: first is soft w/o authentication. Second is session cursor cache hit. Second session, different user: first is soft w/ authentication. Second is session cursor cache hit. So, once everything is cached, the same user case is as expected, and the different user case does even better than you predicted. -Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Should we stop analyzing?
Comment in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors Several years ago I raised an issue with Oracle support where something was clearly going wrong - can't remember what, too long ago - and got told that I couldn't get the issue logged as a bug because the code was performing to specification. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Suggestions Needed: Latch free - library cache
Note in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - Other than the four parse invocations in your message, I think we can add one between your first and second: Invoke a parse to create a new version of the same cursor (same in the sense of same address and hash) due to either bind threshold change or execution plan change. An interesting point there - I think we tend to include the optimisation phase in the concept of parsing; but perhaps there ought to be a breakdown in the statistics so we actually see a statistic called something like: plans generated so that the number of optimisation events stands out from the hard parses. (I suspect a hard parse is probably synonymous with an optimize, but I'm not sure of that). In terms of costing, then, I think we only need five or six statistics: a)search for text b)check objects c)check permissions d)generate plan e)use cached cursor f)use held cursor g)??? (I'm trying to break it down into the major cost areas - obviously a 'check objects' cost would vary with the number of objects in the query, so any very fine detail wouldn't really add value). To the OP: Other people point out common reasons for library cache latch contention. A less common reason is extensive use of public synonyms. If that's the reason, you also see row cache objects latch contention. I'm not sure that's right. If everyone uses a public synonym, then you get one sql text, and one cursor. I think the contention appears because everyone has to have a 'non-existent' reference in memory to say that they don't own an object with the same name as the public synonym - consequently if you have lots of users who have to check long chains of 'non-existent' then the latches get held for longer periods of time. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Problem with understanding Optimization methods.
Check the costs of the two queries (autotrace will be
sufficient).
In this example, rule based uses the index because
it exists and will return the right answer.
Cost based works out that the scan and sort is
cheaper.
The cost of an index full scan is approximately
blevel + leaf_blocks (columns from user_indexes).
The cost of the tablescan is
blocks / 'adjusted db_file_multiblock_read_count'
If your dbfmbrc is 8, use 6.59
If it's 16 use 10
If it's 32 use 16.4
The cost of the sort (which seems to be wrong
in 8.1.7.4) is likely to be about the same as the
cost of the tablescan.
So, as an example, pretend your dbfmbrc is 16,
then if the index is larger than 1/5th of the size
of the table, the scan and sort will work out
cheaper than the index full scan.
I am a little surprised, though, that you don't
have a path that is index FAST full scan.
This suggests that your index is actually
bigger than your table. Maybe it's got
lots of holes in it.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, January 07, 2004 5:29 AM
Hello Listers,
A normal sql query from a data warehouse tool called Sagent.
SELECT COL1, COL2, COL3
FROM TABLE
ORDER BY 3;
The table has approximately 2 mil records.
table has 22 indexes.
The database is set up optimizer CHOOSE.
I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.
OS is Win2k
ORACLE 81741
OK, when doing a explain plan on the above sql, I get the following...
SELECT STATEMENT Optimizer Mode=CHOOSE
SORT ORDER BY
TABLE ACCESS FULL TABLENAME -- Very slow and takes
hours!
When adding the hint /*+RULE*/ for example I get
SELECT STATEMENT Optimizer Mode=Hint:RULE
TABLE ACCESS BY INDEX ROWID TABLENAME
INDEX FULL SCAN TABLE_INDEX --
Much faster!!!
Have I given enough info that anyone can explain why the CHOOSE mode
insists
on doing a TABLE ACCESS FULL?
Is there anything I can do to improve performance? Please remember that
this
query comes from a Data Warehouse tool and hence does not appear to accept
hints.
Any help will be much appreciated!
Denham
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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: Problem with understanding Optimization methods.
Apologies to the list. The previous reply may have been informative, but it was irrelevant. I failed to notice that the indexed access path was followed up by a table access by rowid. (This does explain why the index_FFS path wasn't used, of course). In this case, the cost of the indexed path would be blevel + leaf_blocks + clustering_factor (in 8.1.7.4). Given that the clustering_factor for btree indexes falls between the number of blocks in the table and the number of rows in the table, you could make the access path go either way by hacking the clustering factor between two extremes. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 10:30 AM Check the costs of the two queries (autotrace will be sufficient). In this example, rule based uses the index because it exists and will return the right answer. Cost based works out that the scan and sort is cheaper. The cost of an index full scan is approximately blevel + leaf_blocks (columns from user_indexes). The cost of the tablescan is blocks / 'adjusted db_file_multiblock_read_count' If your dbfmbrc is 8, use 6.59 If it's 16 use 10 If it's 32 use 16.4 The cost of the sort (which seems to be wrong in 8.1.7.4) is likely to be about the same as the cost of the tablescan. So, as an example, pretend your dbfmbrc is 16, then if the index is larger than 1/5th of the size of the table, the scan and sort will work out cheaper than the index full scan. I am a little surprised, though, that you don't have a path that is index FAST full scan. This suggests that your index is actually bigger than your table. Maybe it's got lots of holes in it. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Convert to Locally-Managed Tablespaces
Tanel I am curious - why are you forced to keep segments in specifically named tablespaces. I did come across one system that would actually reject (application) upgrades if the database didn't have exactly the right two tablespaces (E_DATA and E_INDX or some such) but I can't think of any other reason. A couple of add-on points for Paula: There is a drawback with using parallel moves - at parallel 16, you end up with 16 extents with an average of 50% space wastage - This may not be significant, especially on a large table with a reasonable number of extents that will be subject to lots of subsequent inserts - but it could make a big difference in some cases. (Side effects include changes in execution path) Regardless of whether you use export or move, you may also have to consider a few special cases of tables where the typical row starts small, and grows over the course of time. You do get systems where a graph of row-lengths shows things like: 80%140 bytes 10%100 bytes 10% 60 bytes If this is the case, then moving the table can result in a problems with lots of wasted space (if you set pctfree for the new rows that need to grow from 60 bytes to 140 bytes) or lots of chained rows (if you set pctfree to suit the 80% of the data that is never going to grow again). I think the ideal is to MINIMIZE records_per_block on such tables, then set pctfree to zero, then MOVE them, then set pctfree back to what it was. But if you do have any such tables, you might want to experiment. One last thought - does the entire operation have to be done in one shot anyway ? You could create a few spare empty tablespaces, and move a couple of tables and indexes at a time if you wanted to avoid high visibility periods of non-availability, and didn't want to come in at the week-end. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 12:49 AM Hi! This is what I will need to use on our systems, as there are about 400 gig of data and indexes. 200 gig of data is too large to export/import, at least it is for this project. So dbms_space_admin it will be. I'm about to do a reorg+conversion of a 250GB 8.1.6 database in next week, here's what I'll do (there is practically no free space for temporary usage): 1) Export index definitions (normal export with rows=n) 2) Drop all indexes 3) use alter table move with parallel 16 and nologging to move all tables to old index tablespaces (the indexes consumed more space than tables) 4) drop and recreate data tablespaces 5) use alter table move again to move tables back (the segments have to reside in original tablespaces, otherwise I could have skipped this step) 6) drop and recreate index tablespaces 7) get index definitions out of exportfile and modify them to add parallel nologging (with big sort area size) 8) rebuild indexes 9) do a full backup It might help to recreate index tablespaces even before step 3, to speed up parallel table moving a bit.. Maybe you want to test this Jared, this approach is much faster than export/import, because everything can be done with direct path operations and nologging (import doesn't have direct path facility, so regular array inserts are used, which always require logging as well). Also, your tables/datablocks will be optimized after moving them (which is not the case with dbms_space_admin) and you don't have to have any space for reorg in case your cleared index tablespace can temporarily accommodate your data. IIRC one of the drawbacks of using dbms_space_admin to convert is that you won't be converting to nice uniform extent sizes for existing data. Yes, and if your tablespace is fragmented, the fragmentation will remain there, despite your conversions (of course, smaller extents might be able to use some of this fragmented space later on). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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
Re: HOTSOS Conference
I'll be there. Getting together on Tuesday sounds good. I won't be around on Thursday, though as I'm planning to stop off in Charlotte to do a one day event for the user group there. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 1:59 AM While perusing the HOTSOS site, I noticed that the deadline for the discounted registration for the HOTSOS conferences ends after tomorrow. If you're thinking of going, you may want to check it out. Along those same lines, how many listers will be there? We could get together on Tuesday evening for dinner/drinks if any are interested. Possibly some of you with Dallas connection can recommend a suitable location. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Convert to Locally-Managed Tablespaces
The answer is in my book. Assume your original setting for pctfree is correct, by the time rows are full length, they are filling the block. If any rows are still part-grown, there will be the right amount of space left in the block for them to grow. If all the rows are new, there will be plenty of space to grow. Inherently there is a 'right number' of rows that will fit a block when full grown. Set the hakan factor to this, and when you move the table, each block will get exactly the right number of rows, with exactly the right amount of space left for any rows that need to grow to full size. (However, due to a bug in the Hakan-related code, you have to fiddle the Hakan factor by one for some functionality, as it is stored as N-1, rather than N - and some code uses N, some uses N-1 when working out how many rows go into a block. Details are in the book). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 10:54 AM Good point about pctfree, but how could minimize records_per_block help in this case? I´ve used it for optimizing bitmap indexes, but for table moving? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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:
How is the bit-stream presented ?
If it's a string, you could do something like:
select replace(replace('11010','1','11'),'0','00') from dual;
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, January 06, 2004 9:39 AM
Hi,
Well, since I can't sleep, I may as well try solving a problem.
This is a bit odd, and I'm trying to think of the most efficient way to do
it. I've set up some bitmaps in my app. Consider we have documents that
we want to sell. In order to be able to sell a given doc, we need to have
it
stored in the vault and we also need to have negotiated the proper
contract
w/ the publisher. So, I've got two bitmaps, STORAGE and PERMISSIONS.
But, here's the hook. There are 8 different types of storage, so I have
an 8 bit mask. However, for every storage type, there are two types of
permission. So, I have a 16 bit permissions mask.
What I'd like to do is take my 8-bit STORAGE mask, say it's 10110011
and convert it to 1100. Note that all I did there was take
each bit in the input mask, and make the same value repeat. So, 0 would
become 00, 1 would become 11, 10 would become 1100. Does that make sense?
Once I've done that, I can take my STORAGE mask that's now stretched to 16
bits, and directly AND it with my PERMISSIONS mask.
So, my question is: Is there a nice, scalable way to take my 8 bits and
expand them into 16 bits, in the way that I'm describing? A clever bit
twiddling expression would be perfect, but anything that's efficient and
scalable will do. (The end product will be hidden behind a view or stored
procedure.)
Any thoughts, anyone?
Thanks in advance,
-Mark
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bobak, Mark
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.net
--
Author: Jonathan Lewis
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: HOTSOS Conference
Would you like me to bring a couple of bottles of Blackadder to take home with you. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 1:09 PM I'll be there, trying to set a record for the longest time on a plane by any attendee Cheers Connor -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: pga workarea and ora-04030
The workarea_policy stuff does not apply to things like pl/sql tables, only to tuneable memory. Given that you don't have the problem when you disable p_a_t and w_p, it may be that there is some buggy event occurring where the workarea_policy code is being infringed by an abuse of pga memory. You could try setting up test cases where you use a pl/sql loop to build a pl/sql table. Make it a procedure with an input parameter that is the table size, and see how big the table has to before the procedure crashes. Fiddle with the p_a_t, and w_p (they can be set separately) to see if the crash point moves. This may give you (or Oracle Corp) some clues. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 2:54 PM Hi, I have posted a problem before which I can only solve with a workaround but because I'm not getting satisdactory answers from Oracle I'm trying alternatives. - problem is a batch pl/sql package which ends with ora-4030 - batch runs fine on oracle 7.3.4, we migrated to oracle 9.2.0.4 recently - setting pga_aggegrate_target=0 and workarea_size_policy=manual solves the error - This is the only batch which results in errors Due to recent posts I have tried smm_max_size set to 100Mb and I still get this error. All of the following is done with _smm_max_size set and first setting pga_aggegrate_target=50M and workarea_size_policy=auto Monitoring v$sql_workarea_active leads me a max. use of 532Kb. The figures below didn't change during the batch I only saw temporarily another workarea for the same sid OPTYPEACTTIMEWA_SIZE EXP_SIZEACT MAXMEM PASS TEMPSEG TBLSP -- -- -- -- -- -- -- -- --- GROUP BY ( 1378396893 532480 532480 532480 532480 0 14:30:44 SQL / NAME VALUE UNIT - - aggregate PGA target parameter 104857600 bytes aggregate PGA auto target 6553600 bytes global memory bound 104857600 bytes total PGA inuse 1105825792 bytes total PGA allocated 1129529344 bytes maximum PGA allocated 1135382528 bytes total freeable PGA memory 458752 bytes PGA memory freed back to OS 1303117824 bytes total PGA used for auto workareas 737280 bytes maximum PGA used for auto workareas 1163264 bytes total PGA used for manual workareas 0 bytes NAME VALUE UNIT - - maximum PGA used for manual workareas 16384 bytes over allocation count 979 bytes processed 3141169152 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent a) Why do I see manual workarea used despite workarea_size_policy=auto ? It is a test environment with just me and a developer on it b) Total pga used reports as 737Kb and total pga allocated finishes on 1.1Gb How can I relate this to the workarea? c) What's the exact connection with the _smm_max_size? When I increase the pga_aggregate_target to 2Gb and the smm_max_size also the program fails around the following numbers from pgastat QL / NAME VALUE UNIT - - aggregate PGA target parameter 2147483648 bytes aggregate PGA auto target 1895003136 bytes global memory bound 2097152000 bytes total PGA inuse 41918464 bytes total PGA allocated 1137232896 bytes maximum PGA allocated 1137249280 bytes total freeable PGA memory 1074987008 bytes PGA memory freed back to OS 131072 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 0 bytes total PGA used for manual workareas 0 bytes NAME VALUE UNIT - - maximum PGA used for manual workareas 2347008 bytes over allocation count 0 bytes processed 1603424256 bytes extra bytes read/written 6708224 bytes cache hit percentage 99.58 percent d) What is remarkable that auto workareas are now on 0, the manual stuff might be by another testuser, the total pga_allocated is just a little bit higher. I am completely confused by now and I hope you can shed some light on this. Regards, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED
Re: Top level heaps/subheaps
You missed out Terry: quote Oh, it's largely intuitive, Archchancellor. Obviously you have to spend a lot of time learning it first, though. end quote Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 6:19 PM As well as Arthur's books and Douglas's books. Some of the Robert's, too. Personally, I'd recommend Stranger In The Strange Land. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: OCP Question (Perf Tuning)
Note in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 6:59 PM Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. Unless the alternative is a full tablescan on the inner table - in which case merge or hash joins can be considered. The question itself is non-trivial, as the cost of a nested loop is: Cost of outer acquisition + Cost of inner access * cardinality of outer acquisition. But the cost of a merge join is: Cost of first acquisition + cost of first sort + Cost of second acquisition + cost of second sort + Cost of merge It seems likely that if the first table returned 1 or 2 rows, then a nested loop with FTS could be cheaper than a sort merge, but if the outer table returned 3 Oracle would switch to a sort merge. (Assuming equijoin). On the other hand, if the second table required a very large sort, I'm sure you could engineer a sort_area_size that would make the sort cost more than three times the cost of a simple tablescan - which means you could change the access path by changing the sort_area_size. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: OCP Question (Perf Tuning)
I see you're running on Oracle 9 there, and that can make a big difference. After posting my hypothesis, I created a test case, which behaved as I had predicted - but the behaviour changed in Oracle 9, and I had to do some tweaking. Turns out my test case highlighted what looks like a but in the SORT costing in Oracle 8 for a sort/merge join. The Oracle 9 costing is better, so Oracle 9 didn't switch to an NL when Oracle 8 did. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:14 PM Jonathan, you're right. Interesting thing is that bitmap indexes, which were made for DW processing and not for OLTP will also be considered for NL context in First_Rows mode. Here is the proof, which also proves that I'm a lousy typist: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: OCP Question (Perf Tuning)
A bigger error in option (d) is that it leaves open the ambiguity of whether the rows should, or should not, be part of the answer to the join. Oracle's choice of join could be affected by adding 100 rows to the table that should be included in the join, but remain unchanged if you add 100 rows that should not be included in the join. Frankly it's an appallingly bad question, and I think your assumption about the wrong answer you are required to give is correct. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 06, 2004 9:54 PM Jonathan noted that Nope. The answer is b). In the FIRST_ROWS mode, optimizer prefers NL to all other methos despite the price. Unless the alternative is a full tablescan on the inner table - in which case merge or hash joins can be considered. For some reason hash joins were excluded from the question. I can't speculate as to what, other than oversight or limiting the answers to 4, the reason for this might be. I suspect the answer *wanted* is b) the focus being that FIRST_ROWS favours index scans and NL joins. ISTM that d) ought to be ruled out as it refers to rows not blocks but as usual I am probably wrong. Niall -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: max 5% of pga_aggregate_target for a single serial session
Your sample query would only have needed one active area. Another thought: although the maximum you are allowed is 150M, Oracle may decide that there is no point in giving it to you because it won't improve your performance significantly, but it might benefit other people if some of the memory is held back. For example: To get an optimal (in-memory only) sort, you need 200M. To get a one-pass sort you need 90M. Your limit is 150M. Oracle may decline to give you the extra 60 M past the memory required for a one-pass sort, because whatever memory you get you still have to write and re-read the whole data set to disc, so the extra 60M won't change things significantly. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 4:14 PM select BELNR,count(*) from sapr3.bsis group by BELNR order by BELNR This was the SQL running at that time. -Original Message- Sent: Tuesday, December 30, 2003 5:44 PM To: Multiple recipients of list ORACLE-L It is possible for a single session to require more than one sort or hash area at a time. For example a 4 table hash could require 3 in-memory hash tables (and therefore use 3 x hash_area_size in the days before pga_agg_target). Possibly your session used 150MB, but had multiple areas open at once, of which the largest was 90MB.- are the definitions of the columns completely unambiguous, or is there room for error in interpreting their use ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: starting/stopping trace in session
The behaviour may be o/s dependent, and Oracle version dependent. One way of dealing with the problem: alter session set tracefile_identifier = 'somthing' closes the current trace file and opens a new one with 'something' in the file name. So every time you want to restart, you can just do the same again with a new 'something'. This also makes it easier to find the right trace file, of course Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, January 03, 2004 4:24 AM Folks, I've noticed -- at least on our 9.2 instances, that it does not seem possible to generate two trace files from the same session. Meaning, if I start a trace in a session, then stop it, use tkprof to run some analysis, and then erase that trace file, a second start_trace does /not/ generate a new tracefile. Is this expected behavior? Adam -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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.net -- Author: Jonathan Lewis 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: pl/sql open cursor question
There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Should we stop analyzing?
It's just like index rebuilding. Too many people do it too aggressively, too often and waste their time and the machine resources doing it for very little benefit. But if you have the time and resources, then it doesn't often do too much damage. However, there are cases where you really do need to get some statistics up to date - particularly for columns like timestamps or sequences that are always increasing in value. NOTE - up to date= correct for the current moment in time fresh= recently acquired To quote one of the people at the UKOUG conference: The statistics on the US_STATES table aren't fresh, because I gathered them 5 years ago but they are up to date, because the number, names, and abbreviations for the states haven't changed recently. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:34 AM Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Should we stop analyzing?
That's (partly) what the 9i dynamic sampling feature is for. And such tables are, of course, going to be GTTs. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:09 PM I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say the table is full but actually is empty takes a little longer when CBO says use indexes; however, if CBO thinks the table is empty and does a FTS when there's actually a million records, well let's just say it takes a while. Hints work sometimes; however, analyzing these table after they are populated and letting CBO do it's job usually works best. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Should we stop analyzing?
There is a hint, and there is a parameter. optimizer_dynamic_sampling = 2 is probably a good way of making sure that all queries involving GTTs get a dynamic sample of 32 blocks on the GTT Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:14 PM to get dynamic sampling one must specify that as a hint .. right? can cbo use dynamic sampling automatically on GTTs? (Hey, it's new year time and some wishful thinking is in order). Happy New Year. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: pl/sql open cursor question
I wasn't thinking of the boundary conditions, I was thinking of the totally different mechanisms that appear because you are running pl/sql rather than (say) a loop in Pro*C that sends a pure SQL statement 1,000 times to the database. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:49 PM There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: max 5% of pga_aggregate_target for a single serial session
It is possible for a single session to require more than one sort or hash area at a time. For example a 4 table hash could require 3 in-memory hash tables (and therefore use 3 x hash_area_size in the days before pga_agg_target). Possibly your session used 150MB, but had multiple areas open at once, of which the largest was 90MB.- are the definitions of the columns completely unambiguous, or is there room for error in interpreting their use ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 8:59 PM Hi, First of all, thank you to all answered my last question. Now I have another question related to my last one. In my system, pga_aggregate_target is set to 3GB and I think a session would have approximately 150MB work area before temp space is needed (5% of 3GB). But I did a test, it only used 90MB max. Anyone has a explanation? Thanks, Roger Xu SQL 1 select sid 2 ,ACTIVE_TIME 3 ,WORK_AREA_SIZE 4 ,EXPECTED_SIZE expected 5 ,ACTUAL_MEM_USED actual 6 ,MAX_MEM_USED max 7 ,NUMBER_PASSES pass 8 ,TEMPSEG_SIZE tempsize 9 from v$sql_workarea_active; SID ACTIVE_TIME WORK_AREA_SIZE EXPECTED ACTUALMAX PASS TEMPSIZE -- --- -- -- -- -- --- --- -- 13 1644005675 29966336 29966336 24232960 91504640 1 470712320 SQL select * from v$pgastat; NAME VALUE UNIT -- aggregate PGA target parameter 3221225472 bytes aggregate PGA auto target2861061120 bytes global memory bound 104857600 bytes total PGA inuse62332928 bytes total PGA allocated 188590080 bytes maximum PGA allocated 188590080 bytes total freeable PGA memory 81330176 bytes PGA memory freed back to OS 1677459456 bytes total PGA used for auto workareas 20333568 bytes maximum PGA used for auto workareas91521024 bytes total PGA used for manual workareas 0 bytes maximum PGA used for manual workareas 0 bytes over allocation count 0 bytes processed 3.4667E+10 bytes extra bytes read/written 0 bytes cache hit percentage100 percent 16 rows selected. This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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.net -- Author: Jonathan Lewis 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: max 5% of pga_aggregate_target for a single serial session
Chapter 9, page 33 - Cat-Hash-strophes in the seminar notes. (The page number may have changed a little). If you have a plan like this, all the leading tables have been hashed into memory at the same time. HASH JOIN TABLE ACCESS (FULL) OF TABLE_4 HASH JOIN TABLE ACCESS (FULL) OF TABLE_3 HASH JOIN TABLE ACCESS (FULL) OF TABLE_2 TABLE ACCESS (FULL) OF TABLE_1 It is true that Oracle only works out the cost of joining two objects at a time - but the actual execution can pass results from stage to step before a step is completed, just like the NL join. In this example, the 10053 trace showed (if I recall correctly): calc 1: join t2 to t1 with swap_join_inputs calc2: join t3 to result1 with swap_join_inputs calc3: join t4 to result2 with swap_join_inputs Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 12:19 AM I've alway understood that joining occurs always in two steps, first two tables, then their result (row source) with next table and so on, so there's no need for more than 2 hash tables for example? Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Should we stop analyzing?
This makes Oracle's position with 10g interesting, given that the default behaviour is to collect statistics all over the place automatically. If it's built in by the supplier, does it count as a change ? Jared's point is valid - in theory, if you keep statistics up to date, then the CBO should produce the optimum plan; if you fail to keep statistics up to date, the CBO plans can cease to be optimal, or may change to become sub-optimal. Moreover, in theory, if a plan changes on a change of statistics, it will be a better, or at worst equal cost, plan with at worst no change in performance. Of course, in the real world, we know that there are various reasons why things go wrong at the boundary points between plans, which is why we like to stick the statistics down well within our preferred boundary. Of course, following your argument about change control to its logical conclusion, since a change in the data may change execution plans, which may introduce untested portions of Oracle code, any data change should also be subject to change control. Despite any whimsical arguments, though, your basic premise is the important one. You need to know the application to do the job correctly. If you know how the data evolves, you will know how to get the minimum amount of work done that allows the optimizer to do its job well. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 11:44 PM At 03:29 PM 12/30/2003, you wrote: But then again, if re-collecting statistics causes your database performance to suddenly become very bad, it seems at first cut there are only two conclusions you can come to. 1) CBO is broke if fresh statistics result in poor performance That a plan changes due to changes in the statistics doesn't mean that the CBO is broke. That's the whole name of the game. The optimizer uses statistics - together with initialization parameters, heuristics and rules - to develop the anticipated best access path. If you change any of these, statistics by analyzing, initialization parameters by changes to the init.ora, or heuristics and rule by upgrading to a new version or applying a patch. I regard any of these changes as serious changes to the database which should go through a test and acceptance cycle. And that includes refreshing statistics. I am constantly amazed how nonchalantly most shops schedule daily, weekly, or whatever analyze jobs even if they batten down the hatches against changes to the application (Don Burleson alluded to that as well). Most of the time the changed statistics do not cause a change in access plans ( which immediately begs the question why do it then ), but ever so often the changed statistics cross a threshold to make a different plan appears to be better. It may be better, or it may turn out to be horrible. My point is: shouldn't that be tested first? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Exporting a partition with transport tablespace
The need to set primary and unique constraints to NOVALIDATE when doing an exchange partition. (It still doesn't help with problems of parent/child tables when dropping partitions though). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 27, 2003 11:49 PM Jonathan, Which exact behaviour were you talking about? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 27, 2003 11:34 PM Good news ! That bug has been fixed in 9.2.0.4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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 single serial session will never get more than 5% of pga
Notes in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 12:39 AM Hi Jonathan, I'm not sure what you really think about this new feature! I view the feature as a positive step forward. Instead of a DBA having to guess an artificially low limit on the sort_area_size because (say) 1200 users might be connected to a machine with 4GB of memory, you now give Oracle a directive like: I have 1.5GB available for sort operations; please be as generous as you can when the demand for memory is low, and ration it carefully when the demand is high. In theory, this ensures that more processes get in-memory sorting because there is a known spare capacity - in practice, the algorithms and options for over-ride will, no doubt, evolve over time. Are you saying that Oracle is capable now of releasing the extra memory something it was not capable of before? Yes If yes, then what does it have to do with the work policy? Nothing - but since the O/S used to take care of the problem by paging out unused memory there was little point in fixing something which wasn't totally broken. On the other hand, if you are trying to operate a policy of maximising the amount of memory you give to a session, based on your estimates of expected data volume, it makes sense to use code that allows a session to de-allocate memory properly. I see this feature useful (not really) for a database application that hosts N concurrent sessions while the amount of available resources is capable of running only N / m sessions. Where m is any integer. In different words, it's the choice when we don't have the required resources to run the app efficiently without restriction to the performance and by using it, it will be able to torture any session that is asking for memory and give it enough guilt not to ask for it again and just try to get the job done by any means :) Now, if the techies on Redwood Shores could get the concepts of hungry and greedy into the code, perhaps we wouldn't have to do any more tuning ever again ;) Regards, Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Exporting a partition with transport tablespace
So your 'novalidate' referred to the without validation clause of exchange; I thought you were referring to the workaround for uk/pk exchanges where even if you did including indexes without validate on the exchange, Oracle still did a horrendous check of the UK and PK constraints by doing a massive MINUS and INTERSECTION across the entire partitioned table. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 11:34 AM Thanks, I haven't hit this problem before. Actually, in my post I recommended to use novalidate option for exchanging required partition back from temporary transport table, that way Oracle won't check the contents in the partition (should be used only when this partition doesn't change in the meantime). Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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 single serial session will never get more than 5% of pga
Notes in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 11:39 PM To be honest I'm not sure why such a feature is available! I have not used it so I'm not really qualified enough to judge it. But in my opinion, a session asks for memory because it needs memory. So is it possible that a session is asking for memory that it does not really need and it can continue running without the requested memory? The answer could be yes, if the more memory means faster (like sorting) and the sort_area_size is too big to be satisfied for all sessions, in this situation the DBA is responsible for the wrong settings. But what if more memory is required like: memory tables, associative arrays, etc and memory was denied? Would the session fail? I think the answer would be YES - Did anybody try this? Memory for pl/sql objects falls outside the scope of the workarea policy If your memory demands for an associative array are excessive you can still grow your pga to extremes. Is the feature available because Oracle sessions don't deallocate the extra memory and by using this feature, it will encourage the sessions that already succeeded in allocating memory that they don't need any more by punishing the ones that ask for more memory now by saying NO? If the don't need the memory anymore they won't have it, because the code now ensures that the memory is releasee - so no question of punishing other sessions. Or is it going to ask the sessions that have extra allocated memory to release it which should be the normal behavior anyway without using any policies? Should as in 'you think this happens already' (it doesn't) or should as in 'the way the code ought to have been written in the first place' (it tried, but most unix libraries didn't implement the calls) ? The database code does need a policy for sorting (for example), otherwise there is no way to determine whether an operation should be allowed to acquire an arbitrarily large amount of memory to do a sort / merge join rather than doing a nested loop join. In the old days, the DBA produced a policy called the 'sort_area_size', which stopped the optimizer from doing an optimum job in a hybrid system, and depended on the operating system handling issues of over-allocation and idle memory. The issues of idle memory and over-allocation are now (largely) back with the database. Regards, Waleed -Original Message- Sent: Friday, December 26, 2003 5:39 PM To: Multiple recipients of list ORACLE-L pga_aggregate_target For special cases like that I would switch the session back to a manual workarea policy and set a suitable sort area. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 9:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Exporting a partition with transport tablespace
Good news ! That bug has been fixed in 9.2.0.4 Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 27, 2003 8:59 PM Exporting a partition with transport tablespaceMake sure that you use novalidate and exchange indexes as well - that way Oracle won't make unnecessary work. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Re: please help with materialized view question
Normally you can get extra tables involved with an MV by creating a Dimension that describes all the relationships between the tables in the MV and the tables outside the MV - but the only times I've done this, the extra tables have always been at the parent end of a parent/child link to a table in the MV. Given the way the 'create dimension' defines levels and hierarchies, I think this may be a requirement; so you may not be able to do what you want to do. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 6:59 PM I figured it out. I need some help with query re-write. Im not sure its possible. My materialized view joins 3 tables on the primary key/foreign key. I have a query that would join that materialized view to a third transactional table, but that join is not on any primary key or foreign key. I cant get it to re-write my query. My query joins 4 tables. 3 are in the materialized view. One is not. is this possible? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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 single serial session will never get more than 5% of pga_aggregate_target
For special cases like that I would switch the session back to a manual workarea policy and set a suitable sort area. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 26, 2003 9:49 PM pga_aggregate_target Is there any way to give say 75% of pga_aggregate_target to a single session? The reason I am asking this is - sometimes we need to build an index as soon as possible and the index creating is the only thing running and other applications are stopped waiting for the index. Thanks, Roger -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: STATSPACK interpretation
Your Parse time is high, and cpu is close to elapsed, so you are almost certainly hard-parsing all the time. This is either a bug, or you have enough parse activity going on, and a small enough shared pool that you keep invalidating the cursor (and it's dependents). Did you report the Invalidations column in your original post. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 11:19 PM FWIW. The database crashed again.I managed to get in a 10466: BEGIN GENERATE_PRODUCT_KEYS (:1,:2,:3,:4) ; END; call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2737 1213.001184.60 0 0 0 0 Execute 2737 28.57 28.08 0 0 0 2737 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 5474 1241.571212.68 0 0 0 2737 Looks like maybe that it's an anonymous PL/SQL call just as Jonathan described? Thanks for the replies. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: STATSPACK interpretation
Thanks for the information - that test has been on my TODO list for the last couple of years. Apart from being useful information, it also tells us that it's not the OP's problem, as the number of different possibilities is too low. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 11:44 PM Thanks, Jonathan. Of course you are right :-) Playing with this a little longer, I can get up to 4 versions (child_number from 0 to 3) of the same pl/sql cursor by changing bind variable sizes. It ceases being sharable when bv size changes from 32 to 33, from 128 to 129 and from 2000 to 2001: 1-32 33-128 129-2000 2001-4000 I wonder if this behavior can be changed by some init settings? Thanks, Boris Dali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: undo and insert
And then there's the previous version of whichever ITL entry gets taken by the transaction doing the insert. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 8:19 PM For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed. So there will still be undo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 25, 2003 1:49 AM An undo segment is used to save the old value of data. For insert operation, there is no old data to be saved. So, there should be no undo generated. Right? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: undo and insert
Just the previous version of the changed columns, plus an overhead of about 80 bytes which relates to ITLs, linked lists, operation descriptions etc. Bear in mind that undo relating to indexes is not the same as undo relating to tables, though. An update to an indexed column results in one index entry being deleted (so the whole index entry is coped to the undo) and another index entry being inserted (which also means the whole (new) index entry being copied to the undo). There is a statistic relating to undo size in v$sysstat/v$sesstat in the most recent versions of Oracle. While a transaction is active, you can track it in v$transaction, and there are two columns in that view giving you information about the undo - used_urec (undo records created) and used_ublk (undo block used). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 8:44 PM I have a related question : What about update? In rollback segment : Will it store the whole row for before image or just the changed column and rowid. Is there a way to get the size of the rollback from some where in the database. or v$ views. Like we can get an idea about redo size from redo log files generated. Thank you -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Flashback queries
Answers in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 24, 2003 9:44 PM It's been awhile since I've looked at flashback queries, and I have a couple questions: * Given an SCN, is there any way to know for certain whether a flashback query to that SCN is possible? Can a non-DBA user determine this? No, not without doing it. As a general principle, you can always be in a position where one querycan flash back to SCN = t1, but another query can't because the two queries require undo from different segments. * Is there an easy way to determine how much undo data my database is (hopefully) retaining? In other words, can I easily determine how far back in the past I *can* go? No. You could mess about with dumping segment header blocks and looking at the 'retention' table, which has a timestamp per extent of the most recent commit recorded against that extent. The highest (most recent) timestamp would be a valid bound for the earliest point in time that you could reach - but depending on luck, size of extents, etc. you might get some queries going a lot further back. Yes, here it is Christmas eve, and I'm working away... Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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.net -- Author: Jonathan Lewis 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: STATSPACK interpretation
I recall James Morle saying something about code not being sharable if the declared sizes of the bind variables don't match. If Informatica is using a 3GL to call anonymous pl/sql blocks with different bind variables every time, perhaps it is causing a bind variable mismatch. As for the 400MB - I've often noticed oddities where a new entry is created, but carries forward a report of the memory requirements of earlier variants, so if you have 10 cursors, they don't report 10 units of memory, but 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 units. It is possible that you are seeing some effect like this. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 5:09 PM Thomas, The version count is the number of child cursors present in the cache for this SQL. The cursor is not being shared for some reason with 456 versions. The 400m of memory seems a bit excessive. There is a script at Jonathan's site with some info about v$sqlarea and a script you can run that looks at the current memory requirements for a SQL statement. http://www.jlcomp.demon.co.uk/sqlarea.html Does the output match what you see in statspack? Also, the number of executions is much lower than the version count, which is rather odd. There's a bug in early 9i versions that would cause this, but was supposed to be corrected by 9.2.0.2. In experimenting with this, I managed to get 4 different sessions to create 2 versions of a cursor. I'm not sure why as it was pl/sql and variables were used for the calling parameters. A 'select * from v$sql_shared_cursor' did not reveal any reason for it. After bouncing the database and trying this again, I couldn't duplicate it. Maybe a couple of things to pursue here, but perhaps not an abundance of help. :( Jared On Tue, 2003-12-23 at 04:44, Thomas Jeff wrote: Jared, Digging into it more, I found out that it's called from an Informatica client. Apparently, the gist of the client-side algorithim is as follows: For each row in (some view) Call generate_product_keys MERGE (upsert) into product table end loop -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: STATSPACK interpretation
Notes in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 8:44 PM Jonathan, Wouldn't bind variable issue that prevents cursor from sharing be visible in bind_mismatch? I would certainly hope so - but I remember playing around with v$sql_shared_cursor when it first came out and find cases where un-shared cursors came up with a full set of N's in the view. How can one simulate this? var v varchar2(1) begin select count(5) into :v from dual; end; / select address, sql_text from v$sql where sql_text like '%count(5)%'; ADDRESS SQL_TEXT -- - 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; -- Change a bind variable size: var v varchar2(30) begin select count(5) into :v from dual; end; / -- same output, no change, both sql and pl/sql wrapper cursors are still shared Nicely done. I think I'd run event 10046 at level 4 as well to get the bind variable dumps and check if the the SQL (or pl/sql) environment was ignoring the MAXLEN value for your variables. There are a few places where 'special optimisations' exist in Oracle's internal coding. You might also try it with the most extreme case - it may be (for example) that Oracle rounds up varchar2() variables to 32 bytes - I'd go for 1 and 4000 - just in case. -- Change a bind variable type: var v number begin select count(5) into :v from dual; end; / ADDRESS SQL_TEXT -- -- 6DE92A74 SELECT count(5) from dual 6DE960D0 begin select count(5) into :v from dual; end; 6DE960D0 begin select count(5) into :v from dual; end; -- ok, here pl/sql parent (dep=0) cursor is no longer shared [EMAIL PROTECTED] select * from v$sql_shared_cursor where kglhdpar = '6DE960D0'; ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 6DE95B54 6DE960D0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 6DE86F94 6DE960D0 N N N N N N N N N N N N N N Y N N N N N N N N N N N N N N N N 2 rows selected. -- yep, bind variables mismatch Thanks, Boris Dali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: RE: Hit Ratio
Why do people still talk about THE buffer cache hit ratio ? There are lots of them. The one you can get from v$sysstat, the ones you can get from v$buffer_pool_statistics, and the ones you can get from v$segstat. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 23, 2003 6:29 PM are there really that many people who use hit ratio? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Upgrading to Oracle 9.2.0.4 - Any pitfalls?
What's the bug relating to 1,000s of Partitions, was it the one to with monitoring, or something more interesting ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 4:49 AM I have said it on this list before, and I will say it again. With Oracle, quality ends with in 4. 7.3.4 8.1.7.4 9.2.0.4 Oracle v9.2.0.4 is fairly stable. I have had to apply only 1, one-off patch related to having 1000's of partitions. You may also want to add the following to your init.ora to prevent a few known bug's -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Finally! The RAID F Simulator is here ( and attachedd )
I don't think you should be playing with this and having fun when you could use the valuable Christmas period for rebuilding all your indexes. NB Joke But since it's Oracle 9.2 that gets mentioned how about trying the new bit functions: select sys_op_rawtonum( sys_op_vecxor( sys_op_numtoraw(6), sys_op_numtoraw(5) ) ) from dual / I won't guarantee that they're faster, and they certainly look messier than the pl/sql function calls, but I think they are SQL built-ins, and therefore may be faster running than pl/sql function calls. sys_op_vecor and sys_op_vecand are also available. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 22, 2003 11:34 AM LOL! Just to add my 2c worthI think you can get a simpler XOR implementation with: CREATE OR replace FUNCTION bitor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS BEGIN RETURN x + y - bitand(x,y); END; / CREATE OR replace FUNCTION bitxor( x IN NUMBER, y IN NUMBER ) RETURN NUMBER AS BEGIN RETURN bitor(x,y) - bitand(x,y); END; / Cheers Connor --- Jared Still [EMAIL PROTECTED] wrote: Dear All, Jesper from the Copenhagen Business School got this crazy idea some weeks ago, and with input from Michael Möller of Miracle A/S, it's my proud honor to present the World's first Oracle-based RAID-F Simulator. It's all fun and games, of course, and Jesper got inspired after reading James Morle's book. He simply copied the 20 pages or so about RAID and handed it to his boss and said that this was the knowledge he needed to understand the way Jesper was thinking about RAID! The boss did read it, and did understand it, and then Jesper went ahead with the RFS project just for fun. With the kind permission of Jared, I have attached it. So this is your Christmas gift from Jesper :-). Best regards, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Hit Ratio
Easy, A new formula for the hit ratio Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 21, 2003 1:19 AM Actually, it isn't SAP. I was simply creating a set of MV's based on SAP tables in another database. The script I was running is used to keep track of how much IO is going on, just to ensure that everything is still working during the build. Once the physical IO exceeds the logical IO, the HR goes negative. I wonder what I need to tune to fix this? Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Re: New TPC benchmarks
I agree with the benefits of being able to wave benchmark papers around and saying But look what they HAD to do ! The line from one of the HP ones (1M tpcc) that I really liked was: quote Most of the space on the arrays in the tested system was unused during the performance tests, but is available to satisfy the 8-hour log and 60-day storage requirements. unquote If you want performance, you put just a thin stripe of active data on any one of those 180GB disc drives that the accountants love to buy because they're so cheap. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 1:29 AM what's really helpful about these, are the server tweaks made (if you deploy on win32). check out http://www.tpc.org/results/FDR/TPCC/dell_2650_261103_fdr.pdf Pg 170 - there's a list of all of the services that are disabled/stopped - 24 in all. Pg 224 - the section of the MS diagnostics report lists that provides a detail list of the services. lots of unneccesary features. Pd -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: New TPC benchmarks
Not just hash clusters, single-table hash clusters with user-defined, and very carefully designed hash key. Not something you can usually get away with in a dynamic table of 19 billion rows. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 16, 2003 6:49 AM Yes, both DB2, SQL Server and Oracle arrive in special editions for these benchmarks. Note also that no indexes are used - Oracle uses hash clusters, for instance. No indexes in sight. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Re: 24 x 7 x 365
There is a problem with this approach that may only become apparent at high concurrency. Since you are operating with two-phase commits, you may come up against the case where writers block readers. Your client issues a commit to both servers. Each server get the PREPARE message, and when both have responded, each gets the COMMIT message. Between the PREPARE and COMMIT, any blocks updated in the transaction cease to be available to ANY query that started after the PREPARE arrived. For the (hopefully) brief interval between the prepare and commit, neither database knows whether the transaction as a whole has prepared or committed, so any process that wants to see the current version of the data has to wait until there is a known current version. In a high-concurrency system, a problem that used to be buffer busy waits on updates only can turn into enqueue waits on updates and queries. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 13, 2003 8:19 PM Yep, I also think so. I'm currently developing a small prototype for this kind of transparent proxy, which I'll post here when it's stable... Tanel. Tanel, I think this is a good solution, provided the application can handle two phased commit protocol across both the databases, else there could be orphan records on one or both these databases. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Oracle Data Guard
Can you clarify a couple of points for me. The SDU (session data unit) is presumably the packet size that the Oracle client and server want to pass back and forth - which is presumably the maximum size the one synchronous dialogue unit will be. The TDU (transport data unit) is presumably the predicted size of the transport maximum unit of data transfer (MTU). a) Why does Oracle need to know anything about the underlying transport mechanism ? b) If I set the SDU to the largest legal value (possibly 32K, perhaps 64K) the server task switch will occur after building and sending that packet - is there any good reason why I shouldn't do that. After all, if the transport simply accepts the 64K packet and gets it to the other end of the wire (not yet to the client session, just to the receiving transport layer) as rapidly as possible does it matter to Oracle whether the transport is using 1.5K or 8K packets. The fact that the transport layer doesn't have to work its packet synchronously means that some overheads have disappeared as far as Oracle is concerned. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 3:44 PM Hi, Guang, Look up SDU and TDU in Oracle documentation Network configuration. You set them in tnsnames.ora and listener.ora, not sqlnet.ora. protocol.ora allows you to modify some procotol-specific parameters. In addition, in your client application, you can choose a sensible array fetch size, such as arraysize in sqlplus (in fact, sqlplus arraysize changes more than just network data chunk size). You can't magically increase the network transfer rate by lowering network latency. But you can indirectly increase the rate by other means, such as buffering slightly more data in one chunk. Yong Huang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Oracle Data Guard
But the last time I looked at it, you had to enable supplemental logging at the database level if you wanted to use logical standby. Two side effects - 1) As you said, you need a unique/primary key, and database supplemental logging copies such a key into the redo for every change to a row: but if there is not uk/pk, then the whole row is copied. 2) The copy into redo is engineered by copying into the UNDO first - and since changes to GTTs are recorded into the UNDO, this means you get an extra volume of UNDO, hence REDO on all changes to 'supposed to be low-cost' GTTs. The other feature of logical standby is that Oracle scrapes the redo log to generate LCRs (logical change records) which are then checked against your 'Streams rule-sets' - and then written into the local database for propagation to the remote via AQ mechanisms. The overheads could be quite significant. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 9:59 PM Hi Vi, Rows NEED unique identification. So, if there are bunches of raw data with no unique identifier whatsoever (remember, rowid is not allowed) LSB can't generate a where-clause what row to update or delete on the SB database. It's generating SQL based on redolog info, and has to come up with an UPDATE table SET WHERE unique id = unique id. The unique id may be a multi-column key. There is an escape. Enabling supplemental logging can add extra info to do the unique identification, when no usable keys are available. This will cause some extra logging to be generated, there ain't no such thing as a free lunch. For detailed information read chapter 4.1.5 4.1.6 in the Oracle Data Guard Concepts and Administration manual, part no. A96653-02. regards, Carel-Jan At 15:54 8-12-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: analyze problems
Take a look at user_tab_columns - your choice of: method_opt = 'for columns' On a quick test on 9.2.0.3 this seems to leave the columns with null statistics. (check select column_name, num_distinct from user_tab_columns ) In passing, computing histograms with 75 buckets for all indexed columns is a strategy that probably needs refining. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 9:29 PM Hi, I'm in the middle of migrating oracle 7.3.4 to oracle 9.2.0.4 In process of testing we encounter a big query that is now taking full table scans Where it used to take indexes. When we compare plans this is evidently so, Optimizer_mode is on both choose, tables and indexes are analyzed When I add a rule hint on 9.2.0.4 plan it takes the indexes On 7.3.4 we use for analyzing : analyze table, now I tried using exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME = 'VRIJ_UIT',CASCADE = TRUE, METHOD_OPT = 'FOR COLUMNS'); exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME = 'CONTRACTEN',CASCADE = TRUE, METHOD_OPT = 'FOR COLUMNS' ); exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME = 'FIN',CASCADE = TRUE, METHOD_OPT = 'FOR COLUMNS'); rem for all indexes columns size 75 exec DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME = 'VRIJ_UIT', CASCADE = FALSE, METHOD_OPT = 'FOR ALL IND EXED COLUMNS size 75'); Etc... Are there any known do and don'ts concerning dbms_stats which might explain this? Is it better to stay on analyze table ? Can I expect lot's of problems in execute plans when migrating? Any answers, tips and trics are appreciated. Details: HP-UX11.11, Oracle 9.2.0.4 Regards, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: sequences and cursors
That won't help, as the cursor would still
be held open in the pl/sql cursor cache -
despite the explicit close.
It's also more efficient to use the implicit
cursor in pl/sql for a single row fetch in
the user's version of Oracle.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 09, 2003 7:39 PM
What tool are you using? HAve you considered putting select from the
sequence in an explicit cursor, open it, fetch it and close it again?
What Have in mind is something like this:
declare
cursor csr is
select sai.nextval from dual;
num integer :=0;
ind integer :=10;
begin
while (ind=0) loop
open csr;
fetch csr into num;
close csr;
dbms_output.put_line('Sai is:'||num);
ind:=ind-1;
end loop;
end;
/
Here is the output:
QL /
Sai is:13
Sai is:14
Sai is:15
Sai is:16
Sai is:17
Sai is:18
Sai is:19
Sai is:20
Sai is:21
Sai is:22
Sai is:23
PL/SQL procedure successfully completed.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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: sequences and cursors
Run your test case, and check the contents of v$open_cursor. Unless my memory has got it backwards, the pl/sql cursor cache is counted towards max_open_cursors, but the cursors that have been held open by the 'dirty tricks department' are closed as required if the limit is reached: (so should not be responsible for ORA-01000 anyway). cursors held open as session_cache'd cursors are counted independently of max_open_cursors - so should not cause an ORA-01000 Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 2:09 PM It might be held in the cursor cache, it may even be held in session cursors cache but it will not be counted as an open cursor. My suggestion had diagnostic purpose only. The problem is, probably, with the tool which explicitly closes cursors too frequently and insufficiently sized shared pool which throws cursors out soon after they're closed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Re: Little competition
I managed to emulate Don Burleson's problem: SQL create table test_table 2 (c1 number) 3 storage (pctfree 20 pctused 30); storage (pctfree 20 pctused 30) * ERROR at line 3: ORA-02143: invalid STORAGE option But when I read the error message, and corrected the error that was reporting, the statement worked: create table test_table(c1 number) pctfree 20 pctused 30; Table created. = Richard was kind enough to refrain from posting the next paragraph in the tip: quote This could be a serious issue for the Oracle professional unless they remember that locally-managed tablespaces with automatic space management ignore any specified values for NEXT and FREELISTS. end quote There is another error here. For a bonus 10 points can anyone spot it ? Hint - try the following in a tablespace which is locallally managed, with automatic space management, and either system managed or uniform sized extents of no more than 1 M. create table test2(n1 number) storage (initial 1M next 2M pctincrease 100 minextents 3); Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 11, 2003 1:14 PM again, what is so bad with what burleson said about the pctfree and pctused? From: Richard Foote [EMAIL PROTECTED] Date: 2003/12/11 Thu AM 06:39:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Little competition Little competition for you all :) It's a two part question: a.. What's wrong with the following piece of expert analysis ? b.. Which well know Oracle Guru published this (and continues to display it on his web-page) ? Sadly, Oracle9i doesn't allow you to specify the value for PCTFREE if you're using automatic space management. This is a serious limitation because Oracle9i can't know in advance about the amount of VARCHAR expansion in a table row, leading to excessive row chaining and poor access performance. SQL create table 2 test_table 3 (c1 number) 4 tablespace 5 asm_test 6 storage 7 ( pctfree 20 pctused 30 ) 8 ; ( pctfree 20 pctused 30 ) * ERROR at line 7: ORA-02143: invalid STORAGE option However, here's an important point. While Oracle9i rejects the PCTFREE and PCTUSED parameters with locally managed tablespaces with automatic space management, it does allow you to enter invalid settings for NEXT and FREELISTS settings -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: leaf node 90-10 splits
The failure to do the 90/10 split is a bug (it worked properly in 8.1) which is currently being investigated. I can't remember the number, but I passed it to Oracle some time ago. As to the original question - no idea, 100/0 seems to be the correct strategy; however, there was an earlier version of Oracle where I did some tests that showed Oracle doing block splits which whose position seemed to be affected by the relative position of the new entry in the block. But I can't find the tests, and I now wonder if it was just the special split to optimise branch compression that Steve Adams worked out a little while ago. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 9:24 PM Hi! SQL select * From v$sysstat where name like '%split%'; STATISTIC# NAME CLASS VALUE -- --- - -- -- 195 leaf node splits 128612 196 leaf node 90-10 splits 128209 197 branch node splits 128 3 I did a little test few days ago (using stats treedumps): If you insert an equal or larger key to the current max value in a full leaf block *within the transaction which filled the block*, just a new leaf block is added to index and leaf node 90-10 splits statistic is incremented. If you commit in the meantime, before overflowing the block, then the leaf block is split 50-50 and leaf node splits stat is incremented. So, Oracle 9.2 cares about transactions as well, in addition to checking key values... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Expense of 'over ... partition by'
Jared, I think what you've discovered is just a repeat of the fact that different functionality is appropriate in different circumstances. Imagine replacing your v$sql_workarea_histogram with a chunky SQL statement that crunched through a massive table producing a small result set. In those circumstances, your analytic approach would sort a small set twice having done one big crunch. With the group by approach, you would have to crunch the big data set twice. I know which option would be cheaper. (You then have to wonder whether you could produce the small result set using subquery factoring 'with subquery' as another possible optimisation strategy). BTW - did you notice how Oracle didn't do a sort for the order by in the GROUP BY example, because the optimizer could infer that the data had already been ordered by the GROUP BY ? That's the reason why your GROUP BY example did less sorting. (I'm not sure you need the GROUP BY, though I may be missing something). BTW-2: in the analytic clause, the (partition by 1) is not necessary, you can write: , sum(optimal_executions) over ( ) Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 05, 2003 12:24 AM While working on some scripts to monitor PGA usage on 9i, I came across something interesting while experimenting with different forms of SQL. I have recently been forcing myself to make use of 'OVER..PARTITION BY' in SQL so as to be more comfortable in using it. Can't add new tools to the box until I know how to use them. :) Yes, I know I should have been using them long ago. Anyway, I thought it might be interesting to compare the forms of SQL with and without the use of OVER...PARTITION BY. This SQL can be run on any instance that has PGA_AGGREGATE_TARGET set. Here is the SQL using OVER: select low_optimal_size_kb , high_optimal_size_kb , optimal_executions , onepass_executions , multipasses_executions , total_executions , optimal_executions / sum_optimal_executions * 100 pct_optimal_executions from ( select low_optimal_size/1024 low_optimal_size_kb , (high_optimal_size+1)/1024 high_optimal_size_kb , optimal_executions , onepass_executions , multipasses_executions , total_executions , sum(optimal_executions) over ( partition by 1 ) sum_optimal_executions from v$sql_workarea_histogram where total_executions != 0 ) a order by low_optimal_size_kb / and here is the SQL using good old GROUP BY select low_optimal_size_kb , high_optimal_size_kb , optimal_executions , onepass_executions , multipasses_executions , total_executions , optimal_executions / sum_optimal_executions * 100 pct_optimal_executions from ( select h.low_optimal_size/1024 low_optimal_size_kb , (h.high_optimal_size+1)/1024 high_optimal_size_kb , h.optimal_executions , h.onepass_executions , h.multipasses_executions , h.total_executions , hs.sum_optimal_executions from v$sql_workarea_histogram h, ( select sum(optimal_executions) sum_optimal_executions from v$sql_workarea_histogram ) hs where h.total_executions != 0 group by h.low_optimal_size/1024 ,(h.high_optimal_size+1)/1024 , h.optimal_executions , h.onepass_executions , h.multipasses_executions , h.total_executions , hs.sum_optimal_executions ) a order by low_optimal_size_kb / The new version is significantly simpler. It then seemed that it might be interesting to compare the performance and scalability of the two methods. This is where it gets interesting. 16:10:47 rsysdevdb.radisys.com - [EMAIL PROTECTED] SQL @run_stats NAME RUN1 RUN2 DIFF -- -- -- LATCH.lgwr LWN SCN1 0 -1 LATCH.mostly latch-free SCN 1 0 -1 LATCH.undo global data1 0 -1
Re: Analytic bug in 9.2.0.4
Fixed in 10. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 05, 2003 12:29 AM While playing around with SQL for some PGA scripts, I managed to create some SQL that will consistently cause ORA-600 [kkqwrm_noref: COLFDNF set] This appears to be Bug # 2507421, which was supposedly fixed in 9.2.0.3. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: rebuilding indexes - sure to cause a ruckus
Comments in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 9:04 PM Instead, a long stress test has to be done, e.g. running 10 millions of continous transactions and queries (simulating real life). No ! No ! No ! No ! real life is what happens outside Oracle databases. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: ** can two processes use the same rollback extent
A single block can only be 'owned' by one transaction at a time, but when a transaction commits, it may put its last undo block into that segment's 'free pool' list if there is a lot of free space left in the block so that it can be made available for new transactions . (Gaja Vaidyanatha quoted 400 bytes as the limit). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 3:59 AM i can answer the first part of your question.. YES, two transactions can write on the same extent of an RBS, i think the restriction is at the block level, this was true until 8i, might have changed in 9 + -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: cache buffer chains latch
My current understanding is: a)For normal buffer gets, the latch is acquired, the chain is scanned the buffer is pinned the latch is released the buffer is used -- the latch is acquired the pin is dropped (although the pin may be held for the duration of the SQL or pl/sql call if Oracle expects to revisit the buffer) b)Consistent gets - examination the latch is acquired the chain is scanned if the buffer can be found it read the latch is dropped Iin case (b), Oracle can make use of a shared read latch - where the underlying CPU supports it. But I may be wrong - especially about (b), and I haven't identified all the cases where an examination is legal. Question 2 - the best answers come from Steve Adams. But in summary, think library cache latch children - you may need to validate several objects in the library cache at once, and they could be covered by different child latches. Latches have a level#, from 0 to 9, and there is a precedence of latch acquisition across levels that is designed to stop latch acquisition deadlocks (again see S.A.). The holding of multiple latches also explains the presence of the column named (something like) waits_holding in v$latch. You have waited for this latch whilst holding another latch. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 4:19 AM Hi All, My system is suffering in cache buffer chain,I found the reason. My general questions is 1) When the process holding the latch how long the latch will be held ,until the hase chain is read or the process goes to the particular block and return the rows(output) ? 2) When the situation will occur for the process to hold two latches(different or same) simultaneously ? Please reply. Syed. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: select via dblink does not use index
What are the Oracle versions, settings for optimizer_mode and full execution plans. Can you clarify Remote table is a view with dblink. Do you mean your query references a local view which is a select from a remote table; or does your query reference a view at a remote site which is a simple select from a table at that site. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 7:49 PM I did a select like: select name from local_table , remote_table where local_table.account = remote_table.account. Remote table is a view with dblink. I select about 100 records out of about 1M records at the remote db. I found out that oracle does full table scan at the remote site. I will welcome ideas how to make oracle use the index on the remote side. Yechiel Adar Mehish -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: dbms_stats
I think there are various little (hah!) details that
change with versions, but unless your analyze
command was:
analyze table T estimate statistics sample 10 percent
for table
for indexes
for all indexed columns size 2
;
(I may have the sample clause in the wrong place).
then the two commands will be generating different
things. The default for analyze columns is 'size' 75,
which gives Oracle much better precision on range
scans with literal values than 'size 2' - which may be
a significant part of your problem.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 9:04 PM
Hello,
Oracle 8.1.7.4 on HP-UX 11i
A week ago, we replaced 'analyze table ... estimate statistics sample 10
percent' with dbms_stats.gather_schema_stats('x', estimate_percent=10,
cascade=true, degree=4, method_opt=''for all indexed columns size 2')
Performace is good against partitioned tables but not for non-partitioned
tables.
Saw a note in Metalink that its better to do the above with 'cascade=false'
and then do a gather_index_stats separately.
In my tests, I see that 'analyze' makes the CBO use an index while
dbms_stats is making the CBO to use a FTS instead.
Have you faced any similar issues?
TIA
Prakash
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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: Plan stability
Notes in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:04 AM A properly formed hint will cause the CBO to consider the hinted path to be less costly than it would otherwise consider it, but hints do not force a query to use that particular plan. Hints do not change the cost of a query - they simply dictate that Oracle should take the stated path and not consider alternatives which would otherwise be possible at that point in the plan. For a moderately complicated query, you'd potentially need a fair number of hints to get things working the way you want. If the statistics of the table changed, though, your carefully hinted query might well decide to take another path. Even if things work, adding hints-- particularly adding multiple hints-- to a query significantly increases the maintenance costs as future developers have to unravel what all the hints are doing, why they're doing it, whether any hints need to be changed as a result of the modifications, whether future changes to the CBO or new Oracle functionality should cause the ideal plan to change, etc. (a) One would hope that a hinted query would also have some documentation describing the expected execution plan, and the reaons why it was considered desirable - so the maintenance issues should be moot. (b) Plan stability exists to stop execution plans from changing - so any SQL with a plan should, by your comment above, require it's stored outline to be put under review in case any new functionality should be applied and the outline changed. So, again, your point is not entirely sound. If you want to force Oracle to use a particular plan, plan stability is orders of magnitude easier! Only if you happen to have the licence for the 9.2 performance tuning pack, and can use the dinky little GUI for drawing and manipulating outlines. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Plan stability
The biggest problem with hints is that you cannot specify a full set - in particular there is no effective way to handling unnesting of subqueries. For simple cases, you can put the tables in the main query in the 'correct' order and use the ORDERED hint, then name the indexes and join mechanisms you want. But if you have any subqueries things break if Oracle unnests, so you need to hint the subqueries with NO_UNNEST. On the other hand, unnesting may be desirable, in which case you have to rewrite the query in an unnested form, otherwise the unnested tables go to the top of the FROM list, and the ORDERED hint applies incorrectly. There is no easy option until you get to v10 - where a couple of hint enhancements make it much simpler to specify your requirements. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 8:44 AM Hi, my question is about the same, but more general. How can i force Oracle to use my prefered way of explain plan and not use CBO's. I mean, apart from stored outlines, it somehow seems to complicated. I would like to say what order and join types it should use. But, try as I might, I many times cannot force Oracle to use my way, even though I know it is possible, for I saw this kind of explain plan for that specific query.. I tried to use hints like ordered and use_hj etc. Can someone give some examples of full set of hints for some simple queries? Thx, rw -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Oracle websites
I've just bought a new Windows XP machine, Got all the latest downloads on the O/S. Installed a firewall. Got Norton Anti-virus loaded and up to date. Which bit of code is stopping me from getting to Metalink and the Oracle websites ? The pages no longer exist according to my M/S IE. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
I have to admit that I wasn't thinking about replying to your comment when I sent this email. However, I think you are correct - there is an effect of extra items not being releasable from the shared pool when cursor_space_for_time is true. (From memory of one of Steve's seminars, it is the Heap 6 that ceases to be freeable). Whether this eliminates the creation and dropping of an x$kglpn entry I haven't yet checked. Just as a quick test of what sorts of benefits could be achieved on latches by setting this parameter, I ran up a quick pl/sql loop and got the following results - (included in-line in case attachments get rejected, so the formatting is probably rubbish). (Version 9.2.0.3) Comments on cursor_space_for_time = true Setting this parameter does change the latching in the library cache, but does not achieve total elimination. Significantly, more benefit comes from session_cached_cursors than cursor_space_for_time. Tested: --- declare m_junk varchar2(20); begin for i in 1..100 loop execute immediate 'select ''abc'' from dual' into m_junk; end loop; end; / Environment changes: session_cached_cursors = 0 / 100 cursor_space_for_time = true / false Counts taken from: -- v$latch v$library_cache Latchcs4t = true cs4t = false --- (Sess cache = 0) shared pool 379 492 library cache9251035 library cache pin466 674 library cache pin alloca 440 440 (Sess cache != 0) shared pool 102 203 library cache310 416 library cache pin209 415 library cache pin alloca0 0 CacheGetsHits PinsHits - cs4t=true - SQL Area 108 107 225 223 (sess cache = 0) SQL Area1 1 101 101 (sess cache != 0) cs4t=false - SQL Area 108 107 333 331 (sess cache = 0) SQL Area1 1 208 208 (sess cache != 0) == The execute immediate is deliberate to emulate a common coding paradigm, with explicit parse calls. You'll notice that the most significant fraction of the reduction in latch costs comes from the switch to using session cached cursors. But there is a further benefit from the cursor_space_for_time - and the most obvious change is in the 'library cache pin' - but it doesn't drop to zero. The point I was trying to make earlier, though, was about the general issues regarding latching in this area - even when you have a fantastically perfectly written application that only ever does 'parse once execute many' you can still get library cache (etc.) latch contention purely on extreme numbers and concurrency of execution. For those who haven't found it yet, there is a paper by Bjorn Ensig on OTN about (a.o) the cursor_space_for_time parameter and what it's doing to the shared memory. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 9:59 AM Jonathan, I've understood that when cursor_space_for_time is true, then unpin is only done when cursor is closed, thus there's no need for pinning/unpinning for every execution of a cursor. This should reduce hits on library cache latches since pinning is not done so often? Hermant, I've sometimes seen this parameter recommended when having library cache latching issues in large Apps installations, I have not used it myself in Apps though. Also note, that cursor_space_for_time requires 50-100% larger shared_pool (and some more private SQL area in PGA, shared_pool or large_pool, depending on configuration), since shared cursor's frames can't be aged out from library cache until all corresponding cursors are closed (normally if there's not enough free memory in shared pool when parsing a new statement, some unpinned, but open cursors can be thrown out, but with cursor_space_for_time they can't be). So, if you don't find any better cure and decide to use this parameter, you should first increase your shared pool quite much to avoid ORA-4031 errors and then start reducing in small amounts, based on v$librarycache, v$rowcache, x$kghlu and shared
Re: Finding SID of current session
Universal, and works with 7.3.4 - which is still in common
use - but is deprecated in favour of:
select sid from v$session where audsid =
sys_context('USERENV', 'SESSIONID' );
which still doesn't help if you have several SYS sessions
running, as the sessionID for SYS is always zero.
The v$mystat option is the newer 'universal' except there
is a preferred call to dbms_support.mysid (on the platforms
which have, and have installed, dbms_support (dbmssupp.sql) -
and the package only does a
select sid from v$mystat where rownum = 1;
anyway.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 8:09 PM
I believe
select sid from v$session where audsid = USERENV( 'SESSIONID' );
is a universal way to determine one's current internal SID based on the
sessionid returned by userenv.
Adam
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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: raw traces - EXEC: c=10,000 e=40
On your platform, c is measured in centiseconds (10,000 microseconds) whilst e has a notional accuracy to the microsecond. So the extremes are to be expected. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 8:49 PM Reading Cary's book I understand that c and e are measured via different system calls (haven't truss'ed [well tusc'ed] them yet - I am on HP-UX 11.11), but would anybody know what the reasonable upper limit of c-e might be? I am looking at the trace file where c is more than two orders of magnitude greater than e, which make me wonder if I a have some anomaly on my system Some examples: EXEC #98:c=1,e=433,p=0,cr=2,cu=2,mis=0,r=1,dep=1,og=4,tim=1777312113968 EXEC #110:c=1,e=390,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=4,tim=1777312181650 PARSE #103:c=1,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1777314524922 Oracle 9.2.0.4.0 on HP-UX 11.11 Thanks, Boris Dali. __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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.net -- Author: Jonathan Lewis 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).
