I'm trying to export a copy of the Explain Plan from Oracle SQL Developer 2.1.0.63 All I get out is a html file named what I specify, then a newly created folder in the same directory called "images", which is empty. I've taken a squizz at the (cough)tabular(cough) HTML output, found there's half a dozen images specified, but I think t...
I have a query that is returning in vastly different amounts of time between 2 datasets. For one set (database A) it returns in a few seconds, for the other (database B)....well I haven't waited long enough yet, but over 10 minutes. I have dumped both of these databases to my local machine where I can reproduce the issue running MySQL 5....
Hello, I am comparing queries my development and production database. They are both Oracle 9i, but almost every single query has a completely different execution plan depending on the database. All tables/indexes are the same, but the dev database has about 1/10th the rows for each table. On production, the query execution plan it pi...
I have a stored procedure that I am calling using EXECUTE IMMEDIATE. The issue that I am facing is that the explain plan is different when I call the procedure directly vs when I use EXECUTE IMMEDIATE to call the procedure. This is causing the execution time to increase 5x. The main difference between the plans is that when I use exec...
I am trying to optimize the following query: select distinct this_.id as y0_ from Rental this_ left outer join RentalRequest rentalrequ1_ on this_.id=rentalrequ1_.rental_id left outer join RentalSegment rentalsegm2_ on rentalrequ1_.id=rentalsegm2_.rentalRequest_id where this_.DTYPE='B' and this_.id<=1848978...
I have to delete about 10K rows from a table that has more than 100 million rows based on some criteria. When I execute the query, it takes about 5 minutes. I ran an explain plan (the delete query converted to select * since MySQL does not support explain delete) and found that MySQL uses the wrong index. My question is: is there any wa...
I asked PostgreSQL to explain my query. Part of the explanation was: table_name --> Materialize What does materialize do? I'm joining two tables, not views or anything like that. ...
I have a query SELECT foo FROM bar WHERE some_column = ? Can I get a explain plan from MySQL without filling in a value for the parameter? ...
This is mysql explain plan for one of the query I am looking into. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------...
I have an Oracle 10.2.0.3 database, and a query like this: select count(a.id) from LARGE_PARTITIONED_TABLE a join SMALL_NONPARTITIONED_TABLE b on a.key1 = b.key1 and a.key2 = b.key2 where b.id = 1000 Table LARGE_PARTITIONED_TABLE (a) has about 5 million rows, and is partitioned by a column not present in the query. Table SMALL_NONPAR...
I have a table with 2 foreign keys, lets call them fk1 and fk2. Both have identical types, and identical indices. But when I "explain" a simple select query, I get completely different query plans. For fk1: explain select * from mytable where fk1 = 1; Result Index Scan using fk1_idx on mytable (cost=0.00..9.32 rows=2 width=4) Inde...
hi, i ran the explain command on my main table in mysql.it showed like id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE xyz ALL NULL NULL NULL NULL 1722 Using where does this affect the performance of the site? like when i so a select clause ? i have ...
Is there a way in TOAD or some other tool to monitor queries being executed by your web app? I'd like to examine the explain/execution plans for the web app queries. I'm debugging why the webapp queries are slower than when run from sqlplus. ...
If you have an inefficient query, and you add an index to help out performance, should the query "instantly" start using the index? Or do you need to clear out the Oracle "cache" (v$sql I believe) by running alter system flush shared_pool;? ...
Trying to generate an explain plan in SQL Developer, the program puts up a message box with title "failed to query plan_table" complaining "invalid column name". The plan is not generated or displayed. How to fix? ...
I know I can change the way MySQL executes a query by using the FORCE INDEX (abc) keyword. But is there a way to change the execution order? My query looks like this: SELECT c.* FROM table1 a INNER JOIN table2 b ON a.id = b.table1_id INNER JOIN table3 c ON b.itemid = c.itemid WHERE a.itemtype = 1 AND a.busy = 1 AND b.something = 0 ...
I just created a couple of queries that bring the same data but in a different. the first one uses a sub query and the second one uses a self join strategy. checking the documentation, i found the ANALYZE and EXPLAIN commands, Now i'm trying to understand which query is better. this is the result of EXPLAIN ANALYZE for each query. Hope s...
Here's my explain plan: SELECT STATEMENT, GOAL = ALL_ROWS 244492 4525870 235345240 SORT ORDER BY 244492 4525870 235345240 **PARTITION RANGE ALL** 207633 4525870 235345240 INDEX FAST FULL SCAN MCT MCT_PLANNED_CT_PK 207633 4525870 235345240 Just wondering if this is the best optimized plan for querying huge partitioned table...