Client Tools :: Explain Plan Using Toad And SQL Developer

Jul 3, 2009

How to find explain plan of a query in oracle using TOAD(any) and SQL Developer(any)?

If there any functional key to find out let me know.

View -1 Replies


ADVERTISEMENT

Client Tools :: Toad Explain Plan On Current Statement

Sep 7, 2011

1. In previous version of Toad (9.7.2) during executing a statement I was able to click on 'Explain plan current statement'. In this version of Toad (10.6.0. 42) is not a possible to do it.In the Toad options I've filled the sign: "use a separate connection when Toad itself is generating transactions"

2. During executing a statement I still see 'clock' cursor.Is it a possible to disable to see it?

View 1 Replies View Related

Client Tools :: Unable To Use Toad And PL/SQL Developer

Jun 17, 2010

Due to company restrictions, i unable to use oracle third party tools such as toad, pl / sql developer etc except oracle sql developers. I heard Oracle Developer Tools for Visual Studio is best option to work oracle pl/sql and sql.

View 4 Replies View Related

Client Tools :: Date Result Different For Same Query In Toad And SQL Developer?

Sep 11, 2013

The date is different for same query in Toad and Sql Developer.

Below is the query

In SQL Developer

SELECT start_date, length(start_date) from my_table where p_id = 1;

09-MAY-5249 9

In Toad

SELECT start_date, length(start_date) from my_table where p_id = 1;

blank 9

Did some queries below for research.

SELECT start_date, DUMP(start_date, 1016)
from my_table
where p_id = 1;
09-MAY-5249Typ=12 Len=7: 30,37,d5,a,0,a2,d5
SELECT start_date, DUMP(start_date, 1016)
from my_table
where p_id = 2;
01-JAN-2013Typ=12 Len=7: 78,71,1,1,1,1,1

Why is same query returning two different results in two tools.

View 16 Replies View Related

Client Tools :: Compare Schemas And Database Objects Using TOAD And SQL Developer

Apr 17, 2013

My primary objective was to compare objects in schemas in two different databases and find out the differences, Execute DDL's in the database where objects are missing and syn schemas in two different databases. So I need to compare schemas in databases. make a comparison of database objects existing in schemas in two different databases. I'd like to see if I can get a list of pro and cons between Toad and SQL Developer for comparing schemas pros and cons. navigation in SQL Developer to compare schemas.

Connect to Source
Connect to Target
Compare schemas with different object types
Find out differences
Generate DDL's for the missing objects or for the objects in difference report
Run them in missing instace(Source/Target)
Make sure both are in sync.

View 2 Replies View Related

Performance Tuning :: Privilege To Run Explain Plan

May 24, 2012

what privilege is require for a user to execute explain plan? I get below error while try to execute explain plan.

SQL> explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000;
explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000
*
ERROR at line 1:
ORA-01031: insufficient privileges

View 9 Replies View Related

Explain Plan Differences With Or Without Nvl On Bind Variables?

Jul 1, 2013

