RE: Unix question
Hi, You can use the grep command in the following manner: grep -v try filename The option -v negates the search pattern, meaning rows which do not have a try string in them. The string can be enclosed in double quotes as well, this is useful if you are searching for multiple patterns... Hope this will help. Regards kesh -Original Message-From: Gene Sais [mailto:[EMAIL PROTECTED]Sent: Monday, December 22, 2003 6:39 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Unix question grep command will help you or you could venture to the sed command. grep is probably the most used unix command. To learn more, man grep . >>> [EMAIL PROTECTED] 12/22/03 07:54AM >>>Hallo all of you,Is there anyone whom could help me with the unix command how to find all rows , that doesnt exists "try" in a file.I mean how to find all rows which doesnt have the characters try in.Maybe this is too simple, but would appreciate anyone whom could give me some quick help.Thanks in advanceRoland-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing). This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
Performance Query - help
Hi all, I have a query which takes 4 seconds to execute. The query looks like: SELECT DISTINCT tfc_fct_value FROM PD_TMP_AGG_VALS_5071_544,PD_OUTPUT_ITEMS,tp_fact_ctl WHERE OUI_OUT_ID=5071 AND OUI_FACT_ID IS NOT NULL AND oui_item_type=tfc_item_type AND vd17=oui_fact_id ORDER BY tfc_fct_value Another query follows this : SELECT DISTINCT tfc_fct_value FROM PD_TMP_AGG_VALS_5071_544,PD_OUTPUT_ITEMS,tp_fact_ctl WHERE OUI_OUT_ID=5071 AND OUI_FACT_ID IS NOT NULL AND oui_item_type=tfc_item_type AND vd18=oui_fact_id ORDER BY tfc_fct_value Each of these queries take approximately 4 seconds to execute. As you can see above, the two queries differ in the vd columns. I need to execute the same queries atleast twice for various conditions. The queries return 4 rows. Initially I was creating a temporary table to hold the result set and then query the table. Later it was decided that there could be few more queries with differing vd, taking values like VD19, VD20 etc.. Now, my question is will it be alright if i create say 5 temporary tables for each of the distinct VD values or execute the query without creating tables. Suppose i have 5 vd values viz VD17, VD18, VD19, VD20 & VD21... If i am not creating temporary tables, and i am running the same queries twice ( this is for some other condition ), it would take around 2* 4* 5 = 40 seconds... Whereas if i create tables and then query through them, it would take around 22 seconds.. This is because once the table is created for the first time,, then querying it is faster.. Now can anyone please tell me if creating temporary tables is fine or is there any method to cache the query or some other way... Please put forward your comments and suggestions. Sorry for the big mail. Thanks in advance keshav Regards Kesh " I've always felt it was not up to anyone else to make me give my best " This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
Query on Dual
Hi all, Can anyone tell me the performance issues related to the excessive usage of the DUAL table in pl/sql procedures? I remember reading an article about this ( I dont remember if it was in ASKTOM site ) wherein it was told that excessive usage of DUAL comes with a cost and there was a suggestion in that to create a view on the system table X$DUAL.. If anybody has a link to that please send it to me. Thanks to one and all. Regards Kesh This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
RE: procedure/function error
Hi purushotam, since it is a function that you are creating , its return value needs to be collected. hence after BEGIN clause give a statement like v_ret:=EMP_SEL('gali'); where v_ret is a local variable of type varchar2 This will work. Regards Kesh -Original Message-From: purushottam krishna hegde [mailto:[EMAIL PROTECTED]Sent: Friday, June 06, 2003 5:05 PMTo: Multiple recipients of list ORACLE-LSubject: procedure/function error hi all, this is purushottam hegde from Bangalore(IND) i am relatively new to oracle and so to this group. iam having a problem with function... it goes like this. SQL> CREATE OR REPLACE FUNCTION EMP_SEL(ename IN Varchar2) return varchar2 is 2 resex varchar2(3); 3 Begin 4 SELECT sex into resex from emp where name=ename; 5 return(resex); 6 END; 7 / Function created. SQL> EXECUTE EMP_SEL('gali');BEGIN EMP_SEL('gali'); END; *ERROR at line 1:ORA-06550: line 1, column 7:PLS-00221: 'EMP_SEL' is not a procedure or is undefinedORA-06550: line 1, column 7:PL/SQL: Statement ignored pl help me in this regard thanking all of u purushottam hegde Do you Yahoo!?Free online calendar with sync to Outlook(TM). This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
RE: RE: Schedule Analyze using DBMS_STATS ???
Hi all, Everyone is recommending DBMS_STATS to be used for computing the statistics. But even after specifying the parallel option , DBMS_STATS is taking lots of time ! in comparison to Analyze... In case of tables with a million records the query just hangs when i use the stats package... Can anybody tell me whts happening ? Regards kesh -Original Message- Sent: Tuesday, June 03, 2003 8:55 PM To: Multiple recipients of list ORACLE-L Sorry JP, I should have clarified that DBMS_STATS is the way to go, when on a 9i or above release. One of the simple factors being that ANALYZE is being deprecated.. There seemed to be a number of bugs/quirks, whatever you want to call them, in certain 8.1.x versions, which are now fixed within 9i.. There's a number of threads about the issues, available in the list archives (you can find them at http://www.faqchest.com), which I dug around - as I remembered seeing a post by Connor McDonald, where he gave away an alternate script to gather stats, here's the mail: http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0210/ora-021011/ora02100802 _06150.html I think the general consensus is that DBMS_STATS is quicker. I've never personally done any comparative benchmarks to corroborate this sheep following attitude though.. ;) Regards Mark -Original Message- Sent: 03 June 2003 14:55 To: Mark Leith Cc: [EMAIL PROTECTED] Thanx a lot Mark. Sure , your code has given me a starting point. let me change accordingly to use DBMS_STATSe and give a try :-) So,can i conclude that DBMS_STATS is better than ANALYZE ?! Someone over the list mentioned that DBMS_STATS is slower. is it so ? What is your opinion regarding this ?! just curious to know ! Regards, Jp. 3-6-2003 22:01:32, "Mark Leith" <[EMAIL PROTECTED]> wrote: >Prem, > >Use DBMS_JOBS to run the analyze: > >=== > >undef username >undef password >grant analyze any to &&username >/ > >connect &&username/&&password > >CREATE or REPLACE PROCEDURE analyze_tables ( > v_stat_type IN VARCHAR2 := 'COMPUTE') AS > CURSOR c IS > SELECT DISTINCT owner > FROM all_tables > WHERE owner not in ('SYS','SYSTEM'); > BEGIN > FOR any_row IN c LOOP >dbms_utility.analyze_schema( > any_row.owner,v_stat_type); > END LOOP; > END; >/ > >=== > >variable jobno number >declare jobno number; > BEGIN > dbms_job.submit(:jobno, >'begin &&username.analyze_tables; end;', >to_date('03jun0304:00','DDMONYYHH24:MI'), >'trunc(sysdate)+(1+(4/24))'); > END; > >=== > >The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM, >at 4:00am every day. Modify it to your own needs, but it should give you a >starting point.. > >I would also recommend using DBMS_STATS to generate your statistics. > >Have fun! ;0) > >Mark --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.484 / Virus Database: 282 - Release Date: 27/05/2003 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith 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). This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
RE: Need help
Yes, I am talkin about the time it takes. The analyze command takes around 2 mins to esimate statistics of a table with a Million records whereas the dbms_stats package function is merely hanging !! And besides, Djordje is telling that they nearly take up the same time !! But i am not experiencing anything like that.. Do i have to change any settings to make it run faster ?? -Original Message- Sent: Monday, June 02, 2003 9:40 PM To: Multiple recipients of list ORACLE-L Kesh - What do you define as performance? 1. How fast a table is analyzed? 2. The performance of the queries that use the statistics that were collected? What Oracle version are you on? In theory, you can analyze tables when the system load isn't so great. In 9i you can even have Oracle decide when a table needs re-analyzed so it really lowers the system requirements. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, June 02, 2003 9:50 AM To: Multiple recipients of list ORACLE-L Hi all, Can anyone tell me which of the following performs better in collecting statistics of a table: Analyze command or Dbms_stats.gather_table_stats I tried my queries with both the options -- but always Analyze is out-performing the dbms_stats. But i read in a document that Dbms_stats performs better !! Besides I also read that Analyze command will be deprecated from Oracle 9i. So can anyone tell me why dbms_stats is performing poor... Are there any paramters that need to be set to increase the performace of dbms_stats ? Thanks in advance. Regards Kesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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). This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
Need help
Hi all, Can anyone tell me which of the following performs better in collecting statistics of a table: Analyze command or Dbms_stats.gather_table_stats I tried my queries with both the options -- but always Analyze is out-performing the dbms_stats. But i read in a document that Dbms_stats performs better !! Besides I also read that Analyze command will be deprecated from Oracle 9i. So can anyone tell me why dbms_stats is performing poor... Are there any paramters that need to be set to increase the performace of dbms_stats ? Thanks in advance. Regards Kesh This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com