You will no longer receive e-mail notifications from this forum.
Log-In to post
Hi,SQL Optimizer 18.104.22.16859 x64 on Windows 7 x64 (this is enough for problem I face).I have situations that some queries last for several hours, so all candidate are not possible to be run in one session in one day, because db is refresh on daily level (dolly database clone).So when I stop the test, I have original sql value and maybe some better candidates.I save all and load tomorrow for continuing.When I select several queries (that were not run) and choose "Run selected" all testing is not monitoring previous "Best time" so queries runs forever, regardless they go far beyond time of best solution (Org or candidate...never mind).But when in that second day some query run in "acceptable time" (near or even better that current) then again all other testing queries follow that time.
Next day situation is the same if I have to stop the session.
So mine question is:Is this a bug?How to overcome that in next session on not executed or repeatedly executed candidates, they follow timings from the best time.
Of course it is not acceptable to include the best candidate with them again, because this is just waste of time. But even in this scenario, SQL Optimizer will choose whic candidate will run first so.Thx for your help in front.
We have seen a view in our account which is as below (Created by other resource long back)
select /*+ use_merge(sub,seg1) PARALLEL(sub) */ sub."CATEGORY",sub."JE_BATCH_NAME",sub."JE_HEADER_DESCRIPTION",sub."JE_HEADER_NAME",sub."JE_LINE_DESCRIPTION",sub."DESCRIPTION",sub."NAME",sub."LOCATION",sub."TRANSACTION_NUMBER",sub."TRANSACTION_DATE",sub."DIST_LINE_NUMBER",sub."PO_NUMBER",sub."PAYMENT_NUMBER",sub."SALES_ORDER",sub."INVOICE_PAYMENT_STATUS",sub."CURRENCY_CODE",sub."EXCHANGE_RATE",sub."JE_ACCOUNTED_DR",sub."JE_ACCOUNTED_CR",sub."AMOUNT",sub."STATUS",sub."FISCAL_PERIOD",sub."FISCAL_YEAR",sub."PERIOD_NAME",sub."EFFECTIVE_DATE",sub."JE_SOURCE",sub."JE_CATEGORY",sub."TRANSACTION_TYPE",sub."DOCUMENT_NAME",sub."GL_ACCOUNT",sub."COMPANY",sub."DEPARTMENT",sub."ACCOUNT",sub."PROJECT",sub."PRODUCT",sub."CUSTOMER",sub."GL_DISTRIBUTION_DATE",sub."POSTED_DATE",sub."LAST_BUILD_DATE",sub."JE_BATCH_ID",sub."JE_HEADER_ID",sub."JE_LINE_NUM",sub."CODE_COMBINATION_ID",sub."AP_INVOICE_ID",sub."INVOICE_DISTRIBUTION_ID",sub."CUSTOMER_TRX_ID",sub."CUSTOMER_TRX_LINE_ID",sub."PO_HEADER_ID",sub."PO_LINE_ID",sub."ROW_ID", seg1.description Company_Desc, seg2.description Department_Desc, seg3.description Account_Desc, seg4.description Project_Desc, seg5.description Product_Descfrom apps.xx_fnd_flex_desc_v seg1, apps.xx_fnd_flex_desc_v seg2, apps.xx_fnd_flex_desc_v seg3, apps.xx_fnd_flex_desc_v seg4, apps.xx_fnd_flex_desc_v seg5, apps.xx_gl_subledger_mv subwhere sub.company = seg1.flex_value and seg1.flex_value_set_id = 1003775 and sub.department = seg2.flex_value and seg2.flex_value_set_id = 1003774 and sub.account = seg3.flex_value and seg3.flex_value_set_id = 1002347 and sub.project = seg4.flex_value and seg4.flex_value_set_id = 1002348 AND SUB.PRODUCT = SEG5.FLEX_VALUE and seg5.flex_value_set_id = 1002349
But, I am not sure how this hint works. Can anyone please help me on how this hint works. I googled, but I didn't understood :(
Thanks in Advance
i have a virtual machine with windows 8.1 and installed on that Toad for oracle 22.214.171.124 in most time working and run a query will be take a few minutes and will be so slow but i use this version of toad on my desktop pc with same resources such as virtual machine and don't have any problem on desktop pc now i want to know is there any specific config on toad for optimize or get best performance ?
how can solve problem ?
Hello, all. I am very new to the SQL language. Here’s hoping someone can help with my question.
I am trying to select data from two tables using a column that is in both tables. My question requires a bit of setup.
Table1 has a lot of data in it. For the sake of my question, we’ll call the columns I am interested in as IDTag and ColumnA.
Table2 has its own data in it, but also has the identical IDTag column from Table1. The column I am interested in this table is ColumnB.
ColumnA doesn’t exist in Table2, nor does ColumnB exist in Table1.
My question is…what query do I need to use to select the information in ColumnA and ColumnB using the IDTag column?Thank you in advance for any help.
SQLOptimizer 9.2 x64 on Oracle 11.2.03 x64.
Have a lot of queires that are attacking a tables in which number of rows are frequently changed. Consequence is that SQL Optimizer fail to verify queries which doesn't return the exact number of rows as original.
How to disable that checking or is there any way to overcome this behavior?Brg
Oracle 126.96.36.199 x64, Client 188.8.131.52 x64, SQL Optimizer 9.2 x64.Plase fix this or at least alow me to test some beta ...
This is probably a really basic question, but I've had to pause long-running jobs in Inspect SGA and Optimize SQL, but then when I reopen those jobs, I don't know how to restart from the paused location without completely restarting from scratch. What's the trick?
Toad for Oracle Xpert 12.1 with DB Admin. Auto optimize SQL "View by" pulldown allows I/O, but when I run it, in the I/O column every alternative shows "not setup". What do I have to set up to make this work?
SQL Optimizer 184.108.40.20659 64 against Oracle 220.127.116.11 with Oracle 18.104.22.168 x64 client
Have notified that when you use longer list (1400 bind sets with 8 binds in each set) on 5 chosen queries, SQL Optimizer become very unstable and slow.
Probably all data is used in memory ...
BrgDamirP.S.If needed, sending example file on mail (I have Alex one)
Different Binds values use...Curious "Average Psychical Reads" column value in Original query result (8.333.333.333.....) when all inside values are 1 or 0:
Do you need something else to see where the bug is?
Sometimes I have a request to test some optimization SQLs agains several hundreds of binds set.For that I have wrote a utility that generate a file in a way that SQL Optimizer save it.
But when I want to import it into new optimizer session (no binds were imported before!!!) I got mixed data and errors.
When I remove first three rows and all "Set xxx" column from mine file, I can import data but then I have to define manually many columns data types.
How to make it work without human touch ?
Old desktop: Windows 7, Toad for Oracle Xpert v 12.1 with DB Admin Module. Simple query, Optimization works fine when run in Toad for Oracle Editor Window. 400 rewrites checked, 53 to be tested, many better alternatives found already.
New desktop: OSX El Capitan with Crossover, Toad for Oracle Xpert 12.10 with DB Admin module. Same query, optimization fails as follows.
When run from Toad for Oracle Editor Window:"No better alternatives found" 0 of 0 tested. (1 rewrites checked)
When run from Dell SQL Optimizer for Oracle 9.2.1 window, error "Failed to retrieve scenario explain plan" OLE 80004001 (even though I generated an Explain Plan under Toad.
When I try to optimize any batch, I get this error:
ORA-01423: error encountered while checking for extra rows in exact fetch ORA-00942: table or view does not exist ORA-06512: at line 57
This is the query I am tuning:
SELECT REGEXP_REPLACE (ftvorgn_title, ',', NULL) FROM ftvorgn x WHERE ftvorgn_orgn_code = '162232' -- v_department AND ftvorgn_eff_date = (SELECT MAX (ftvorgn_eff_date) FROM ftvorgn WHERE ftvorgn_orgn_code = x.ftvorgn_orgn_code AND ftvorgn_coas_code = x.ftvorgn_coas_code AND ftvorgn_eff_date <= SYSDATE);
Am using Advanced SQL Optimization, Optimize SQL by SQL Rewrite options.
Thank you for your assistance.
I would like to get those infos in grid.
Current situation is (22.214.171.12459 x64)
Hello, I have some queries in our production environment that I believe that can be optimized
I have installed this product and I would like to ask if I choose to optimize SQL and copy the statement there and choose the "Rewrite" option, will I have anything changed to my database?
For example will any indexes be added or anything modified?
I do not want to change anything. I just want to see if there are any alternatives and check if they are more effecient, but not to change something in the database.
First time using both Optimize SQL & Batch Optimize SQL. I'm just using a basic SQL statement for both and I keep getting a Invalid SQL with an ORA-00904 - Invalid Identifier. I put in the schema and execution schema name and I still get the error. any suggestions?
FROM (SCHEMA NAME).ACT_CDS
I need to know the concept Analysis Impact, when i test indexes or change parameters,
and we choose to collect SQL workload, i want to know if optimizer run all sql statement which i define to test the change with sql workload ?
what the difference between this features and benchmark factory
Hi,I have one SQL Optimization task which runs on database where daily refresh is done (db start at 03:00 and shutdown at 00:00).Optimizer generate 1300 cases and all cannot be run in one day.
When db shutdown, Optimizer hangs and in many cases it is not possible to resume later (after SQL Optimizer crash).If I stop after some cases, save the result and reopen again-want to resume, SQL Optimizer resume from the beginning.
Seems that all should be in such a cases done manually. Select portion of cases and run and manually save and continue next time where it stopped previously.
is there a better way to do that?
P.S.I would be possible if SQL Optimizer can be started from command line to start task automatically...this will be really a nice new feature.
I want to know how can i install Quest SQL Optimizer for Oracle 126.96.36.199 which is not being installed through Toad for Oracle 9.7 Commercial Installer , the screenshot is attached here . My OS is Windows 7 Enterprise . The Error is Net Framework 2.0 is not found on this system .
N.B. The problem described here may be related with this issue https://support.software.dell.com/kb/46926 . If so , then as per the solutions provided , where can I find "Quest SQL Optimizer 7.4 Vista compatible installer hotfix"(In WORKAROUND 3) or where can i download version 7.4.1 (Current Status of this hotfix).
Hi,SQL Optimizer 9.1 x64. here are steps that I do ... want to have two scenarios of the same query.
Why? And how to perform "Save As"? I need tio have "x" and "y" saved.Pls forgive me if I miss something.