Q what does it take to get to the oracle

Thanks for an amazing turnaround time, even in the midst of OracleWorld. Just amazing! Best wishes, Ted P. Very useful, but I have a question It's just a number, but I would like to know what the number means.

November 15, - pm UTC. If timed statistics is TRUE, this number is very accurate. How to find whether any specific package is being executed currently kumar, March 06, - pm UTC. Tom, I have a different situation , I need to run some key purging process, in order to do that I need to find and process whether one specific package is being executed or not?

Well this package is being called through another auto-job. Can you hint me? March 06, - pm UTC. This has given the exact information and shown that procedures name etc. My need here is I need to get the exact information whether the procedure is executing or not executing. Any suggetions. Chippa's invention is mind blowing, however if I need to run that query from another user who is not sys , perhaps I can not get the select grants on those sys.

Any work around to query these views from non sys user. Thanks Kumar. March 07, - pm UTC. Excellent warning - I will complete the just for the record J. SO, really, IF the asker wants to be always sure, he will need serialize to assure consistency , and make the needed alters in the code to "instrument". Regards, Chiappa. A reader, August 15, - pm UTC. Dear Tom, 1.

I have a third party application that is giving problem. It connects to oracle 8. When I click on a particular button it is giving, "ora table or view does not exist". Unfortunately, we don't have support for this application now. How to find out what sql it is trying to execute when I click that button. If I can find it, I may be able to replace the table that it is not able to find. When does this become active. Does that mean that the status will be active from the time I start executing the sql, till the time it finishes fetching the results?

Why is this so? Kindly clarify. Thanks a lot. August 23, - am UTC. Tom, I was hoping that the showsql script would show the current sql command so that I could follow scripts as they execute.

Even so, it is a great script!!! Using "shotgun programing" type selects, I narrrowed it down to one SQL statement in a script which goes into never never land. Running your showsql.

I am looking for something on the order of: user SQL Joeuser select blah,blah from tableX where boolean and soforth Thanks Evan. October 24, - am UTC. I want those SQL executed in my application not the onse executed by Oracle itself. November 12, - pm UTC. Houman, November 13, - pm UTC. I want this happen at the same time or before each query is executed in the database. November 13, - pm UTC. How to track down possible causes? March 27, - pm UTC. March 28, - am UTC. March 28, - pm UTC.

Perhaps what changed is "we are supporting more users" and the bind thing which kills scalability You see, I do not have as much information as you. All I knew was -- app ran for a while. I would trace the application and see what the application is waiting on -- that was mentioned before : avoid statspack for now, go after the application itself.

Hi Tom, I have the following code to update one column of a table and it took a long, long time. So then I tried to find out how bad it is, I only run 10 records see the commented out part in the where clause.

The elapsed time shows: My questions are: 1. The update time is way too long. How to improve this? I used your showsql. April 30, - am UTC. Tom, Thanks a lot for the code revision and suggestion. But how about my second question? That is "why the showsql. April 30, - pm UTC. Tom, I do have an update trigger on this table. Any suggestions? May 01, - am UTC. I'd be very very very concerned about the commit's and rollbacks in a trigger. All those databases are in different geographic locations.

The data of four tables in R1 database is replicated into R2 with a third party software in real time. The triggers on R2 tables are fired to populate the data into T5 and T6 with more detailed service information retrieved from other R2 tables. The R1 database is maintained by other company and we have no control on it. The third party software data replication is based on Oracle redo log files.

Whenever a transaction is committed on R1 database the data is replicated to R2 database. A transaction on R1 could be any size and any order of DMLs on those four tables.

This database architecture could introduce some potential problems: deadlock, because in addition to replicate data on R2 and R3, we also need to maintain other massive service data, which could cause DMLs on T5 and T6 tables concurrently with those replication DMLs. No matter how we tune our application code, the deadlock always happens due to the disordered DMLs applied by the replication software.

So finally we decided to use autonomous transaction in triggers to break our transaction down to single DML to avoid deadlock. We know it is not good programming practice to use autonomous transaction in a trigger, but it seems we have no choice. Why rollback takes so long to complete? Thanks again for you time.

May 02, - am UTC. I'm afraid you have a non-performat, non-scalable, rarely available data destroying implementation! Using autonomous transactions is the worst decision you could make well, besides "real time replication" that is Think about what commit and rollback means and which might entail "less work". Commit just says -- hey, what you've already done, lets roll with it. Rollback says -- hey, everything you just did -- regardless of how much work it was -- go back and UNDO it.