We have recently upgraded application (from Oracle Applications 11.5.9 to 12.1.3) and database (from 9.2.0.5.0 to 11.2.0.3.0).Since we are confronting to performances issues, i try to analyse some queries which Explains plans seems strange (in my opinion).Studying one of them i discover the next case (which according to my logic, i can't explain): --

Just bind variable --select *from   MTL_MATERIAL_TRANSACTIONS mmtwhere  1 = 1and    mmt.INVENTORY_ITEM_ID = :p1and    mmt.ORGANIZATION_ID   = :p2and    mmt.TRANSACTION_REFERENCE = :p3--and    mmt.SUBINVENTORY_CODE = :p4 PlanSELECT STATEMENT ALL_ROWS Cost: 5 Bytes: 361 Cardinality: 1 2 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_MATERIAL_TRANSACTIONS Cost: 5 Bytes: 361 Cardinality: 1 1 INDEX RANGE SCAN INDEX XXSPE.XXSPE_MTL_MATERIAL_TRANSAC_N99 Cost: 3 Cardinality: 2-- Nvl on bind variable --select *from   MTL_MATERIAL_TRANSACTIONS mmtwhere  1 = 1and    mmt.INVENTORY_ITEM_ID = :p1and   

[code]...

View 3 Replies View Related

Client Tools :: New TOAD After Old One

Sep 26, 2011

I was downloaded new version of TOAD for Oracle version 10.6 and I try to connect to my current database (oracle xe) and TOAD raise an error ORA-12154 Could not connect to XE..., but with old version of TOAD 9.6 all works fine. What can be an solutions?

View 6 Replies View Related

Performance Tuning :: Same Query With Different Explain Plan In Two Database?

Aug 17, 2012

I am facing a weird situation wherein the explain plan of same sql in SIT and PROD is different.In fact the explain plan is very costly in Prod.Also the DB version of both SIT and PROD is same.

Below is the sql and corresponding explain plan in Prod and SIT respectively.

Query:
SELECT seq,CCN,ProcessorPart,root_item,comp_path,Item,comp_item,comp_item_type,
lag(comp_item_type,1,'PART') over(PARTITION BY seq ORDER BY lvl)Nxt_comp_item_type,lvl,bom_qty,
ROUND(CASE min(abs(bom_qty)) OVER (PARTITION BY seq ORDER BY lvl)
WHEN 0 THEN 0 ELSE 1 END * EXP (SUM (LN (nullif(abs(bom_qty),0))) OVER (PARTITION BY seq ORDER BY lvl))) Ulti_qty,
'AMER'

[code]...

The tables referred in above query is small tables containing arnd 10k records.The above tables are partitioned on Region and not indexed.

Explain Plan in Prod: COST CARDINALITY BYTES

SELECT STATEMENT, GOAL = ALL_ROWS165173613539322883634804
SORT UNIQUE236360
UNION-ALL
PARTITION LIST SINGLE117240

[code]...

Explain Plan in SIT: COST CARDINALITY BYTES

SELECT STATEMENT, GOAL = ALL_ROWS3211689
SORT UNIQUE347240
UNION-ALL
PARTITION LIST SINGLE172120

[code]...

I am not able to attribute why there is a huge change in Cost between SIT and Prod.Apparently the Job is going for 3-5 hours which used to get completed within 20mins in SIT.

View 5 Replies View Related

Performance Tuning :: Explain Plan - %CPU Seems To Be Worse For JOIN Near Top?

Oct 24, 2011

however I was able to identify a poorly performing query that seemed to be maxing out our CPU. I have been trying to understand the Explain Plan. The plan below is from our test system which has considerably less information in the tables than our PROD system.

I can see there are a bunch of table scans at the end which may indicate missing indexes, but I am unclear on whether this is actually a problem as the %CPU seems to be worse for the JOIN near the top of the plan.

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1870M| 3018G| | 677M (1)|999:59:59 | | |
| 1 | SORT ORDER BY | | 1870M| 3018G| 3567G| 677M (1)|999:59:59 | | |

[code]...

View 4 Replies View Related

Client Tools :: Toad Out Of Memory

Aug 30, 2012

2 questions:

I'm fighting with a fairly large set of dynamic queries created by some developer (often SQL is over 2MB) accessing plenty of tables having something like 1000 columns...

Formatting of such query takes ages in Toad while is pretty fast in Oracle SQL Developer... SQL developer is running plenty write operations in local app data directory... moving it onto a ram-drive didn't worked how I could optimize Toad's formatter?

I'm often accessing a small table which has 100 columns of type VARCHAR2(4000).

The problem is that I cannot see the results of such query as Toad is throwing "out of memory" exception. How to deal with that?

View 4 Replies View Related

Client Tools :: Installation Of Toad 10.1

Apr 22, 2010

Had some annoying problems with installing TOAD on Windows 7, 64 bit.Got I working now, but anyway I thought I share it here.

1. Toad 10.1(latest) doesn't work with the Oracle 64 bit client. The problem is that this is a limitation of the tool Quest uses to build Toad. So that will not work in a "near" future.

2. Never choose the default suggested installation path which is "C:Program Files(86)",that will mess it up totally. Point to a new or existing directory without brackets.

View 1 Replies View Related

Client Tools :: Toad 10.6.1.3 And Windows 7?

Sep 15, 2011

Our company has finally undertaken the task of getting us all off Windows XP and onto Windows 7. My turn came this afternoon, they gave us all clean installs and after walking through oracle client, toad, visual studio setup I finally started using it an Toad is unbearably bad. I have found:

Loaded files suddenly become read only for no apparent reasonAfter selecting a file on a network drive and clicking the open button, a blank edit pane is loaded in place of the file, sometimes it has the name of the file sometimes no name and sometimes I get an error that the file does not exist Empty file listings, I will browse into a directory that has files in it but toad shows nothing or freezes completely

I have not used Windows 7 much or this version of Toad at all I am loading much of this over the network so that is one possible culprit but it has never been a problem in the past with XP or older versions of toad

View 2 Replies View Related

Client Tools :: Can Install Toad In Mac OS

Mar 2, 2010

whether we can install Toad in Mac OS.

View 6 Replies View Related

Client Tools :: Wait Tab In Toad

Mar 30, 2011

I open the session browser and wait tab . here is the image

what does the wait_time and second_in_wait indicate , some times I saw wait_time more that 15 seconds what does this mean ?

View 1 Replies View Related

Performance Tuning :: Change In Explain Plan Due To Oracle Upgrade

Dec 14, 2010

I need to warn readers that I am not a DBA but am heavily involved in application development. Whatever I know about database tuning is whatever I've managed to pick up via self-learning, and I must admit that the sum total of my knowledge isn't a lot.

Anyway, our "DBAs" recently did an upgrade to our 10g database, going from version 10.2.0.2.0 to 10.2.0.4.0. Immediately after the upgrade, a particular query has started to under-perform. The query itself was not altered in any way during the upgrade.

We have two explain plans for the query, a before and an after plan. The two plans are similar but not identical. The plans are too massive to post here, so I hope the following synopsis of the differences will do.

The 10.2.0.2.0 plan:

shows a HASH GROUP BY
has a TempSpc column in the explain plan
shows a particular table (EMP_HISTORY) as having ~1700 rows

The 10.2.0.4.0 plan:

shows SORT GROUP BY instead of HASH GROUP BY does not show a TempSpc column in the explain plan shows the EMP_HISTORY table as having only 25 rows

Other than these points, no other discernible differences can be noted. I'm wondering what would cause HASH to change to SORT. I'm told that stats are up-to-date.

View 5 Replies View Related

Performance Tuning :: Explain Plan Analysis - Execution Ordering

Jul 21, 2010

I have two Oracle instances that are setup identically.When I run a query on one of them, it takes around 3 seconds, on the other it takes around 200 seconds.

I have looked at the explain plans, and it has shown me what I think is the problem. On one instance, it does a join on two tables, then runs the other filter/access predicates. On the other instance it runs the filter/access predicated first, then does the expensice join. The one that does the join first is the one that takes around 200 seconds. How to tell Oracle to make this join after runnning the other predicates?

View 15 Replies View Related

Performance Tuning :: Run Explain Plan Syntax - Show Error?

Aug 3, 2010

when i runnung the explain plan syntax , show error :
running --- SELECT * FROM TABLE(dbms_xplan.display) ;
ERROR: an uncaught error in function display has happened;
please contact Oracle support
Please provide also a DMP file of the used plan table
PLAN_TABLE
ORA-00904: "OTHER_TAG": 無效的 ID

View 4 Replies View Related

Client Tools :: How To Enable Profiler In Toad

Oct 2, 2010

I'm using toad 9.2.7.5 to do my plsql development. How I can enable the Profiling option so that I can analyse the performance of my stuff - currently the grey profiler icon is greyed out

View 1 Replies View Related

Client Tools :: Out Of Memory Using DDL Export In Toad?

Feb 11, 2013

I am trying to export all indexes (DDL:s) to a file from our datawarehouse. But Toad gets a "out of memory".

Is there a way for Toad to manage this task without getting out of memory?

View 2 Replies View Related

Client Tools :: Compilation Errors In Toad

May 15, 2008

I know that this my question is a little to much for some DB tool like TOAD, but maybe this tip is exist and I can't find it!

Does TOAD when I write something wrong in PL/SQL (trigger for example) and after compilation (run as script) when show message:

"Warning: compiled but with compilation errors" to show what is wrong in sql code? I found this option is some db tools, but I can't find this in TOAD, of course if it can do this!

View 4 Replies View Related

Client Tools :: Toad On Ubuntu 8.04 Installation?

Feb 12, 2010

I want to install Toad on Ubuntu 8.04 . provide me link where step by step instruction guide is available

View 3 Replies View Related

Client Tools :: Installing Toad On Ubuntu 8.04?

Jul 5, 2010

I need Step By Step Instructions to Install Toad on Ubuntu 8.04.

View 9 Replies View Related

Client Tools :: TOAD Update Via ROWID

Jun 25, 2010

I'm placing this here since the only tool I have is TOAD to hack database. I have discovered a bad record with the system but as the date field is '0000' and the values are all hieroglyphics which I can't place in UPDATE statement. Only fields able to determine are that it's a journal entry. I tried the blow code only to be forced out of TOAD via ORA-03113: end-of-file on communication channel error is a generic error. How can I update the bad date fields with such limited access? Is the ROWID acceptable?

Update ft_os
set ost_upd_dt = '06-dec-2010'
where ost_tran_code = 'JEN'
and ost_doc_ref = 'IS'

View 9 Replies View Related

Client Tools :: TOAD Not Finding Oracle 11g?

Jul 2, 2013

I am running TOAD 10.6 on Windows 7 (64-bit). I have two 64-bit Oracle 11g clients installed (all on the same machine). The first is Instant Client (client_1); the second is the Runtime client (client_2). The installation of Instant Client was a mistake, since it doesn't have a Network / Admin folder to drop a tnsnames.ora file into. The Runtime client is my desired client, which has the aforementioned folder that I subsequently dropped the file into.

On starting TOAD, it says that "No valid Oracle Client found. Please note that Toad only supports 32 bit Oracle Client installations". (This is odd, since my colleague is running the same Oracle Runtime client as me and has TOAD 11.6, and yet his is working fine. But, this is aside the point.) When, I click on the ellipsis button next to the "Connecting Using" field, TOAD says that I don't have any Oracle homes installed. I checked my registry key (via regedit) under Computer / HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE. It looks like I have 2 Oracle homes (because of the 2 clients).

Also, I right-clicked on My Computer and brought up Properties, Advanced system settings, Environment Variables (on the Advanced tab). My PATH variable has both of my clients.

How to make my available clients (particularly, my client_2) show up in TOAD?

Here is what I have tried thus far:

-Alter the PATH variable to just have client_2. This didn't do anything.

-Try to uninstall client_1 and client_2 via:

a) There is a Universal Installer under Oracle Installation Products in my Runtime client. I do Deinstall Products, click on any of my Oracle homes, click on Remove, and a Warning window pops up saying to run a command at some location, but the location is truncated since I can't expand the window.

