SQL Optimizer for Oracle Forum - Toad for Oracle - Toad World
Srivathsava
Follow / 25 Apr 2017 at 3:18pm / Toad for Oracle
Latest post by on 21 Jul 2017 at 7:52am

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 220 2 / Create an account to join the discussion.
T. Ung
Follow / 12 Jul 2017 at 4:13pm / Toad for Oracle
Latest post by on 19 Jul 2017 at 11:01am

Why, How and When to concatenate '' to column values for comparisons...

Expand content
Hello All, I am new to using SQL Optimizer for Oracle. I am trying to understand why, how and when to concatenating column values with single quotes ('') to improve or optimize SQL performance...

Hello All, 

I am new to using SQL Optimizer for Oracle. I am trying to understand why, how and when to concatenating column values with single quotes ('') to improve or optimize SQL performance and cost?

For example, I have the following excerpt of the original and optimized query suggested by the Optimizer. An alternative query introduced 2 instances involving concatenation using the pipes ("||") operator and appending '' to column values for comparison.

  1. W.WOCLASS || '' = 'WORKORDER'
  2. W.OWNERGROUP = P2.PERSONGROUP || ''

What's the purpose of concatenating '' to values for comparisons? Is it because a column used in the comparison could be NULL thus adding a '' char to the nullable column implicitly makes the comparison quicker by not having to check for NULLs?

Thanks in advance.

Table WORKORDER

Column OWNERGROUP has DataType = VARCHAR2 (20 Byte) and NULL? = YES
Column WOCLASS has DataType = VARCHAR2 (16 Byte) and NULL? = NO

Table PERSONGROUP

Column PERSONGROUP has DataType = VARCHAR2 (20 Byte) and NULL?= NO

SQL Optimizer for Oracle v.8.7.0.2431

Excerpt of Original SQL:

SELECT (SUBSTR(P.DESCRIPTION, 1, INSTR(P.DESCRIPTION, '-') - 2)) SITEID,

W.PERSONGROUP WORKGROUP,
SUBSTR(W.WORKTYPE, 1, 2) WORKTYPE,
ROUND(AVG(W.ACTLABHRS), 2) HOURS

FROM WORKORDER W

JOIN PERSONGROUP P

ON P.OXY_PERSONGROUPTYPE LIKE 'A%'  

AND W.OWNERGROUP = P.PERSONGROUP 

WHERE W.WOCLASS = 'WORKORDER'

AND W.ISTASK = 0
AND W.STATUS IN ('MCOMP', 'COMP', 'CLOSE')
AND SUBSTR (W.WORKTYPE, 1, 2) < > 'NM'
AND W.WORKTYPE IS NOT NULL
AND W.STATUSDATE > SYSDATE - 365

GROUP BY (SUBSTR (P.DESCRIPTION, 1, INSTR (P.DESCRIPTION, '-') - 2)),

W.PERSONGROUP, SUBSTR (W.WORKTYPE, 1, 2)

Excerpt of Optimized Alternative:

SELECT SUBSTR(P2.DESCRIPTION, 1, INSTR(P2.DESCRIPTION, '-') - 2) SITEID,

W.PERSONGROUP WORKGROUP,
SUBSTR(W.WORKTYPE, 1, 2) WORKTYPE,
ROUND(AVG(W.ACTLABHRS), 2) HOURS

FROM PERSONGROUP P2,

WORKORDER W

WHERE W.WOCLASS || '' = 'WORKORDER'

AND W.ISTASK = 0
AND W.STATUS IN ('MCOMP', 'COMP', 'CLOSE')
AND SUBSTR (W.WORKTYPE, 1, 2) <> 'NM'
AND W.WORKTYPE IS NOT NULL
AND W.STATUSDATE > SYSDATE - 365
AND P2.OXY_PERSONGROUPTYPE LIKE 'A%'
AND W.OWNERGROUP = P2.PERSONGROUP || ''

GROUP BY SUBSTR (P2.DESCRIPTION, 1, INSTR (P2.DESCRIPTION, '-') - 2),

W.PERSONGROUP, SUBSTR (W.WORKTYPE, 1, 2)

Unanswered 101 2 / Create an account to join the discussion.
Damir Vadas
Follow / 17 Jul 2017 at 11:32am / Toad for Oracle
Latest post by on 18 Jul 2017 at 9:59am

SQL Optimizer 9.2.2

Expand content
Hi guys from SQL Optimizer, Just installed new version (SQL Optimizer 9.2.2) and may say only compliments. Fixed avg LIO (binds sets), execution thread is smooth, better performance by eye, stability...

Hi guys from SQL Optimizer,

Just installed new version (SQL Optimizer 9.2.2) and may say only compliments. Fixed avg LIO (binds sets), execution thread is smooth, better performance by eye, stability improved...great, great work.

Just this.

Brg
Damir

48 1 / Create an account to join the discussion.
Damir Vadas
Follow / 17 Jul 2017 at 12:19pm / Toad for Oracle
Latest post by on 18 Jul 2017 at 9:57am

9.2.2 export binds set result in excel bug