Subash, May 02, - pm UTC. Can use please explain the concept with example. May 02, - pm UTC. Tom, 1. So I thought the update is nothing to do with the data replication. Yes, we do know the autonomous transactions in a trigger could cause data inconsistency. We have a background process that constantly checks it and does proper work.

You are absolutely right. We found a bug in the trigger, which cause many rows updating to be rolled back. Now we have fixed it. I really appreciate your help and time. A reader, May 10, - pm UTC. May 10, - pm UTC. Hi I am using Oracle 10g on Red Hat 3. I am trying to find out who is executing it because it consumes over million buffer gets in 2.

I executed your showsql. Note: I find 10g is not suitable for production! July 01, - am UTC. I've found bugs in windows, I've found bugs in linux, I've found bugs in word, I've found bugs in cisco routers -- now what?

If RMAN has bugs which make restore impossible, stop using it. A reader, July 02, - pm UTC. How to find out all the sql statements executed by a session if I know the session number? I know Oracle is going to flush part of shared pool whenever it is full. July 02, - pm UTC. Thanks for your update. Can I use any trace or other mechanism to get all sql statement in the executed order? In sql trace I am going to see 1 sql with n executions if the user executed this stmt multiple times I am just looking for a log of all sql staments from a particular session using ascending order by execution time.

It does not appear to. Is there a way to? August 02, - pm UTC. Dear Tom, Is it possible to find out the number of times a procedure or a function get executed? Please do reply. August 03, - am UTC. I checked at OS level as following: ps -ef grep grep -v grep ggs 1 3 ? Please give me some help. Thanks as always. August 19, - am UTC. Tom, Thanks for your quick response.

What are they and what kind of jobs they are doing? Please help. Thanks again. August 19, - pm UTC. Tom, Showsql has proven itself a most useful tool! Thanks very much! I have been toying with the idea of being able to do "focused" tracing and auditing of SQL statements this way; for example, I contemplated using such a statement as a cursor within the body of a trigger, hoping to capture the updating application's SQL statement that launched the trigger.

Is there any way to accomplish this for the current session i. And how to track DML statements? Dear Tom. I want to track DML on tables of specific schema.

But there is DDL option for shema based trigger. Can you help me? September 08, - am UTC. Thanks Tom. I do understand that, I have to write sql script in order to create all trigger at once. A reader, September 21, - am UTC. Tom, showsql gives only few lines of code while tracking users code. September 21, - am UTC. Before I would run any script on my system, I would make sure I knew what it was doing - the "fix" here is pretty simple.

Russell, November 27, - pm UTC. Sometimes I like to see what query is running that is taking so long. To show me this I use the following script: clear breaks clear columns break on username skip 2 on SID skip 1 on osuser column username format a23 column osuser format a16 column users justify centre column sid justify centre select nvl sesion.

I have executed the script as SYS with autotrace to try to see what is happening. But I still don't get it. The result set is small, I expected the order by to result in a memory sort.

It looks like the join is causing a sort. Is that likely to be the disk sort shown in the stats? Hopefully this isn't a dumb question but, why so much redo? In fact, why is there ANY redo? I thought these were memory structures, therefore no IO required. Is there a less costly way to determine what SQL is being performed by active sessions? November 28, - am UTC. Russell, November 29, - am UTC. Thanks Tom, I think we do need some additional sort size, currently 4M. After some experimentation, I still can't change the behaviour of this query!

Elapsed: Does the alter session take immediate effect? If so, why are there still disk sorts when there is 1GB of sort area??? The time taken to execute these is not causing me any real problems, personally, I can wait. I am concerned that there is another problem here that may be contributing to other performance problems that are currently written off as an IO limitation. Is it possible that Oracle is performing IO on these internal tables and that this may be a source of performance drain???

November 29, - am UTC. In the last discussion thread, you mentioned that it is doing IO on temp and autotrace is telling you that. Can you please clarify me where in autotrace output I could find it. December 30, - pm UTC. Where'd my statement go? Neil, September 29, - am UTC. Hi Tom, I've been using showsql to see if my long-running statement is, well, still running.

