What Is Oracle Cost-Based Optimizer
Jun 3, 2013what is oracle Cost-Based Optimizer? Any material easy to follow?
View 3 Replies
ADVERTISEMENT
Performance Tuning :: High CPU Cost And Low Optimizer?
Jan 22, 2009how to reduce the cpu cost for a query at query level.
View 10 Replies View RelatedServer Administration :: To Find Out The Database Is Using Cost Based Analyzer
May 23, 2012How to find out the database is using Cost based analyzer?
Currently database is set to ALL_ROWS.
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- -----------
optimizer_mode string ALL_ROWS
SQL>
SQL>
Performance Tuning :: How Oracle Optimizer Choose Joins (hash / Merge And Nested Loop Join)
Oct 18, 2012I want to know how the Oracle optimizer choose joins and apply them while executing the query. So that I will insure about optimizer join before writing any query.
View 2 Replies View RelatedAutomated Gathering Of Optimizer Stats
Sep 10, 2013I am quiet confused with the optimizer collection stats job on 11g. when run the following query i see the statistics enabled.
SQL> select client_name, status from dba_autotask_client;
CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
I can gather statistics manually using DBMS_STATS, but there is no automated gathering of optimizer stats. how can i have system run and collect statistics on a daily bases?
Optimizer Cannot Merge A View That Contains Set Operator
Jul 18, 2012Query -
SELECT *
FROM sysadm.ps_tmtl_post_vw a
WHERE a.month_prepared_for = 'JUNE,2012'
AND a.ca_status = 'P5 CUST GO AHEAD'
[code]...
When I try for the SQL-Tuning sets its throws error that
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
I read earlier forum where it says that optimizer unable to interpret the conditions like order by etc etc.Now there is one view which is getting used in the query when I did select * from vw it took more than 16 hrs to complete. (bad view).
Attached File(s)
exec_plan.txt ( 2.06MB )
Number of downloads: 1
view_def.txt ( 14.12K )
Number of downloads: 2
Optimizer Behavior With Hierarchical Query And A Join?
Jul 23, 2013The full statement is:
SELECT v.key$ FROM VERSION_TABLE v, DOCUMENT_TABLE d, CLASS_TABLE z WHERE
v.documentKey = d.key$ AND
d.classKey = z.key$ AND
z.key$ IN (SELECT zz.key$ FROM CLASS_TABLE zz
START WITH zz.name = 'esDTTemplate'
CONNECT BY PRIOR zz.key$ = zz.parentKey) AND
v.ESGROUP = 'SearchOperatorsMapping' ORDER BY d.name
Now I noticed that the subquery is never used to seed the join: indexes - if any - are used. Otherwise a full table scan is performed.In the example - if ESGROUP is indexed, then it's chosen to start the join evaluation. If not, a full table scan is performed.Is there any way to suggest to the optimizer to use the subquery in case there are no indexes - as a fallback ?
In the above example where VERSION_TABLE contains nearly two million records, the no index solution takes 60 secs. vs. less than 1 sec. in the index case.Wrapping the hierarchical query in a inline view leads to same result.
PS: the execution plan (without index) is:
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | SORT ORDER BY | | 1 | 171 | 9 (23)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 171 | 8 (13)| 00:00:01 |
[code]...
Performance Tuning :: Force Optimizer To Consider All Join Permutations?
Oct 14, 2013I'm looking to see if there is a way (fully expecting it to be an underscore, or two...) to force the optimizer to keep churning until all permutations are exhausted.I'm aware that it, to paraphrase, cuts out when it's spent more time parsing than it would just running it based on it's estimates.
I've got some irritating problems with xml rewrite, xml indexes and access paths/cardinalities etc and I'm really needing the entire thing considered as a one off for debugging this. I've already cranked up the maximum permutations to the max but it's not enough, it shorts out after 5041 permutations (I'd set that to 80000 max).
I know you'd not want to do this in the real world but I cant get the damned thing to run the plan I want in a 10053 so I can see the values it has there. I know I can hint it, but I'm trying to ascertain why it's not even considering it in a "normal" parse.
Client Tools :: Auto Optimizer Stats Collection
Sep 20, 2012In my database auto optimizer stats collection job scheduled. It is successfully running,i am confirming this by viewing DBA_AUTOTASK_JOB_HISTORY. My doubt is whether stats gather job collect statistics when 10%(default value) of data modified in a hole table or table partitions.
Below is the output for user_tab_modification.I am able to see two entry for same table.
SQL> select TABLE_NAME,PARTITION_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED from USER_tab_modifications where table_name='DLM_PERFORMANCE_DATA';
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU
------------------------------ ------------------------------ ---------- ---------- ---------- ----------- ---
DLM_PERFORMANCE_DATA 169812174 0 0 20-SEP-2012 NO
DLM_PERFORMANCE_DATA SYS_P2663580 4946409 0 0 20-SEP-2012 NO
SQL & PL/SQL :: Units Of Cost?
Sep 28, 2010I was questioned in an interview what is the unit of cost and based on what was it calculated.
View 1 Replies View RelatedSQL & PL/SQL :: Sum (cost) Basing On ID Entered
Oct 1, 2012my data :
id cost
aa_aa 100
ab_aa 100
bb_bb 100
ba_bb 100
ab_aa 100
I need to get my data as
if id like 'aa_%' then it should sum cost for ids for aa_aa and ab_aa and print only 1 row with putput as
id cost
aa_aa 300
If id is like 'ab_%' then it should sum up
ids only for ab_aa..
Output as:
id cost
ab_aa 200
SQL & PL/SQL :: Finding The Cost Of Query
Sep 2, 2010 I am using oracle 10g, i want to know the cost of query. In the explain plan and by auto trace am able to get some query cost, but i dont know how to calculate the query cost.
How i will know the query is costly and take more time..? But i know about the hash table and nested tables in the explain plan.
Other than this how to calculate the query cost..?
PL/SQL :: Lag Function Cost Almost All Of CPU Resource
May 24, 2013select *from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
prepare data:
CREATE TABLE a AS SELECT LEVEL ID ,decode(mod(LEVEL,10),1,'CODE'||LEVEL) CODE from dual CONNECT BY LEVEL<20001;
sql1:
CREATE TABLE TEST AS
SELECT ID,
NVL(CODE,LAG(CODE ignore nulls) OVER(ORDER BY ID )) CODE
FROM a;
the sql is slow,most of the waits on session is resmgr:cpu quantum and almost all of the cpu resource is occupied
sql2:
CREATE TABLE TEST AS
SELECT ID,
NVL(CODE,last_value(CODE ignore nulls) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) CODE
FROM a;
this sql is fast
How lag function is implemented if the offset is not deterministic?
SQL & PL/SQL :: Query Is Using More Cost When Executing Using DB Link?
Oct 5, 2010I am trying to insert data from 9i to 11g using db link with the below query but while doing so the select statement is going for a full table scan even though rowid is used. But when we execute the same select statement without this insert command it is using proper index. Similar issues I am facing for updates.
Query:
INSERT INTO /*+ APPEND */ emp@db_link
select *
from emp where rowid ='AAC2SmAIIAAAHQgAAZ'
Performance Tuning :: Low Cost And High Cpu?
Jul 23, 2010I am facing one performance issue, in which the query cost is very low compare to cpu cost and as a result the cpu always show the high graph.I am also attaching the gv$sql and gv$sql_plan data of this query.
This is the query:
SELECT PTLS.ITEMTYPE , PTLS.ITEMID , PTLS.STAGEID, TS.USERID, SUM(PREVIOUSHOURS) AS PREVIOUSHOURS, MIN(STARTDATE) AS STARTDATE, MAX(STARTDATE) AS ENDDATE FROM PROJECTTIMELOGSSTAGE PTLS, PROJECTTIMESHEETITEM PTSI, TIMESHEET TS WHERE PTLS.PROJECTID = :B2 AND TS.TIMESHEETID = PTSI.TIMESHEETID AND TS.USERID = :B1 AND PTSI.TIMESHEETID = PTLS.TIMESHEETID AND PTSI.ITEMTYPE = PTLS.ITEMTYPE AND PTSI.ITEMID = PTLS.ITEMID AND (PTSI.ISPWFITEM = 'N' OR PTSI.ISPWFITEM IS NULL) AND PTLS.ITEMTYPE NOT IN ('OtherTsk','NewTsk','Loc','Glb') AND (PTLS.ITEMTYPE, PTLS.ITEMID ) IN (SELECT ITEMTYPE, ITEMID FROM PROJECTTIMELOGSSTAGE PTLS1 WHERE PTLS1.PROJECTID = :B2 AND PTLS1.TIMESHEETID = :B3 ) GROUP BY PTLS.ITEMTYPE, PTLS.ITEMID, PTLS.STAGEID, TS.USERID
SQL & PL/SQL :: Strange Cost - Summary Lower Than Ingredients?
Aug 1, 2012I've been experiencing strange Oracle behavior from time to time when using "explain plan". what could be the reason? (I'm going to create an Oracle ticket when I could find the cause...)
The total cost of a query was low (ex. ~500) while sub-parts of the query has high cost (ex. ~10000).I don't want to provide the query itself as the issue is observed for huge queries (like 2M characters!).The execution plan looks like this:
+- SELECT STATEMENT , ALL_ROWS, Cost 736
|
+--+- WINDOW SORT , Cost 736
|
[code]...
Performance Tuning :: Difference Between Cost And Bytes
Aug 4, 2010I am working on Tuning a Materialized view. I was getting an error saying ORA-12008: error in materialized view refresh path
ORA-12801: error signaled in parallel query server P002, instance hrms-stg-db01:HRSTG1 (1)
ORA-01652: unable to extend temp segment by 16 in tablespace TEMP1.
so i increased the temp size. I am trying out various hints to get it rectified. But i am not sure if i should go for less cost or less bytes.
Multi Table Query For A Summary Cost?
Aug 24, 2007I'm wanting to create a query that will give me a summary of parts and labor from work orders. However there are three tables. work_order, parts, labor. Sometimes there will only be parts, sometimes just labor, or sometimes both. Well my query will only return results when they are on both. Sample query is below. Do I need to create a temp table to gather the data or is there a way to do this with a sub query.
select work_order.wono, sum(parts.cost), sum(labor.cost)
from work_order, parts, labor
where
work_order.wono = parts.wono and
work_order.wono = labor.wono
group by work_order.wono;
Performance Tuning :: How To Reduce Cost Of Query
Mar 21, 2013I have Following wjich takes some minutes to executes i want to be tune so this query Executes fast.
Query :
SELECT a.CHDR_EXCH_CD ,TMHR_EXCH_TM_CD,'S' Sec_type,
round(SUM (Decode(csdt_Depo_Typ,'I',(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-RR'),csdt_stsc_cd,csdt_scp_qty)*csdt_scp_qty)-
(Cal_Scheme_Rate(csdt_rsm_code,TO_DATE(:P_DT_FR,'DD-MM-
[Code]...
Explain Plan Result :
Plan
SELECT STATEMENT ALL_ROWS
Cost: 1,669 Bytes: 67 Cardinality: 1
15 HASH GROUP BY
Bytes: 67 Cardinality: 1
14 CONCATENATION
[Code].....
After i see result , no 4 in explain plan result gives TABLE ACCESS FULL . i want to be indexing on that how to do this..
This table MG_COLL_SCP_DTL have index like this
CREATE UNIQUE INDEX CSDT_PK ON MG_COLL_SCP_DTL
(CSDT_CHDR_TRANS_NUM, CSDT_PROD_TYP, CSDT_TRAN_SR_NO, CSDT_CHDR_CDTL_COLL_TYP, CSDT_CHDR_CDTL_COLL_TYP_CD,
CSDT_STSC_CD, CSDT_CHDR_CLNT_CD, CSDT_CHDR_CLNT_TM_CD)
[Code]....
How to Reduce cost ???
Performance Tuning :: Cost Of Query Is Brought Down But Taking Same Time
Sep 1, 2010For an query, cost was 16Lakhs and was taking 30min, I brought down the cost to 1.5lakhs, but still it is taking 30min.
There were many outer joins and same table has been Used(FROM clause) 5 times in the query. I have introduced WITH clause, and brought down the cost.
Performance Tuning :: Cost Calculation For Nested Loop Join
Mar 27, 2012Following is the query on TPC-H schema.
explain plan for select
count(*)
from
orders,
lineitem
where
o_orderkey= l_orderkey.
The trace 10053 (as shown below) for this query shows nested loop join with Lineitem as outer table and Orders as inner table. It is effectively join on composite index (pk_lineitem) of Lineitem and unique index(Pk_orderkey) of Orders table. The cost calculation formula as given in the book as "outer table cost + cardinality of outer table * inner table cost " fails here. I am not able to understand this.
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: LINEITEM Alias: LINEITEM
#Rows: 6001215 #Blks: 109048 AvgRowLen: 124.00
Column (#1): L_ORDERKEY(NUMBER)
AvgLen: 6.00 NDV: 1500000 Nulls: 0 Density: 6.6667e-07 Min: 1 Max: 6000000
[code]....
how the cost has been calculated. This does not follow the traditional nested loop cost formula as mentioned in the book.
Forms :: Create Two Datablocks Based On One Table In Oracle?
Jun 2, 2011can we create two datablocks(header&line) based on one table in oracle forms.any way to create?
View 7 Replies View RelatedPL/SQL :: Updating CSV Values In Oracle Table Based On Condition
Aug 27, 2013I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.
View 1 Replies View RelatedPL/SQL :: Oracle Database 11g - Add Partition Based On Amount Of Data To Be Populated
Oct 30, 2013I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production and TNS for Linux: Version 11.2.0.3.0 - Production.Requirement is to create a script to add a LIST partition to some selected tables in a schema (tables do not have data, they are not partitioned). There are about 300 such tables (can vary) and their names are maintained in a separate table. Example -Existing table
-CREATE TABLE test_part(id number (2),
name varchar2(20),
audit_userid number (9));
Expected table
-CREATE TABLE test_part(id number (2), name varchar2(20), audit_userid number (9))
PARTITION BY LIST (audit_userid) (PARTITION p1_audit_userid VALUES (1));
Ultimate goal is to add more partitions based on the amount of data to be populated.
SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source
Jun 1, 2010I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....
Security :: Authenticating Oracle User Based On Windows User ID?
Mar 11, 2011We are trying to implement the following security to our database.
As of now, the access rights are same for all the windows users logging into the Oracle application with the same Oracle user Id.
But now, we want to improve our security by granting different levels of rights to the users based on their Network Id even though they use the same Oracle User Id to log into the application.
We are not looking for the users to be identified externally.
(CREATE USER "OPS$ORACLE-BASE.COMTIM_HALL" IDENTIFIED EXTERNALLY;
GRANT CONNECT TO "OPS$ORACLE-BASE.COMTIM_HALL";
)
SQL & PL/SQL :: Select Whole Row Based On Max Value?
May 10, 2013Suppose I have the following, what is the best way to select the whole row that has the highest (IDX+LEN desc, LEN desc)
IDIDXLEN
5109153
7273118
9165356
I currently have made the following two method, which work but I don't particularly care for.
WITH bob AS
(SELECT 9165 id, 35 idx, 6 len FROM DUAL
UNION
SELECT 5109 id, 15 idx, 3 len FROM DUAL
UNION
SELECT 7273 id, 11 idx, 8 len FROM DUAL)
[code]....
Max Row Based On Value In Column?
Mar 3, 2011How to get the max row based on a value in a column
View 1 Replies View RelatedSessions Based On SQL ID
Aug 31, 2012One of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?
View 9 Replies View RelatedPL/SQL :: Sum Based On Group 2
Nov 26, 2012I have the following table and data , I have six employees and carton named A,B,C,D,E and F.
Drop table a ;
Create table a (id number(9), emp_id number(9), cartoon varchar2(20), no_cartton number(9));
Insert into a values(1,1,’A’,10);
Insert into a values(2,1,’B’,20);
Insert into a values(3,1,’D’,25);
Insert into a values(4,1,’E’,15);
[code].......
I have also Third Query: the first and second query already solved in this link . Sum based on group
it is multiple value of carton A of emp_id 1 with 2 + multiple value of B of emp_id 1 and 2 and so on
More detalies : Multiple value of A for Empid 1 and 2 then add it to multiple value of multiple value of b for empid 1 and 2 then add it to multiple value of c of empid 1 and 2
After finished all carton for 1 and 2 then go to 1 and 3 after finished go to 1 and 4 then 2 and 3 then 2 and 4 and so on
Output will be like this
Empid Total
1 with 2 2050
1 with 3 200
1 with 4 500
1 with 5 2250
1 with 6 700
2 with 3 0
2 with 4 700
2 with 5 4300
2 with 6 1700
3 with 4 0
3 with 5 400
3 with 6 0
4 with 5 400
4 with 6 200
5 with 6 1900