vhp
Follow / 29 Jun 2018 at 3:41pm / Toad for Oracle
Latest post by on 3 Jul 2018 at 4:36pm

View several independent views more than one query at the same ti...

Expand content
Hello Excuse me, I'm new to the group, it may not be the right group. In toad I have the possibility of making a double query in different tables and being able to visualize the results in the same...

Hello

Excuse me, I'm new to the group, it may not be the right group.
In toad I have the possibility of making a double query in different tables and being able to visualize the results in the same window?

Example
Select * from person
select * from ctacte

View 1
zzzzz zzzzz zzzzz
View 2
zzzzzz zzzzzz zzzzzz

I hope you understand.
thank you already

Victor

Answered 395 2 / Create an account to join the discussion.
ben_michaud
Follow / 21 May 2018 at 4:10pm / Toad for Oracle
Latest post by on 21 Jun 2018 at 4:24pm

SQL runs but not in optimizer - need error details

Expand content
I have a select statement with bind variables gathered using Oracle tooling while running a package via a Java Web application. I can run the SQL in Toad using the "Execute/Compile statement at caret"...

I have a select statement with bind variables gathered using Oracle tooling while running a package via a Java Web application. I can run the SQL in Toad using the "Execute/Compile statement at caret" command, setting the variables. Here is the SQL:

Options screens:

(NOTE: I turned off "Refactor SQL before optimizing," and that got rid of a very unhelpful "Exception was thrown" error.)

When I try to run the same SQL using the "Auto-optimize SQL at caret using re-writes", I get this error message: "ORA-00905: missing keyword"

Is there any way I could get more detail on where this mysterious error is in the statement? I tried running it in SQL*Plus, but it works there.

Thanks in advance for any help you could offer.

Oracle 11.2.0.4.0

Toad 32-bit 13.0.0.80

Windows 10

1024 8 / Create an account to join the discussion.
Vikharankar
Follow / 13 Jun 2018 at 11:15am / Toad for Oracle
Latest post by on 15 Jun 2018 at 9:02am

Toad window closed automatically

Expand content
Hi , Could you please help me to resolve the following issue. I am installed Toad 11.6 and it was working fine from last 6 months but now when I am logging to Toad , Toad window closed down automatically...

Hi ,

Could you please help me to resolve the following issue.

I am installed Toad 11.6 and it was working fine from last 6 months but now when I am logging to Toad , Toad window closed down automatically.The Toad closed around in 20 to 25 seconds.

Thanks 

Shailesh Vikharankar

547 1 / Create an account to join the discussion.
Damir Vadas
Follow / 5 Jun 2018 at 1:54pm / Toad for Oracle
Latest post by on 6 Jun 2018 at 3:56am

SQL Optimizer 9.2.3 different "Save As Excel" format.

Expand content
Hi, In 9.2.2 When I exported ested bind values test to Excel file, "Elapsed Time" and "first Row Time" column was recognized by Excel because it had "," (comma) instead...

Hi,

In 9.2.2 When I exported ested bind values test to Excel file, "Elapsed Time" and "first Row Time" column was recognized by Excel because it had "," (comma) instead, now "." (punkt). Here is current values in Excel:

Also, "Record Count" column is not recognized as number (it is text) and only search replace can set proper numbers. Tried to format - no success.

In any way, please return old formatting from 9.2.2 version.
Brg

Damir

P.S:

File can be sent on your request on dedicated mail.

722 2 / Create an account to join the discussion.
Damir Vadas
Follow / 4 Jun 2018 at 5:43am / Toad for Oracle
Latest post by on 5 Jun 2018 at 7:13am

how to search by "Plan hash value"?

Expand content
Hi, when u have, let's say, 200 scenarios, then you need to search quickly amount them. for instance tuning task find something and shows better plan, but there you see only plan and hint...and...

Hi,

when u have, let's say, 200 scenarios, then you need to search quickly amount them.
for instance tuning task find something and shows better plan, but there you see only plan and hint...and would like to find real query, if is existing in SQL optimizer.
also, ask yourself...how do you manage 200 scenarios? how to reference them later or in any analyze?

Only by plan value!

So please, place that value in Grid and allow to be at least sort by column, if search is not possible.

Brg

Damir