It started some hours ago and was showing up quite nicely. Now, it seems to have gone. I get no rows returned. This is odd as I can still see the text of statements I ran prior to starting the long-runner. September 30, - am UTC. It was just an ordinary piece of SQL. It took nearly eight hours to run, and it disappeared from showsql's sight after about half an hour Version 8.

This morning, with the help of this inestimable site, I have reduced the run-time to 10 minutes - so it's not such an issue! However, I'd still be interested to know where it went. I noticed you had a very long day yesterday first postings at 6 am, then onto FSOUG, and last post at 9pm - so this can wait till Monday. Put your feet up, man!

Hmm, I cannot reproduce that - can you define "ordinary piece of sql"? Tom, I tried your script showsql and it works great in a dedicated server with one database. But have you updated the script to run in a RAC one than once instance? Thank you.

April 18, - am UTC. A specific detail that I don't think has been covered so far Is it possible to determine whether a transaction has made any uncommitted changes to the database? April 18, - pm UTC. Does that do it - if not - why not? Note: distributed transactions also have a rollback segment associated to them even if they don't make any modification. Regards Michel. Michels response seems to give me pretty well what I want?

April 26, - am UTC. But it will not tell you if the last sql statement executed made a change. Check whether the certain procedure is being used, and by which session Sean, December 11, - pm UTC. Hi Tom, I want to check whether the certain package is being used. But I can still see the entry in these two views even the execution completes, until I quit the session. Here is the procedure: Create procedure p1 is Begin Sys.

For some sessions of our Application, It shows the program name with the complete path and for few other sessions it just shows the Program Name exe without any path. Could you please explain Why, and how can I change that to Not to show Path. Thanks in Advance.. January 11, - am UTC. Finding a bad qry marc, January 12, - pm UTC. I have a 3rd party application that is error-ing out with ORA table or view does not exist.

But this app is not giving back the sql. I have run traces but the trace only returns valid sqls. Is there a way to trace a broken sql statement? Tom, I'm looking around for some help with this problem. Tkprof is awesome but I'm having a hard time marrying back the SQL issued in tkprof to the raw sql trace files to determine timestamps.

Basically I'm trying to replay the SQL from one machine on to another. I'd love to measure and benchmark it. I just can't seem to pull it off. We've identified a handful and we know that there will be more.

I s there an easier way to get the SQL from the 9i instance replayed on the 10g instance? Ram, March 26, - am UTC. Can we do Audit trail with out enabling the Init. March 26, - am UTC. A reader, March 26, - pm UTC. Thanks For your prompt reply. Is there any other way out to do Audit in Oracle 10gRelease2.

March 27, - am UTC. HI TOM.. A reader, March 28, - am UTC. To Ahlam Sadagh, 1. Please use lower case letters. What does your post question have to do with the original thread? Does Oracle store any history of this?

Thanks, Suprabhat. Hi Tom, I've been using your script showsql. I am using 9. Please could you explain 1. Thanks very much. May 14, - pm UTC. So how do we check the DDL in 9i? Is there another view where it can be seen. May 15, - pm UTC. A reader, May 22, - am UTC. How can i get the sql executed within the procedure. If i have three result set out from procedure and some select into statements within procedure.

I want to get all these statements. May 22, - am UTC. May 22, - pm UTC. Any suggestions why this behaviour and how to then track individual statements for the same session. I am only interested in how long it takes to run. May be I am missing something fundamental here. Thank you Sir. June 12, - am UTC. And it is the time the last CALL started - if the call was to a plsql procedure with thousands of SQL statements - they are all part of "that call".

The individual 'statement' is the CALL - the plsql block or whatever. I would expect it to reset at evey new DML even if the session is active. Is there any way to track that. Tom, Thanx for you response. Is there no other way then to track individual SQL statement Times if we dont want to use sql tracing. Only 1 SQL running? Ramanathan, September 12, - am UTC. Hi Tom I ran the shoqsql. What am I missing here? September 15, - pm UTC. One of them - well, it was looking at the other 14 sessions doing nothing that active one is the session doing the query to see what the sessions are doing.

But I need to know the sql statement that cause the table lock. June 16, - pm UTC. Now, if you have access to the ASH tables active session history , you can see most of the sql executed by a session - but in a connection pool environment that might not be very meaningful as sessions are 'long' and filled with lots of requests over time. Is it because the ash never sample the "delete from t1"?

