* gather_plan_statistics *


Gather_plan_statistics hint result not visible

on my Oracle, I tried to use that hint as in example: set autotrace on explain select /*+ gather_plan_statistics */ 'x' from dual; Execution Plan Plan hash value: 272002086 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time... Read More

Gather_Plan_Statistics + DBMS_XPLAN A-rows for parallel queries

Looks like gather_plan_statistics + dbms_xplan displays incorrect A-rows for parallel queries. Is there any way to get the correct A-rows for a parallel query? Version details: Oracle Database 10g Enterprise Edition Release - 64bi on HPUX... Read More

Gather_plan_statistics hint ignored

Hi, I am facing a situation when my gather_plan_statistics hint gets occasionally ignored. ALTER SESSION SET STATISTICS_LEVEL = ALL doesn't help either. I suspect that this may be one of Toad irregularities. I was given advice to run the query in sql... Read More

Dynamically apply gather_plan_statistics hint to a sql_id

Hello, We have set statistics_level=typical in our production database. And we do not have GATHER_PLAN_STATISTICS hint in any of our queries either. The reason being it can be performance inhibitor in production. Now I want to get ALLSTATS for a part... Read More

Estimated Rows Column, when using /*+ gather_plan_statistics*/

Hi, does anybody know, where the Information of the estimated_rows (e-rows) Output comes from? I would like to auto check, when a explain uses wrong statistics. I know that is inside the dbms_xplan.display.display Package but i would like to use it o... Read More

Gather_plan_statistics which reported step is the longest one

Hi, got plan like that from | Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   | |   1 |  VIEW                               | SYS_DBA_SEGS   |  ... Read More

What can I do to make this query run faster

Hi All, The below query is taking a long time. Is there any thing that I can do to shorten its time. SELECT C.FOLIO_NO, C.CO_TRANS_NO TRANS_NO, to_char(C.CREATED_DATE, 'dd/mm/yyyy') DOC_DATE, DECODE(PP.NAME, NULL, D.EMP_NAME, PP.NAME) LODGED_BY, deco... Read More

HOW TO: Post a SQL statement tuning request - template posting

This post is not a question, but similar to Rob van Wijk's "When your query takes too long ..." post should help to improve the quality of the requests for SQL statement tuning here on OTN. On the OTN forum very often tuning requests about singl... Read More

Elapsed time went up from 1min to 22min after migrating from 10g to 11g

I just migrated one of my database from hat Linux, 2 node RAC, sga= 1Gb) to (red Hat Linux 2 Node RAC, SGA=7GB) The timing for one of the specific query shoot up from 1min to 22 min. Following is the query: SELECT /*+ gather_pla... Read More

E-Rows = NULL and A-Rows=42M? Need help in understanding why.

Hi, Oracle Standard Edition CPU Oct 2012 running on Windows 2008 R2 x64. I am using Oracle 10g syntax for WITH clause as the query will also run on Oracle 10gR2. I do not have a Oracle 10gR2 environment at hand to comment if this behaves t... Read More

Performance of query in report..

Hi All, We have the below SQL which is taking 6-8 hrs to execute. Of which 'analysis_results_details' is huge table with 329 million records, its "IOT - TOP" [index oraganised] table. Can you please suggest some idea how to improve the performan... Read More

Performance problem - Tablespace Free Space

Hi, Version 10204 I am running the following statment in order to monitor tablespace free space. There are some tablespaces that i am not interesting to monitor , and i also whant to be alert only when less than 4 GB left in the tablespace. I build t... Read More

How to tune this SQL Query?

Hi all expert out there, I am using Oracle Database 10g Enterprise Edition Release - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options. I have this ERP 11i query took... Read More

How to measure the performance of sql query?

Hi Experts, How to measure the performance, efficiency and cpu cost of a sql query? What are all the measures available for an sql query? How to identify i am writing optimal query? I am using Oracle 9i... It ll be useful for me to write efficient qu... Read More

Why does not my query use an index?

I have a table with some processed rows (state: 9) and some unprocessed rows (states: 0,1,2,3,4). This table has over 120000 rows, but this number will grow. Most of the rows are processed and most of them also contain a group id. Number of groups is... Read More

How to tune this query

Hi, Im trying to select the latest set of data from a table using a simple query for inserting into a new table. Details are given below. There are no indices for the table. Its taking around 18 sec. Pls advice on how to proceed so that I can bring d... Read More

Need Help in sql tuning in EXADATA environment

I am uploadin the sql with its current explain plan, this sql in Prd database is executing in 6-10 mins and we need to improve this sql perf to 1-2 mins as the requirement from business team. I am giving some backgroud about this sql tuning requireme... Read More

Expression Filter Performance Issues / Misuse?

I'm currently evaluating the Expression Filter functionality for a new requirement. The basic idea of the requirement is that I have a logging table that I want to get "interesting" records from. The way I want to set it up is to exclude known,... Read More

Many-to-many Performance Problem (Using FAQ Template)

Having read "HOW TO: Post a SQL statement tuning request - template posting" I have gathered: I have included some background information at the bottom of the post The following SQL statement has been identified as performing poorly. It takes ~1... Read More

Long running query--- included steps given by Randolf

Hi, I have done my best to follow Randolf instruction word-by-word and hope to get solution for my problem soon. Sometime back I have posted a thread on this problem then got busy with other stuff and was not able to follow it. Here I am again with s... Read More