SQL Optimizer for Oracle Forum - Toad for Oracle - Toad World
Damir Vadas
Follow / 28 Apr 2017 at 9:20am / Toad for Oracle
Latest post by on 2 May 2017 at 5:18am

Optimizing queries in two sessions problem (obey previous best time...

Expand content
Hi, SQL Optimizer 9.2.0.4159 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...

Hi,
SQL Optimizer 9.2.0.4159 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.


Brg
Damir Vadas

141 3 / Create an account to join the discussion.
Srivathsava
Follow / 25 Apr 2017 at 3:18pm / Toad for Oracle
Latest post by on 26 Apr 2017 at 4:45am

How the 'use_merge' hint works

Expand content
Hi Experts, 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"...

Hi Experts,

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_Desc
from
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 sub
where
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

Unanswered 95 1 / Create an account to join the discussion.
baber
Follow / 18 Apr 2017 at 8:09pm / Toad for Oracle
Latest post by on 19 Apr 2017 at 3:44am

how can get best performance from toad on virtual machine

Expand content
Dear all Hi i have a virtual machine with windows 8.1 and installed on that Toad for oracle 11.6.0.43 in most time working and run a query will be take a few minutes and will be so slow but i use this...

Dear all

Hi


i have a virtual machine with windows 8.1 and installed on that Toad for oracle 11.6.0.43 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 ?

my resources:

8G memory

8vcpu

how can solve problem ?

124 1 / Create an account to join the discussion.
aemeditor
Follow / 23 Jan 2017 at 10:06pm / Toad for Oracle
Latest post by on 31 Jan 2017 at 4:26pm

How do I select different columns from two different tables?

Expand content
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...

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.

Answered 527 2 / Create an account to join the discussion.
Damir Vadas
Follow / 12 Dec 2016 at 1:45pm / Toad for Oracle
Latest post by on 14 Dec 2016 at 1:35am

Disable checking of returned number of rows

Expand content
Hi, 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...

Hi,

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

Damir

Answered 588 5 / Create an account to join the discussion.
Damir Vadas
Follow / 12 Dec 2016 at 10:09am / Toad for Oracle
Latest post by on 13 Dec 2016 at 3:09pm

sysdate@! stil makes huge problems

Expand content
Hi, Oracle 11.2.0.3 x64, Client 11.2.0.3 x64, SQL Optimizer 9.2 x64. Plase fix this or at least alow me to test some beta ...

Hi,

Oracle 11.2.0.3 x64, Client 11.2.0.3 x64, SQL Optimizer 9.2 x64.
Plase fix this or at least alow me to test some beta ...

490 1 / Create an account to join the discussion.
Eric Gan
Follow / 29 May 2014 at 8:22pm / Toad for Oracle
Latest post by on 24 Nov 2016 at 11:02am

Restarting a paused activity

Expand content
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...

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?

Answered 33745 19 / Create an account to join the discussion.
james.ritterbusch
Follow / 8 Nov 2016 at 7:58pm / Toad for Oracle
Latest post by on 9 Nov 2016 at 1:05pm

View by I/O shows "not setup"

Expand content
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...

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?

737 1 / Create an account to join the discussion.
Damir Vadas
Follow / 4 Nov 2016 at 9:27am / Toad for Oracle
Latest post by on 4 Nov 2016 at 3:52pm

Performance problems using bind list with more than 1400 entries

Expand content
SQL Optimizer 9.2.0.4159 64 against Oracle 11.2.0.3 with Oracle 11.2.0.3 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...

SQL Optimizer 9.2.0.4159 64 against Oracle 11.2.0.3 with Oracle 11.2.0.3 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 ...

Brg
Damir
P.S.
If needed, sending example file on mail (I have Alex one)

770 2 / Create an account to join the discussion.
Damir Vadas
Follow / 4 Nov 2016 at 8:54am / Toad for Oracle
Latest post by on 4 Nov 2016 at 10:34am

Binds Values and "Average Psychical Reads" column value

Expand content
SQL Optimizer 9.2.0.4159 64 against Oracle 11.2.0.3 with Oracle 11.2.0.3 x64 client Different Binds values use...Curious "Average Psychical Reads" column value in Original query result (8.333...

SQL Optimizer 9.2.0.4159 64 against Oracle 11.2.0.3 with Oracle 11.2.0.3 x64 client

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?

766 1 / Create an account to join the discussion.
Damir Vadas
Follow / 28 Oct 2016 at 10:48am / Toad for Oracle
Latest post by on 3 Nov 2016 at 12:20pm

Import binds from file in new session

Expand content
Hi, 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. i.e. ...

Hi,

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.

i.e.

QSOBindvars
"QSOSetName",":b0",":b1",":b2",":b3",":b4",":b5",":b6",":b7"
"QSODataType","NUMBER","VARCHAR2","VARCHAR2","NUMBER","NUMBER","NUMBER","NUMBER","NUMBER"
"Set 1","350932188","20031103","20160927","-1","0","0","9999999","1"

...
"Set 1076","343972578","20031103","20160930","-1","0","0","9999999","1"

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 ?

Brg

Damir

Answered 1032 10 / Create an account to join the discussion.
james.ritterbusch
Follow / 31 Oct 2016 at 7:01pm / Toad for Oracle
Latest post by on 1 Nov 2016 at 2:00pm

Optimizer not providing optimizations

Expand content
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...

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.

Suggestions?

834 1 / Create an account to join the discussion.
eboyen
Follow / 27 Jun 2016 at 5:47pm / Toad for Oracle
Latest post by on 29 Oct 2016 at 6:36pm

Sql Optimizer 9.2.0 ORA-01423: error encountered while checking for...

Expand content
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...

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.

Unanswered 2796 7 / Create an account to join the discussion.
Damir Vadas
Follow / 25 Oct 2016 at 10:42am / Toad for Oracle
Latest post by on 27 Oct 2016 at 8:19am

Avg, min, max logical reads in Different bind grid

Expand content
I would like to get those infos in grid. Current situation is (9.2.0.4159 x64)

I would like to get those infos in grid.

Current situation is (9.2.0.4159 x64)

984 3 / Create an account to join the discussion.
chris02
Follow / 11 Apr 2016 at 1:03pm / Toad for Oracle
Latest post by on 13 Apr 2016 at 11:43am

"Rewrite" option for sql optimization queries - are occured...

Expand content
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...

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.

Thank you!

Unanswered 2408 7 / Create an account to join the discussion.
ted.keys83
Follow / 17 Mar 2016 at 12:58pm / Toad for Oracle
Latest post by on 18 Mar 2016 at 1:30am

SQL/BATCH OPTIMIZE SQL INVALID SQL/IDENTIFIER

Expand content
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...

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?

SELECT AC_CD,

AC_DESC,

AC_CREATE_BY,

AC_CREATE_DT,

AC_UPDATE_BY,

AC_UPDATE_DT,

AC_CLOSE_DT

FROM (SCHEMA NAME).ACT_CDS

Unanswered 2263 1 / Create an account to join the discussion.
EE
Follow / 12 Jan 2016 at 5:44pm / Toad for Oracle
Latest post by on 28 Jan 2016 at 5:19am

Analysis Impact

Expand content
Hello , 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...

Hello ,

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

Thanks

2750 3 / Create an account to join the discussion.
Damir Vadas
Follow / 27 Jan 2016 at 12:32pm / Toad for Oracle
Latest post by on 28 Jan 2016 at 5:16am

Continue optimize after instance restart

Expand content
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...

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?

brg
Damir

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.

Answered 2348 1 / Create an account to join the discussion.
Nilabja Saha
Follow / 17 Jan 2016 at 4:02pm / Toad for Oracle
Latest post by on 19 Jan 2016 at 1:53am

Installation of Quest SQL Optimizer for Oracle 7.4.0.80

Expand content
Hi , I want to know how can i install Quest SQL Optimizer for Oracle 7.4.0.80 which is not being installed through Toad for Oracle 9.7 Commercial Installer , the screenshot is attached here . My OS is...

Hi ,

I want to know how can i install Quest SQL Optimizer for Oracle 7.4.0.80 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).

Unanswered 2531 2 / Create an account to join the discussion.
Damir Vadas
Follow / 24 Dec 2015 at 7:32pm / Toad for Oracle
Latest post by on 14 Jan 2016 at 8:04am

Save as option

Expand content
Hi, SQL Optimizer 9.1 x64. here are steps that I do ... want to have two scenarios of the same query. Create new tuning session generate rewrites run tuning with some binds save results under...

Hi,
SQL Optimizer 9.1 x64. here are steps that I do ... want to have two scenarios of the same query.

  1. Create new tuning session
  2. generate rewrites
  3. run tuning with some binds
  4. save results under name "x" (only "Save session" icon is possible to choose)
  5. change something (the way of optimizer runs or bind variables)
  6. Want to save under different name...
  7. press save, change name to "y" (only "Save session" icon is possible to choose)
  8. Old "x" is gone!

Why? And how to perform "Save As"? I need tio have "x" and "y" saved.
Pls forgive me if I miss something.

brg
Damir

2862 5 / Create an account to join the discussion.