June 17, - am UTC. It will not be complete, it is a sample from above Now, if you have access to the ASH tables active session history , you can see most of the sql executed by a session I am asking that because my DB control is showing a number that I could not believe.

An sql was executed about times within every hour. That is 8 times per second. So i want to crosscheck this number. Click on a snapshot.

And i have this statistic below. Why does Oracle Explorer no longer run the 'prometheus' module? Why does Oracle Explorer no longer run the 'cst' and 'netconnect' modules? Where can I submit my questions about Oracle Explorer? Oracle Explorer is a data collection tool. The tool comprises shell scripts and a few binary executables. This varies from version to version. You can get a list of all the collected files and executed commands if you specify the following option on the command line:.

The resulting file contains the list of all files and commands collected by each Oracle Explorer module. Oracle Explorer supports many command-line arguments.

To find out what arguments the current version of Oracle Explorer supports, consult the man page:. Go to the Oracle Explorer Document Collection web page and read the Oracle Explorer Third Party License Agreement , which explains the terms and conditions under which the third-party software that is included in Oracle Explorer is available for use. The target directory for Oracle Explorer output must satisfy at least one of the following conditions, or else Oracle Explorer will not run:.

The size of the previous Oracle Explorer output does not exceed the available space in the target output directory. For permanent removal of the IPS version, you should run explorer -clean to remove all traces of the application including configuration files and cron entry.

Data files stored in output directry is not removed. Also refer to the upgrade instructions found on the SunSolve web site. Install Oracle Explorer on an NFS-exportable file system and mount that file system on the other systems on which you want to run Oracle Explorer. You need to create a defaults file for each system on which you will be running Oracle Explorer.

Create one file with the common information in it, and make copies for each system on which you want to run Oracle Explorer. The best approach would be to use the host ID in the file name for the defaults file. For each defaults file, enter the machine-specific information. The defaults file is easy to read and follow. When you are ready, you can run Oracle Explorer on each of the systems by using the following syntax along with any other options you want to use:.

Oracle Explorer can also be run using cron as long as the NFS mount is accessible when the cron job runs. You can also specify serial numbers and platform names for all systems in one defaults file. Yes, Explorer can be installed via Jumpstart successfully. However, during the Jumpstart installation some system resources, like crontab, are not available. In such cases appropriate warning message will be displayed on the console.

In case of 'crontab' example, Explorer installation will throw the following message to the console:. Oracle Explorer 3. Oracle Explorer 4. To mask IP addresses, use the following commands:. For sh and ksh , use explorer -w! After Oracle Explorer 4. Oracle Explorer cannot run without the defaults file, and the version of the defaults file must match the version of the package.

The file is used to provide valuable information so that the Oracle Explorer data can be retrieved more easily by Field Engineers and System Support Engineers. The defaults file should not be removed. Oracle Explorer notifies you when fields are missing from the defaults file.

More information on the defaults file format is available in the explorer 4 man page. Because Oracle Explorer can stress the system, it might uncover an operating system bug. In this case, you can disable a module temporarily or permanently. For a temporary, one-time change when using the sh or ksh shells, type the following command:.

For a temporary, one-time change when using the csh or bash shells, type the following command:. For example, the explorer -v -w default,! Oracle Explorer's run time is a function of the server it is running on. Servers with many disks run substantially longer than servers with a small number of disks.

Oracle Explorer allows individual data gathering modules to be excluded or groups of modules to be excluded. For data gathering on a scheduled weekly basis, if possible, exclude the network and storage modules:. When using sh or ksh , use explorer -w default,!

The t3 script checks each disk to determine if it is a SUN-T3 model. This check takes longer on servers with a large number of disks than on servers with a small number of disks. This check is done even if no SUN-T3 disks are attached to the server. If the server does not have any SUN-T3 disks attached, this module can be excluded from the Oracle Explorer runs as follows:. When using sh or ksh , use explorer -w t3. When using csh or bash , use explorer -w!

The t3extended script logs in to the T3 array for each command. No alternative other than streaming four to six commands per telnet session is provided. For servers with a large number of T3 arrays attached, this process takes a considerable amount of time.

If possible, avoid running the t3extended module as part of the scheduled weekly Oracle Explorer run as follows:. When using sh or ksh , use explorer -w t3extended. Using one time format allows Sun to know exactly when each Oracle Explorer process was run without performing time conversions. Converting the time would require that the Oracle Explorer output file be parsed and the TZ variable extracted. Information gathered from customer machines is transmitted to Sun and stored to enable faster, more effective diagnosis and customer support.