b) In my client_2, there is a deinstall Windows Batch File in the deinstall folder. When I run this, the command window sits there for a little bit, saying that some files were copied and that 1 directory was removed. It then closes on its own. This doesn't seem to do anything.

View 1 Replies View Related

Client Tools :: Unable To Login Through TOAD

Aug 17, 2010

ORA-00604:error occured at recursive SQL level 1
ORA-20501:Warning: Login Using <UNAME> Prohibited, Please contact GSD/DB-Services
ORA-06512: at line 73

I'm encountering the above msg while connecting to oracle through TOAD. While connecting through SQL * Plus I find no issues.

I'm using TOAD for Oracle 9.0.1.

View 1 Replies View Related

Client Tools :: TOAD 8.6 And Oracle 10g - Cannot Connect?

Feb 22, 2012

I have TOAD 8.6 installed and hoping to use with Oracle 10g databases. I have SQL Navigator also - it works fine. However TOAD does not. Open up TOAD and see the TNS dropdown, select a database and try to log in - click on Connect and the little SQL hour glass comes on, goes off - there is no error, no message and no connection. I have tried several databases and again, SQLPlus, SQL Navigator are both working fine.

View 2 Replies View Related

Client Tools :: Toad Converts Chinese

Oct 26, 2012

I have some data in an Excel Sheet which is in Simplified Chinese, which I have to enter in oracle database. As soon as I paste the data into the insert statement on TOAD it gets converted into question marks.