Expand content
Hi SQL Optimizer 9.2.2 x64 (last official). When exporting general results into Excel, sheet has unnecessary grids drawings. This was not a case in 9.2.0 :-) Brg Damir

Hi

SQL Optimizer 9.2.2 x64 (last official).

When exporting general results into Excel, sheet has unnecessary grids drawings. This was not a case in 9.2.0

:-)

Brg
Damir

52 1 / Create an account to join the discussion.
Damir Vadas
Follow / 11 Jul 2017 at 10:24am / Toad for Oracle
Latest post by on 11 Jul 2017 at 1:37pm

How to SQL Optimizer update?

Expand content
SQL Optimizer 9.2.0 x64 on Win7 I have license which allows me to make some updates. When I Open SQL Optimizer I get: When I click "Yes" I get this (look the opened file is local)...and...

SQL Optimizer 9.2.0 x64 on Win7

I have license which allows me to make some updates.

When I Open SQL Optimizer I get:

When I click "Yes" I get this (look the opened file is local)...and no way to get where is the update???

What to do to make update possible?

Brg

Damir

Answered 198 10 / Create an account to join the discussion.
Damir Vadas
Follow / 26 Jun 2017 at 7:20am / Toad for Oracle
Latest post by on 29 Jun 2017 at 1:08pm

Really "static" SQL based execution

Expand content
Hi, Whenever you choose execution type (dynamic or static) on a lower value it is basically "dynamic". Because it generates hint by which you lately make some statistic analyses for each...

Hi,

Whenever you choose execution type (dynamic or static) on a lower value it is basically "dynamic".

Because it generates hint by which you lately make some statistic analyses for each execution. Here is brief case of mine words (taken while SQL Optimizer was executing):

SQL>@sql_id 1 72w638vps8zs1

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ Set 40_00000000*624533 */ NVL(TOTAL_POINTS_BALANCE, 0)
  from CLUB_BILL_SUBSCRIBER
 where MEMBER_ID = :b1
   and CLUB_CODE = :b2
   and BAN = :b3
   and SUBSCRIBER_NO = :b4
   and BEN = :b5
   and BILL_SEQ_NO = (select /*+ USE_MERGE(B,CBS) */ max(CBS.BILL_SEQ_NO)
                        from CLUB_BILL_SUBSCRIBER CBS,
                             BILL B
                       where CBS.MEMBER_ID = :b1
                         and CBS.CLUB_CODE = :b2
                         and CBS.BAN = :b3
                         and CBS.SUBSCRIBER_NO = :b4
                         and CBS.BEN = :b5
                         and CBS.BAN = B.BAN
                         and CBS.BILL_SEQ_NO = B.BILL_SEQ_NO
                         and CBS.BEN = B.BEN
                         and B.CLUB_CALCULATION_STATUS = 'S')


Current sessions run that sql

no rows selected


gv$sql data ...

 IID  CH PLAN_HASH_V         EXECS         TIME Sleep         ROWS   AVG TIME Sleep     AVG ROWS          AVG LIO      AVG CPU Sleep LAST_LOAD      LAST_ACTIVE    SH SQL_PROFILE                  BA BS
---- --- ----------- ------------- ---------------- ------------ -------------- ------------ ---------------- ---------------- -------------- -------------- -- ---------------------------- -- --
   1   0  2570093969             1          0.96700            1        0.96656        1.000        258.00000        0.0030000 06-23/14:59:19 06-23/14:59:20 Y                               N  N


@sql_id [inst_id] [sql_id]
SQL>@sql_id 1 7pfurkfvyytrj

SQL_FULLTEXT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ Set 43_00000000*624610 */ NVL(TOTAL_POINTS_BALANCE, 0)
  from CLUB_BILL_SUBSCRIBER
 where MEMBER_ID = :b1
   and CLUB_CODE = :b2
   and BAN = :b3
   and SUBSCRIBER_NO = :b4
   and BEN = :b5
   and BILL_SEQ_NO = (select /*+ USE_MERGE(B,CBS) */ max(CBS.BILL_SEQ_NO)
                        from CLUB_BILL_SUBSCRIBER CBS,
                             BILL B
                       where CBS.MEMBER_ID = :b1
                         and CBS.CLUB_CODE = :b2
                         and CBS.BAN = :b3
                         and CBS.SUBSCRIBER_NO = :b4
                         and CBS.BEN = :b5
                         and CBS.BAN = B.BAN
                         and CBS.BILL_SEQ_NO = B.BILL_SEQ_NO
                         and CBS.BEN = B.BEN
                         and B.CLUB_CALCULATION_STATUS = 'S')


Current sessions run that sql

no rows selected


gv$sql data ...

 IID  CH PLAN_HASH_V         EXECS         TIME Sleep         ROWS   AVG TIME Sleep     AVG ROWS          AVG LIO      AVG CPU Sleep LAST_LOAD      LAST_ACTIVE    SH SQL_PROFILE                  BA BS
