SQL Optimizer for Oracle Forum - Toad for Oracle - Toad World
Danny Torres
Follow / 1 Nov 2016 at 1:46pm / Toad for Oracle

We are now on Twitter!!! Contact a Technical Support expert today...

Expand content
Do you have a quick support question and short of time? Contact us Via Twitter @ QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest

Do you have a quick support question and short of time? Contact us Via Twitter  @QuestExperts and we will take care of you. #Jointheinnovation #WeareQuest

355 0 / 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 181 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 ...

107 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 32687 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?

359 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)

384 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?

377 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 538 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?

420 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 1945 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)

516 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 1846 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 1861 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

2342 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 1957 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 2078 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

2373 5 / Create an account to join the discussion.
EE
Follow / 10 Jan 2016 at 9:13pm / Toad for Oracle
Latest post by on 11 Jan 2016 at 2:51am

Connection Error

Expand content
When i try to connect to oracle DB appear the below error: ORA-06413: connection not open Note: I am using Toad for oracle 2015 suite 32 bit & i am using oracle client 32 bit any help to solve...

When i try to connect to oracle DB appear the below error:

ORA-06413: connection not open

Note: I am using Toad for oracle 2015 suite 32 bit & i am using oracle client 32 bit

any help to solve this error?

Thanks

2642 1 / Create an account to join the discussion.
ss
Follow / 5 Jan 2016 at 4:14pm / Toad for Oracle
Latest post by on 11 Jan 2016 at 2:24am

Run Test- Error

Expand content
I installed Toad DBA suite for oracle, when trying to optimize any sql statement, I got on the below error when run test for original & alternatives sql statements but from toad we can run all sql...

I installed Toad DBA suite for oracle, when trying to optimize any sql statement, I got on the below error when run test for original & alternatives sql statements but from toad we can run all sql statements without any error:

 Error

 ORA-06550: line 19, column 15:

PLS-00103: Encountered the symbol "=" when expecting one of the following:

    . ( , % ; limit

ORA-06550: line 21, column 7:

PLS-00103: Encountered the symbol "FETCH" when expecting one of the following:

    constant exception <an identifier>

   <a double-quoted delimited-identifier> table long double ref

   char time timestamp interval date binary national cha 

How can i solve this issue?

Thanks

3265 6 / Create an account to join the discussion.
EE
Follow / 28 Dec 2015 at 8:10pm / Toad for Oracle
Latest post by on 5 Jan 2016 at 3:22am

Plan Control

Expand content
Hello , I need to know when I use plan control to optimize excustion plan and deploy the best alternative to be plan baseline. this mean that when we run this SQL statement in oracle database from...

Hello ,

I need to know when I use plan control to optimize excustion plan and deploy the best alternative to be plan baseline.

this mean that when we run this SQL statement in oracle database from toad, automatic the excustion plan which we deploy it before or what will happen?

Thanks

2049 1 / Create an account to join the discussion.