The data is also reviewed in the aggregate, without reference to individual customers, for planning future offerings and enhancements. Once the data is received, Sun protects it to the same high standards it applies to its own information resources.

Data storage machines are tightly secured and both physical and electronic access to the machines is restricted to approved support personnel only. Removal or sharing of any data outside of Sun is not permitted by Sun's Data Stewardship policies.

Only internal Sun employees and approved contractors can access the stored Oracle Explorer data, which is controlled by Sun's internal network-access control policy. The output format from Oracle Explorer is based largely upon the output from system commands. If those commands have different output when a new version or bug fix comes out, then the Oracle Explorer output will also change.

To do this, use the explorer -g option to configure Oracle Explorer to send its output automatically by means of HTTPS every time the explorer command is run with the -P option.

Oracle Explorer 5. Explorer 5. Here is an example entry:. For Platinum contracts, subscription to Sun System Analysis is included into the contract. You can also view the raw text files, which are located at:. The munpack utility is a freeware utility used to decode MIME messages. Because Oracle Explorer can come in multiple parts, a tool is needed to put the MIME messages together into the original explorer tar file.

While there a several possible causes, one possibility is a reverse-lookup failure. All email directed to Sun is reverse-address checked. If the sender's return address is not a valid, reachable address, the email is rejected. Before sending an email, verify that your return email address can be accessed externally.

Run the explorer -g command to add or edit a Oracle Explorer cronjob. When prompted about whether you want to schedule running Oracle Explorer in cron, answer yes. You can choose to schedule running Oracle Explorer on a weekly basis or on a monthly basis. Oracle Explorer adds an entry in the root crontab file. The following are example crontab entries:. Oracle Explorer version 5. Moreover, it deletes old cron entry when you run explorer -g and opt to not schedule the cron job. Explorer 6.

If you opt to save it, Explorer leaves the cron entry commented out during removal; otherwise, it removes the entry. Explorer looks for the exact executable with full path to identify the cron entry, instead of the trailing comment SUNWexplo.

Modify the superuser's crontab entry with any of the following options that is, explorer -E myaddress mycompany. This feature is not currently implemented on the receiving end. To run a group in interactive mode, specify the keyword interactive with the group alias. In OpenSolaris, Explorer is packaged and installed differently than on other operating systems; however, the commands and various options used to run Explorer remain the same. To use Explorer from an alternate path, complete the following steps.

Explorer supports pkg 5 standard of packaging mechanism from 6. In addition to being delivered in pkg 5 form, Explorer will continue to be delivered as an SVR4 package. Explorer must be published first to a Depot server and must be installed from there using the STB for OpenSolaris bundle. No, IPS packaging mechanism does not support or invoke any script during pkg uninstall , so pkg uninstall cannot remove the cron job for Explorer automatically. A new command line option, explorer -clean has been provided to uninstall Explorer pkg 5 , remove all traces of Explorer including configuration files and cron entry.

This command should be run to remove the application completely. For upgrading to a higher version of Explorer pkg 5 , removal of configuration files and cron entry are not required. No, starting from 6. Though the write cache status data is important in the diagnosis of data integrity and performance issues, it causes unwanted failover behavior in some Veritas DMP configurations. Explorer needs a user with admin privileges in order to connect to ILOM to execute snapshot command.

Yes, Explorer 6. This gzip binary is capable of handling large files that contains 2GB or more data. If system already has a gzip installed, the bundled gzip utility will not be installed on the host.

It recognizes all of the vendors who are listed in the recent libfrureg. However you can run explorer by installing gzip on your system. You are probably running explorer X86 9 or below. If user does not replace '-' with a valid dataset before running the module, snapshot data will be collected for default dataset 'fruid'.

Yes, Explorer ilomsnapshot module supports encryption for snapshot data collection on ILOMs in interactive mode. In non-interactive mode using configuration file , encryption is not supported. User will be prompted to opt for encryption and subsequently will be asked to provide encryption password.

User will have to run the following commands to decrypt and unzip data. All collectibles of the prometheus module are now collected under RAIDmanager. Oracle recognizes only service request numbers and not the case numbers. Starting with Oracle Explorer 6.

Previous : Book Information. Note — Oracle Explorer includes some third-party redistributable software.