758 3 / Create an account to join the discussion.
Damir Vadas
Follow / 29 May 2018 at 9:11am / Toad for Oracle
Latest post by on 31 May 2018 at 10:43am

Terminate time in "Bind Values testing"

Expand content
Hi, Scenario: Start test Bind values of "Original" query for several binds. Then add more scenarios in same test When Original single run is less than second (let's say 0.1 sec) then...

Hi,
Scenario:

  1. Start test Bind values of "Original" query for several binds.
  2. Then add more scenarios in same test
  3. When Original single run is less than second (let's say 0.1 sec) then it is not possible to set proper terminate time (limit is one second) for new added scenarios

So, please allow to set in seconds decimals terminate time or place a valu to define (as it is in original test) percentage of total time to terminate test (could be set in time).
Brg
Damir

805 4 / Create an account to join the discussion.
JDL_FRA
Follow / 4 Sep 2008 at 2:43pm / Toad for Oracle
Latest post by on 29 May 2018 at 9:07am

Alter session statements

Expand content
Hi I would like to test alter statement such as alter session set hash_join_enabled = false; alter session set "_optimizer_sortmerge_join_enabled" = false; as they will typically alter the explain...
Hi I would like to test alter statement such as
alter session set hash_join_enabled = false;
alter session set "_optimizer_sortmerge_join_enabled" = false;
as they will typically alter the explain plan of the SQL.
However as they are not listed in the Alter statemet nwwindows they are not available , is ther a workaround ?
10574 24 / Create an account to join the discussion.
antoine_mc1
Follow / 15 Feb 2018 at 6:30am / Toad for Oracle
Latest post by on 20 Feb 2018 at 2:57am

result set or all rows fetched , sys_refcursor

Expand content
Please could you help me? I need to print the result set or all rows fetched about the query: CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno IN hr.employees.department_id%TYPE, p_recordset OUT...

Please could you help me?

I need to print the result set or all rows fetched about the query:

CREATE OR REPLACE
PROCEDURE get_emp_rs
(p_deptno IN hr.employees.department_id%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT first_name,
salary,
department_id
FROM hr.employees
WHERE department_id = p_deptno
ORDER BY first_name;
END get_emp_rs;
/

declare
v varchar2(4000);
z varchar2(4000);
begin
z:=get_emp_rs( 90, v);
dbms_output.put_line(z);
end;
/

ORA-06550: line 5, column 6:

PLS-00222: no function with name 'GET_EMP_RS' exists in this scope
ORA-06550: line 5, column 3:

I need the result set for example:

Steven  24000  90
Neena  17000  90
Lex       18000  90

Thanks a lot  :)

2512 3 / Create an account to join the discussion.
antoine_mc1
Follow / 30 Jan 2018 at 6:11am / Toad for Oracle
Latest post by on 30 Jan 2018 at 8:10am

lists in plsql oracle

Expand content
Hello, Obviously this example is working declare cursor c_salary is select first_name, salary from hr.employees where salary in(9000, 6000, 4800); begin for s in c_salary loop dbms_output.put_line...

Hello,

Obviously this example is working

declare
 cursor c_salary is
 select first_name, salary from hr.employees 
 where salary in(9000, 6000, 4800);
begin
 for s in c_salary loop 
  dbms_output.put_line(s.first_name || ' ' || s.salary);
 end loop;
end;

Alexander 9000
Bruce 6000
David 4800
Valli 4800
Daniel 9000
Peter 9000
Allan 9000
Pat 6000

PL/SQL procedure successfully completed.

But I need another example which I can not see the data obtained

set serveroutput on;
declare
v_salary varchar2(50):= '9000,6000,4800'; -- I NEED THIS LIST
cursor c_salary is
select first_name, salary from hr.employees
where to_char(salary) in(v_salary);
begin
for s in c_salary loop
dbms_output.put_line(s.first_name || ' ' || s.salary);
end loop;
end;

PL/SQL procedure successfully completed.

I can not see the printing or output of data, do you have any idea that allows me to see the printing of data

Thanks a lot

:)

Unanswered 2652 2 / Create an account to join the discussion.
Damir Vadas
Follow / 26 Jan 2018 at 12:14pm / Toad for Oracle
Latest post by on 29 Jan 2018 at 3:32am

Cost shows 0 when is actually Tera cost number?

2811 1 / Create an account to join the discussion.
Damir Vadas
Follow / 30 Nov 2017 at 2:18pm / Toad for Oracle
Latest post by on 7 Dec 2017 at 12:07pm

terminate option in second run

Expand content
SQL Optimizer 9.2.2.270 Add 5 scenarios and some number of bind sets. Run all 5 of them with option toi terminate according 110% of all time against original one Finish testing. Save Close Optimitzer...

SQL Optimizer 9.2.2.270

Add 5 scenarios and some number of bind sets.

Run all 5 of them with option toi terminate according 110% of all time against original one

Finish testing.

Save

Close Optimitzer

Open same test and now check original and only other 1-2 others.

Now there is no same termination policy as it was run on first run?.

Why?

3313 7 / Create an account to join the discussion.
Hans-Werner Schlote
Follow / 16 Nov 2017 at 12:21pm / Toad for Oracle
Latest post by on 22 Nov 2017 at 8:19am

Optimize context dependent queries

Expand content
Let's say we have a view depending on a package variable like this: <quote> CREATE OR REPLACE PACKAGE ptest AS ps_login VARCHAR2(100 CHAR); FUNCTION get_login RETURN VARCHAR2; PROCEDURE...

Let's say we have a view depending on a package variable like this:

<quote>

CREATE OR REPLACE PACKAGE ptest
AS
ps_login VARCHAR2(100 CHAR);

FUNCTION get_login
RETURN VARCHAR2;

PROCEDURE set_login(is_login IN VARCHAR2);
END ptest;
/


CREATE OR REPLACE PACKAGE BODY ptest
AS
PROCEDURE set_login(is_login IN VARCHAR2)
AS
BEGIN
ps_login := is_login;
END set_login;

FUNCTION get_login
RETURN VARCHAR2
AS
BEGIN
RETURN ps_login;
END get_login;
END ptest;
/

CREATE OR REPLACE VIEW v$test
AS
SELECT ptest.get_login FROM dual;

</quote>

Now I want to optimize a query to v$test like 

SELECT * FROM v$test;

Of course this is a very simple example.

The basic question is how to optimize context dependent queries with sql optimizer?

Answered 4849 6 / Create an account to join the discussion.
Damir Vadas
Follow / 20 Nov 2017 at 8:05am / Toad for Oracle
Latest post by on 22 Nov 2017 at 7:35am

Bug with graph after several alter scenario / bind set

Expand content
SQL Opt 9.2.2 (latest production). This happened when you alter several times scenarios and bind sets...graph shows what it wants (not showing real values). This happened when you test Alt12 and Org...

SQL Opt 9.2.2 (latest production).

This happened when you alter several times scenarios and bind sets...graph shows what it wants (not showing real values).
This happened when you test Alt12 and Org and do not touch Alt9 in this test.
Tried to switch between Performance variation and "overal", but seems that "SQL Optim" shows only Alt 9 and nothing more-like nothing exists.

Brg

Damir

3455 6 / Create an account to join the discussion.
Hans-Werner Schlote
Follow / 8 Nov 2017 at 2:31pm / Toad for Oracle
Latest post by on 13 Nov 2017 at 6:50am

ORA-01039

Expand content
SQL Optimizer for Oracle 9.2.2 SELECT owner, synonym_name, table_owner, table_name FROM dba_synonyms dba_syns WHERE table_owner = user AND (NOT EXISTS -- no target table/view (SELECT 0 FROM user_views...

SQL Optimizer for Oracle 9.2.2

SELECT owner,
synonym_name,
table_owner,
table_name
FROM dba_synonyms dba_syns
WHERE table_owner = user
AND (NOT EXISTS -- no target table/view
(SELECT 0
FROM user_views uv
WHERE uv.view_name = dba_syns.table_name
UNION
SELECT 0
FROM user_tables ut
WHERE ut.table_name = dba_syns.table_name
UNION
SELECT 0
FROM user_objects uo
WHERE uo.object_name = dba_syns.table_name)
OR NOT EXISTS -- no grant for target table/view
(SELECT 0
FROM table_privileges tp
WHERE tp.grantee = dba_syns.owner
AND tp.table_name = dba_syns.table_name));

returns ORA-01039 while getting plan

The statement runs absolutely well when executed in TOAD for Oracle 12.12.

Any ideas?

Answered 3280 2 / Create an account to join the discussion.
Damir Vadas
Follow / 13 Oct 2017 at 11:20am / Toad for Oracle
Latest post by on 19 Oct 2017 at 12:18pm

Sord order when scenarios were run in multiple times

Expand content
SQL Optimizer 9.2.2 I think I run one case in several times (parts). Then, when i wanted to sort by time, it was impossible to get the proper order. Why is this happening? brg Damir

SQL Optimizer 9.2.2

I think I run one case in several times (parts). Then, when i wanted to sort by time, it was impossible to get the proper order.

Why is this happening?

brg
Damir

Unanswered 3480 9 / Create an account to join the discussion.
Damir Vadas
Follow / 10 Aug 2017 at 1:34pm / Toad for Oracle
Latest post by on 15 Sep 2017 at 6:21am

How to remove scenarios

Expand content
Hi, SQL Opt 9.2.2 x64. Why can't I remove some scenarios in overall performance graph to have ability like in "performance variation" type where I see only clicked one? No way to...

Hi,

SQL Opt 9.2.2 x64.

Why can't I remove some scenarios in overall performance graph to have ability like in "performance variation" type where I see only clicked one?

No way to remove some as you see.

Brg

Damir

Unanswered 3516 5 / Create an account to join the discussion.
IbkIfe
Follow / 13 Sep 2017 at 5:23pm / Toad for Oracle
Latest post by on 13 Sep 2017 at 9:09pm

PLS-00103: encountered the symbol

Expand content
BEGIN FUNCTION coyname ( sql_id IN planchangetbl . sql_id% TYPE) RETURN VARCHAR2 IS echange planchangetbl . sql_id% TYPE; BEGIN select distinct s . sql_id , s . plan_hash_value , b . begin_interval_time...

BEGIN

FUNCTION coyname(sql_id IN planchangetbl.sql_id%TYPE)

RETURN VARCHAR2

IS

echange planchangetbl.sql_id%TYPE;

BEGIN

select distinct s.sql_id, s.plan_hash_value, b.begin_interval_time, b.end_interval_time,

s.min_snap_id, s.max_snap_id INTO echange from sys.hashchange s, sys.planchangetbl b

where s.sql_id = b.sql_id

and b.snap_id BETWEEN s.min_snap_id AND s.max_snap_id

RETURN echange;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

RETURN NULL;

END;

END;

3514 2 / Create an account to join the discussion.
Damir Vadas
Follow / 10 Aug 2017 at 1:29pm / Toad for Oracle
Latest post by on 11 Aug 2017 at 3:35am

Change graph line colors

Expand content
Hi, SQL Opt 9.2.2 x64. Many times if you are unlucky, result graph have similar coluors so it is hard to see the difference in graph: Is there any way to change colours? Brg Damir

Hi,

SQL Opt 9.2.2 x64.

Many times if you are unlucky, result graph have similar coluors so it is hard to see the difference in graph:

Is there any way to change colours?
Brg

Damir

Answered 3468 1 / Create an account to join the discussion.
Damir Vadas
Follow / 10 Aug 2017 at 10:27am / Toad for Oracle
Latest post by on 11 Aug 2017 at 3:21am

Autosave option, is it existing?

Expand content
Hi, When dealing with lots of scenarios or lots of "different binds values" I noticed that SQL Optimizer hang sometimes (lost connection, connection timeout) or even worse, test environment...

Hi,

When dealing with lots of scenarios or lots of "different binds values" I noticed that SQL Optimizer hang sometimes (lost connection, connection timeout) or even worse, test environment db is restarted.

In all of those cases previous testing results (which may be bigger) are lost.

So I am wondering if there is existing option, or it could be added an option to save results (based on user choice). I.e.

  • Save after completed scenario
  • Save after n executions
  • Save after n minutes (on first available moment after)

In this case testing of bigger sessions would be highly easier.

Brg
Damir

P.S.

9.2.2 has fixed bug with ANSI encoding (non UTF 8) file recognition-now is automatic
Great.

3480 1 / Create an account to join the discussion.
T. Ung
Follow / 12 Jul 2017 at 4:13pm / Toad for Oracle
Latest post by on 25 Jul 2017 at 12:03pm

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)

Answered 3394 4 / Create an account to join the discussion.