Toad Version : 9.5.0.31
Oracle DBMS: 10.2.0.4.0
NLS_CHARACTERSET= UTF8

Chinese language is also installed on the instance with base language as US.

View 1 Replies View Related

Client Tools :: Debugging PL/SQL Web Forms In Toad?

Nov 3, 2008

I am doing some work in Banner (the SCT Sungard ERP for educational institutions) and am attempting to use TOAD to debug a PL/SQL web form.

Everything that I've come across thus far indicates that I need to enable debugging, attach the package in TOAD to the external session using Debug > Attach External Session, and then call the web package [URL].

Here are the steps I have followed thus far:

1. Execute the following anonymous block via a SQL window in TOAD to get the debug session ready (I don't have SQL *Plus installed, so I did it from a SQL window instead)

DECLARE
id VARCHAR2(255);
BEGIN
id := DBMS_DEBUG.INITIALIZE('PABLO_TEST_DEBUG');
DBMS_DEBUG.DEBUG_ON;
END;

2. Load the package/procedure I want to debug into a pl/sql window in TOAD and set a breakpoint.

3. Select 'Debug > Attach To External Session', and enter 'PABLO_TEST_DEBUG', which I earlier defined as the debug session id.

At this point Toad just spun its wheels for about five minutes, and then a probe query timeout error was received.

On a subsequent attempt executing the anonymous block simply resulted in the query executing for over 20 minutes, at which point I canceled execution. I'm using TOAD for my development environment, and it's essential that I can get decent debug information.

Given the depth and complexity of the system I am working in (Banner from SCT Sungard), it is incredibly inefficient to have to insert calls to htp.p to find out where the problem is. This has, at times, resulted in a few hours of testing to track down a problem that with a proper debugging environment would be identified almost instantly.

how to enable debugging within TOAD of pl/sql packages/procedures/functions that are called via the web.

View 6 Replies View Related

Client Tools :: TOAD - Execute As Script Not Available

Mar 31, 2010

I've got Toad installed on my local machine, but for some reason editor windows won't allow me to execute SQL statements using the 'Execute as Script' option. Only the 'Execute Statement' option is available.

I can't see results from multiple queries at the same time.Tried doing a google search, and a search on here, I'm using Toad 9.7.2.5.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved