You will no longer receive e-mail notifications from this forum.
Log-In to post
Contact Support on Twitter
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?
ExampleSelect * from personselect * from ctacte
View 1zzzzz zzzzz zzzzzView 2zzzzzz zzzzzz zzzzzz
I hope you understand.thank you already
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:
(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.
Toad 32-bit 188.8.131.52
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.
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
File can be sent on your request on dedicated mail.
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.
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).BrgDamir
Please could you help me?
I need to print the result set or all rows fetched about the query:
CREATE OR REPLACEPROCEDURE get_emp_rs(p_deptno IN hr.employees.department_id%TYPE,p_recordset OUT SYS_REFCURSOR) AS BEGIN OPEN p_recordset FORSELECT first_name,salary,department_idFROM hr.employeesWHERE department_id = p_deptnoORDER BY first_name;END get_emp_rs;/
declarev varchar2(4000);z varchar2(4000);beginz:=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 scopeORA-06550: line 5, column 3:
I need the result set for example:
Steven 24000 90Neena 17000 90Lex 18000 90
Thanks a lot :)
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 9000Bruce 6000David 4800Valli 4800Daniel 9000Peter 9000Allan 9000Pat 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;
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
SQL Optimizer 184.108.40.2060
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
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?.
Let's say we have a view depending on a package variable like this:
CREATE OR REPLACE PACKAGE ptestAS ps_login VARCHAR2(100 CHAR);
FUNCTION get_login RETURN VARCHAR2;
PROCEDURE set_login(is_login IN VARCHAR2);END ptest;/
CREATE OR REPLACE PACKAGE BODY ptestAS 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$testAS SELECT ptest.get_login FROM dual;
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?
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.
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.
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?
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.
FUNCTION coyname(sql_id IN planchangetbl.sql_id%TYPE)
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
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
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.
In this case testing of bigger sessions would be highly easier.
9.2.2 has fixed bug with ANSI encoding (non UTF 8) file recognition-now is automaticGreat.
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.
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.
Column OWNERGROUP has DataType = VARCHAR2 (20 Byte) and NULL? = YESColumn WOCLASS has DataType = VARCHAR2 (16 Byte) and NULL? = NO
Column PERSONGROUP has DataType = VARCHAR2 (20 Byte) and NULL?= NO
SQL Optimizer for Oracle v.220.127.116.111
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)
SELECT SUBSTR(P2.DESCRIPTION, 1, INSTR(P2.DESCRIPTION, '-') - 2) SITEID,
FROM PERSONGROUP P2,
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),