Performance Tuning :: Using Stats Table Instead Of Regular Stats To Get Plan?

Jan 27, 2011

DBMS_STATS.CREATE_STATS_TABLE
DBMS_STATS.EXPORT_SCHEMA_STATS
DBMS_STATS.GATHER_TABLE_STATS

I have used the above to get a copy of schema stats and gather new stats for specific tables into a STATS TABLE in my personal schema. What I want to do now is use this stats table to generate plans for queries where I believe stats are off. Is it even possible? To be clear, I do not want to import stats because this replaces the stats currently there. I just want to point the CBO to my stats table for generating plans.

there was a session parameter I could set to tell oracle to use my stats table when generating plans, or an explain plan clause I could use or a DBMS_XPLAN paramter I could provide that would tell these tools to use my stats table when generating a plan, or even some way to tell autotrace. But I have found none of this.

View 2 Replies


ADVERTISEMENT

Performance Tuning :: Why Elapsed Time Changed While Execution Plan And Stats Remains The Same

Jun 4, 2010

attached query giving consistent execution plan but different timings across run

SELECT /*+ INDEX (CRT CRT_CUN_FK_I)*/
DISTINCT odr.dve_id
FROM company_requirements crt, orders odr, lelo_products la_pct
WHERE crt.qtn_cun_id = 10035637--10000021--10035667
AND crt.ID = odr.crt_id_quote_implemented
AND NVL (odr.cancellation_date, '31-Dec-9999') = '31-Dec-9999'

[code]....

we have 4 databases, 2 on each servers, such that db1 and db2 on server1 and db3 and db4 on server2

refer count of the records for column of biggest table in the query, taken on all 4 databases (The column is nullable)

select count(*) from company_requirements crt WHERE crt.qtn_cun_id = 10035637
db1 = 73335
db2 = 89073
db3 = 81182
db4 = 82936

First I executed the query on db1 and db2 while there wasn't any user logged on to the system

db1
**********
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.08 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 17.47 473.39 85704 1508102 0 0

[code]...

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
db file sequential read 85704 0.31 460.55
latch free 1 0.00 0.00
SQL*Net message from client 1 14.98 14.98

[code]...

Why the elasped time changed when data and plan hasn't changed at all? Also why the plan has different stats for round 1 and 2 on db1 and db2?

I ran it 2 times each round each database so hard parsing shall not be issue.Also why the number of rows accessed are different in db1,db2 and db3,db4 especially for step1 when count of crt.qtn_cun_id is similar?

In fact when the query was taking long I was the only user on the system Also I used hard coded value (no bind variables at all)

I checked num_rows, distinct keys as well which are quite similar across all 4 databases Also no stats where gather during the query execution

What I should have checked or monitored?

View 10 Replies View Related

Performance Tuning :: Stats On A Partitioned Table?

Aug 8, 2013

Is it possible for the DBMS_STATS "LIST STALE" command to show a stale partition but NOT have its table show as stale?

I had a scenario where the table itself AND 1 partition showed as stale. I ran a fnd_stats gather table stats just on that 1 partition. Once it was completed it showed the partition to no longer be stale. it also showed that the table was no longer stale. so I guess I do not need to run stats on the whole table as well?

so if this is the case, when would I need to run stats on the full partitioned table if running it on the partitions themselves removes the staleness of the table?

View 3 Replies View Related

Performance Tuning :: Frequent Changed Table Stats

Aug 6, 2012

Our UNDO space remains at a high level 85 to 95 percent. We keep adding database files and it doesn't seem to go down significantly. When we do a backup of the system where we shut the database down, it does go down some but then within a week or so it is back up again.

View 11 Replies View Related

Performance Tuning :: Analyze Table Compute Statics To Gather Stats

Sep 11, 2012

I load a table through sql loader which takes nearly 14 min for 8-9 millions records, once the records complete i run the analyze table compute statics to gather stats and it takes nearly 15 min. is there any ways so that i can reduce the stats timing. the stats collection command runs from other schema not from where the table is residing.

View 1 Replies View Related

Performance Tuning :: Incremental Stats Not Happening

Oct 15, 2012

