This is the second collection of reader 's question and my answer.
Thanks to all reader who showed interest with asking question.
I should apologize for my current delay in response (I was away for a while).I will try to catch up;Wait for more post and answers.
Question (Date : July 26th,2008)
=========
How to reset the statistics in oracle 10g ?
Answer
----------
You can see history of stats operation with running the following :
select * from DBA_OPTSTAT_OPERATIONS;
Also you can get table statistics with running the following :
select * from DBA_TAB_STATS_HISTORY;
Old statistics can be restored with the following code :
exec dbms_stats.restore_table_stats({owner} ,{table name}, {timestamp});
Question (Date : July 20th,2008)
=========
In my database open_cursiors are 500 in parameter file, when I seen in v$open_cursors while running the dml operations at that 2800 it is showing , any suggestions to increase performance.
Answer
----------
v$open_cursors takes into account :
implicit cursors used
distinct explicit cursors opened
dynamic cursors PARSED and NOT CLOSED
As the result it shows accumulated data of cursors which PARSED but NOT CLOSED over time.
Also select value from v$sysstat where statistic# = 3 takes into account :
implicit cursors used
distinct explicit cursors opened
dynamic cursors OPENED.
To fix the situation :
Make sure that explicit cursors are closed by CLOSE.
Make sure to use bind variable to reduce number of requried cursor.
If you can not use bind variable, you may get benefit of proper setting of cursor_sharing parameter.
Question (Date : July 20th,2008)
=========
Can we restore the defintion of the production data base(without data) with RMAN?
Answer
----------
As you know, the defintion of database is called metadata and is kept is SYSTEM (and SYSAUX in >10g) tablespaces.
What you need to do is to Restore/recover a tiny database from production backup with only SYSTEM/UNDO (and SYSAUX if >10g) tablespaces and then uses different views like dba_source, dba_tables, dba_indexes, dba_objects and ... to reterive different object definitions.
Question (Date : July 20th,2008)
=========
I have a dbajob which stopped last week without anyreason. I can see that job in the dbs_jobs table and its next date set to 9th of july whcih is the last date it ran. I cant change the next date or I cant manually run it. its a stored procedure creates emails and send to users..i tried - exec dbms_job.change(14006, NULL, NULL, 'SYSDATE + 1/24');exec dbms_job.run(14006);but nothing happenedany idea?
Answer
----------
It could be job_queue_processes is zero.
Please run the following to find out job_queue_processes
select name,value from v$parameter where name like 'job_queue_processes';
If it is zero, you need to change it with the following command.
alter system set job_queue_processes=10;
To be safe on next database bounce, you also need to make sure pfile/spfile will have this change.
Question (Date : July 20th,2008)
=========
I want to put a condition in a column that the data would be inform of 'VA0001'. How i do this please help me
Answer
----------
I assumed that you want a column to have the constant value of 'VA0001'. Here is options that you may use
1. Add check constraint.
Example :
drop table tbl1;
create table tbl1 (id1 number, id2 varchar2(20));
alter table tbl1 add constraint check_id2 check (id2 in ('VA0001'));
insert into tbl1 values(10,'VA0001');
insert into tbl1 values(20,'AAAA');
--- ORA-02290: check constraint (AVAIL.CHECK_ID2) violated
2. Define default value for a colum (It is doable if you have control over insert/update SQL statement)
drop table tbl2;
create table tbl2 (id1 number, id2 varchar2(20) default 'VA0001');
insert into tbl2(id1) values(10);
select * from tbl2;
ID1 ID2
---------
10 VA0001
Question (Date : July 3rd,2008)
=========
My replication push is defined as sysdate + 1/(60*60*24) but userscomplain that it runs every 2 minutes. DBA_JOBS has LAST 2008-07-02 00:50:53 Next 2008-07-02 00:50:54 Now 2008-07-02 00:52:33 Whya 2 minute gap?
Answer
----------
Interval definition is right, it should run every second.
However, if the code that is executed takes more than a second, current run will overlaps with next run. To make sure that scheduler can run every second, you need to make sure that code can be completed at any situation in less than a second.
Question (Date : May 22th,2008)
=========
How to provide a rough estimate of how much space (in Gigabytes)could be saved by exporting and re-importing a database ?
Also, if possible,please include something that will give (even rough) estimates of the down-time that we would require to perform the task
Answer
======
If the whole purpose of export/import is to regorganize table to save space, I would like to offer the following as a better option.
- Check metalink note 262353.1 for finding out a way to shrink eligible tables. In 10g this could be done by running a simple shrink command.
- Check http://oradbatips.blogspot.com/2007/01/tip-21-shrink-datafiles.html on how to shrink datafiles.
Question (Date : May 22th,2008)
=========
I have Oracle 10g running on an Integrity box with two single core CPU's, Oracle is only using one ofthem, any ideas?
Answer
======
Distribution of processing to the CPUs is handled by OS, not Oracle. The parallel query option is not required for this distribution of load to occur by the OS.If you are running a SINGLE query, OS would not be able to dispatch it to multiple processors without parallel execution but that doesn'tmean both processors wouldn't be active when there may be operating system processing occuring on one of the CPUs.If you want a SINGLE query to take advantage of multiple CPUs, this could be done with parallel execution which is only licensed withOracle Enterprise Edition. Also, parallel execution can improve performance with hardware configurations that do not include multiple processors although this is where the biggest gain can be achieved.
Question (Date : April 18th,2008)
========
Is it any necessary to change the default port on the Listener file in 10g? Is changing it an app. killer and does changing it really increase security?
Answer
=======
No, it is not necessary. However be aware that in 10g, you do not need to put listener in password proctected mode because only Oracle (owner of software) can bounce listener by default.
Also be aware of a bug in 10g listener which may chew up whole CPU. To fix this, you need to put the following entry in listener.ora file.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
Question (Date : April 17th,2008)
========
I'm running a expdp and impdp of a schema which is of 170 GB,there is enough space in target database.export took 1:15min but import is hanging for several hours.Both the schemas have different tablespace name sofirst time my import failed as it was searching for the same tablespace name of the source.Then i generated SQLfile for the creation of tables and indexes and then changed the tablespace name and then created the tables manually.Then i ran the impdp then it is not importing the data in the table and in the log file when it is abuot to import the statistics it gets hanged pls tell me a solution for this problem.Beacuse of this problem I'm not able to procedd with this job.Thnak you in Advance
Answer
=======
Two different things have been asked in this question.
To use different tablespace in target database than source database, you can use REMAP_TABLESPACE during import.
To address performance issue, I would say that import is always slower than export.However, when you run import, you may consider the following :
- Import Structure first with no index and then import data.(CONTENT parameter).
- During import check v$session_waits to find out on which events sessions are waiting and what the bottleneck is. Sometimes PGA, sort_area_size and ... may need to be tunned.
- If tables are big, it is better off to import with DIRECT PATH. User ACCESS_METHOD parameter during import.
Question
========
I have a question regarding oracle cbo. environment we have is oracle 10g in windows and siebel on the application side. statistics gathering was scheduled on regular basis. since we faced some performance issues stats gathering was turned off entirely after that. after switching of the stats gathering process data has grown considerably. now the developers want to start the dbms_stats gathering package again. my contention is if we analyze and gather statistics now we will end up having skewed data . and the performance might get worse than before. metalink doc id (Note:44961.1 what is your thought on this? would really appreciate your time & advice
ANSWER
========
I would do the followings :
- Export stats for existing schema.
- Save statspack report before stats gathering.
- Gather statistics.
- If it is 9i, keep monitoring on for table.
If there is performance issue for most queries, drop new stats and import stats from step1 and tune SQL.
Question
========
How do you copy Oracle_Home from windows and load it to a different server and bring up the database?Is there any relink in Windows or do I need to update window registry.Basically what is the steps in cloning a database from windows 2003 to another server that is also windows 2003?
ANSWER
========
The following two options are feasible options.
- Use Grid control for clone.
- Use OUI for clone.
Each option has its own pros/cons however, I think Grid control clone feature is the easiest and the best.
Question
========
After applying patch p6832736_101210_LINUX.zip on OAS Form and Report service version 10.1.2.0.2, my applycation use form webutil can not working.After click loading form webutil, only white screen appear and application was hang. I must close all page and login again.
Webutil 1.06
JInitiator 1.3.1.22
OAS Forms and Report services version 10.1.2.0.2
DBS 10g v.10.2.0.3
ANSWER
========
This is known issue which has been report in metalink Note:377609.1. Fortunatelly, workaround is simple.
Question
========
I take daily rman backup of 360gb database. Usually, it takes 4-5 hours, but, nowadays, every other day, it takes more than 24 hours.Please, help me regarding this issue?
ANSWER
========
Please follow below steps :
1. Make sure that there is not major change with checking backup size.You could do it with checking rc_backupset.
2. Check archivelog generation for days which RMAN takes long and compare it with other days. It could be RMAN have some more data to be backedup.
You could use the following script :
select to_char(sysdate,'DAY') DAY,sum(blocks*block_size)/1024/1024/1024 GB , count(*) Archivelog_number from v$archived_log where FIRST_TIME>=trunc(sysdate,'DD')
3. To Check RMAN performance, you could use V$BACKUP_ASYNC_IO or V$BACKUP_SYNC_IO.These views show performance information about ongoing and recently completed RMAN backup.The biggest benefit from these views is the EFFECTIVE_BYTES_PER_SECOND column in rows where TYPE column is set to AGGREGATE.This column represents the rate at which the objects are being backed up or recovered, in bytes per second.
(Refer to metalink 237083.1)
Question
========
I forgot my jobid. i want to stop job ...how can i do that
Answer
======
If job is running, the best way to kill it is to remove it from dba_jobs. Here are steps :
1. Find job number from dba_jobs. (select job,what from dba_jobs;)
2. Remove job, use job value from step1. (exec dbms_job.remove(&job);
3. Finalize remove with commit command. (commit;)
Question
========
Developers keep on telling that my queries are getting slow, as I am fresher for oacle dba what are the things i need to concentrate.
Answer
======
You need to find out the bottleneck. To take it simple, here are first steps that I recommend.
1. Take a SQL statement from developer.
2. Create two connection to database.
3. On first session, run the following :
sqlplus userid??/password??@connect_string
select sid from v$mystat group by sid; --> Keep the result.
--- Run query
4. On second session, run the following :
sqlplus '/ as sysdba'
select sid,'Event='event '=>'parameter1'='p1','parameter2'='p2','parameter3'='p3 Event, wait_time/100 wait_in_second from v$session_wait ses,(select parameter1,parameter2,parameter3,name from v$event_name)evntwhere ses.event=evnt.name and sid=&first_session_sid order by wait_time desc; --- Replace first_session sid with value of step 3.
-- Result shows what SQL statement is waiting for.
-- Repeat running above SQL and save the result until session 1 is finished.
-----
Also you can check execution plan with following below steps.
sqlplus userid???/password ???
set pages 1000
set autotrace traceonly
--- Run sql statement.
Having above information will enable you to find bottleneck and suggest remedy accordingly. Please send me the result if you need my help.
Question
========
I want to know when ever we create oracle object(index, table) is it necessary to generate stats for server to choose best execution plan since as per my knowledge if object structure changes(say 15%)we need to generate stats is it true or not
Answer
======
It depends which version of Oracle you are running.
For 10g onwards, Oracle gather stats at time of object creation.
For 9i, The best way is to enable monitoring on objects. (alter table table_name monitoring;) and let Oracle tracks object modification. For stats gathering, if you run dbms_stats with GATHER_AUTO, oracle knows which objects need statistics.
Question
=======
How to open an sys account ,if i forgot my password?
Answer
======
As installation owner (Possibly Oracle) login to box and run the following. (Insert desired password in password??)
sqlplus /nolog
connect '/ as sysdba' (On Unix) or connect "/ as sysdba" (On windows)
alter user sys identified by password??;
Also you can reset password with any user who has sysdba privilege.
Question
========
I face an issue in Oracle 10g (10.2.0.3.0 on Linux 64 bits). While executing a query using the CONNECT BY clause, I find that it executes faster if directly executed on TOAD/sqlplus. However, when I put the same query in a function, it slows down considerably.
Typically, the direct execution takes less than a second while the function takes more than 30 sec. The query is like below:
SELECT DISTINCT ner.neh_parent_id
FROM nw_element_relations ner
WHERE parent_ne_type = 'BTS'
AND ( job_dep_pkg.fn_job_last_ner_image(227987, ner.job_id, ner.ner_id, ner.active) = 'Y' OR ner.job_id = 227987) AND ner.action != 'R' CONNECT BY neh_child_id = PRIOR neh_parent_id AND ( job_dep_pkg.fn_job_last_ner_image (227987, ner.job_id, ner.ner_id, ner.active) = 'Y' OR ner.job_id = 227987) AND ner.action != 'R' AND child_ne_type != 'BTS';
I have even set the _old_connect_by_enabled = TRUE to avoid the issue of poor performance in 10g for the CONNECT BY clause
Answer
=======
Before going into the matter of whether or not SQL statement needs tunning, the followings are recommended.
1. Minimize switching between SQL engine and PL/SQL engine : Try to execute most statement as SQL statement if you can.Switching between SQL engine and PL/SQL engine is costly.You should see performance side affect if you switch between these two engines. It is recommended just to run SQL in PL/SQL if you run out of option.
2. Make sure PL/SQL optimizer level set properly. Check value of plsql_optimize_level value.
3. Check http://oradbatips.blogspot.com/2007/09/tip57-how-to-use-plsql-profiler.html to find out how troubleshoot PL/SQL code.
4. I see that in start with a package is called, There is a published bug in Oracle 10.2.0.3 that CONNECT BY query containing a subquery in the START WITH clausemay be slow. (Bug 5211863). It is supposed to be fixed in 10.2.0.4.
Please send content of PL/SQL function and job_dep_pkg.fn_job_last_ner_image if you need more help.
No comments:
Post a Comment