---- --- ----------- ------------- ---------------- ------------ -------------- ------------ ---------------- ---------------- -------------- -------------- -- ---------------------------- -- --
   1   0  2570093969             1          1.56600            1        1.56574        1.000        360.00000        0.0040000 06-23/14:59:26 06-23/14:59:27 Y                               N  N


@sql_id [inst_id] [sql_id]
SQL>

Mine question is to introduce "full static on server" execution type, which could be used ONLY in bind sets execution. This would mean to leave query as it is and just change binds sets in execution and make one sql calculation

Why?

In this case 100 of executions will have the one entry in gv$sql, this would be easy to find in ASH, AWR or parallel monitoring and on the end will be really summary for binds set use-like it is in real life-one sql_id for all executions.

Once again, stat for such a query would be only possible in binds set usage, will not have stat for each execution but for summary only.

Is that possible?

Thx

Damir

151 8 / Create an account to join the discussion.
Damir Vadas
Follow / 27 Jun 2017 at 6:33am / Toad for Oracle
Latest post by on 28 Jun 2017 at 5:48pm

How to save test results for several scenarios in one Excel

Expand content
Hi, when have several scenarios, until now I was going into each and save each testing results one by one into separate Excel: How to do all in on Excel with each scenario in own sheet? Brg Dami...

Hi,

when have several scenarios, until now I was going into each and save each testing results one by one into separate Excel:

How to do all in on Excel with each scenario in own sheet?
Brg
Damir

Answered 91 2 / Create an account to join the discussion.
Damir Vadas
Follow / 26 Jun 2017 at 1:48pm / Toad for Oracle
Latest post by on 28 Jun 2017 at 4:45pm

columns in grid results

Expand content
Hi, SQL Optimizer 9.2 on Oracle 11.2.0.3 x64. Whenever I save testing results (in any format) and later open them I get u bunch of columns which has "-1" value: why are their content...

Hi,

SQL Optimizer 9.2 on Oracle 11.2.0.3 x64.

Whenever I save testing results (in any format) and later open them I get u bunch of columns which has "-1" value:

why are their content like that?

which Oracle view are you using to show values ...when do they have different values (what to do ....which test to made).

Brg

Damir

Answered 130 5 / Create an account to join the discussion.
soujanyanaganuri23
Follow / 28 Jun 2017 at 7:47am / Toad for Oracle

Oracle Business Intelligence Standard

Expand content
This suite is extra modest, very affordable for SMBs. includes everything you need to take a quick time running in business Intelligence device. it's if everything has been hooked up on a server, and...

This suite is extra modest, very affordable for SMBs. includes everything you need to take a quick time running in business Intelligence device. it's if everything has been hooked up on a server, and this should be a windows Server.
The license does not permit using more than CPU's the server and allows simplest different source of direct records apart from the BD includes. The obligatory licensing per person is nominal, and may be certified among 5 and 50 customers.
installation is carried out easily and in a unmarried processor installed inside the server database, ETL tool Oracle Warehouse Builder (basic model), the BI server and different packages.
The BI server lets in get admission to to net analytical reporting tool and dashboards, for layout to operation. The whole surroundings is the purchase of Siebel and Oracle made some time ago, and its previous name become Siebel Analytics.
BI environment is a very whole and easy to apply, but does not use systems of OLAP, works immediately at the relational version of database.

The suite includes tools which might be:
Oracle BI Server: Centralized get entry to to information and calculation engine that relies on a logical model of common employer records (stage of abstraction of the metadata)
Oracle BI Server Administrator: advent of metadata and stages of abstraction
Oracle BI solutions: Self-carrier ad-hoc that lets in end users to easily create charts, pivot tables, reviews and dashboards, and drill skills navigate up / down.
Oracle BI Interactive Dashboards: interactive dashboards for checking out environments.
Oracle BI publisher (también conocido como XML writer): Reporting commercial enterprise operational and distribution of stories with first-rate detail.
Oracle Database general version One: Database
Oracle Warehouse Builder (middle ETL): layout of database and extract, rework and cargo (ETL) that enables manage the lifecycle of records and metadata

The simple technique as a way to create analytical reports and dashboards with this suite would be:
ETL design and production of systems on tables with OWB dimensional
Definition of metadata and bodily layers, the enterprise and introduction to Oracle BI Server Administrator
growing reviews and dashboards with BI solutions and Interactive Dashboards
To create and distribute operational reports can be used independently BI writer.

The reviews are designed using Microsoft phrase or Adobe Acrobat (installing an upload-in) after which also can put up and edit internet server thru WebDav.
This software, despite the fact that the portal is integrated with BI Dashboards for Oracle's own, and is a chunk more bulky to use. it's miles rarely used until there are specific needs that cannot be solved with BI solutions.

65 0 / Create an account to join the discussion.
baber
Follow / 18 Apr 2017 at 8:09pm / Toad for Oracle
Latest post by on 26 Jun 2017 at 6:50pm

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 ?

243 2 / Create an account to join the discussion.
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

275 3 / 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 657 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 758 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 ...

602 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 34430 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?

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

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

891 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 1211 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?

962 1 / Create an account to join the discussion.