I have set the incremental stats for my partition table as it takes more than 20 min to gather , though the incremental is set to 'true' the table is getting analyzed completely.

View 3 Replies View Related

Performance Tuning :: DBMS Stats Is Gathering

Oct 26, 2010

Oracle 10g has the feature of automatic stats gathering in this case is it necessary to run DBMS_STATS on tables manually. Does the stats gathered become stale when the auto stat runs ?

View 1 Replies View Related

Performance Tuning :: Speed Up Gathering Of Stats

Jun 2, 2011

I am gathering stats by using below block i.e., for some 3 million records and there are 6 indexes on the table. What is the relevance of value 4 here (i.e., method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4')? If I increase 4 to 250 will there be any speed change in gathering stats. My intention is to speed up the gathering of stats.

begin
dbms_stats.gather_table_stats(
ownname => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
tabname => 'LEGAL_VIEW_TARGET',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4',
cascade => TRUE
);
END;

View 12 Replies View Related

Performance Tuning :: Analyze Stats Running Long

Jul 31, 2013

I have a Datamart DB which has a continuously increasing volume. I run a daily optimize job, to have the data analysed using the below:-

EXECUTE dbms_stats.gather_schema_stats(ownname=> 'xxx' ,estimate_percent=> 25 , DEGREE => 4, cascade=> TRUE );

analyze table xxx.abc compute statistics; But this optimization itself is taking nearly 4 hrs to complete and I can't afford to have the delay.

Is there a better way of running this optimization?

View 9 Replies View Related

Performance Tuning :: Auto-Stats Gathering Not Including Schema?

May 16, 2011

I have several databases that i've recently upgraded from 9i to 11g. With all of them, the automatic stats gathering process has worked just fine every night during the maintenance window.

However, i have this other database that i created and it seems that the only stats being gathered are on the sys and system schemas and not the actual schema that holds all of our tables.

I did some searching, but i'm not sure i was using the right search terms, because i came up empty.

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Solaris: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

View 16 Replies View Related

Does Old Plan Stay After Re-gathering Stats

Oct 1, 2012

I am on 11.2 on Linux.I am looking into a performance issue. The issue is around 1 particular SQL, involving about 5 tables.I re-gathered statistics on 2 main tables in the query (out of 5 tables).

When I say re-gathered, I first did DBMS_STATS.DELETE_TABLE_STATS and then did DBMS_STATS.GATHER_TABLE_STATS.

Earlier, we had histograms on these tables, which I removed and gathered stats without generating histograms.
SQL> select table_name, num_rows, sample_size, last_analyzed from user_tables where
  2  table_name in ( 'DETAIL_TABLE','MASTER_TABLE');

TABLE_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
------------------------------ ---------- ----------- -------------------
MASTER_TABLE                     50615338    50615338 01/10/2012 11:09:27
DETAIL_TABLE                    353550440   353550440 01/10/2012 11:10:05

2 rows selected.Then ran the SQL again couple of times (actually, that SQL is in a stored procedure, which I ran couple of times).I found this wonderfull SQL on internet, which tells me when the SQL ran and which plan (identified by its hash value) it used. Using this SQL I tried to check if my SQL was run using any different plan, but it used exactly same plan it used before I re-gathered the stats. See the last analyzed time above and begin_interval_time below, same SQL has run before and after stats collection, with same plan_hash_value.

SQL> select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
  2  nvl(executions_delta,0) execs,
  3  (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
  4  (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
  5  from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS

[code]....

7 rows selected.

My question is, when I re-gathered stats on 2 tables out of 5 tables in a given SQL, are the plans not flushed out of SGA? I was expecting that, at least a new plan hash value would show up front of my SQL, before and after stats collection.

View 8 Replies View Related

Server Administration :: Delete Stats Gather Stats

Sep 11, 2012

During STATS gather running for the table, unknowingly i deleted the old stats using EXEC DBMS_STATS.DELETE_TABLE_STATS. I would like to know will it affect the stats gather job currently running for the table and whether my stats will be gathered successfully.

View 5 Replies View Related

Performance Tuning :: Same Execution Plan For Create Table Statement When Name Changes?

May 18, 2010

Can we have same execution plan for a create table statement where the name of the table changes every time as follows:

create table test
as
select * from t1

Here table name changes from test to another table name next time

View 6 Replies View Related

Gather Stats On Table

May 26, 2011

I have doupts in gathering stats on table. I analyzed one table it took 2 hours first time.. the same table after one week later i analyzed, its got completed within 45 minutes.. I don't know exact reason why i got completed very soon. Is there any specific reason?

View 2 Replies View Related

SQL & PL/SQL :: 11g Oracle Upgrade Stats Table?

Jan 30, 2012

When we upgrade from 10g to 11g oracle upgrades the stats table by EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('OWNER','TABLE'); But When we import from 10g to 11g do we need to upgarde the stats table??

View 2 Replies View Related

PL/SQL :: Generate Stats For DBLink Table (SQL)

Sep 7, 2012

I am trying to generate some statistics on tables connected by a dblink. I know with oracle you have table_columns which you can reference and pull some stats from.

Trying to get the column count and record counts for each table connected by a dblink. I have tried these queries below to see if I could see any db properties: (some just to try something different)

select * from "table_owner".table_column@dblink ;
select * from "status"@dblink;
select /*DRIVING_SITE(a) */ count(*) from @dblink a;

What is the best method to finding this out without spending a lot of time? I have over 30 tables which are with large record sets and would love to learn a faster approach then pulling a sample table and doing a manual count and query for each table to count the rows.

View 3 Replies View Related

Performance Tuning :: Composite Index - Explain Plan Full Table Scan On Lookup_fein?

May 12, 2011

I have created an non unique index lk_fein on lookup_fein( code,map_id,trash). When I check the explain plan it does a full table scan on lookup_fein. if I force it to use index by it does and the cost also decreases.

SQL> SELECT WORK_FEIN,
2 NON_FEIN ,
3 FI_FEIN ,
4 MFEIN ,
5 TOTAL_FEIN ,

[code]...

View 1 Replies View Related

Server Administration :: Collect Table Stats?

Oct 10, 2013

We are running 11g (11.2.0.3)We have a "working table" that is empty at the beginning of the day.Then we start adding rows (insert) with a key column called STATE with a value of 100.At the same time, there are other apps that pickup data in state 100 , process that data and change that state to 200 or 300.There is also another app that pickup data in state 200 , process that data and change that state to 300 or 400.

So in summary, the data on that table is at the beginning empty, then all the rows are in state 100, they slowly move to different states (200, 300, etc) and by the end of the day, they are all in 400.

My question is what would be the best way to collect stats on this table?

I was thinking to create an hourly job to collect stats on that table:
exec dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'WORK_T

View 6 Replies View Related

SQL & PL/SQL :: Dynamically Filling Data In Table And Rebuilding Indexes Gathering Stats

Jan 26, 2012

I have created a table like below-

PROMPT CREATE TABLE tst_fetch_vendor_data
CREATE TABLE tst_fetch_vendor_data (
vendor_data_seq_no NUMBER NOT NULL,
study_seq_no NUMBER NOT NULL,
vendor_record_seq_no NUMBER NOT NULL,
control_column_seq_no NUMBER NOT NULL,
resolved_value VARCHAR2(4000) NULL,
original_value VARCHAR2(4000) NULL,
transaction_user VARCHAR2(30) NOT NULL,
[code]....

Its just a temporary table, in which data comes and goes. I am using this in middle of a process.I am using it in a process like below--

--EXECUTE IMMEDIATE 'TRUNCATE TABLE TST_FETCH_VENDOR_DATA DROP STORAGE';

insert /*+ append */ into tst_fetch_vendor_data
(select * from vendor_data vd
where vd.control_column_seq_no in
(select control_column_seq_no from temp_control_column));
dbms_stats.gather_table_stats('EPDSYSREP','TST_FETCH_VENDOR_DATA',ESTIMATE_PERCENT=>100,
METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);

code to use that table..This table can contain data from 0 to 108000000 records.Now my questions are-

1. How much should I select sampling size (currently its 100%)Can I use dbms_stats.auto_sample_size, what will be the effect?

2. dbms_stats is good approach or should I use dynamic sampling.

3. what about the approach using CTAS instead of inserting data through insert.

4. What about pl/sql table with index or with clause query.

5. Do I need to rebuild index after inserting data into table.

View 3 Replies View Related

Table Stats Copy Amount Sub-partition To Save Load On System - Error?

Oct 10, 2012

i have a large OLTP database and we are doing table stats copy amount subpartition to save the load on system. while doing an copy default subpartition stats: I see the following error:

SQL> exec DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true);
BEGIN DBMS_STATS.COPY_TABLE_STATS('cusms','STATUS_HIST','P_VDEF_10_2012_S100','P_VDEF13_10_2012_S100',force=>true); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

View 2 Replies View Related

Performance Tuning :: Different Execution Plan In Different DB

Jul 20, 2013

Why the query is behaving differently with the different database.(execution plan)

Whatever the production database is having same database instance replicated to a new schema. I tried both the queries running on both environment.In prod the index has been used but in newdev it is not. This case existing primary key index were not been used.

View 6 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

Performance Tuning :: Avg Time Taken By Execution Plan

Apr 12, 2013

How can i check the avg time taken by an execution plan. Actually i have a very big query and it changes its execution plan very often, we would like to lock the best execution plan and to find it , i would like to know the Average Execution Time the query takes when it runs using different different execution plans.

View 7 Replies View Related

Performance Tuning :: Execution Plan Of SQL Statement

Mar 25, 2012

I have queries on the execution plan of a sql statement

Following is the example

create table t1 as select s1.nextval id,a.* from dba_objects a;
create table t2 as select s2.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
commit;

create index i1 on t1(id);
create index i2 on t2(id);
create index i11 on t1(object_type);

exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

select count(*) from t1 where object_type='VIEW';

COUNT(*)
----------
8934

set autotrace traceonly explain

Can we say in the following case, that,

(1) First index on object_type is accessed to get rowids - t1.object_type='VIEW'
(2) Then the filter on owner is applied - t1.owner='SYS'
(3) Then the table T1 is accessed to fetch data from the rowids returned by the index I11 and filer application - TABLE ACCESS BY INDEX ROWID

Though I am unable to understand how filter can be applied to the rowids retrieved from index, we can see from the plan below that The rows accessed have reduced from 8550 to 1221 before we access the table...Thus filter "t1.owner='SYS'" is applied in between. Right?

another question is

Case 1 - do we retrieve a rowid from index for a given value, then retrieve required values from table for that rowid
Thus row at a time in both ... in loop
OR
Case 2 - we first fetch all rowids from index and then retrieve values from table one row at a time from the collection of rowids fetched?

Suppose Case 1 is what is happening then can we say, both the steps mentioned by IDS 2,3 in plan below are executed exactly equal number of times and the filter "t1.owner='SYS'" is applied at some later stage? Of course in this case the values in ROWS stand misleading then

select * from t1,t2 where t1.id = t2.id and t1.object_type='VIEW' and t1.owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 26873579
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1221 | 233K| 915 (1)| 00:00:11 |
|* 1 | HASH JOIN | | 1221 | 233K| 915 (1)| 00:00:11 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1221 | 116K| 381 (1)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | I11 | 8550 | | 24 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T2 | 161K| 15M| 533 (1)| 00:00:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."OWNER"='SYS')
3 - access("T1"."OBJECT_TYPE"='VIEW')

View 7 Replies View Related

Performance Tuning :: SQL Execution Plan Different On Different Hardware

Oct 31, 2012

So the situation is like this

- Database A (20 tables)
- Database B (20 tables)

- Both A and B are Oracle 11gR2

- Both of these databases run on different hardware (A is a VM, B is on a physical host)

- The 20 tables in A and B have exactly same number of rows and after preparing the data, the schemas were analysed using the same DBMS_STATS parameters

Despite this, the execution plans appear to be quite different for the same queries between A and B

I imagine there is something outside of the Oracle table rowcounts, table stats, column stats, index stats that's resulting in the different execution plans.

View 3 Replies View Related

Performance Tuning :: Achieve Same Execution Plan?

Apr 30, 2012

refere to below 2 queries and their execution plans:

First Query
INSERT INTO temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag)
SELECT /*+ USE_NL ( vd1 ,vd2 ,vd3 ) leading ( vd1 ,vd2 ,vd3 , tvd) */
vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U'
FROM vendor_data vd1,

[code]...

Second Query
SELECT vd1.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U'
FROM ( select * from vendor_data vd1
where vd1.study_seq_no = 99903
AND vd1.control_column_seq_no = 435361232

[code]...

Both are to achieve same output but written in different ways. CAn I get same exectuion plan from 1st query as there is for 2nd using hints

View 10 Replies View Related

Performance Tuning :: Execution Plan Changing With Same SQL Profile

Mar 5, 2013

One of our clients is using Rule Based Optimizer on Oracle 10.2.0.3.0

2-3 weeks backs, during performance issue in one of the sql queries, one of our team members executed tuning adviser for it, created SQL profile and the subsequent execution of the SQL did not took much time (less I/O). Now it took hardly a minute to execute

When this happened I checked that the SQL profile forced that particular query to use CBO (say plan_hash_value is PHV1 here). Yesterday the same query again took 15-20 minutes for execution. I checked that even for this execution the query used the same SQL profile but "this time" with different plan_hash_value - say PHV2.

Today again the query executed in less than a minute and used the plan_hash_value as PHV1.

select distinct plan_hash_value,timestamp from dba_hist_sql_plan where sql_id='mysqlid' order by 1,2;

PLAN_HASH_VALUE TIMESTAMP
--------------- --------------------
890360113 20-feb-2013 16:38:39
3736413466 04-mar-2013 08:12:52
1237282258 03-jan-2013 17:15:02

I confirmed from awrsqrpt as well that different plans were used for different plan_hash_values and every time same SQL profile was used

SQL> select name,CATEGORY,SIGNATURE,CREATED,LAST_MODIFIED,TYPE,STATUS,FORCE_MATCHING from dba_sql_profiles;

NAME CATEGORY SIGNATURE CREATED LAST_MODIFIED TYPE STATUS FOR
------------------------------ ------------------------------ ---------- -------------------- -------------------- --------- -------- ---
SYS_SQLPROF_015ffffcc3e1c5b000 DEFAULT 1.5512E+19 20-feb-2013 16:30:48 20-feb-2013 16:30:48 MANUAL ENABLED NO

I am unable to understand how execution plan and thus plan_hash_value is changing for the same SQL Profile. I read that SQL Profile (unlike stored outline) keeps up with increasing data volume and may not keep up with changing data distribution.

I checked that values for 4 bind variables out of 81 are different for execution between today and yesterdays' run(queried v$sql_bind_capture based on last_captured)

My questions are
1) does the different plan_hash_values with different execution plans for query using same SQL profile mean the query was hard parsed multiple times and still used the same SQL profile?
2) If that is the case why I never saw child_number = 1 in any of the views for the same sql_id. I tried it repeatedly over last 2 weeks and always found child_number=0 in v$sql (also loaded_versions=1)
3) Does the different values of bind variable are causing this flip-flop of the plans? How can I conclude this?

I have 2 plans with 2 different plan_hash_values. I know which would be better. How can I force the sql to use better plan in the two in this case where I am using Rule Based Optimizer and have SQL profile created If this is not possible then how can I create stored outline from the existing plan (not waiting for subsequent execution to take place).

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

Performance Tuning :: XML / XPath Rewrites And Plan Baselines?

Oct 29, 2013

The types of query I refer to in the title are of this pseudo-code ilk:

select t.column_value
from table1 o, xmltable('for $co in $data
where $co/path1=$bind1
and $co/path2=$bind2
passing o.field as "data", :b1 as "bind1", :b2 as "bind2") t
where o.field = :b3

They're querying a table with a (binary) xmltype with a path/domain index over this column.As those who have had the (mis)fortune to run into these will know, the queries are extensively rewritten under the covers to access to xml via the paths supplied.

getting a baseline to work with queries like this? I was suspicious because whilst I can hint it to pick a certain access path first (leading()), the plan hashes remain the same.

I'm not sure, however, if I'm simply "doing it wrong" or it is just not possible with the level of recursive rewriting going on.NB: I consider myself reasonably competent in applying baselines to "traditional" queries...

View 7 Replies View Related







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