Re: Suggestions Needed: Latch free - library cache
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. 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. 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).
Re: Suggestions Needed: Latch free - library cache
It would be good if Oracle could break SQL parse down into not just hard and soft, not just hard-soft-softer (Tom Kyte's wording), but different levels. Oracle may have to work slightly harder to update these new statistics but the benefit for OLTP databases is huge. 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. In fact, these two types of changes may be broken down to two statistics. Looking at the columns in v$sql_shared_cursor, I'm afraid we may need much more statistics? 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. Yong Huang Jonathan Lewis wrote: ... 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 ... NOTE: This description is probably not complete and I'd welcome any corrections and refinements that anyone can supply. __ 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).
Re: help with estimate row count from asktom
One minor caveat about setting timed_os_statistics. On Solaris, if you set timed_os_statistics to non-zero, microstate accounting at the OS level is enabled for the server process. Common practice is to leave it off for performance reason. But I've never seen experimental data proving the negative effect of turning it on. Yong Huang Tanel Poder wrote: Hi! Statistics level ALL means TYPICAL + row source execution stats + timed_os_statistics. If you want to switch to ALL for performance reasons, you can switch only row source stats on with parameter setting _rowsource_execution_statistics to true (on session level). But I doubt it'll help in current case anyway. Tanel __ Do you Yahoo!? Find out what made the Top Yahoo! Searches of 2003 http://search.yahoo.com/top2003 -- 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).
Correct way to accuse BCHR tuning method (Was: Hit ratio)
[This message is not technical, but educational. Readers interested in technical info only may want to skip] Hi, Cary and Gopal, My last message is misunderstood. Nowadays most DBAs that still use buffer cache hit ratio as a primary performance tuning method are those that rarely browse public forums. When we convince them that's a wrong method, we should not say Look. I can bump up BCHR to an arbitrary value. If he doesn't think, he'll say Indeed. If I can get any value, it must be rubbish. But if he's a logical person and thinks for a few minutes, he'll say It's unfair to run that choose_a_hit_ratio program to get an arbitrary hit ratio and say the method is wrong, because you can use the same logic to write a program to get an arbitrary library cache hit ratio, OS in-core inode cache hit ratio or directory name cache hit... My last message is not meant to revive the outdated and probably never correct tuning method. Instead it's meant to let oracle-l members know that when you need to convince those DBAs that still use that method, you need to accuse the BCHR method for correct reason, namely, BCHR does not contain sufficient information for tuning, not because you can raise its value by constantly scanning a table in Oracle; you won't be able to convince some stubbon DBAs who enjoy thinking in a quiet place. I agree that It's not the ratio that needs condemning, it's the advice about... What I disagree is the wrong educational tool people on public forums have recently used again and again to show the inadequacy of the BCHR tuning method. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
RE: Hit Ratio
Hi, Carel-Jan and Rich,
Connor's script to bump up buffer cache hit ratios is meant to be a humor. Only
if you carefully comtemplate it will you see that there's no relevance of the
fact that you can get any hit ratio to the fact that hit ratios are
insufficient in performance tuning.
It would be equally easy to write scripts to bump up some wait event times. If
you need very long db file reads, create a big table and keep scanning it. If
you need long enqueue waits, create a table and insert a row. Create 10 or 100
sessions (depending on your patience) and delete from that table and wait. The
fact that you can get arbitary wait times does not reduce the efficacy of wait
event interface as a performance tuning tool.
Buffer cache or library cache hit ratios are not sufficient, very insufficient
used alone, to tune the database. The reason is that they don't contain enough
information to tune the system with. This is the only reason we should not
solely rely on them; in fact, not using them at all doesn't hurt much. The
reason is not that we can get any value we want by playing pranks.
Hit ratios are still used in other performance tuning and not condemned.
Although in UNIX performance tuning one looks at absolute numbers such as scan
rate, CPU usage and netstat output more often, hit ratios in some sar output
are still occasionally used. Most ratios could still be distored by a rogue
user repeatedly doing, say, find / for inodes or find / -exec grep SomeThing
{} \; for page cache.
In any tuning practice, Oracle or OS, artificially distorting usage patterns
invalidates your numbers even if you're using a well respected tuning method.
So only play pranks on a play box, not production.
Yong Huang
At 11:14 22-12-03 -0800, you wrote:
My BCHR is currently 96.62%. In the past, it was normally over 99%. What
should I do?
I'll be waiting for Mladen's reply... :)
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
Cary's book), and download one of the fabulous BCHR enhancement scripts.
Especially when your bonus depends on it, this is a good time to perform
some BCHR tuning.
Regards, Carel-Jan
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
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).
Re: Hit Ratio
As a friendly reminder, when debunking myths, I suggest we keep sober and never go overboard. The recently popular formula to get an arbitrary hit ratio is not what a database in normal usage naturally gets. Unless a mischievous developer plays a prank, hit ratios are still useful to some extent in checking database health, although other indicators such as wait events should be given a greater weight. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
Risk of knowing password hash value (Was: OEM permissions)
Hi, Gregory,
I only have access to Oracle 9.2 on my laptop. Here's my test. I have ORCL and
AUX1 databases, the latter created by RMAN DUPLICATE some time ago. I logon
AUX1 as SYSTEM. Set SYSTEM password hash value to the same as in ORCL. Create
link L to ORCL without password. Selecting from a table in ORCL @L (i.e. select
* from [EMAIL PROTECTED]) throws ORA-1017 invalid username/password.
Alternatively, I logon as SYS and create a procedure owned by SYSTEM, with one
line execute imediate('select count(*) from [EMAIL PROTECTED]'). When I execute
system.this procedure as SYS, I get ORA-1005 null password given. (I could
use DBMS_SYS_SQL but using the execute immediate trick obviates the need to
remember the syntax in that undocumented package).
If I use connect to current_user to create the link, I always get ORA-28030
Server encountered problems accessing LDAP directory service.
Could you try on your databases and show how you do it? As I said, this may be
a security problem. I'm just too ignorant of it and can't reproduce it for now.
Yong Huang
Norris, Gregory T [ITS] wrote:
There's no reason I can see that he couldn't create the dblink first, and then
reset the password using the encrypted value. Alternately, the dblink could be
created using the DBMS_SYS_SQL package... no knowledge of the current password
required.
create database link foo
connect to current_user
using 'bar';
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
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).
Re: Risk of knowing password hash value (Was: OEM permissions)
Jared,
I see you log out and log back in as SYSTEM to DB2. But how do you know the
password for SYSTEM to log back in with after you change it?
What if you don't log out? When I tried that (i.e. not logging out), I got
ORA-1017.
Yong Huang
--- Jared Still [EMAIL PROTECTED] wrote:
Environment:
DB1: RH 8.0 with Oracle EE 9.2.0.4
DB2: Win2k SP3 with Oracle EE 9.2.0.1
SYSTEM user on each database initially have different passwords.
It goes something like this:
DB1:
select password from dba_users where username = 'SYSTEM';
Let's say the result is 'AC424SDK4398'
DB2:
Logon to DB2 as SYSTEM.
alter user SYSTEM identified by values 'AC424SDK4398';
create database link systemlink using 'DB1';
Logout, and log back on to DB2 as SYSTEM.
select count(*) from [EMAIL PROTECTED];
Works for me in this environment. DB2 is compromised.
HTH
Jared
On Mon, 2003-12-22 at 08:29, Yong Huang wrote:
Hi, Gregory,
I only have access to Oracle 9.2 on my laptop. Here's my test. I have ORCL
and
AUX1 databases, the latter created by RMAN DUPLICATE some time ago. I logon
AUX1 as SYSTEM. Set SYSTEM password hash value to the same as in ORCL.
Create
link L to ORCL without password. Selecting from a table in ORCL @L (i.e.
select
* from [EMAIL PROTECTED]) throws ORA-1017 invalid username/password.
Alternatively, I logon as SYS and create a procedure owned by SYSTEM, with
one
line execute imediate('select count(*) from [EMAIL PROTECTED]'). When I execute
system.this procedure as SYS, I get ORA-1005 null password given. (I
could
use DBMS_SYS_SQL but using the execute immediate trick obviates the need to
remember the syntax in that undocumented package).
If I use connect to current_user to create the link, I always get ORA-28030
Server encountered problems accessing LDAP directory service.
Could you try on your databases and show how you do it? As I said, this may
be
a security problem. I'm just too ignorant of it and can't reproduce it for
now.
Yong Huang
Norris, Gregory T [ITS] wrote:
There's no reason I can see that he couldn't create the dblink first, and
then
reset the password using the encrypted value. Alternately, the dblink
could be
created using the DBMS_SYS_SQL package... no knowledge of the current
password
required.
create database link foo
connect to current_user
using 'bar';
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
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).
Re: Duplicating with rman
Hi,
I'm not sure why your RMAN output says
LOGFILE
GROUP 1 ( '/z01/oradata/DEVL/redo_01a.dbf',
'/z02/oradata/DEVL/redo_01b.dbf',
GROUP 2 ( '/z01/oradata/DEVL/redo_02a.dbf',
Where's the ) reuse shown in your RMAN script? Are you sure the script you
showed here was run?
Yong Huang
you wrote:
RMAN-06162: sql statement: CREATE CONTROLFILE REUSE SET DATABASE DEVL
...
LOGFILE
GROUP 1 ( '/z01/oradata/DEVL/redo_01a.dbf',
'/z02/oradata/DEVL/redo_01b.dbf',
GROUP 2 ( '/z01/oradata/DEVL/redo_02a.dbf',
'/z02/oradata/DEVL/redo_02b.dbf',
...
RMAN-06136: ORACLE error from auxiliary database: ORA-02236: invalid file
name
RMAN-06097: text of failing SQL statement: CREATE CONTROLFILE REUSE SET
...
LOGFILE
GROUP 1 ( '/z01/oradata/DEVL/redo_01a.dbf',
'/z02/oradata/DEVL/redo_01b.dbf',
GROUP 2 ( '/z01/oradata/DEVL/redo_02a.dbf',
'/z02/oradata/DEVL/redo_02b.dbf',
...
RMAN script is:
run {
allocate auxiliary channel d1 type disk;
set until logseq 5115 thread 1;
set newname for datafile 1 to '/u03/oradata/DEVL/system_01.dbf';
...
duplicate target database to DEVL nofilenamecheck
logfile
group 1
('/z01/oradata/DEVL/redo_01a.dbf','/z02/oradata/DEVL/redo_01b.dbf') reuse,
group 2
('/z01/oradata/DEVL/redo_02a.dbf','/z02/oradata/DEVL/redo_02b.dbf') reuse,
group 3
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
--
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).
RE: OEM permissions
Hi, I think you're describing a real security hole. But I'm not sure how it's exploited exactly. Let's say John Doe sets up his database on his desktop, which is part of the production database network. He sees the hash value of SYSTEM's password on production and sets the hash value for his own SYSTEM user to be the same. Since now he doesn't know the clear text password for SYSTEM (Pete Finnigan may know how to find it, though), he can't easily create a private database link owned by SYSTEM. He can still create a public link, or a private link owned by somebody else, his SYS user e.g. Then what? (He can still create a link owned by SYSTEM from another account such as SYS using a little bit hacking. But he won't know SYSTEM's password. I don't know how security of the production database is compromised in any way) Yong Huang you wrote: Maybe I'm a being a bit touchy here; but it seems that my comments about having access to dba_users went completely unnoticed. Let's put it this way: There is NO WAY you can prevent somebody from setting up their own private oracle instance. It they have access to dba_users in your database, they can create the SAME users with the SAME passwords in their private database. And they can create database links in their private database. Now, is this a problem? __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
RE: no longer listening
Hi, Julio, Windows doesn't have a nsswitch.conf equivalent. Windows hosts file is equivalent to UNIX /etc/hosts. Imagine Windows has a nswitch.conf like this: hosts: files dns But it's missing other lines such as protocoles, services... in this file. This topic is much less off-topic than some others. Thanks for reminding everybody. Yong Huang QuijadaReina, Julio C wrote: Mladen, The equivalent of nsswitch.conf on Win2K is the hosts file in winnt\system32\drivers\etc. You'd probably say: the /etc directory in M$ ...But, well this is way off the topic we are dealing with. Like Jared said, we need to focus ;) __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
RE: OEM permissions
Hi, Raj, 9i doesn't allow a user with select any table privilege to view any object owned by SYS. So the sys.link$ risk is gone. But select any dictionary, a new privilege in 9i, allows that. In practice, I always grant select_catalog_role to any developer, but refrain from granting select any dictionary or select any table. As DBAs, we should encourage developers to make full use of data dictionary views and open the database to them as much as they can study it. I would help the consultant in your case instead of just throw back a NO to him. Yong Huang Jamadagni, Rajendra wrote: Dennis, select any table has to be a big no no ... anyone can select from sys.link$. But I am still trying how OEM can be used for _development_?? what am I missing? As for ... One of our groups hired a new consultant and he (claimed to have DBA background) immediately shot off an email saying he needed select any table and select catalog role to do his work. We shot off reply Thanks for your email, while we appreciate your requirements for development, the privileges you are requesting are a tad different than we grant other developers. However we request that you submit a justification for these privileges and tell us how your development would be affected without these and we will accommodate your request. This was 3 months ago and we _still_ haven't heard back. __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
Re: Oracle Data Guard
Hi, Jonathan, I think your question is why I mentioned TDU, not just SDU, in my response to Guang's message. I admit I didn't give much thought and threw that in. Note:44694.1 says it's set to 32k by default and its adjustable range is 0 to 32k. Then the question is why Oracle chose the magical 32k. Would changing it to anything else yield any SQL*Net performance gain? It won't be too much extra work for Guang Mei to find out while he's experimenting with SDU. It's a little confusing when you say TDU is MTU, because, I think, the term MTU (Maximum transmission unit) is already used by network engineers to refer to the maximum number of bytes a data link layer frame can contain (1500 bytes for Ethernet e.g.). But I understand what you mean. Regarding a less than maximum SDU size, maybe it's useful if most of the SQL result is much less than 32k? Somebody can experiment and find out. Yong Huang Jonathan Lewis wrote: 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 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 __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
Re: leaf node 90-10 splits
Hi, Tanel, Where do you see this statistic? I only see leaf node splits in 8.1.7 and 9.2 documentation. If the index is on strictly monotonically increasing numbers, won't a new node be added to the right without a block split? Yong Huang I wonder why does statistic leaf node 90-10 splits imply that right-hand index leaf block is split as 90-10, not 100-0 as it really is. (tested on 9.2.0.4 W2k). Historical reasons? Tanel. __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
RE: PERL?
Dan, If I were to use Expect.pm in Perl, I would program in Expect directly. If we can have one less layer of wrapping, why not? On the other hand, sqlplus is not an application that insists on terminal input as telnet does. So you can use a shell here document or coprocess to talk to it; Expect is an overkill. (Some people use Expect to simply ftp files without knowing that ftp -n allows you to use a here document; an Expect ftp script is necessary only if you need to respond differently to each of the ftp errors). Yong Huang Daniel Hanks wrote: I think Perl with Expect.pm could likely do this without much effort. Expect allows you to interact with just about anything that uses a terminal. One fun case comes to mind. We have this LED sign (think large rectangular array of LEDs), with an undocumented serial interface protocol. All that came with it to control it was this old DOS program which would talk to the sign over a serial port. So I whipped up a Perl script which used Expect to interact with dosemu (a Linux DOS emulator) to run the program, which interacted with the sign, all running on Linux. Works pretty good. Expect.pm is also nice to interact with network hardware that offers telnet/shell command interfaces. Interacting with sqlplus via Expect.pm would be pretty easy as well, I would think. It basically works like this: - Spawn the program you want to interact with - Expect a particular regex of output from the spawned process - Act based on that output (send commands, run processes, annoy the NT admin with net send packets, etc) - Wash, rinse, repeat. -- Dan __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
RE: PERL?
Jared, Thanks for correcting me that shutdown is a sqlplus command. I wanted to make the point that piping strings to sqlplus can do more than Perl DBI can. (But Perl DBI has advantages in many cases) Yong Huang [EMAIL PROTECTED] wrote: All *SQL* commands will work work with the DBI. 'SHUTDOWN' is not a SQL command, it is a sqlplus command, and therefor will not work with the DBI. This has been checked into, and Oracle does not make this functionality available via OCI, so shutting down and starting a database on *nix requires sqlplus. Here is one of the few instances where Win32 makes things easier than on *nix: Oracle can be stopped and started via a service, which means you can easily shut it down via the command line, and via the Win32 Perl module Win32::Service. If you want in depth discussion on this check the archives for the dbi-users list. I don't recall where the archives are, but the list is found at lists.perl.org. Jared __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
Re: Documenting databases
I used to document database objects (including columns) with the COMMENT commands. I stopped doing that because I think it unnecessarily increases the size of data dictionary. It's just a little, though. Yong Huang Daniel Hanks wrote: How about in each database itself. COMMENT ON TABLE|COLUMN tab|tab.col IS '...' comes to mind. It's simplistic, yes, but at least you don't have to remember where you put your documentation... HTH, -- Dan __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
RE: Oracle Data Guard
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 Guang Mei wrote: I have never worked on Network stuff. But is there any easy parameters we could set in sqlnet.ora so that we could increase the DB performance by increase the network transfer rate (without doing anything else)? BTW my sqlnet.ora (on a Sun Box) has only two lines: ... NAMES.DEFAULT_DOMAIN = incyte.com NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) Guang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
Re: rebuilding indexes - sure to cause a ruckus
Thanks, Richard. I'll read your long message more carefully later. I like your statement that rebuilding an index or not is not rocket science. One needs to measure the performance before and after the rebuild and make a conclusion himself. Many times we discuss performance issues and get very technical and sophisticated, without showing experimental results! Having been a science researcher before, I'd like to emphasize that facts speak louder than theories. There may be 10,000 24x7 databases in the world that don't easily allow even testing an index rebuild. But there may be 100 times more production databases in the world that are not 24x7. The individual DBA needs to do his control study and conclude, using experts' opinions as reference. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
RE: PERL?
I don't think any UNIX shell has some package or module written for any database. So the only way to talk to Oracle is using shell as a wrapper around sqlplus (or any application you developed). In sqlplus, you can use bind variables easily. In this sense, we can say shell does allow you to use bind variables. Speaking of Perl versus shell, Perl may still be quite primitive in supporting two-way communication with an external program e.g. sqlplus. (I have an example at www.stormloader.com/yonghuang/computer/OracleAndPerl.html#2waytosqlplus using IPC::Open2). But I think a KornShell coprocess (not a here document) does it nicely, i.e. piping a SQL command in and reading the result back, piping another command in, reading again, without exiting your sqlplus session. If you use Perl DBI (or the old OraPerl), Jared may know this but I'm not sure if you can send any arbitary SQL command such as explain plan, shutdown... and read its output. Yong Huang One BIG advantage of Perl is DBI. Via shell you can't use bind variables which sometimes come in handy. An admin dweeb here developed a __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
Re: Oracle Data Guard
Jumbo frames are new to me. The Ethernet Definitive Guide book says it was proposed by one vendor and adopted by several, so may not have good interoperability. But I wonder how much performance improvement there is by going from MTU 1500 with SDU 8k to MTU 8k with SDU 8k. I assume the lower the OSI level, the faster assembling and disassembling those protocol data units is done. Yong Huang With TCP over standard ethernet the maximum transfer unit (MTU) is about 1500 bytes, this means if you want to send 2000 bytes over network, you have to fragment it in 2 packets and send them separately. This means double packet headers, double latency etc. Jumbo frames is a capability of some Gbit ethernet cards which allow them to transfer about 9000 bytes in a single packet. SDU is session level transfer unit (session data unit). When you enable jumbo frames and set MTU/SDU to 8192 for example, you'll fit much more in single packet, thus increasing performance for larger transactions. __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- 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).
Re: rebuilding indexes - sure to cause a ruckus
Tanel, I think you're saying a query almost always runs faster right after the index rebuild and there's no point in finding the criterion whether to rebuild an index. (What is 42?) Some time ago I posted a message somewhere else showing a case where rebuilding or coalescing an index may be benefitial. A data warehouse is found to have some data errors. Deletes and updates are done. Then the database goes to mostly read-only again, and will last for a month or quarter. Then shrinking frequently used B*Tree indexes is a good idea. Now I'd like to add one more criterion as a result of reading Jonathan Lewis' dbazine article and email with him (errors are mine): the index is full scanned, or if range scanned or unique scanned, the index selectivity has to be fairly low (but not too low for the index to be ignored by CBO). In a typical working environment, a data warehouse does have plenty of relatively quiet period. I worked on a monthly data load project at an insurance company. I remember we rebuilt a partitioned IOT (one partition at a time) and fast full index scan (certain partitions) did run faster. There're some errors in Don Burleson's dbazine article (e.g. pct_used in dba_indexes) and Mike Hordila's Oramag article (structurally unbalanced index). But one thing alluded to in there is important: study Oracle performance problems as scientific research. You said setting _wait_for_sync to false improves performance. That's a fact. We can only explain and analyze it but not deny it. Similarly, when Mike says queries run 10 to 50% faster after index rebuild, we can't deny unless we find his measurement is wrong. Wouldn't it be nice if Oracle researchers write articles with sections like Abstract - Experimental - Results - Discussion in that order? Yong Huang Tanel Poder wrote: There's no point of arguing about whether a query ran faster right after you rebuilt your index. Nor there is no point in finding some ultimate algorithm for finding the point of index rebuilding, we all know the answer - it's 42. Instead, a long stress test has to be done, e.g. running 10 millions of continous transactions and queries (simulating real life). Do one 10M without rebuilding indexes in the meantime, measure total execution time, IO amount, CPU usage, segment sizes etc. Then restore your database back to starting point and do the same test again with regular index rebuilds during the operations (online or taking users offline, depending on environment type). And then measure the same statistics, especially total execution time. Note, that statistics and time also for rebuilding indexes should be accounted in totals, because in real life they don't just disappear somewhere as in some simple-minded tests. Tanel. __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- 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).
Re: Re: _wait_for_sync , dirty buffer flushing and direct reads in parallel
Tanel, Did you observe better performance? By how much? Do please let us know! From what I read, _wait_for_sync when set to false means LGWR immediately notifies user (foreground) processes that redo record writes are done (even though they're not). When you say the parameter only affects LGWR, you need to clarify what you mean by affect; it changes the notification (posting) behavior of LGWR therefore changes the behavior of waiting processes (*when* they stop waiting). Just semantics. Yong Huang --- Tanel Poder [EMAIL PROTECTED] wrote: Anjo, I also thought it affects only lgwr sync, but Jonathan Lewis once told that it affects any disk writes... If it affects only lgwr, then great, I can make Apps upgrades, which do really lots of DDLs and small transactions, quite much faster that way... Thank you, Tanel. _wait_for_sync basically meant that a session is waiting for the sync of the redo by the lgwr. Normally the redo log writer writes to disk and then notifies the session that the transaction is completed. By setting this to false, you no longer wait for the redo to go to disk. That has no impact on your situation. Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 19, 2003 11:20 PM query Hi! I've sometimes used setting _wait_for_syncúlse during Apps upgrade projects, to upgrade performance. (As long as your database doesn't crash during the parameter is set to false, no problems should occur). I just started wondering, what would be the case if a parallel query starts during someone is modifying data... As I understand, when doing parallel query: 1) the dirty blocks which are supposed to be read by PQ in direct mode, are flushed to disk 2) PQ reads the blocks in direct mode But when _wait_for_sync is set, the writes get acknowledged immediately (or acknowledgement is not waited for). Could this result in the unlikely situation, that PQ issues the flush command to dirty buffers and starts to read them, but actually reads the old images of the blocks, since it thinks the write has already occurred? (actually, this doesn't touch only PQ, it's possible to have direct reads to PGA in serial mode too...) Tanel __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- 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).
Re: Re: _wait_for_sync , dirty buffer flushing and direct reads in parallel
The message I posted a minute ago may be wrong in one aspect. From what I read, _wait_for_sync when set to false means LGWR immediately notifies user (foreground) processes that redo record writes are done (even though they're not). When you say the parameter only affects LGWR, you need to clarify what you mean by affect; it changes the notification (posting) behavior of LGWR therefore changes the behavior of waiting processes (*when* they stop waiting). Just semantics. Looks like it doesn't change the LGWR notification behavior. It just suppresses foreground processes' waiting for LGWR to write redo records. Yong Huang __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- 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).
Re: Re: _wait_for_sync , dirty buffer flushing and direct reads in parallel
I think my understanding was wrong. _wait_for_sync actually only changes the behavior of foreground processes. When set to false, they don't wait for LGWR to write redo records to disk; instead they continue to do their work as if log file sync already finished. It *does not* change any behavior of LGWR, notification or not. Correct me if I'm wrong again. I'm still interested in Tanel's benchmark, though. Only that is scientific. Yong Huang --- Yong Huang [EMAIL PROTECTED], i.e. myself, wrote a few minutes ago: Tanel, Did you observe better performance? By how much? Do please let us know! From what I read, _wait_for_sync when set to false means LGWR immediately notifies user (foreground) processes that redo record writes are done (even though they're not). When you say the parameter only affects LGWR, you need to clarify what you mean by affect; it changes the notification (posting) behavior of LGWR therefore changes the behavior of waiting processes (*when* they stop waiting). Just semantics. Yong Huang --- Tanel Poder [EMAIL PROTECTED] wrote: Anjo, I also thought it affects only lgwr sync, but Jonathan Lewis once told that it affects any disk writes... If it affects only lgwr, then great, I can make Apps upgrades, which do really lots of DDLs and small transactions, quite much faster that way... Thank you, Tanel. _wait_for_sync basically meant that a session is waiting for the sync of the redo by the lgwr. Normally the redo log writer writes to disk and then notifies the session that the transaction is completed. By setting this to false, you no longer wait for the redo to go to disk. That has no impact on your situation. Anjo. __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- 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).
RE: RE: orbitz fiasco
Murali, Could you point us to a document about the TAF and database link issue? Thanks. Yong Huang --- Murali_Pavuloori/[EMAIL PROTECTED] wrote: we implemented RAC (not me personally --but my predecessors) It did not work for us. Oracle RAC does not support TAF for sessions coming through dblinks.(Yes verified this with Oracle product development). But that is a application architecture issue. If your app does not require this feature, I guess you can use RAC to reap the benefits of high availability. Murali. __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- 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).
Re: Oracle Magazine Awards
Just a congratulations is nothing. For one, Arup should tell us the secret of achieving the .03 second transaction goal on a 7-terabyte OLTP database. How is that done? What advice can he give? Yong Huang --- [EMAIL PROTECTED] wrote: Congratulations to the following folks that appeared in the 2003 Editors Choice Awards ( I finally received my issue of the mag ) Arup Nanda - DBA of the Year Tony Jambu - Consultant of the Year Mogens Nogaard - Educator of the Year Tom Kyte - Oracle Book Author of the Year There were many other, I only mentioned those that I have had the opportunity to meet and/or converse with via email, or sometimes even in person. ( all of these conditions allow me to drop their names when the opportunity arises ) Congratulations folks! ( I don't know if all of them frequent this list ) Jared __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- 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).
Re: Oracle shared object files on Solaris, and ELF class.
Grant, You can simply use the file command to find out the ELF class. If it reports 32-bit, it's ELFCLASS32; if it's 64-bit, i's ELFCLASS64. When you say client shadow process, I assume you mean the shadow process on the server (as a shadow of the client in dedicated configuration). You may want to check all libraries under $ORACLE_HOME/lib: cd $ORACLE_HOME/lib; file * | grep 64; file * | grep 32. If they're mixed, you need to relink. Yong Huang --- Grant Allen [EMAIL PROTECTED] wrote: Hi all, Looking for possible causes for a ELFCLASS64 version of /oracle/product/8.1.7/lib/libobk.so accidentally cropping up for a 32-bit install. This was on a client site ... so some details I can't disclose ... but it's a clean 8.1.7.0.0 install under Solaris (8 I think). Caused the client shadow process to bomb out with a fatal error (Looked like this in the trace file dlopen gets error 'ld.so.1: ora_s000_ORCL: fatal: /u01/app/oracle/product/8.1.7/lib/libobk.so: wrong ELF class: ELFCLASS64 ' when opening object ) Failing that, does anyone know of a utility that can check the elf class of .so files under Solaris? A google (and search on sun.com) for such a beast drew a blank. Thanks Fuzzy __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: ** find whether table or index being accessed
Tanel,
Raj must be talking about userenv('SCHEMAID'). Change that to another user's
user_id as seen in dba_users, you should see that user's object usage:
SQL select * from v$object_usage;
no rows selected
SQL select io.name, t.name,
2 decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
3 decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
4 ou.start_monitoring,
5 ou.end_monitoring
6 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
7 where io.owner# = 32
8and i.obj# = ou.obj#
9and io.obj# = ou.obj#
10and t.obj# = i.bo#;
NAME NAME DEC DEC
START_MONITORINGEND_MONITORING
-- -- --- ---
--- ---
AGENT_LICENSE_STATEPRDNO_IND AGENT_LICENSE YES YES
11/18/2003 10:06:27
...
Yong Huang
--- Tanel Poder [EMAIL PROTECTED] wrote:
Well, it's code is:
select io.name, t.name,
decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),
decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),
ou.start_monitoring,
ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
All of the referenced objects are tables under sys.
Maybe you were talking about v$session_object_cache?
Tanel.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, November 19, 2003 3:20 PM
MG,
AFAIK v$object_usage is ONLY for current user ... you have to hack it to
see remaining data.
Raj
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
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).
Re: html output
Maybe use HTP, HTF and OWA_UTIL packages? You still need to do some writing in using them. Yong Huang --- AK [EMAIL PROTECTED] wrote: I am looking for a utility package for throwing output in html format from a query . This should use utl_file to write the file ( no sqlplus markup ). Is there any package /procedure oracle has to do this job ? This is just a html report and it will be sent to users by email. ( this is not a OAS/IAS report ). Thanks -ak __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: 'internal' role and 9i
Tim, I checked v$reserved_words. At least in 9.2.0.1, INTERNAL is not in there. Oracle should address this issue. When I trace the SQL GRANT SELECT ON SOMETABLE TO INTERNAL, it stops in parsing. Nonetheless, it's confusing to say the least to create a role called internal. Yong Huang --- Tim Gorman [EMAIL PROTECTED] wrote: Barry, Why make life difficult? It's just a role, not a data object referenced by applications (hopefully). Change it's name to something that is not a reserved word and move on. There is a list of reserved words in the SQL Language reference. Hope this helps... -Tim Hello all, I'm attempting to import into 9.2.0.1.0 from 7.3.4 and I'm getting loads of the same error: IMP-00017: following statement failed with ORACLE error 9275: GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL IMP-3: ORACLE error 9275 encountered ORA-09275: CONNECT INTERNAL is not a valid DBA connection I'm aware that connect internal does not exist in 9i, but 'internal' is a role. So as a test I dropped the role, recreated it and then manually tried to grant it something - The same error occurred: SQL select * from dba_roles where role like 'INTER%'; ROLE PASSWORD -- INTERNAL NO SQL SQL drop role internal; Role dropped. SQL create role internal; Role created. SQL GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL; GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL * ERROR at line 1: ORA-09275: CONNECT INTERNAL is not a valid DBA connection SQL This doesn't make any sense to me. Can anybody help to shed any light on this?? TIA for any response, they're much appreciated. Cheers, Barry __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
alter database character set (Was: RE: 'internal' role and 9i)
Gopal, Are you saying with an undocumented parameter or command, I can alter database (national) character set us7ascii even if my current (national) character set is utf8? Yong Huang --- K Gopalakrishnan [EMAIL PROTECTED] wrote: INTERNAL_USE is an keyword (to enable an undocumented feature) in ALTER DATABASE command. THis can be used to convert the database character set if the existing char set (national charset) is the superset of the db charset. You can just run the ALTER Database command to convert the db charset. Best Regards, K Gopalakrishnan -Original Message- Barry Deevey Sent: Tuesday, November 18, 2003 7:09 AM To: Multiple recipients of list ORACLE-L As of yet I'm unsure how the application would be affected if I rename the role - I need to do some investigation. I tried this in Oracle 8 and it worked fine - It just seems to be oracle 9 that doesn't like it. I've also checked v$reserved_words and INTERNAL is not listed, INTERNAL_USE and INTERNAL_CONVERT are. As a test I created roles for INTERNAL_USE and INTERNAL_CONVERT, hoping that it would not allow me to create them, but it did, so I then ran the grant again and it also allowed it. Now I'm really confused!! __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
RE: 'internal' role and 9i
Barry, I suggest you open a Tar with Oracle, unless you're sure changing your application is easy. Oracle obviously missed this little detail by over-rejecting a previously legitimate role. If 9i's Release note doesn't say how to deal with this case, then Oracle support should open a bug. Tom, It's not always easy to have a futuristic view to avoid potential problems. The developer probably shouldn't have granted anything to internal back then. But it wasn't obvious at that time that doing so could cause a problem later. Yong Huang --- Barry Deevey [EMAIL PROTECTED] wrote: This would explain why it worked when I tried it on oracle 8 then. The developers that originally created the application left quite a while ago, so I don't think I'll be able to ask them why they did it this way. But basically you're saying that it shouldn't have been done like this and now it needs to be changed. Not the answer I was hoping for, but at least now I know!! Thanks for your help all. -Original Message- Mercadante, Thomas F Sent: 18 November 2003 14:54 To: Multiple recipients of list ORACLE-L Yong, It seems to me that you are missing the point here. The real point is that you should not have granted select on some table to internal - ever. And a new release caught you on this mistake. And now, you have to fix it. It is not an Oracle problem, but a mis-use of an Oracle internal user. What you did implies that you are running sql statements while connected as internal. Why in the world you would take this chance is beyond me - strictly a no-no. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, November 18, 2003 8:59 AM To: Multiple recipients of list ORACLE-L Tim, I checked v$reserved_words. At least in 9.2.0.1, INTERNAL is not in there. Oracle should address this issue. When I trace the SQL GRANT SELECT ON SOMETABLE TO INTERNAL, it stops in parsing. Nonetheless, it's confusing to say the least to create a role called internal. Yong Huang --- Tim Gorman [EMAIL PROTECTED] wrote: Barry, Why make life difficult? It's just a role, not a data object referenced by applications (hopefully). Change it's name to something that is not a reserved word and move on. There is a list of reserved words in the SQL Language reference. Hope this helps... -Tim Hello all, I'm attempting to import into 9.2.0.1.0 from 7.3.4 and I'm getting loads of the same error: IMP-00017: following statement failed with ORACLE error 9275: GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL IMP-3: ORACLE error 9275 encountered ORA-09275: CONNECT INTERNAL is not a valid DBA connection I'm aware that connect internal does not exist in 9i, but 'internal' is a role. So as a test I dropped the role, recreated it and then manually tried to grant it something - The same error occurred: SQL select * from dba_roles where role like 'INTER%'; ROLE PASSWORD -- INTERNAL NO SQL SQL drop role internal; Role dropped. SQL create role internal; Role created. SQL GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL; GRANT SELECT ON DOWNLOAD_SEQ TO INTERNAL * ERROR at line 1: ORA-09275: CONNECT INTERNAL is not a valid DBA connection SQL This doesn't make any sense to me. Can anybody help to shed any light on this?? TIA for any response, they're much appreciated. Cheers, Barry __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: Mercadante, Thomas F 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
RE: alter database character set (Was: RE: 'internal' role and 9i)
Gopal, In case I didn't make my message clear, I wanted to know if using that keyword allows us to change character set from a superset to a subset (e.g. from UTF8 to US7ASCII). The documented command ALTER DATABASE CHARACTER SET new charset is only for changing from a subset to a superset. If changing to a lower character set works, what's the syntax? Personally, I can't imagine how that's possible. Thanks. Yong Huang --- K Gopalakrishnan [EMAIL PROTECTED] wrote: INSERT STANDARD DISCLAIMERS Yes. You can use INTERNAL_USE keyword to convert the database character set. I think there is a utility called 'csscan' character set scanner which can be used to determine the possibility of the INTERNAL_USE conversion. END DISCLAIMERS Best Regards, K Gopalakrishnan -Original Message- Yong Huang Sent: Tuesday, November 18, 2003 9:00 AM To: Multiple recipients of list ORACLE-L Gopal, Are you saying with an undocumented parameter or command, I can alter database (national) character set us7ascii even if my current (national) character set is utf8? Yong Huang --- K Gopalakrishnan [EMAIL PROTECTED] wrote: INTERNAL_USE is an keyword (to enable an undocumented feature) in ALTER DATABASE command. THis can be used to convert the database character set if the existing char set (national charset) is the superset of the db charset. You can just run the ALTER Database command to convert the db charset. Best Regards, K Gopalakrishnan -Original Message- Barry Deevey Sent: Tuesday, November 18, 2003 7:09 AM To: Multiple recipients of list ORACLE-L As of yet I'm unsure how the application would be affected if I rename the role - I need to do some investigation. I tried this in Oracle 8 and it worked fine - It just seems to be oracle 9 that doesn't like it. I've also checked v$reserved_words and INTERNAL is not listed, INTERNAL_USE and INTERNAL_CONVERT are. As a test I created roles for INTERNAL_USE and INTERNAL_CONVERT, hoping that it would not allow me to create them, but it did, so I then ran the grant again and it also allowed it. Now I'm really confused!! __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: ut_file limit
AK, Do you get INVALID_MAXLINESIZE exception? Documentation says it maxes at 32767. I didn't find file size limit. UTL_FILE.FOPEN also has a max_linesize that can be set to 32767. Yong Huang --- AK [EMAIL PROTECTED] wrote: what is max filesize for utl_limit.fopen ? I am unable to past beyond 51K . -ak __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: ** find whether table or index being accessed
I think there's another complication in using buffer cache (x$bh or v$bh). If
the index is scanned in parallel, the blocks are not cached in buffer cache.
To avoid counting buffers created due to index update, maybe we can simply say
where v$bh.status in ('CR','READ').
Yong Huang
--- Tanel Poder [EMAIL PROTECTED] wrote:
Jacques provided some great suggestions already (about monitoring index
tablespace read/write ratio), so monitoring buffer cache should be done as a
last resort IMHO. Monitoring only for existence of index root block in cache
wouldn't be good enough anyway, I'd check for touch count and last touch time
too (TCH and TIM columns in x$bh), but again, as Joze pointed out nicely,
these blocks can get to cache because of updates... So no luck in tracking
indexes from there :)
Tanel.
- Original Message -
From: Daniel Fink
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, November 18, 2003 11:34 PM
Subject: Re: ** find whether table or index being accessed
This is just an idea, so please test it thoroughly (and then test it
again!) Any and all comments (including Are you brain-dead, Dan?) are
welcome.
How about periodically sampling v$bh for index segment headers? This
assumes that any index access reads the header (true/false?) for the
statement using the index. I'd set the sample frequency fairly high (several
times a day sounds reasonable) and monitor any impact. This will not show
every index that is used, as one could be used and flushed from the cache
between samples. However, I think it would be fairly likely to catch the ones
really in use.
Of course, under no circumstances remove indexes on primary keys, unique
constraints or foreign keys, even if they don't show up.
Daniel Fink
A Joshi wrote:
Looking to see if any statement has accessed the index in say 30 days.
So basically : how often index blocks are being read. So I can decide to
drop unused indexes. TThanks Daniel for your help.
Daniel Fink [EMAIL PROTECTED] wrote:
Are you looking to see if statements are using indexes or how often
index blocks are being read?
Daniel Fink
A Joshi wrote:
Hi, I had sent this some time back but got no answer for version
8.1.7. For table I understand auditing is an option. What about for index?
Thank You
A Joshi [EMAIL PROTECTED] wrote:
Hi, Is there an easy way to find out if a table or an index is
being used. I mean short of going thru all code or keeping looking at
v$sqlarea. I mean even if code is covered there are always ad hoc SQL queries
etc. Same for other objects like views etc. Is there a place where oracle
stores objects accessed and any other related info. Thanks
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
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).
Re: Bad performance because of wrong characterset?
Carel-Jan, About two weeks ago, Faan DeSwardt [EMAIL PROTECTED] posted to the thread Unicode: UTF-16 to UTF-8 conversion, saying UTF-8 is variable-length encoding and so is slower than the fixed-width UTF-16, and also US7ASCII. Maybe it's the same issue here comparing UTF-8 with WE8ISO8859P15. Faan can comment. Yong Huang --- Carel-Jan Engel [EMAIL PROTECTED] wrote: Hi List, One of my customers gets HP Servicedesk 4.5 implemented. The database that has been created by on of the DBA's for the application is Oracle 8.1.7.4, on AIX 4.3. The database has characterset UTF8, which is the standard here. Now ServiceDesk has bad performance. HP claims that the characterset is wrong, and a new database has to be created with character set WE8ISO8859P15. I've never heard of bad performance due to differences in character sets, probably because of my lack of experience. Anyone familiar with this issue? TIA, Carel-Jan __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: Why is Oracle process using 25 MB of RAM when idle?
Gogala, I've been searching for a /proc filesystem implementation on HPUX for years. I don't think it's there yet. Yong Huang --- Mladen Gogala [EMAIL PROTECTED] wrote: Your process has parts of SGA attached to it. The only way to actually find out is to examine the process address space wia kernel debugger or /proc file system. First, try with ps -lp PID and see how big is the RSS (resident set size). On 11/17/2003 02:09:26 AM, Daiminger, Helmut wrote: Hi, we are running 9.2 on HP-UX here. We have pg_aggregate_target configured, but I realized (in my opinion) very high memory consumption of Oracle Unix processes. a) How come that one Oracle Connection (i.e. dedicated Unix process on HP) is using up at least 22 MB of RAM? It is using 22 MB if the user is just connected, not doing anything. Any way I can modify this? b) If the user is querying data and the like, the memory consumption goes up to 60 MB. How come? Thanks! Regards, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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). Mladen Gogala Oracle DBA __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: Bad performance because of wrong characterset?
Here's a wild guess. If the character set is changed, a 10 MB segment (from the first extent to high water mark) may become 20 MB or 5 MB. Then CBO changes the plan and it happens to be a bad one. Yong Huang --- Carel-Jan [EMAIL PROTECTED] wrote: At 10:34 17-11-03 -0800, you wrote: Performance depends on a lot of things, such how much string operations you got, what are the contents of your strings (are they mainly latin chars or are there lots of asian ones etc..). Tanel. Thanks Tanel for your respond. I doubt whether a query will go from several minutes to seconds, even when a fixed-width single-byte characterset is chosen. I'm not supposed to interfere with this Service Desk project, but just couldn't keep my mouth shut when I heard HP scheduled a meeting for tomorrow (when I'm not there) and claimed that recreation of a database with the proper characterset would do the job. I suggested to do some tracing analysis first, before start shooting at a moving target in the dark. And of course posted the question to this bunch of knowledgable people. Regards, Carel-Jan __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: Fwd: Looking for help.
KamYee, Can we take a look at the SQL*Loader control file? Also the output of select * from v$sql where lower(sql_text) like '%yourstagingtable%' and invalidations 0 Yong Huang --- Jonathan Gennick [EMAIL PROTECTED] wrote: I wonder whether the invalidation comes about from the use of TRUNCATE, which is considered a DDL statement. I'd guess that any DDL to a table would invalidate existing SQL statements. 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. Wednesday, November 12, 2003, 8:34:24 AM, Jonathan Gennick ([EMAIL PROTECTED]) wrote: JG I don't usually forward my reader email to the list, but the JG question below strikes me as rather interesting. In this JG case, SQL*Loader appears to be causing all SQL statements JG that refer to the table being loaded to be invalidated. Is JG this normal behavior? Does anyone know why it might be the JG case? JG -- JG Best regards, JG Jonathan Gennick --- Brighten the corner where you are JG http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] JG Join the Oracle-article list and receive one JG article on Oracle technologies per month by JG email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, JG or send email to [EMAIL PROTECTED] and JG include the word subscribe in either the subject or body. JG Wednesday, November 12, 2003, 1:07:41 AM, JG [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: JG Hi Jonathan, JG I was unable to find the answers from your book SQL*Loader: The Definitive Guide and the web. I am running out of sources. I hope you can help me with the following questions. JG We are using Oracle 9i sqlldr, direct path to load data from external files into JG staging tables. After data is loaded, we invoked stored procedures to JG transform data and move them to the target tables. The steps are: JG 1. delete all entries from 20 staging tables JG 2. invoke sqlldr userid=dbimpl/dbimpl control=controlFile direct=true to JG load data to all 20 staging tables JG 3. invoke stored procedures to transform data from the staging tables to the JG final tables. Currently these stored procedures are standalone. JG 4. invoke stored procedures to remove out-of-date entries from the final JG tables. JG I monitor invalidations column in v$sqlarea. Every time JG after sqlldr is invoked for data loading (step 2), all the JG sql statements that reference the staging tables are JG invalidated, including delete from stageing_table sql JG statement. I setup a test and used a java program to loop JG steps 1-4 every ~2 minutes. There were no other activities JG in the database except data loading and transformation. JG After a couple days, I got the following error: ORA-04031: JG unable to allocate 4212 bytes of shared memory (shared JG pool,unknown object,sga heap(1,0),stat array mem) JG The questions are: JG 1. Do we need to delete entries in the staging table prior to loading. Will JG sqlldr remove the entires in the staging table first prior to loading? JG 2. There are no changes in the stored procedures, how / why sqlldr would JG invalidate the sql statement in the stored procedures? JG 3. The error ORA-04031 in this case, is it due to shared memory fragmentation? I suspect that the culprint is invalidations. How do invalidations cause shared memory fragmentation? JG I would appreciate if you can send me some pointers or suggestions. JG Thanks, JG KamYee __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: Fwd: Looking for help.
Ron and Jonathan, Glad to know the problem is solved. We do need SQL*Loader control file whenever we have such a problem. Any DDL would invalidate a cursor in library cache. I'm testing with the harmless GRANT, which is a DDL. SQL create table yongtst (a number); Table created. SQL delete from yongtst; 0 rows deleted. SQL select sql_text, invalidations from v$sql where sql_text like '%yongtst%'; SQL_TEXT INVALIDATIONS - - delete from yongtst 0 select sql_text, invalidations from v$sql where sql_text like '%yongtst%' 0 SQL grant select on yongtst to system; Grant succeeded. SQL select sql_text, invalidations from v$sql where sql_text like '%yongtst%'; SQL_TEXT INVALIDATIONS - - delete from yongtst 1 select sql_text, invalidations from v$sql where sql_text like '%yongtst%' 0 After GRANT, the delete statement has an invalidation of 1 so a hard parse will occur next time. I think this hard parse is slightly less expensive than a brand new SQL. After invalidation, the cursor (cursor head or parent cursor) still shows up in v$sql, but it disappears from v$sql_plan. A brand new SQL does not have an entry in v$sql. Other harmless DDLs acting on tables are COMMENT, ANALYZE, REVOKE, but not EXPLAIN PLAN as you might believe. Yong Huang --- Ron Rogers [EMAIL PROTECTED] wrote: Daniel, I understand what is you are saying and what you tested but I don't the why or what it means. Does it mean that the sql command is not going to work? Does it means that you have to issue it again to get it to work? Ref: Doc ID: Note:123214.1 invalid Type: PROBLEM Status: PUBLISHED Seems that truncate command invalidates object definition and existence in library cache. Invalidation can also be seen on temporary tables! .. Jonathan, The memory problem is described in Doc id:1157495.8Support Description of Bug 1157495 Ron [EMAIL PROTECTED] 11/12/2003 1:14:25 PM Ron, I don't know about the TRUNCATE option w/ sql*loader, but the regular DDL TRUNCATE invalidates sql that references the table. Example: 1 select sql_text, invalidations 2 from v$sql 3* where sql_text = 'select * from emp' SQL / SQL_TEXT INVALIDATIONS -- - select * from emp 0 SQL truncate table emp; Table truncated. SQL select sql_text, invalidations 2 from v$sql 3 where sql_text = 'select * from emp' 4 / SQL_TEXT INVALIDATIONS -- - select * from emp 1 Ron Rogers wrote: Daniel, How does using the TRUNCATE command is a sqlldr invalidate anything? The sqlldr truncate command reuses the storage that the table originally used and does not change the HW mark. If there are indexes on the tables then they are placed in the DIRECT PATH state during the load and updated with the now block info. Please explain whet you mean by invalidate. Ron __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
RE: for years I have been using korn shell
How about just use ESC for filename completion? I'm using 11/16/88 version ksh. $ set -o emacs $ what /bin/ksh /bin/ksh: Version M-11/16/88i SunOS 5.8 Generic 110662-04 May 2001 $ touch yongtest $ ls yongtest yongtest The last line was typed by pressing ls yongtESCESC. Yong Huang -Original Message- Sent: Tuesday, November 11, 2003 8:05 AM To: Multiple recipients of list ORACLE-L Here's a snippit from my ~/.kshrc, which kinda-sorta approximates bash's default behavior. Unfortunately, there doesn't seem to be any way to bind TAB for filename completion in ksh88. :-( Apparently ksh93 can handle this, but it isn't available on any of the servers I support. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
RE: SQL comparison addition:
I don't see any extraneous characters in
sys_context('userenv','session_user')either. But the trailing null in
v$session.machine for Windows connections is a known problem. Bug 646174 shows
version 8.1.5. I tried in 9.2.0.1. It still exists:
SQL select dump(machine) from v$session where machine = 'ICONIX\YONGHUANG';
no rows selected
SQL select machine from v$session where machine like 'ICONIX\YONGHUANG_';
MACHINE
ICONIX\YONGHUANG
SQL select dump(machine) from v$session where machine like
'ICONIX\YONGHUANG_';
DUMP(MACHINE)
Typ=1 Len=17: 73,67,79,78,73,88,92,89,79,78,71,72,85,65,78,71,0
Yong Huang
--- Chris Stephens [EMAIL PROTECTED] wrote:
SQL select sys_context('userenv','session_user'),
2 dump(sys_context('userenv','session_user')), a.reports_login,
3 dump(a.reports_login)
4 from global.client_dim a
5 WHERE sys_context('userenv','session_user') = a.REPORTS_LOGIN;
SYS_CONTEXT('USERENV','SESSION_USER')
DUMP(SYS_CONTEXT('USERENV','SESSION_USER'))
REPORTS_LOGIN
--
DUMP(A.REPORTS_LOGIN)
REPORTS_DELTA
Typ=1 Len=13: 82,69,80,79,82,84,83,95,68,69,76,84,65
REPORTS_DELTA
Typ=1 Len=13: 82,69,80,79,82,84,83,95,68,69,76,84,65
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
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).
RE: pattern search
Naveen and Shiva, Please see my article at http://www.stormloader.com/yonghuang/computer/OracleRegExp.html for a summary of the usage of owa_pattern, a very little known package since probably Oracle 7.3. It also has a link to Tom Kyte, Mark Piermarini and Daniel Savarese's external Java approach, as well as Jonathan Gennick's article on 10g regular expressions. Yong Huang --- Naveen, Nahata (IE10) [EMAIL PROTECTED] wrote: Not until 10g Regards Naveen -Original Message- From: Shiva Maran [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Subject: pattern search Hi All, I need a means to search for a pattern (With basic wildcard characters like %, _, ^, []). How do I do this in oracle. I also need to get back the string that matches the pattern. Is there any predefined function or procedure that does this. Would like to avoid implementing this on my own. TIA, ShivaM __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: Re[2]: How do you genrate primary keys?
Mladen, If the session doesn't start a transaction, I don't see how this can work. First, multiple sessions can find the same SCN, no matter what kind of SCN you're talking about. Secondly, v$datafile.last_change# is set to null unless the datafile is offline. If each session has its own transaction, i.e. not just a regular query, then their own SCN's at the time the transaction started as shown in v$transaction may be used as a uniqur identifier. But the session can't use a savepoint and hope that has another number in v$transaction; that's not a common requirement though. Yong Huang --- Mladen Gogala [EMAIL PROTECTED] wrote: Well, if the commit is not too frequent, one natural way of generating primary key would be select max(last_change#) from v$datafile; These numbers are guaranteed to be unique. As DBWR wakes up every 3 seconds, the intervals between commit should be at least 3 seconds. As you're very well aware, this is the natural mechanism that ensures that any change is properly enumerated and, thus, the best and most generic primary key. I understand that someone might doubt this mechanism as I would never even dream of using it, but SCN is the thing that comes naturally. Alternatively, one could produce SCN from V$TRANSACTION (base + wrap). On 11/06/2003 12:54:38 PM, Cary Millsap wrote: The implementations I've seen all did SELECT...FOR UPDATE. Works. Doesn't scale. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Jonathan Gennick Sent: Thursday, November 06, 2003 7:59 AM To: Multiple recipients of list ORACLE-L Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale ([EMAIL PROTECTED]) wrote: HKC 1. Hit a table that keeps a counter. HKC Used to be a mechanism in the Oracle5 days [If I remember correctly, HKC Sequences came in Oracle6]. Issues were with locking the single HKC record used as the generator or scanning for the max(value) of the HKC key. HKC Not quite sure I understand how you encountered concurrency issues, though. My concurrency issues probably boil down to the locking business. The app I'm thinking of originally did something like: SELECT counter INTO :1 FROM counter_table WHERE counter_name = 'table name'; ...some app code goes here... UPDATE counter_table SET counter := counter+1 WHERE counter_name = 'table name'; Well, it all worked fine in single-user modegrin. But it was easy enough for me to sit down in front of two computers, create two new records, press SAVE at the same time, and cause two sessions to grab the same key value, because they would both issue the SELECT before either one got around to the UPDATE. I couldn't screw things up consistently, but just by hitting the SAVE button at the same time I could screw things up often enough to make the problem obvious. Maybe there's a way to lock the table, to make the above approach work. In my case, I didn't bother trying to find that solution. Once I did my little demo, it was easy enough to convince the project manager that we should switch to using Oracle sequences. 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: Cary Millsap 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
Re: SQL*Plus errors... how to hide?
Saira,
How about use the same method as suggested in another thread, i.e. pipe and
remove unwanted text? In your case
whatever comes in | sed -n '/^BEGIN/,/^ORA-06512: at line/!p'
Yong Huang
--- Saira Somani-Mendelin [EMAIL PROTECTED] wrote:
List,
I have a shell script that executes a sql*plus script (which executes a
procedure) based on user input.
But what if the user inputs an invalid datatype? The exception section
handles the error and displays a user-friendly message but I still get
an error stack. I want to hide this from the user. How can I do this? I
have set feedback and echo options off. See output below:
PO Reconciliation Batch Release
Enter batch number to be released:
yrugis
You have entered an invalid number! Exiting program...
**[I want to suppress the errors below]**
BEGIN RELEASE_PO_B_H('yrugis'); END;
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at TRAIN65D.RELEASE_PO_B_H, line 16
ORA-06512: at line 1
Thanks,
Saira
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
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).
Re: any problem rebuilding indexes used for replication
renu (and Jared), The reason I'm very interested in whether there's performance improvement is that there's a thread on the newsgroup about index rebuild recently. See http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk Jonathan Lewis says there's almost no need to rebuild indexes, although his Practical Oracle8i lists at least one case you may benefit by rebuilding. (I don't have the book with me). Asktom.oracle.com has numerous messages advising against rebuilding indexes. Let's set theory aside for a moment and do the experiment. Please post your report of performance change. Thanks. Yong Huang --- renu r [EMAIL PROTECTED] wrote: Jared : I think it is fragmented based on scripts and knowing that there have been lot of deletes. One script uses the table index_stats and looks at field del_lf_rows which should be less at least in comparison to field lf_rows. I mean less is good. more bad. I am not sure about the script but I will post it here if someone says so or send it to anyone if they want. I am sure the experts here know about it and can clarify if it is any good to look at the index_stats table. One other simple useful script is : SELECT owner, index_name, blevel FROM all_indexes WHERE blevel 2 This can be bacause the size of table is big so it is not definitive. I will check the level after rebuild. Benefits expected : Space savings (definitely). performance (hopefully). I will let you and Yong know about the benefits if any. If I get some help. [EMAIL PROTECTED] wrote: I'm curious, how have you identified the fragmentation? What benefits do you expect from the rebuild of the indexes? Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes? Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 06:14 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any problem rebuilding indexes used for replication Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: any problem rebuilding indexes used for replication
--- [EMAIL PROTECTED] wrote: Tom Kyte states that they are never needed, which I don't believe to be correct. As I said two weeks ago, somewhere on the Internet people over-trusted authorities. It's different here. I love this place! I'll try to back this up with data in the future. As a Ph.D in chemistry (sorry to say this), I know how more important repeated experiments done by different people in different labs are than theory. Furthermore, a lab experiment is nothing if a product coming out of a chemical plant says no good. So if Tom asks any of you why you don't use automatic space management in your LMT's, you can ask him why he doesn't use 'alter index rebuild'. ;) Jared Yong Huang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2003 02:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: any problem rebuilding indexes used for replication renu (and Jared), The reason I'm very interested in whether there's performance improvement is that there's a thread on the newsgroup about index rebuild recently. See http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk Jonathan Lewis says there's almost no need to rebuild indexes, although his Practical Oracle8i lists at least one case you may benefit by rebuilding. (I don't have the book with me). Asktom.oracle.com has numerous messages advising against rebuilding indexes. Let's set theory aside for a moment and do the experiment. Please post your report of performance change. Thanks. Yong Huang --- renu r [EMAIL PROTECTED] wrote: Jared : I think it is fragmented based on scripts and knowing that there have been lot of deletes. One script uses the table index_stats and looks at field del_lf_rows which should be less at least in comparison to field lf_rows. I mean less is good. more bad. I am not sure about the script but I will post it here if someone says so or send it to anyone if they want. I am sure the experts here know about it and can clarify if it is any good to look at the index_stats table. One other simple useful script is : SELECT owner, index_name, blevel FROM all_indexes WHERE blevel 2 This can be bacause the size of table is big so it is not definitive. I will check the level after rebuild. Benefits expected : Space savings (definitely). performance (hopefully). I will let you and Yong know about the benefits if any. If I get some help. [EMAIL PROTECTED] wrote: I'm curious, how have you identified the fragmentation? What benefits do you expect from the rebuild of the indexes? Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes? Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 06:14 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any problem rebuilding indexes used for replication Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: any problem rebuilding indexes used for replication
zhu chao,
Your numbers are impressive. But they're not ultimate gauge, which should only
be performance (well, in very special cases, taking less disk space becomes
more important). Jonathan and Tom's points are indexes should not be too
densely packed unless the DML's on them already make them so.
So show us some timing data.
Yong Huang
--- zhu chao [EMAIL PROTECTED] wrote:
Hi,
In some case, rebuild index can help, this is from my work log on my
Datawarehouse project.
The effect of rebuiding index:
SQL exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
Free Blocks.22
Total Blocks4090
Total Bytes.33505280
Unused Blocks...823
Unused Bytes6742016
Last Used Ext FileId11
Last Used Ext BlockId...52851
Last Used Block.547
PL/SQL procedure successfully completed.
SQL select index_name,owner,blevel,index_type from dba_indexes where
blevel4;
INDEX_NAME OWNER BLEVEL
INDEX_TYPE
-- -- --
---
INX_BID_BIDTYPEDAILYLOAD 10
BITMAP
INX_TX_CC_STATUS_IDDAILYLOAD 6
BITMAP
INX_TX_BD_STATUS_IDDAILYLOAD 5
BITMAP
SQL ALTER INDEX INX_BID_BIDTYPE REBUILD;
Index altered.
SQL exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
Free Blocks.0
Total Blocks5
Total Bytes.40960
Unused Blocks...3
Unused Bytes24576
Last Used Ext FileId15
Last Used Ext BlockId...39837
Last Used Block.2
PL/SQL procedure successfully completed.
DAILYSOURCEINX_STD_ST_STATEMENTID
122910
149 rows selected.
SQL CONN SYSTEM/[EMAIL PROTECTED]
Connected.
SQL ALTER INDEX DAILYSOURCE.INX_STD_ST_STATEMENTID REBUILD;
Index altered.
SQL select 122910*16/1024 from dual;
122910*16/1024
--
1920.46875
SQL select blocks from dba_segments where
segment_name='INX_STD_ST_STATEMENTID';
BLOCKS
--
54642
SQL SELECT 54642*16/1024 FROM DUAL;
54642*16/1024
-
853.78125
SQL SET PAUSE OFF
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, November 07, 2003 6:59 AM
renu (and Jared),
The reason I'm very interested in whether there's performance improvement
is
that there's a thread on the newsgroup about index rebuild recently. See
http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk
Jonathan Lewis says there's almost no need to rebuild indexes, although his
Practical Oracle8i lists at least one case you may benefit by rebuilding.
(I
don't have the book with me). Asktom.oracle.com has numerous messages
advising
against rebuilding indexes. Let's set theory aside for a moment and do the
experiment. Please post your report of performance change. Thanks.
Yong Huang
--- renu r [EMAIL PROTECTED] wrote:
Jared : I think it is fragmented based on scripts and knowing that there
have
been lot of deletes.
One script uses the table index_stats and looks at field del_lf_rows
which
should be less at least in comparison to field lf_rows. I mean less is
good.
more bad. I am not sure about the script but I will post it here if
someone
says so or send it to anyone if they want.
I am sure the experts here know about it and can clarify if it is any
good
to look at the index_stats table.
One other simple useful script is :
SELECT owner, index_name, blevel
FROM all_indexes
WHERE blevel 2
This can be bacause the size of table is big so it is not definitive. I
will
check the level after rebuild.
Benefits expected : Space savings (definitely). performance (hopefully).
I
will let you and Yong know about the benefits if any. If I get some help.
[EMAIL PROTECTED] wrote:
I'm curious, how have you identified the fragmentation?
What benefits do you expect from the rebuild of the indexes?
Are you targeting certain indexes that have been identified as
benefiting from a rebuild, or just planning to rebuild all indexes?
Jared
renu r [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/05/2003 06:14 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED
RE: How do you genrate primary keys?
Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? 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
Re: How do you genrate primary keys?
Rachel, That's a good case to remember. Java programmers (or architects) sometimes miss those little things. I would ask why you used triggers to populate the PK field instead of saying INSERT ... MYSEQUENCE.NEXT_VAL in the code, or even INSERT ... SELECT ROWNUM (or ROWNUM+somefixedvalue). Wouldn't these perform better? Yong Huang --- Rachel Carmichael [EMAIL PROTECTED] wrote: At one site I worked at, the programmers insisted on using Java milliseconds as the primary key -- so that they wouldn't have to hit the database twice (once to get the sequence number, once to insert the row). They swore up, down and six ways from Sunday that there could never, ever, EVER be a collision. After we had collisions in development, we switched to sequences (one per table), with a trigger to populate the field on insert so that they wouldn't have to make the second round-trip. --- Jonathan Gennick [EMAIL PROTECTED] wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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
Re: nologging for IOT
Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
RE: How do you genrate primary keys?
Cary, If hitting a table that keeps a counter causes so many performance problems, I wonder why hitting sys.seq$ is much faster. I'd like to have some education on this Oracle magic. The only thing I can think of is that Oracle keeps some numbers in library cache as seen in sys.v$_sequences. Your own table doesn't do that. Yong Huang --- Cary Millsap [EMAIL PROTECTED] wrote: Hit a table that keeps a counter will not scale (will not perform at high concurrency). It will cause you no end of buffer busy waits waits, latch free waits for a cache buffers chains latch (even if db_block_buffers, _db_block_hash_buckets, and _db_block_hash_latches could be set to infinity), lots of unnecessary CPU service consumption due to the spinning (especially if you try to tinker with _spin_count), and possibly a wide range of side effects including write complete waits waits and others. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Wednesday, November 05, 2003 8:25 AM To: Multiple recipients of list ORACLE-L My comments [probably off-the-cuff without spending much time thinking the issues through .?] 1. Hit a table that keeps a counter. Used to be a mechanism in the Oracle5 days [If I remember correctly, Sequences came in Oracle6]. Issues were with locking the single record used as the generator or scanning for the max(value) of the key. Not quite sure I understand how you encountered concurrency issues, though. 2. Stored sequences. Although I prefer not to use a Sequence as a PK in itself [preferring natural column/s which are Unique keys, with the NOT NULL, of course], I have used a Sequence in an Advanced Replication implementation that had no Primary Key and I needed a PK for Conflict Resolution [this was years ago and, if you ask me, I can't remember all the details] 3. SYS_GUID SYS_GUID I've never used. It doesn't generate a NUMBER value so it is not really similar to a Sequence. Can user's key in a SYS_GUID-generated value ? Is it really human readable or recallable as a plain NUMBER, Security Security Number, ZIP Code ?? 4. Similar to SYS_GUID .. You hit on a fortuitous combination of columns. Hemant At 05:19 AM 05-11-03 -0800, you wrote: The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number), is SYS_GUID really all that viable? 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
RE: nologging for IOT
I see. Sorry for misreading. How about direct path load? sqlldr direct=true. But this means your data source is on the filesystem. What is M.b.? Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: Igor Neyman 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: nologging for IOT
Thanks, Denny. That's it. I imagine Tom's test database is running in noarchivelog mode and the tablespace is logging. Yong Huang --- Denny Koovakattu [EMAIL PROTECTED] wrote: Yong, If the database is in ARCHIVELOG mode, then the table must be set to NOLOGGING for append hint to work. If the database is in NOARCHIVELOG mode, then the table setting does not matter. Tom has not specified whether the database he tested against was in NOARCHIVELOG mode or whether the tablespace was set to NOLOGGING. If the tablespace was set to NOLOGGING the table would have also got created as NOLOGGING and would have worked even if the database was in ARCHIVELOG mode. Regards, Denny -- Denny Koovakattu Quoting Yong Huang [EMAIL PROTECTED]: Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
RE: How do you genrate primary keys?
I'm fully convinced. SSN should not be used as a PK. Can we also conclude that natural keys in general are only good if you sit in an ivory tower and do unrealistic lab test? Yong Huang --- Bellow, Bambi [EMAIL PROTECTED] wrote: Having worked for the government in a situation where we were actually tracking information BY Social Security Number, let me tell you the problems with it. 1) Social Security Numbers ARE NOT GUARANTEED TO BE UNIQUE 2) Individuals ARE NOT GUARANTEED TO HAVE ONE AND ONLY ONE Social Security Number 3) Not all Social Security Numbers are numeric 4) Not all Social Security Numbers which ARE numeric are 9 characters in length 5) Social Security Numbers can be changed by the holder 6) It is illegal to use the Social Security Number for any purpose other than that which the government specifically uses Social Security Numbers for (ie., the distribution of benefits). I'll bet *that* one is strictly enforced. HTH, Bambi. -Original Message- Sent: Wednesday, November 05, 2003 8:00 AM To: Multiple recipients of list ORACLE-L Tom, I think using a natural key such as Soc. Sec. # as the primary key is a good idea. You don't need to maintain the sequence so there's no performance issue associated with sequences. There's no issue of gaps. No index root block contention. It doesn't seem to be industry common practice though. In your college student case, changing primary keys is rare so it's not a big problem. Yong Huang --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: Jonathan, I think your idea of a paper is a good one. But I think we need to back th question up to what the requirements are. First, to me, a primary key should not be something that a user would ever see or use. So the Soc. Sec. # is out. (A side issue - I used to work at a college. Want to know how many times we had to change the Soc. for an individual student because the parent filled the form out and used their soc, or the kid used the wrong one?). Any id entered by a user is subject to mistakes and changes. So the PK value must be protected from these types of errors. The next requirement that may be needed is sequentiallity (is this a word?). Does the application require that every sequence number be used. Sometimes the answer is yes, and sometimes it just doesn't matter. These are the only two requirements I can think of. Based on the answers, we then have options. Right now, Oracle sequences are working well for me. I like the idea of SYS_GUID, just not sure where I would need it. Good idea and good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, November 05, 2003 8:19 AM To: Multiple recipients of list ORACLE-L The recent article that mentioned sequences got me to thinking. I might pitch a more detailed article on sequences to Builder.com. But a more interesting article might be one that explored various ways to automatically generate primary keys. So, in the name of research, let me throw out the following questions: What mechanisms have you used to generate primary keys? Which ones worked well, and why? Which mechanisms worked poorly? I've run up against the following approaches: * Hit a table that keeps a counter. This is the roll your own sequence method. The one time I recall encountering this approach, I helped convert it over to using stored sequences. This was because of concurrency problems: with careful timing, two users could end up with the same ID number for different records. Is there ever a case when this roll-your-own approach makes sense, and is workable? * Stored sequences. I worked on one app that used a separate sequence for each automatically generated primary key. I worked on another app, a smaller one, that used the same sequence for more than one table. The only issue that I recall is that sometimes numbers would be skipped. But end users really didn't care, or even notice. * The SYS_GUID approach. I've never used SYS_GUID as a primary key generator. I wonder, was that Oracle's motivation for creating the function? Has anyone used it for primary keys in a production app? What's the real reason Oracle created this function? * Similar to SYS_GUID, I once worked on an obituary-tracking application that built up a primary key from, as best I can recall now: date of death, part of surname, part of first name, and a sequence number used only to resolve collisions, of which there were few. The approached worked well, actually, because whatever fields we munged together to generate a primary key gave us a unique key the vast majority of the time. The SYS_GUID approach is interesting, but if you need an ID number that users will see, and that users might type in themselves (e.g. social security number
RE: nologging for IOT
Yes, direct-path load works on IOTs, at least in 9.2 running in Solaris 2.8. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Unfortunately my source is another table. By the way (btw.), will sqlldr direct=true work with IOT? m.b - may be. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 12:25 PM To: Multiple recipients of list ORACLE-L I see. Sorry for misreading. How about direct path load? sqlldr direct=true. But this means your data source is on the filesystem. What is M.b.? Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: Yong, M.b. my question was not clear. I know, nologging doesn't work with IOTs. What I'd like to know, if there are any tricks (similar to direct-path) to minimize undo/redo when inserting into IOT. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Yong Huang Sent: Wednesday, November 05, 2003 9:49 AM To: Multiple recipients of list ORACLE-L Hi, Igor, Direct-path insert does not work for IOTs. This is documented in SQL Reference for INSERT. Whether it works for a table without NOLOGGING set (i.e. LOGGING) is not clear to me. Documentation says the table has to be NOLOGGING, or its tablespace has to be so. But Tom Kyte seems to show us that as long as you say INSERT /*+ APPEND */ SELECT, there won't be redo (except for the minimum data dictionary change), regardless of the table logging setting. See his demo at http://groups.google.com/groups?selm=a3k2kp08q5%40drn.newsguy.com (that message was not intended to prove my observation). If somebody reads that differently, please correct me. Yong Huang --- Igor Neyman [EMAIL PROTECTED] wrote: As it was recently discussed, Insert /*+ append */ into destination_table select * from source_table will produce minimum redo/undo if destination_table specified as nologging. But, what if destination_table is index-organized table? Is it possible to achieve the same results (in regards to amount of redo/undo)? Igor Neyman, OCP DBA [EMAIL PROTECTED] __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: Igor Neyman 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: Igor Neyman 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
Re: IMP using the same DMP file
Jerry, There shouldn't be any problem. It's better to use different log files or run them in different directories. Yong Huang --- Whittle Jerome Contr NCI [EMAIL PROTECTED] wrote: Hi, We were just wondering if you can IMP into two instances using the same dmp file at the same time? We need to refresh both our development and test instances with data from our production database and doing both at once might save some time. 8.1.7 and Unix. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: any problem rebuilding indexes used for replication
Hi, renu, I'll let experts anwser your question. But I have a request for you. Before and after you rebuild (or coalesce) your indexes, please make close observation on your application performance, as well as the statistics and sizes of the indexes. I'd like to know whether rebuilding them actually makes much difference. (I don't know the answer but am very interested to know). Thanks. Yong Huang --- renu r [EMAIL PROTECTED] wrote: Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: explain plan conundrum
Hi, Ryan, Where's the 20 billion rows? There's 1 G rows and 20 G bytes. What are the values of NUM_ROWS in xxx_INDEXES for PK1 and xxx_TABLES for TABLE2? Did you analyze using ANALYZE command or DBMS_STATS? Yong Huang --- [EMAIL PROTECTED] wrote: I cant sql trace it now. I hae run statspack. this query is running now and I dont want to run another copy with a trace on until this finishes, since I dont want to suck up resources. Im at a loss as to where the 20 billion rows comes from in this explain plan? Everything including the indexes are analyzed. when the two tables involved have 36k and 5k rows involved. looks like some form of cartesian join, but its not showing up in the plan. The two tables are joined by a column. any place to look on this? I know I need the 10046 trace, but I cant get that yet and it make take 12 hours to get it after this runs. select col1, col2, col3 from tab1 tab2 where tab1.col1 = tab2.col2; Operation Object Name RowsBytes CostObject Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=CHOOSE1 G 237 HASH JOIN 1 G 20G 237 INDEX FAST FULL SCAN PK1 5 K 11 K3 TABLE ACCESS FULL TABLE2 366 K 4 M 231 __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: ** SQL WHERE clause order
Hi, Joshi, The only hint that may affect the evaluation order in the WHERE clause is ORDERED_PREDICATES. But I don't know who actually got it to work. The chapter Optimizer Hints in Performance Tuning Guide talks about it. It also says if you don't have this hint, there's a certain order in which Oracle evaluates predicates in the WHERE clause. Not sure if that's true. You can try switching the predicates around and look at the execution plan for each. Documentation is wrong in saying that you should be put that hint in the WHERE clause. Yong Huang --- A Joshi [EMAIL PROTECTED] wrote: Hi, In a SQL statement I want a certain where clause to be done first. Is it enough to list it first as follows or do I (and can I) do something else to make it get checked first before other WHERE/AND clause are looked at. Thanks : SELECT emp_id FROM emp WHERE select_sen_emp_chk_first = 'Y' AND dept = :dept AND salary :min_sal __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: ** other oracle forums
The newsgroups comp.databases.oracle.XXX, where XXX is server, misc, tools or marketplace. DBA issues are mostly discussed at ...server. If you don't have direct access to Usenet, go to groups.google.com or www.mailgate.org. Personal opinion: Jonathan Lewis is still there. Other experts only visit once in a while. Generally free of off-topic messages. The only problem may be that some people go overboard in trusting authorities, in spite of Jonathan's warning every time The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Other than Lazy Dba (www.lazydba.com) and others, you can think of otn.oracle.com or Metalink forums or even asktom.oracle.com as forums. Only on-topic messages are allowed on these forums. Yong Huang --- A Joshi [EMAIL PROTECTED] wrote: Hi, Can someone post a list of other oracle dba forums like this one. Again. I think someone had posted it sometime back. Thank You. __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- 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).
Re: 10g new features
That's a nice article. The author could have enhanced it by adding or changing some wording. The buffer cache can be flushed with alter system flush buffer_cache. - ... instead of using the undocumented alter session set events = 'immediate trace name flush_cache' in 9i SQL: Regular Expressions (finally) - SQL: Regular Expressions; much enhanced over the less known owa_pattern (see http://www.stormloader.com/yonghuang/computer/OracleRegExp.html) Plus Oracle trace collection is deprecated (or gone?) after it's finally improved to the level of being quite useful (see http://www.dbazine.com/jlewis1.html) One of the biggest improvements to me is the datafile reuse across OSes. When my previous employer outsourced Financials to Oracle.com (and laid off all DBAs including me incidentally), Oracle had to use Solaris because it was too difficult to export. All other databases we outsourced are running on Linux now. Yong Huang --- [EMAIL PROTECTED] wrote: Found a site with some 10g new features. http://www.adp-gmbh.ch/ora/misc/10g.html I'm sure some will like the new 'alter tablespace rename' http://www.adp-gmbh.ch/ora/concepts/tablespaces.html#sysaux Jared __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- 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).
Re: 10g new features
Gopal was joking about the fact that Jared only said 'alter tablespace rename' instead of ALTER TABLESPACE RENAME DATAFILE. In fact, 9.2 SQL Reference has this Moving and Renaming Tablespaces: Example This example moves and renames a datafile associated with the tbs_01 tablespace from 'diskb:tbs_f5.dat' to 'diska:tbs_f5.dat': If you only read the subtitle here (first line), you *will* be surprised. Yong --- Mladen Gogala [EMAIL PROTECTED] wrote: Actually, I don't understand what you mean. Here is 9.2: SQL create tablespace test datafile '/data/oradata/data/test01.dbf' 2 size 10M extent management local autoallocate 3 segment space management auto; Tablespace created. SQL alter tablespace test rename to test01; alter tablespace test rename to test01 * ERROR at line 1: ORA-01904: DATAFILE keyword expected SQL So, what did you mean? On 2003.11.03 21:59, K Gopalakrishnan wrote: Jared: 'alter tablespace rename' is not the REAL 10g feature. It is available from 9.2 onwards... :) Hope you know what I mean,, KG - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 04, 2003 12:54 AM Subject: 10g new features Found a site with some 10g new features. http://www.adp-gmbh.ch/ora/misc/10g.html I'm sure some will like the new 'alter tablespace rename' http://www.adp-gmbh.ch/ora/concepts/tablespaces.html#sysaux Jared -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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).
Re: 10g new features
How can *you* not get it? Gopal says in Oracle 9.2, 'alter tablespace rename' is possible. Of course. The full syntax is alter tablespace mytablespace rename datafile '/path/file.dbf' to '/path/newfile.dbf'. You see the three words 'alter', 'tablespace' and 'rename' in there? You'll get it in the morning. ihomo sapien/i think better during the day. Yong --- Jared Still [EMAIL PROTECTED] wrote: Not sure I get it either. :) The command listed on the 10g site that I referred to is this: alter tablespace ts_user rename to ts_user_01; Renaming a tablespace is apparently possible with 10g. Must be a joke here somewhere I'm missing. Jared On Mon, 2003-11-03 at 20:09, Yong Huang wrote: Gopal was joking about the fact that Jared only said 'alter tablespace rename' instead of ALTER TABLESPACE RENAME DATAFILE. In fact, 9.2 SQL Reference has this Moving and Renaming Tablespaces: Example This example moves and renames a datafile associated with the tbs_01 tablespace from 'diskb:tbs_f5.dat' to 'diska:tbs_f5.dat': If you only read the subtitle here (first line), you *will* be surprised. Yong --- Mladen Gogala [EMAIL PROTECTED] wrote: Actually, I don't understand what you mean. Here is 9.2: SQL create tablespace test datafile '/data/oradata/data/test01.dbf' 2 size 10M extent management local autoallocate 3 segment space management auto; Tablespace created. SQL alter tablespace test rename to test01; alter tablespace test rename to test01 * ERROR at line 1: ORA-01904: DATAFILE keyword expected SQL So, what did you mean? On 2003.11.03 21:59, K Gopalakrishnan wrote: Jared: 'alter tablespace rename' is not the REAL 10g feature. It is available from 9.2 onwards... :) Hope you know what I mean,, KG - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Tuesday, November 04, 2003 12:54 AM Subject: 10g new features Found a site with some 10g new features. http://www.adp-gmbh.ch/ora/misc/10g.html I'm sure some will like the new 'alter tablespace rename' http://www.adp-gmbh.ch/ora/concepts/tablespaces.html#sysaux Jared -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: Jared Still 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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
Re: 10046 level 8 trace - help required with 'direct path
Hi, Tim, Assuming you don't have more than 1000 files, what's your db_files set to and what's select file#, name from v$tempfile? If you do have more than 1026 files, select file#, name from v$datafile. Also show us select * from v$sort_usage if you can run that DELETE again. XCTEND rlbk=0: your transaction end marker says it's not rolling back; i.e. it's committing. Yong Huang --- Andy Rivenes [EMAIL PROTECTED] wrote: Looks sort spillage to disk due to the where clause. Andy Rivenes [EMAIL PROTECTED] At 06:44 AM 10/30/2003 -0800, Tim Onions wrote: Gurus I've applied many of the things I've learnt from this list over the years and today I tried a 10046 trace for the first time on a reported slow transaction. From what I can tell the biggest offender is a wait seemingly associated with rollback (see below) called 'direct path write'. Is this just a traditional wait for a row lock to be released or something more sinister? Any help much appreciated. Also (daft question time) what units are tim= in? (ie how many seconds between tim=131853898 and tim=131853270). This SE 8.1.7.4.12 on Windows 2000. Thank you T¬ PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270 hv=2073223040 ad='8e9a2080' DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1 END OF STMT PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270 WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0 EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270 XCTEND rlbk=0, rd_only=0 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1 WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1 ... WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3 FETCH #14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Onions __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- 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).
Re: 10046 level 8 trace - help required with 'direct path
Hi, Gopal, Now I know where you are! I just joined this list. I left here two years because of too much spam (off-topic messages). I know that number is db_files + tempfile#. But if he indeed has 1026 datafiles, p1 *will* be file# in v$datafile. Yong --- K Gopalakrishnan [EMAIL PROTECTED] wrote: Yong: They are temp files. The file# for the temp files start with db_files+1. Because of this, there may be chances your pl will have 1000+ though you have only hand ful of datafiles. KG - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, October 30, 2003 10:39 PM Hi, Tim, Assuming you don't have more than 1000 files, what's your db_files set to and what's select file#, name from v$tempfile? If you do have more than 1026 files, select file#, name from v$datafile. Also show us select * from v$sort_usage if you can run that DELETE again. XCTEND rlbk=0: your transaction end marker says it's not rolling back; i.e. it's committing. Yong Huang --- Andy Rivenes [EMAIL PROTECTED] wrote: Looks sort spillage to disk due to the where clause. Andy Rivenes [EMAIL PROTECTED] At 06:44 AM 10/30/2003 -0800, Tim Onions wrote: Gurus I've applied many of the things I've learnt from this list over the years and today I tried a 10046 trace for the first time on a reported slow transaction. From what I can tell the biggest offender is a wait seemingly associated with rollback (see below) called 'direct path write'. Is this just a traditional wait for a row lock to be released or something more sinister? Any help much appreciated. Also (daft question time) what units are tim= in? (ie how many seconds between tim=131853898 and tim=131853270). This SE 8.1.7.4.12 on Windows 2000. Thank you T¬ PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270 hv=2073223040 ad='8e9a2080' DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1 END OF STMT PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270 WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0 EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270 XCTEND rlbk=0, rd_only=0 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1 WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1 ... WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3 FETCH #14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Onions __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- 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: K Gopalakrishnan 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
RE: 10046 level 8 trace - help required with 'direct path
Hi, Tim, John has good input. You can't conclude that your DELETE requires a sort. v$sort_usage (or preferablly v$tempseg_usage beginning with 9.2) shows all types of usage of temporary segments. If the segtype column says it's 'SORT', the session is sorting. If it's 'HASH', it's hashing. For other values, look at Version 9.2 documentation for v$tempseg_usage, or item 22 at http://www.stormloader.com/yonghuang/computer/OracleIdiosyncrasies.html. Also note that this view records data for running sessions only; if the operation on the temporary segment is finished, the row will be gone. So you have to query it when your DELETE is running and identify the row based on v$session.saddr = v$sort_usage.session_addr. But it's better to corroborate with other columns such as session serial number, SQL address and hash. BTW, does the ROUTING_NEXT_JOB table have LOBs or is it a global temporary table? Does the delete have cascaded delete on other tables? Yong Huang --- John Kanagaraj [EMAIL PROTECTED] wrote: Tim, As you have seen, this is due to writes to and reads from the TEMPORARY tablespace of that user. This could be due to both SORT segments (SORT_AREA_SIZE overflow) as well as HASH segments due to HASH Joins going to TEMP when they overflow HASH_AREA_SIZE. This can be seen from V$SORT_USAGE.SEGTYPE. Since a DELETE should normally not generate sorting or Hashing, I am assuming that either there are triggers that are forcing this to occur, or this is a view and the INSTEAD OF is performing some inefficient joins... Andy - just curious how a WHERE clause on a DELETE would generate Sort usage (outside of that explained above)... John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Yong Huang [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:10 AM To: Multiple recipients of list ORACLE-L Subject: Re: 10046 level 8 trace - help required with 'direct path Hi, Tim, Assuming you don't have more than 1000 files, what's your db_files set to and what's select file#, name from v$tempfile? If you do have more than 1026 files, select file#, name from v$datafile. Also show us select * from v$sort_usage if you can run that DELETE again. XCTEND rlbk=0: your transaction end marker says it's not rolling back; i.e. it's committing. Yong Huang --- Andy Rivenes [EMAIL PROTECTED] wrote: Looks sort spillage to disk due to the where clause. Andy Rivenes [EMAIL PROTECTED] At 06:44 AM 10/30/2003 -0800, Tim Onions wrote: Gurus I've applied many of the things I've learnt from this list over the years and today I tried a 10046 trace for the first time on a reported slow transaction. From what I can tell the biggest offender is a wait seemingly associated with rollback (see below) called 'direct path write'. Is this just a traditional wait for a row lock to be released or something more sinister? Any help much appreciated. Also (daft question time) what units are tim= in? (ie how many seconds between tim=131853898 and tim=131853270). This SE 8.1.7.4.12 on Windows 2000. Thank you T¬ PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270 hv=2073223040 ad='8e9a2080' DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1 END OF STMT PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270 WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0 EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270 XCTEND rlbk=0, rd_only=0 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1 WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1 ... WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3 FETCH #14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim
RE: 10046 level 8 trace - help required with 'direct path
Thanks, Paul. Your message is more insightful than John's and mine! Sometimes it's too easy to see the tree without seeing the woods. Yong --- [EMAIL PROTECTED] wrote: John/Tim, The 'direct path read/write' are for cursor #14. The delete is cursor #15. Check the trace file for the preceding cursor #14. Paul -Original Message- Sent: Thursday, October 30, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Tim, As you have seen, this is due to writes to and reads from the TEMPORARY tablespace of that user. This could be due to both SORT segments (SORT_AREA_SIZE overflow) as well as HASH segments due to HASH Joins going to TEMP when they overflow HASH_AREA_SIZE. This can be seen from V$SORT_USAGE.SEGTYPE. Since a DELETE should normally not generate sorting or Hashing, I am assuming that either there are triggers that are forcing this to occur, or this is a view and the INSTEAD OF is performing some inefficient joins... Andy - just curious how a WHERE clause on a DELETE would generate Sort usage (outside of that explained above)... John Kanagaraj Oracle Applications DBA DB Soft Inc Work : (408) 970 7002 Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Yong Huang [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:10 AM To: Multiple recipients of list ORACLE-L Subject: Re: 10046 level 8 trace - help required with 'direct path Hi, Tim, Assuming you don't have more than 1000 files, what's your db_files set to and what's select file#, name from v$tempfile? If you do have more than 1026 files, select file#, name from v$datafile. Also show us select * from v$sort_usage if you can run that DELETE again. XCTEND rlbk=0: your transaction end marker says it's not rolling back; i.e. it's committing. Yong Huang --- Andy Rivenes [EMAIL PROTECTED] wrote: Looks sort spillage to disk due to the where clause. Andy Rivenes [EMAIL PROTECTED] At 06:44 AM 10/30/2003 -0800, Tim Onions wrote: Gurus I've applied many of the things I've learnt from this list over the years and today I tried a 10046 trace for the first time on a reported slow transaction. From what I can tell the biggest offender is a wait seemingly associated with rollback (see below) called 'direct path write'. Is this just a traditional wait for a row lock to be released or something more sinister? Any help much appreciated. Also (daft question time) what units are tim= in? (ie how many seconds between tim=131853898 and tim=131853270). This SE 8.1.7.4.12 on Windows 2000. Thank you T¬ PARSING IN CURSOR #15 len=60 dep=2 uid=38 oct=7 lid=38 tim=131853270 hv=2073223040 ad='8e9a2080' DELETE FROM ROUTING_NEXT_JOB RNJ WHERE RNJ.NEXT_JOB_ID = :b1 END OF STMT PARSE #15:c=0,e=2,p=0,cr=1,cu=0,mis=1,r=0,dep=2,og=0,tim=131853270 WAIT #15: nam='latch free' ela= 0 p1=-1856345836 p2=106 p3=0 EXEC #15:c=0,e=0,p=0,cr=3,cu=14,mis=0,r=2,dep=2,og=4,tim=131853270 XCTEND rlbk=0, rd_only=0 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59401 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59404 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59407 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59410 p3=1 WAIT #14: nam='direct path write' ela= 2 p1=1026 p2=59411 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59414 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59417 p3=1 WAIT #14: nam='direct path write' ela= 1 p1=1026 p2=59421 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59425 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59428 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59431 p3=1 WAIT #14: nam='direct path write' ela= 0 p1=1026 p2=59434 p3=1 ... WAIT #14: nam='direct path read' ela= 79 p1=1026 p2=41389 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41396 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41397 p3=7 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41404 p3=1 WAIT #14: nam='direct path read' ela= 0 p1=1026 p2=41405 p3=3 FETCH #14:c=100,e=628,p=221,cr=5629,cu=12,mis=0,r=1,dep=2,og=4,tim=131853898 __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- 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
RE: Finding the session causing compile to hang
Hi, Bruce, Steve Adams' code is based on x$kglpn (librarycache pin), which is correct. But the code based on dba_lock_internal blindly assumes id1 is the object name. There's a similar common misinterpretation; many DBAs assume v$lock.id1 is the object ID, which is only true for type = 'TM' (or maybe several other types). This info *is* in dba_lock_internal, but the script below just needs a type predicate in the WHERE clause. A simpler solution may be just look at v$sql where users_executing 0 for your package or procedure (shown in sql_text column). Although v$open_cursor could also be used, I don't think a row showing in there always indicates a library cache pin (executing) on the object. Yong Huang --- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: David, You could use Steve Adam's script Executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm to see what packages are executing. More generally, use dba_lock_internal to look at what is being blocked: based on Oracle-L script by Diego Cutrone [mailto:[EMAIL PROTECTED] (Friday, 29 August 2003 7:54 AM) COLUMN lock_id2 FORMAT A30 select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,45) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 lock_addr FROM dba_lock_internal WHERE mode_requested 'None' and mode_requested mode_held ; and use inverse of this with a given object_name to find who has the internal locks. HTH, Bruce Reardon -Original Message- Sent: Friday, 31 October 2003 10:59 AM I need to figure out a way to see if a procedure is running before attempting a compile and I can't figure out what tables to look in. Here's a test I set up create or replace procedure sleep(i_val number) is begin dbms_lock.sleep(i_val); end; / exec sleep(60); I then check v$lock, v$access, dba_locks(9i I think only) and can't seem to spot the sleep stored procedure or it's session. Of course I could look in v$session and see it in this example but in a stored procedure that has more to it you will only see the current step it is at in the procedure and not the procedure itself. I'm trying to be able to identify sessions that hold the lock/latch on a stored procedure so I can kill them when sometimes the session is disconnected and just hangs. Thx, Dave __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- 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).
RE: Finding the session causing compile to hang
Hi, Bruce, I happen to be loading a lot of data using our stored procedure right now. select sql_text from v$sql where users_executing 0 shows: INSERT /*+ APPEND PARALLEL(CLAIM) */ INTO CLAIM ( CLAIM_ID, [snipped] BEGIN sp_insert_claim; END; SELECT /*+ Q7898000 NO_EXPAND ROWID(A1) */ A1.LOSS_DA C0,[many other columns] ,A1.CLAIM_ID C8 FROM MCILR.CLAIM PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A1[snipped] The first INSERT line is definitely part of our sp_insert_claim. The third SELECT is heavily rewritten by the PL/SQL engine. But I can tell it's part of the procedure simply by looking at the FROM clause, plus I'm the only one on this database at this moment. (Tomorrow they need the data to be loaded) Rows in dba_lock_internal shows more than just blocked sessions. For instance, many Cursor Definition Locks in null mode are there to cause parsed cursors to be thrown away if referenced objects are altered in any way. Oracle calls these breakable parse locks. They're not really locks; just a trigger mechanism for dependent cursors to be invalidated on certain conditions. BTW, according to Anjo Kolk's Wait Event paper, in addition to TM locks, IV (library cache invalidation) and DL (direct loader) locks also use ID1 for object number. Yong Huang --- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: I don't think v$sql will give you the same information - it would show the top level actual package being executed, but not those which are called by that package - these do show up in Steve's code. Regarding the dba_lock_internal code - the only problem appears to be in the name used for the column - any rows returned are actually blocked - is that correct? Bruce Reardon -Original Message- Sent: Friday, 31 October 2003 12:34 PM To: Multiple recipients of list ORACLE-L Hi, Bruce, Steve Adams' code is based on x$kglpn (librarycache pin), which is correct. But the code based on dba_lock_internal blindly assumes id1 is the object name. There's a similar common misinterpretation; many DBAs assume v$lock.id1 is the object ID, which is only true for type = 'TM' (or maybe several other types). This info *is* in dba_lock_internal, but the script below just needs a type predicate in the WHERE clause. A simpler solution may be just look at v$sql where users_executing 0 for your package or procedure (shown in sql_text column). Although v$open_cursor could also be used, I don't think a row showing in there always indicates a library cache pin (executing) on the object. Yong Huang --- Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] wrote: David, You could use Steve Adam's script Executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm to see what packages are executing. More generally, use dba_lock_internal to look at what is being blocked: based on Oracle-L script by Diego Cutrone [mailto:[EMAIL PROTECTED] (Friday, 29 August 2003 7:54 AM) COLUMN lock_id2 FORMAT A30 select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,45) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 lock_addr FROM dba_lock_internal WHERE mode_requested 'None' and mode_requested mode_held ; and use inverse of this with a given object_name to find who has the internal locks. HTH, Bruce Reardon -Original Message- Sent: Friday, 31 October 2003 10:59 AM I need to figure out a way to see if a procedure is running before attempting a compile and I can't figure out what tables to look in. Here's a test I set up create or replace procedure sleep(i_val number) is begin dbms_lock.sleep(i_val); end; / exec sleep(60); I then check v$lock, v$access, dba_locks(9i I think only) and can't seem to spot the sleep stored procedure or it's session. Of course I could look in v$session and see it in this example but in a stored procedure that has more to it you will only see the current step it is at in the procedure and not the procedure itself. I'm trying to be able to identify sessions that hold the lock/latch on a stored procedure so I can kill them when sometimes the session is disconnected and just hangs. Thx, Dave __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- 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
Re: Re: ora-600 question
If there're already hints like ordered and use_nl that tell Oracle how to join, lack of statistics is less of a problem. In fact, you may need to use those two hints in some queries against data dictionary even in pre-9i Oracle. Yong Huang --- Tanel Poder [EMAIL PROTECTED] wrote: Hi! Just for the record, in 9.2 some views such dba_extents use ordered and use_nl hints, which force usage of CBO. If you don't have statistics calculated nor optimizer_dynamic_sampling set to at least 2, then you'll be using CBO with default statistics, which usually are quite misleading. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 11:09 PM The data dictionary should not have any statistics on them and thus will use rule as a rule so to speak. If you have run stats on the data dictionary you coul dbe running into some odd bugs. __ Do you Yahoo!? Exclusive Video Premiere - Britney Spears http://launch.yahoo.com/promos/britneyspears/ -- 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).
RE: char vs. varchar2
I want to add that even if you update a field to a *smaller* size, initial data in the varchar2 column has to move to the empty space beyond the end of row data inside the block, leaving a hole in its original place, just as if it were updated to a longer string. Char data does not have this problem. This test is done with block dumps on 8.1.7 on NT. Yong Huang [EMAIL PROTECTED] Miller, Jay wrote: Actually char has some minor performance advantages over varchar2 when the column is frequently updated to a larger size since the full space is already claimed within the block. __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT : kernel using 75% of CPU
Hi, Jerry, Next time you see top output like shown below, please provide this information: In SQL*Plus: select * from v$session_wait where sid = (select sid from v$session where paddr = (select addr from v$process where spid = 2286)); may need to run it a few times. Post it to the list (or email me) unless the wait is like 'SQL*Net%' or '%timer%' or 'rdbms%'. And also find the SQL by select * from v$sql where address = (select sql_address from v$session) once you know the SID and On OS: truss -flp 2286 (the first 100 lines or so should be OK; if there's a pause in running truss, indicate where the pause happens) Also let us know if there's anything special in alert.log and any new file in udump. Yong Huang [EMAIL PROTECTED] you wrote: PID USERNAME THR PRI NICE SIZE RES STATE TIMECPU COMMAND 2286 oracle 1 00 1844M 1814M run 9:44 13.90% oracle 11068 oracle 1 00 2056K 1536K cpu00:02 1.53% top 11333 oracle 1 00 1150M 1124M cpu10:01 1.39% oracle __ Do You Yahoo!? Get email alerts NEW webcam video instant messaging with Yahoo! Messenger http://im.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
CPU number vs speed (Was: Comment on 'Practical Oracle 8i')
I read Cary Millsap's message (on newsgroup or a mailing list). Based on my understanding, he says if user transactions come in less frequently, fewer faster CPUs (like on HP typically) gives better response time; if transactions come in frequently, it's better to have more slower CPUs (typically what Sun does). Yong Huang [EMAIL PROTECTED] Jonathan Lewis wrote: Cary Millsap has an article on his website www.hotsos.com which describes a case where upgrading the CPUs to a higher speed (same number) resulted in the OLTP users complaining about a drop in performance. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Delete followed by Select Count(1) - SLOW
I remember in slightly older versions of Oracle, delayed_logging_block_cleanouts is by default true, so redo won't be written immediately at block cleanout. Correct me if I'm wrong. Yong Huang [EMAIL PROTECTED] Jonathan Lewis wrote: bear in mind that a scan after deleting 100,000 rows would probably be doing a lot of delayed block cleanout - resulting in plenty of redo log, and possibly a lot of dbwr activity. Jonathan Lewis __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: X$_kglcursor.kglnaobj
Ian and Riyaj, These table_... objects look like Java classes. The way to find this out is matching the kgl handle address and looking at the indx column of x$kglob. Matching this indx column with dba_objects.object_id. Yong Huang [EMAIL PROTECTED] you wrote: I use the following statment when monitoring the database select sid, s.username,s.osuser, c.kglnaobj from sys.x$kglpn p, sys.x$kglcursor c, v$session s where p.kglpnhdl = c.kglhdadr and p.kglpnses = s.saddr; Here is some output showing sid and kglnaobj: 28 table_1_0_116_0_0_ ... __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: negative value for buffer cache hit ratio
Interesting. In 8.1.7 32-bit oracle binary, select * from v$type_size where type = 'UB4' tells me it's 4 bytes. So it's 32 bit and the maximum is 4 billion. Glad to see Jonathan on this forum. Yong Huang [EMAIL PROTECTED] you wrote: From: K Gopalakrishnan Welcome Jonathan, I think most of the counter are limited by ub4maxval and that makes the negative hit ratio. Welcome again ! --- Jonathan Lewis wrote: It is possible that after 4 months your stats have wrapped around the ( ? 64 bit ?) limit value for your platform. Check the actual values from v$sysstat to see if some of them have gone negative or appear to be 'counting backwards'. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Year of Unix file
How about using stat(2)? $ ls -l proc.txt -rw-rw-r-- 1 oracle dba 3414 Jul 1 00:10 proc.txt $ perl -e '$a=(stat proc.txt)[9]; print int $a/31536000+1970,\n' 2001 There's a better way to convert seconds since epoch to year but for now I just divide it by number of seconds in a year and add 1970 to it. The above perl one-liner is just a convenient way to call stat(2). Yong Huang [EMAIL PROTECTED] you wrote: But the year replaces the time in the 8th field only when the last modification time for the file is more than 6 month (even if it is in the current year :) For example, take a look at line 1,2 (less than 6 month old as of today) 3,4,5 (over 6 months old as of today).. -rw-rw-r-- 1 oracle dba 2880 Feb 5 08:05 junk.lst -rwxrwx--- 1 oracle dba410 Jan 30 11:08 show_all.ksh -rwx-- 1 oracle dba 77 Jan 25 2001 t1 -rw-rw-r-- 1 oracle dba 3971 Jan 10 2001 my.lst -rw-rw-r-- 1 oracle dba720 Jan 7 2001 bdf.out HTH... Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
comp.databases.oracle.server (Was: Re: DBMS_REPAIR package usage)
Hi, Murali, You don't subscribe to a newsgroup such as comp.databases.oracle.server as you do to a mailing list. If your company has a news (NNTP) server, configure your browser to user it and type the URL news:comp.databases.oracle.server to read/post messages. Most companies use "news" or "snews" as the news server name. Try: telnet news 119 help quit to find out. Or ask your Help Desk. If your company does not have a news server, read Question 2 at http://groups.google.com/googlegroups/help.html. I use news.interbulletin.com currently. If you insist on using a public news server instead of a Web gateway, www.jammed.com/~newzbot/sorted-speed.html does a fairly good job on listing public servers, which come and go at their will. To use one of them, type the URL news://[the IP of the server]/comp.databases.oracle.server in your browser. News readers don't use these URLs. Yong Huang [EMAIL PROTECTED] you wrote: How does one subscribe to this ? Murali Vallath Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Sat, 24 Mar 2001 05:05:21 -0800 Hi, Winnie, How did you find the file# to be 9 (unless you messed with your original error message)? I suggest you post your message to news:comp.databases.oracle.server (or http://news.interbulletin.com/cgi-bin/ibwrn/post/comp.databases.oracle.server if your company doesn't have a news server). Hopefully it will attract... __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: APPEND hint (Was: Which is faster??)
Hi, Connor, The append hint to insert does not disable generating rollback info. It does stop redo generation for a nologging table. Yong Huang [EMAIL PROTECTED] you wrote: If you're on 8.0 or higher, try insert /*+ APPEND */ into table select * from other_table; where "table" is defined as nologging. Then you won't hit either redo logs or rollback segments..Its the equivalent of a sqlldr direct load hth connor __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: DBMS_REPAIR package usage
Hi, Winnie, Just a little more research. I wonder how you can have an rdba that big, 0x24070020, which is 604438560 in decimal. SQL var a number; SQL exec :a := dbms_utility.data_block_address_file(604438560); PL/SQL procedure successfully completed. SQL print A - 144 SQL exec :a := dbms_utility.data_block_address_block(604438560); PL/SQL procedure successfully completed. SQL print A - 458784 This is done on 8.1.6. It says the block is in file 144, block 458784. Why does your error say file=0? Anyway, in case you do have a file numbered 144, check to see if there's an object there. If it's indeed file 0, the dba should be the same as block#, 458784, or 0x70020. DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS can confirm this. However, that file# 0 may be just an indicator that that information is lost, as multiple other 0's look like. I believe dbv reports an error when it encounters a fractured block, i.e., the first two bytes of tail (0003 in your case) does not match the last two bytes of rdba (0020). We know how a fractured block is created during hot backup. But I don't understand why an offlined datafile (as you said in another email) can contain fractured blocks. Maybe Jeremiah Wilton can give a better answer. Yong Huang [EMAIL PROTECTED] you wrote: I have a datafile in my production box (a user data tablespace), when I run dbv against it, it showed that 5 blocks are "influxed" Page 458784 is influx - most likely media corrupt *** Corrupt block relative dba: 0x24070020 file=0. blocknum=458784. Fractured block found during dbv: Data in bad block - type:0. format:0. rdba:0x last change scn:0x. seq:0x0 flg:0x00 consistancy value in tail 0x0003c204 check value in block header: 0x0, check value not calculated spare1:0x0, spare2:0x0, spare2:0x0 We can copy this file to tape, dd this file. On the OS disk level, the OS does n ot treat this as corrupted. But it is corrupted on the oracle (software) level. I've checked and can't find any object associate with these 5 corrupted blcok. That means that there is no data inside those blocks. Since the tablespace is about 12 GB on a highly active system (which only got 3 hours maintance window each month), export/import (then drop the tablespace) which Oracle support suggested is mostly out of the question. (Especially, it is very hard for me to convince the sysadmin that the blocks are corrupted as they don't see any I/O error associate with this file and the developers don' t see any problem with the application either!) I am currently thinking about upgrading this database to 8.1.6 to make use of th e DBMS_REPAIR package to make those blocks as "unusable". But I am not sure that if the DBMS_REPAIR package can run against the blocks which do not belong to any objects!! Can someone give me some guidences? thanks Winnie __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: 8i - Seeing which columns disabled
Hi, Tracey, From my own test, I remember data in a column set unused can still be dumped to a trace file. Depending on your knowledge of interpreting datafile dump, you can get the result back one row at a time (or even write a script to automate it; nobody outside Oracle can say that's easy). Also note that Oracle documention says: You can view all tables with columns marked as unused in the data dictionary views USER_UNUSED_COL_TABS, DBA_UNUSED_COL_TABS, and ALL_UNUSED_COL_TABS Yong Huang [EMAIL PROTECTED] you wrote: I found though that once marked unused U couldn't find a reference to the column name anywhere ie to confirm what columns have been dropped. I get the feeling that 'set unused' may be an irreversible process? Tracey __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FK Constraints
Hi All, Nobody has proposed this before so let me throw it out for discussion. Think about a compromise between having and not having referential integrity constraints. The approach is that during development and testing, all RIs are enabled. But when you push to production, disable or drop all of them. The advantage of not having or enabling RIs in production is not portability in this case; it's performance instead, however marginal it is. I think I read somewhere about Oracle's official answer to this little performance hit due to RIs. Think about it. There must be some. It shouldn't be hard to measure but I have not done it. Yong Huang [EMAIL PROTECTED] you wrote: We have a situation where are no relationships are defined at the database level. i.e no foreign keys constraints have established at the Database. The application is still at the Development Stage. Everything is controlled at the application level. I as the DBA appose this design for Data security and also cannot reverse engineer from the tables into Designer. Can you please share you pros / Cons. Thanks __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: NT Batch processes continue to run
Hi, Ron, Is this because you run batch files as CMD /K? If so, change it to CMD /C. If you're not using CMD command and if your script is not too long, please post it and we'll examine it for you. Yong Huang [EMAIL PROTECTED] you wrote: We have several NT Dos batch scripts that execute sql statements, copy NT files, send email, etc. For some reason after a couple of weeks the NT server slows to a crawl. We look at the running process and there are dozens of EXE and CMD processes running. The batch scripts are scheduled to run each day and complete normally. Any idea why the processes don't end? Ron Smith Database Administration [EMAIL PROTECTED] __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Enqueue types TC and JI
"Hagedorn, Linda" posted a message a few weeks ago about enqueue types JI and TC. Anjo Kolk says the following: TC is the incremental checkpoint, if I remember correctly the JI for the job queue processe. He's working on a new version of the wait events paper but not sure when it'll come out. Yong Huang [EMAIL PROTECTED] __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Thankx and more questions about SID
Hi, Bipin, The statistic# is the same as in v$statname or v$sysstat or v$sesstat. By the way, select distinct sid from v$mystat is not as fast as select sid from v$mystat where rownum = 1, which is the fastest way I've known to get your own SID. Yong Huang [EMAIL PROTECTED] you wrote: But what is significance of STATISTIC# and VALUE in v$mystat?/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
