KOTC Posted April 24, 2013 Author Report Posted April 24, 2013 [quote name='Silence..Please' timestamp='1366824734' post='1303656994'] yes, you can [url="http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch23.htm"]http://docs.oracle.c.../SCN73/ch23.htm[/url] [/quote] mari ikkada endi vayya vedu No anta? http://www.dbametrix.com/download/Oracle-DBA-interview-questions-1.pdf
Gachibowli_Diwakar Posted April 24, 2013 Report Posted April 24, 2013 [quote name='STFU' timestamp='1366825218' post='1303657078'] mari ikkada endi vayya vedu No anta? [url="http://www.dbametrix.com/download/Oracle-DBA-interview-questions-1.pdf"]http://www.dbametrix...questions-1.pdf[/url] [/quote] i didnt go through that link .. kani when db is online .. a full backup constitutes control file, archive logs and data-files in it
KOTC Posted April 24, 2013 Author Report Posted April 24, 2013 [quote name='Silence..Please' timestamp='1366825504' post='1303657127'] i didnt go through that link .. kani when db is online .. a full backup constitutes control file, archive logs and data-files in it [/quote] ok aithe mari oracle interview questions and answers by gitesh trivedi lo No ani undi.enduku antav ?he was talking about something else?? Can Full Backup be performed when the database is open? No. What are the steps involved in Database Shutdown? Close the Database, Dismount the Database and Shutdown the Instance. What is Archived Redo Log? Archived Redo Log consists of Redo Log files that have archived before being reused. What is Restricted Mode of Instance Startup? An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege
Gachibowli_Diwakar Posted April 24, 2013 Report Posted April 24, 2013 [quote name='STFU' timestamp='1366826263' post='1303657253'] ok aithe mari oracle interview questions and answers by gitesh trivedi lo No ani undi.enduku antav ?he was talking about something else?? Can Full Backup be performed when the database is open? No. What are the steps involved in Database Shutdown? Close the Database, Dismount the Database and Shutdown the Instance. What is Archived Redo Log? Archived Redo Log consists of Redo Log files that have archived before being reused. What is Restricted Mode of Instance Startup? An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege [/quote] the question must have been .. Can Full consistent Backup be performed when the database is open?
peru_edaite_enti Posted April 24, 2013 Report Posted April 24, 2013 Question: DB is on 11g... heard that auto gather stats job is enabled... dba_scheduler_jobs nundi chuste. I can see that job is enabled.. but dani logs, adi ee ee schema ki run avuntundi.. aa stats job procedure ela pull cheyali? issue enti ante, oka batch job running long, query lo tables lo 2 tables stats were stale, so manual ga table stats tesukoni job run cheste, complete aindi.. so ippudu post mortem chestunte, auto stats gather ane job enable aiyi undi.. so aa job em chestundi.. dani, last runs ela aiyyai.. asalu aa job lo maa schema meda chestunda leda chudali..
KOTC Posted April 24, 2013 Author Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366830263' post='1303657945'] Question: DB is on 11g... heard that auto gather stats job is enabled... dba_scheduler_jobs nundi chuste. I can see that job is enabled.. but dani logs, adi ee ee schema ki run avuntundi.. aa stats job procedure ela pull cheyali? issue enti ante, oka batch job running long, query lo tables lo 2 tables stats were stale, so manual ga table stats tesukoni job run cheste, complete aindi.. so ippudu post mortem chestunte, auto stats gather ane job enable aiyi undi.. so aa job em chestundi.. dani, last runs ela aiyyai.. asalu aa job lo maa schema meda chestunda leda chudali.. [/quote] question koncham ardham avaledhu e query run cheyi,neku ippudu a job run details kavala? [color=#333333][font=Georgia,]select * from dba_scheduler_job_run_details;[/font][/color] select job_name, owner, nvl(to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS'), schedule_name) as next_run_date, to_char(last_start_date, 'DD-MON-YYYY HH24:MI:SS') as last_run_date, job_class, run_count from dba_scheduler_jobs ;
Gachibowli_Diwakar Posted April 24, 2013 Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366830263' post='1303657945'] Question: DB is on 11g... heard that auto gather stats job is enabled... dba_scheduler_jobs nundi chuste. I can see that job is enabled.. but dani logs, adi ee ee schema ki run avuntundi.. aa stats job procedure ela pull cheyali? issue enti ante, oka batch job running long, query lo tables lo 2 tables stats were stale, so manual ga table stats tesukoni job run cheste, complete aindi.. so ippudu post mortem chestunte, auto stats gather ane job enable aiyi undi.. so aa job em chestundi.. dani, last runs ela aiyyai.. asalu aa job lo maa schema meda chestunda leda chudali.. [/quote] check LAST_ANALYZED column from dba_tables for those tables that are in question .. that should tell you also remember, CBO will gather the stats on the table based on [color=#000000][size=3][background=rgb(242, 241, 242)]estimate_percent [/background][/size][/color]that is defined .. for example, [color=#000000][size=3][background=rgb(242, 241, 242)]estimate_percent [/background][/size][/color]15% ichaav anuko .. unless table lo data change (like update/insert/delete) 15% hit aithe kani stats will not be gathered .. so sometimes u will see a table stats may not have been gathered since a month or 2 .. to prove the point that table has not changed morethan 15% look at NUM_ROWS column from dba_tables and do a count(*) (or count on unique column) and see if the difference between NUM_ROWS and this count is more than 15% (generally speaking Optimizer is good enough to capture the stats when the threshold reaches [color=#000000][size=3][background=rgb(242, 241, 242)]estimate_percent [/background][/size][/color]kakapothe once in a while developer edavalu evariki inform cheyakunda massive updates chesestharu during business hours .. where CBO will run during specific time[color=#000000][size=3][background=rgb(242, 241, 242)] ) [/background][/size][/color]
CheGuevara Posted April 24, 2013 Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366830263' post='1303657945'] Question: DB is on 11g... heard that auto gather stats job is enabled... dba_scheduler_jobs nundi chuste. I can see that job is enabled.. but dani logs, adi ee ee schema ki run avuntundi.. aa stats job procedure ela pull cheyali? issue enti ante, oka batch job running long, query lo tables lo 2 tables stats were stale, so manual ga table stats tesukoni job run cheste, complete aindi.. so ippudu post mortem chestunte, auto stats gather ane job enable aiyi undi.. so aa job em chestundi.. dani, last runs ela aiyyai.. asalu aa job lo maa schema meda chestunda leda chudali.. [/quote] select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual; above statement run chey. output AUTO aite it will collect the stats for all objects in the database.
seenugadu Posted April 24, 2013 Report Posted April 24, 2013 Hi Frnds,, As per the suggestion by SFTU, I am posting my request here.. [color=#282828][font=helvetica, arial, sans-serif]naku "Oracle DBA Interview Questions by Gitesh Trivedi" book kavali interviews ki prepare avadaniki...[/font][/color] [color=#282828][font=helvetica, arial, sans-serif]vadi site lo sample book chusa ,,, i feel its good to crack interviews[/font][/color] [color=#282828][font=helvetica, arial, sans-serif]evari deggaraina softcopy vunte pm cheyra plzzzz...[/font][/color] Please consider this request and help to find what I require.... Give your suggestion on this book if anyone had it
peru_edaite_enti Posted April 24, 2013 Report Posted April 24, 2013 [quote name='Silence..Please' timestamp='1366833731' post='1303658440'] check LAST_ANALYZED column from dba_tables for those tables that are in question .. that should tell you also remember, CBO will gather the stats on the table based on [color=#000000][size=3][background=rgb(242, 241, 242)]estimate_percent [/background][/size][/color]that is defined .. for example, [color=#000000][size=3][background=rgb(242, 241, 242)]estimate_percent [/background][/size][/color]15% ichaav anuko .. unless table lo data change (like update/insert/delete) 15% hit aithe kani stats will not be gathered .. so sometimes u will see a table stats may not have been gathered since a month or 2 .. to prove the point that table has not changed morethan 15% look at NUM_ROWS column from dba_tables and do a count(*) (or count on unique column) and see if the difference between NUM_ROWS and this count is more than 15% (generally speaking Optimizer is good enough to capture the stats when the threshold reaches [color=#000000][size=3][background=rgb(242, 241, 242)]estimate_percent [/background][/size][/color]kakapothe once in a while developer edavalu evariki inform cheyakunda massive updates chesestharu during business hours .. where CBO will run during specific time[color=#000000][size=3][background=rgb(242, 241, 242)] ) [/background][/size][/color] [/quote] how come estimate percent is related to num of rows got updated re?? afaik, estimate percent is % of sampling oracle takes into account..kada.. estimate_percent can also be set to auto, where oracle comes up with % rate of sampling, to perform analyze kada....
peru_edaite_enti Posted April 24, 2013 Report Posted April 24, 2013 [color=#282828][font=helvetica, arial, sans-serif]select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual;[/font][/color] [color=#282828][font=helvetica, arial, sans-serif]AUTO [/font][/color] select job_name, owner, nvl(to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS'), schedule_name) as next_run_date, to_char(last_start_date, 'DD-MON-YYYY HH24:MI:SS') as last_run_date, job_class, run_count from dba_scheduler_jobs where job_name='BSLN_MAINTAIN_STATS_JOB'; --------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ---------- BSLN_MAINTAIN_STATS_JOB SYS 28-APR-2013 00:00:00 21-APR-2013 00:00:01 DEFAULT_JOB_CLASS 81 Last RUN: 21 APRIL, NEXT run 28 APR ani undi.. kaani oka table lo: SQL> select TABLE_NAME, NUM_ROWS, LAST_ANALYZED from dba_tables where OWNER='SCHEMA' and table_name like 'PERF%'; TABLE_NAME -------------------------------------------------------------------------------- NUM_ROWS LAST_ANALYZED ---------- --------------- TABLE1 0 08-OCT-11 TABLE2 621674979 19-APR-13 Last analyzed: okati 08 oct, 11, and inko 19th april-13-- idi nen manual ga analyze chesa, not with the job. enduku ala avutundi??
CheGuevara Posted April 24, 2013 Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366836957' post='1303658788'] [color=#282828][font=helvetica, arial, sans-serif]select dbms_stats.GET_PREFS ('AUTOSTATS_TARGET') from dual;[/font][/color] [color=#282828][font=helvetica, arial, sans-serif]AUTO [/font][/color] select job_name, owner, nvl(to_char(next_run_date, 'DD-MON-YYYY HH24:MI:SS'), schedule_name) as next_run_date, to_char(last_start_date, 'DD-MON-YYYY HH24:MI:SS') as last_run_date, job_class, run_count from dba_scheduler_jobs where job_name='BSLN_MAINTAIN_STATS_JOB'; --------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------ ---------- BSLN_MAINTAIN_STATS_JOB SYS 28-APR-2013 00:00:00 21-APR-2013 00:00:01 DEFAULT_JOB_CLASS 81 Last RUN: 21 APRIL, NEXT run 28 APR ani undi.. kaani oka table lo: SQL> select TABLE_NAME, NUM_ROWS, LAST_ANALYZED from dba_tables where OWNER='SCHEMA' and table_name like 'PERF%'; TABLE_NAME -------------------------------------------------------------------------------- NUM_ROWS LAST_ANALYZED ---------- --------------- TABLE1 0 08-OCT-11 TABLE2 621674979 19-APR-13 Last analyzed: okati 08 oct, 11, and inko 19th april-13-- idi nen manual ga analyze chesa, not with the job. enduku ala avutundi?? [/quote] check whether it is getting executed successfully all the time
peru_edaite_enti Posted April 24, 2013 Report Posted April 24, 2013 [quote name='CheGuevara' timestamp='1366838094' post='1303658904'] check whether it is getting executed successfully all the time [/quote] SQL> select FAILURE_COUNT, RUN_COUNT, MAX_RUNS from dba_scheduler_jobs where job_name='BSLN_MAINTAIN_STATS_JOB'; FAILURE_COUNT RUN_COUNT MAX_RUNS ------------- ---------- ---------- 0 81 failure count 0 undi ...
KOTC Posted April 24, 2013 Author Report Posted April 24, 2013 [quote name='CBZ' timestamp='1366838396' post='1303658949'] SQL> select FAILURE_COUNT, RUN_COUNT, MAX_RUNS from dba_scheduler_jobs where job_name='BSLN_MAINTAIN_STATS_JOB'; FAILURE_COUNT RUN_COUNT MAX_RUNS ------------- ---------- ---------- 0 81 failure count 0 undi ... [/quote] adi default job kadha "BSLN_MAINTAIN_STATS_JOB'' denio tho ne pani enti???
KOTC Posted April 24, 2013 Author Report Posted April 24, 2013 [color=#FFFFFF][font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3][background=rgb(20, 20, 20)]select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’[/background][/size][/font][/color] [color=#FFFFFF][font=Arial, Tahoma, Helvetica, FreeSans, sans-serif][size=3][background=rgb(20, 20, 20)]order by log_date desc;[/background][/size][/font][/color] http://osamamustafa.blogspot.com/2012/09/bslnmaintainstatsjob.html
Recommended Posts