Performance Tuning :: Verify If Procedure Has Been Completed
May 10, 2011Is there a way to know if a procedure has been completed/ still running?
View 2 Replies
ADVERTISEMENT
Performance Tuning :: How To Verify If A Row Exists (index Range Scan)
Oct 18, 2011In the link below [URL] Thomas kyte has said, use the CBO and select /*+ FIRST_ROWS */ primary_key from table where rownum = 1; it'll read the index and stop at the first row. very fast on a big empty table (as the index is small and empty).
very fast on a big full table as the index is just read to find the first leaf node and then "stop".
It gives faster result if the primary key is used. But what if we have a table with around 1000 million rows and for the predicates there is a index range scan on the table.
What if we have a table say big_table (10000000000 rows) and the sql is something like
select /*+ first_rows */ 1 /* id, attribute_id*/
from big_table
where attribute_name ='Gross Premium'
and value ='10000'
and version_date is null
and rownum=1; --it's taking around 3 min
We observed that in such case there will be a range index scan for the index on the predicates. For a particular id there may be different values for attribute 'Gross Premium' and may have multiple versions.
How I would tune such a query where the purpose is to check if at least 1 records exists in the table for the input?
Performance Tuning :: Procedure Performance On New Database Import?
Nov 15, 2010Testing our 9i to 11g upgrade, we've imported the entire DB into the new machine.We've found that certain procedures are really suffering performance problems. BUT, we've also found, that if we check out a production copy of the procedure from our source code control, and reinstall it, the performance issue goes away. Just alter the procedure and recompiling does NOT work.
The new machine where the 11g database exists is slightly different than the source, but it's not like we have this problem with every procedure. It's only a couple.
any possible reason that we'd have to re-install a procedure to correct a performance problem?
SQL & PL/SQL :: Procedure Successfully Completed But Not Able To View The Data
Sep 26, 2010I've created one procedure with following code.
PROCEDURE MaxNo IS
A Number;
BEGIN
Select Max(Supplier_ID)+1 into A from supplier ;
END;
SQL> Select Max(Supplier_ID)+1 from supplier;
MAX(SUPPLIER_ID)+1
------------------
6
when I execute it in a SQL PLus
SQL> SET SERVEROUTPUT ON
SQL> exec MaxNo;
PL/SQL procedure successfully completed.
Client Tools :: Remove / Prevent PL/SQL Procedure Successfully Completed Message
Aug 20, 2010I do not want the "PL/SQL procedure successfully completed." message in my output file - I don't see/recognise an option to suppress or remove this.
-- Test Proc
--
set linesize 500
set pagesize 50000
set tab off
set wrap off
set serveroutput on
set feedback on
[code]...
PL/SQL procedure successfully completed.
Performance Tuning :: One Procedure Is Blocking And Another Is Locking It
Jan 25, 2011I am new to performance tuning. One procedure is blocking and another is locking it.
SQL> @glockers
Oracle OS
INSTANCE SID TY Usernam SERIAL# SPID Process LMODE REQUEST
---------- ------ -- ------- ---------- ------------ ------- ------------------------------------ ------------------------------------
[code]...
Performance Tuning :: Calling Stored Procedure In Jsp Page
Feb 16, 2011We are calling a stored procedure in jsp page. The sql where bottleneck was assumed is giving better performance in sql plus (0.093 seconds) but when called from jsp its taking too much time.
Here is the plan for the sql
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 |
| 1 | SORT GROUP BY | | 1 | 219 |
| 2 | NESTED LOOPS | | 1 | 219 |
| 3 | NESTED LOOPS | | 1 | 197 |
| 4 | NESTED LOOPS | | 1 | 162 |
| 5 | MERGE JOIN | | 1 | 127 |
| 6 | TABLE ACCESS BY INDEX ROWID | policy_tbl | 1 | 78 |
| 7 | INDEX FULL SCAN | pk_pol_id | 1 | |
| 8 | SORT JOIN | | 1585 | 77665 |
| 9 | TABLE ACCESS BY INDEX ROWID| policy_p_tbl | 1585 | 77665 |
| 10 | INDEX RANGE SCAN | pp_param_1 | 1679 | |
| 11 | TABLE ACCESS BY INDEX ROWID | policy_group_tbl | 1 | 35 |
| 12 | INDEX RANGE SCAN | pg_indx_2 | 1 | |
| 13 | TABLE ACCESS BY INDEX ROWID | policy_group_tbl | 1 | 35 |
| 14 | INDEX RANGE SCAN | pg_indx_2 | 1 | |
| 15 | TABLE ACCESS BY INDEX ROWID | policy_dtl_tbl | 1 | 22 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 16 | INDEX UNIQUE SCAN | pk_pg | 1 | |
--------------------------------------------------------------------------------
Performance Tuning :: Tools For Database Tuning And Instance Tuning
Jul 12, 2010Looking to understand the difference between instance tuning and database tuning.
What is the difference between these two tuning exercises? I understand that an instance is memory based structures (logical) where as database consists of physical structures.
However, how does one tune a database the physical structure? Does it have to do with file placements/block sizes etc. Would you agree that a lot of that is taken care by ASM now in 11g? What tools are required/available (third party as well as oracle supplied) for these types of tuning scenarios?
Performance Tuning :: Merge Statement Tuning For 100M Records In Table?
Oct 31, 2011I have two tables with 113M records in DWH_BILL_DET & 103M in prd_rerate_chg_que and Im running following merge query, which is running for 13 hrs to update records, which is quiet longer time.
SQL> explain plan for MERGE /*+ parallel (rq, 16) */
INTO DWH_BILL_DET rq
USING (SELECT rated_que_rowid,
detail_rerate_flag_code,
rerate_sel_key,
[code].....
Performance Tuning :: How Length Of Column Width Effects Index Performance
Sep 30, 2010How the length of column width effects index performance?
For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)
Table key consist of(id, job, time)
Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).
What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.
Performance Tuning :: Fragmentation Can Reduce Performance In Query Times
Jun 16, 2010I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..
I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.
Any useful script or query to do this, any interesting oracle system view?
Performance Tuning :: Method Of Tuning Database - Row Reduction?
Oct 20, 2010There is a simple way to increase the performance of a query by reducing the row-size of the table it hits. I used it in the past by dividing the table into smaller parts and querying respective smaller table in each query.
what is this method called ? just forgot the method and can't recall it. what this type of row-reduction optimization is called ?
Performance Tuning :: Performance Standard Edition Without Partitioning?
Jun 16, 2011How many records could I have in a single table without performance degradation with Standard Edition without partitioning with cutting-edge server (8 or 12 cores, 72 GB RAM, FC 4 Gbit, etc...) and good storage?
300 Millions in only one table with 500K transactions / day is too much?
Simple database with simple schema.
How many records begin to be too many?
Performance Tuning :: Checking Delete Performance In Package
Apr 12, 2013I need to check the package performance and need to improve the package performance.
1. how to check the package performance(each and every statement in the package)?
2. In the package using the delete statement to delete all records and observed that delete is taking long time to delete all the records in the table(Table records 7000000). This table is like staging table.Daily need to clean the data before inserting the data into it. what can I use instead of Delete.
Performance Tuning :: Query Performance Gain Using Statistics?
Aug 9, 2010Somewhere I read that we should not use hints in Oracle production environments, but we can use hints in the development environment and on achieving the desired execution plan we can adjust the 'statistics' to follow that plan without hints.
Q1. If it is true what statistics do we adjust for influencing the execution plan and how?
For example, I have the following simple query:
select e.empid, e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
emp.empid, emp.deptno and dep.deptno columns have indexes and the tables have the standard structure as found in the basic oracle examples.
If I look at the execution plan of the above query then I see that the driving table is empand the driven table is dept.Also the type of join that is taking place is 'Nested Loop'.
Questions: With respect to the above query,
Q 2. If I want to make dept the driving table and emp the driven table then how can I adjust the statistics to achieve that?
Q 3. If I want to use hash join instead of a nested loop join then then how can I adjust the statistics to achieve that?
I can put the ordered and the use_hash hint to effect this but again I have heard that altering statistics is a more robust way to control an execution plan as compared to hints.
Performance Tuning :: How To Improve The Performance Of Export Job (expdp)
Dec 6, 2011I have an issue with export(expdp).
When i exporting an user using expdp utility, the load the on the server is going up-to 5. The size of the database is 180GB. Below is the command that i use for export.
expdp sys/xxxx directory=dbpdump dumpfile=expdp_trk_backup.dmp logfile=expdp_trk_backup.log exclude=statistics schemas=trk
Do i need any look into any memory parameters for this?
Performance Tuning :: DECODE In WHERE CLAUSE Performance?
Oct 17, 2011The following query gets input parameter from the Front End application, which User queries to get Reports.There are many drop down boxes like LOB, FAMILY, BRAND etc., The user may or may not select values from drop down boxes.
If the user select any one or more values ( against each drop down box) it has to fetch all matching values from DB. If the user does'nt select any values it has to fetch all the records, in this case application will send a value 'DEFAULT' (which is not a value in DB ) so that the DB will fetch all the records.
For getting this I wrote a query like below using DECODE, which colleague suggested that will hamper performance.From the below query all the variables V_ are defined in procedure which gets the values selected by user as a comma separated string here V_SELLOB and LOB_DESC is column in DB.
DECODE (V_SELLOB, 'DEFAULT', V_SELLOB, LOB_DESC) IN
OPEN v_refcursor FOR
SELECT /*+ FULL(a) PARALLEL(a, 5) */
*
FROM items a
WHERE a.sku_status = 'A'
[code]...
Performance Tuning :: Same Data But Different Performance Results
Sep 3, 2010what the principal things to look at when we have for the same query different performance results are?I have 2 different bases: the plan and data are the same but performance results are very differents.
View 10 Replies View RelatedPerformance Tuning :: DB Performance Keys?
Mar 17, 2012are the most important performance keys we have to calculate or take in account to preserve or to increase the DB performance in terms of response times, and whatsoever according to performance ?
View 8 Replies View RelatedSQL & PL/SQL :: Anonymous Block Completed?
Apr 29, 2011I am trying to run the following script but some how i am getting message: anonymous block completed. where i am doing wrong and also how can i display the values with their column names.
set serveroutput on size 1000000
Declare
v_pr_nbr document.document_nbr%type:= ' ' ;
cursor c1 is
[code]...
SQL & PL/SQL :: Execution Completed With Warning
May 2, 2011I'm trying to run the following procedure and I'm getting he following message:
Warning: execution completed with warning
PROCEDURE etl_bago Compiled.
The procedure is created, the problem is that when I tried to run it with parameters I'm getting this error:
exec etl_bago ('july19.csv');
Error report:
ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.ETL_BAGO is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
[code]......
Btw, I'm using SQL Developer 1.5.3. And the procedure script is here:
[URL].....
Partitions For Archiving - Date With Completed Flag?
Jan 29, 2011I am working on an archiving strategy. I want to roll off transactions that are older than seven days, but only if they are flagged as Completed. The numbers of transactions are very large so this is a worthwhile venture.
The only strategy I have been able to come up with so far is to partiton on date. Then when 7 days comes up, sweep the about-to-be archived day for the few remaining not Completed transactions, put those into a new table (a new version of this partiton) and switch partitions. Each day I do this until the older parititions are empty.
How Many GB Completed In X Time-frame By RMAN On Oracle 11.2.0.2
Jun 23, 2011is there a way to find-out how many GB completed in x time-frame by RMAN on oracle 11.2.0.2.
View 5 Replies View RelatedServer Utilities :: IMP-00018 - Partial Import Of Previous Table Completed
Oct 11, 2011I am trying to import the schema into 11g database, which i took on Oracle 9i database. While import is running, data file is full as auto extension was not enabled.
I got the following error:
. . importing table "WO_GL_ACCOUNT_SUMMARY"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table PWRPLANT.WO_GL_ACCOUNT_SUMMARY by 1024 in tabl
espace PWRPLANT
IMP-00018: partial import of previous table completed: 7055845 rows imported.
Then I increased the datafile size and finally Import terminated successfully with warnings. At this point, I want to know whether WO_GL_ACCOUNT_SUMMARY Table was imported with out missing any rows .
Performance Tuning :: Options While Using RBO?
Mar 27, 2013I am working on an assignement where client is using Oracle 10g but stuck to using RBO Now the application team, from the GUI available to them build dynamic queries and some of them run very slow.
Neither the code can not be changed to tune the queries nor do we get the exact step in the plan which is an issue (being RBO).For some long running queries the Tuning advisor is not producing any recommendations.
Another hurdle is that all the application users are using same application user id so we can not write a logon trigger to use CBO for some particular queries to see what is happening in the background!
Performance Tuning :: Next SQL Sentence
Jul 4, 2012I want to tuning the next sql sentence. In this sql I want to get the hash_value and sql_text of the sentences that it's causing TX blocks. Is it possible?. This sentence works fine but sometimes It's slow.
SELECT DISTINCT hash_value,
sql_text
FROM gv$sql sq
WHERE hash_value IN (SELECT DISTINCT prev_hash_value
FROM gv$session se
WHERE sid IN (SELECT sid
FROM gv$lock l
WHERE type = 'TX'
AND ctime >= 2000
AND l.inst_id = se.inst_id
AND l.sid = se.sid)
AND sq.inst_id = se.inst_id);
[code]....
Performance Tuning :: Different SQL ID For Same HASH-VALUE?
Nov 2, 2012I see one of my SQL's which is ran by the user on a 10.2.0.3 database changing its SQL_ID after some runs even if the query is not changed a bit! However the HASH VALUE for this query remains the same.
how a same query can have different SQL_ID's but same HASH_VALUE?
Note: Statistics are not modified on the base tables of this query.
Performance Tuning :: SGA-MAX-SIZE To More Than 2G?
Aug 10, 2011I am running Oracle 10.2.0.1.0 on MS Windows 2003 server 64-bit with 16G RAM.
Here is the findings for my Oracle database.
SQL> select * * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1293560 No
Redo Buffers 7094272 No
Buffer Cache Size 830472192 Yes
[code]...
I find that the SGA component "Buffer Cache" is decreasing from the start "1.8G" and down to now 0.8G. On the other hand, the component "Shared Pool" is increasing from the start 0.3G to now 1.2G. I noticed that there are 100 operations of shrinking of "Buffer cache" and growth of "Shared Pool" in Oracle every day.Is it a indicator that I should raise up the SGA_MAX_SIZE?
I tried to increase the SGA_MAX_SIZE to 4G. But I cannot start the Oracle afterward.Is it a limitation of MS Windows(OS) or Oracle?I set the SGA_MAX_SIZE to 3G. This time, I can startup Oracle.What is the optimum/maximum I can set to SGA_MAX_SIZE?Is there any adverse effect/concern when setting the SGA_MAX_SIZE more than 2G?
Performance Tuning :: How To Know Host Name Using Sql ID Or SID
Jul 11, 2012Here i have three tier application. I want to know it host name from sid or sqlid . I want to know which query run on which host. Because i have one user from application to database. So i want to know which query consume more time on which host ?
View 19 Replies View RelatedPerformance Tuning :: No Redo For DML?
Mar 17, 2006How can I disable redo generation for DML statements.
View 25 Replies View Related