Default Parameter For Statistic Generation / DBMS_STATS.SET_DATABASE_PREFS()
Dec 10, 2012
I am running a database version 11.2.0.3 on a RHEL 5.3 system.A nightly ETL job drops all tables of a certain schema and recreates them as part of this job.After recreation of the tables statistics are generated to make query execution faster.
The ETL job uses following statement to gather the statistics for each table: EXEC dbms_stats.gather_table_stats('SCHEMA', 'TABLE', cascade => TRUE); As we had performance issues on the database I analysed the statistics and found that there were no histograms collected for the tables although the data are skewed.
Therefore I searched a database wide parameter where I can change the default statistic collection method from 'SIZE AUTO' to 'SIZE SKEWONLY'. I found following method: DBMS_STATS.SET_DATABASE_PREFS () I used this package to set the database prefs to 'size skewonly' and recreated the statistics. All was fine. exec DBMS_STATS.SET_DATABASE_PREFS (pname=>'METHOD_OPT', pvalue=>'FOR ALL COLUMNS SIZE SKEWONLY');But one day later after the ETL job we had the same issue again. After a further analyse I saw that there were no histograms generated on the tables. It seems that the drop and recreation of the tables make the system wiede default parameter useless.
how to set system wide optimizer defaults even after dropping and recreating a table?
View 4 Replies
ADVERTISEMENT
Oct 26, 2012
I've hunted everywhere, and I'm beginning to think this is impossible, however I'll give it a shot...
I would like my Discoverer report start date/end date to default to YESTERDAY.
View 2 Replies
View Related
Mar 20, 2013
If we provide an object filter list in dbms_stats.gather_database_stats specifying the partition name to be analysed , then the partition doesn't gets analysed.The version details are as below
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 -
ProductionAs already pointed out ,the version 11.2.0.3 is already having a bug for dbms_stats.gather_schema_stats where the object filter list doesn't work.Is this behaviour also a bug ?
View 8 Replies
View Related
Nov 16, 2010
I was looking for a way to see if a default value for a procedure was passed NULL or it got NULL by default. [URL]
View 11 Replies
View Related
Nov 17, 2010
what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.
View 1 Replies
View Related
Aug 21, 2012
I have a question.
When and Where HASH VALUE for a given SQL statement get generated?
View 5 Replies
View Related
Jul 18, 2013
Here i've a case of, For single test there are multiple chemist's assigned to it. I want a result for each chemist assigned how many test.
I want the output like below.
CREATE TABLE SAM.TEMP
(
ID NUMBER(4),
CHEMIST_ID VARCHAR2(30)
)
/
[Code]....
View 9 Replies
View Related
Feb 27, 2013
Need to generate auto number as below
A00001 to A99999. After A99999 it should begin with B00001 and ends with B99999. till Z99999 we need to generate.
View 1 Replies
View Related
Jul 2, 2012
i need to write to word doc from pl/sql . these are letters which needs to printed . Can i use the same UTL_FILE and will i be able to control the font etc from pl/sql
View 6 Replies
View Related
Jul 26, 2010
I am using following Procedure/function for inserting DATA into New table:
PROCEDURE NEMPID IS
OFFICER CHAR(1) := '0';
OFFICIAL CHAR(1) := '9';
CURSOR DOJSUP IS
[Code]....
Get/Retrieved data from personal and cat9 files and Inserted Data stored in file NEMP as
EMP_ID NEMP_ID
------- ---------
111 01-8722
132 01-9224
88 01-8318
114 01-8717
106 01-8517
[Code]....
I want also generate a sequence/serial number for same category/designation of personals/employees exist in personal file ?
Like 001, 002 ,003 at the end of NEMPID field.
EMP_ID NEMP_ID
------- ---------
111 01-8722-001
132 01-9224-002
88 01-8318-003
114 01-8717-004
[Code]....
HOW CAN GENERATE SEQUENCE/SERIAL NUMBER OF SAME CATEGORY EMPLOYEES AND INSERT INTO TABLE NEMP?
View 16 Replies
View Related
May 15, 2013
Here is my situation.
1. I generate file_name through a function.
2. File name like File_DD_MM_YY_FLXXX.
3. Now this FLXXX is the filenumber. It will be 1 for the 1st run of the day and for each of the new run it will be +1. Again next day it will start from 1.
So right now I am using a sequence and resetting it at 12 AM to 0. Is it a good approach to solve the scenario?
View 3 Replies
View Related
Sep 25, 2013
11gr2, HP-UX, 64-bit. From last one week, we are seeing tremendous increase in archive-log growth. Its comes almost around 110GB per a day (db size=600g). I don't think its usual.Alertlog is clean and we don't see any alerts. Remedy app is built on this db and its creating tables on fly with LOB columns and indexes on them. As a first step. shall I disable logging for indexes on LOB columns ?
View 4 Replies
View Related
Oct 12, 2010
I am using :
Database : Oracle 8i
Application Server: Oracle AS 9i
Developer Suite : Oracle 6i(forms & reports)
I have created some character reports in oracle reports 6i.. when reports used run from my ERP(oracle 6i oriented) ... report usually took time to create on server. Sometimes my ERP used to hang up due to busy reports generation. And then we have to kill some processes to finally create charater reports on emergency basis.What is the valid reason for slow generation of report(character file )?
View 1 Replies
View Related
Mar 16, 2012
on weekends we have too many archive logs generated .i have taken the data of a week and found that average archive log generated from monday to friday is 7 files per day but on satuarday and sunday the average is 60 files and FG1 gets full. on weekends we have all type of backups running like incremental,archival and logical backups and on sunday we have full physical backup
what is the reason of too many archive log files generations at weekends. is it due to hot and logical backups , if yes then how ?
View 9 Replies
View Related
Aug 24, 2011
i an using this code to generate invoice numbers auntomatically:
declare
po number;
pi number;
begin
[code]...
Problem is it does not increment after 10.
View 12 Replies
View Related
Dec 14, 2011
We need to generate barcode through oracle forms and print the barcode using Zebra Printer.
How to generate the barcode using oracle forms and which software we should use to generate the barcode as well as how can we print the barcode label with customize information on label with barcode.
View -1 Replies
View Related
Apr 12, 2010
Im generating a csv file using TEXT_IO Utility.The file generated contains one blank line at the last record.Say there is 10 records. Open the generated csv file in notepad and move the cursor down. After 10 records, the cursor will move down one more time.
How to avoid that blank line as im using cursor in my script. The script is attached below.
Version:
Oracle 9i Database
Oracle Forms 6i
View 2 Replies
View Related
Jul 26, 2010
I have oracle 9i running on HP-UX, I would like to find how much redo we are generating in a given period of time, is there any script that I can use to get this information?
View 3 Replies
View Related
Jul 4, 2013
I'm in 11203, and generation of ASH report is very slow.AWR and ADDM reports are generated quickly.To understand what happen, I check the wait event on the session that is executing ASH report, and I found that this session is waiting 99% with "controlfile sequential read". Is there any way to make the generation of ASH report quick ?Why the generation need to access to the controlfile?.
View 0 Replies
View Related
Sep 11, 2012
I have a base table by name EMP_MASTER.The Create Statement goes something like this....
CREATE TABLE EMP_MASTER(ST_CODE NUMBER,EMP_CODE NUMBER);We would like to insert records in such a way that there are 10 st_codes and for each st_code we need to insert 100 records.
FOR EX : for st_code 1 we need to have emp_code from 1 to 100 and then we need to insert st_code 2 and the emp_code must be from 101 to 200 and so on...... It must go in this way till we have st_code 10 and hope the emp_code will be in the range of 901 to 1000.
We need some thing similar to proc or PL/SQL block(declare begin..... end)
View 5 Replies
View Related
Dec 6, 2011
I have written a Java class which generate and place the XML file in a particular location. This the java class is loaded into Oracle using the loadjava command. When I call the sql java procedure java classn will be called and the XML file is generating.
But my problem is with xml header i.e in the header it is getting as
<?xml version = '1.0' encoding = 'UTF-8'?>
Where as I am generating the xml file from by just java call instead from Oracle the header is as follows
<?xml version="1.0" encoding="UTF-8" ?>
I am thinking that this could be reason with different jar using Oracle side.
View 3 Replies
View Related
Jan 18, 2012
I have a question on export dump file generation.
select sum(bytes)/(1024*1024*1024) "GB" from dba_segments where owner='JACK';
The above select query give the output of Schema size with 15 GB. When i perform the same schema export, the dump file size generating is 2 GB. What is the difference between the two scenarios as how come there could be a variation in file size?
View 6 Replies
View Related
Jun 22, 2009
I am trying to move my archive from linux to window as the log switch occur.... for this i mount my directory of window in linux .. but it doesn't moving... dest 1 on linux and dest 2 for windows...
In alert log the permission denied appears... the mounted directory in linux of windows is owned by root ... and is not changing to oracle.
View 1 Replies
View Related
Jul 1, 2010
How to Find Out Which SQL Statements Causing excessive Redo generation?
View 3 Replies
View Related
Jun 6, 2008
Give me the pls / sql trigger code to generate an xml file for a table.Say for example employees table.
View 13 Replies
View Related
Aug 20, 2010
redo generation. As I found the below statement in another forum."Undo segment generates the redo data also, because undo segment is database changes, so it generates the redo data also."
How a Undo segment can generate Redo and Undo datas.
View 12 Replies
View Related
Jan 7, 2013
Redo is getting generated very high. how to find out the reason ? database kept under 2 node cluster. chcked alert log trace and log writer trace files. pasted the content as below:
--alert log trace from node1 ( node2 also has same type of message ). Archive destination disk group - TXCOM_BACKUP_01 having enough space ( 80gb )
Mon Jan 7 00:49:10 2013
Thread 1 advanced to log sequence 448546 (LGWR switch)
Current log# 1 seq# 448546 mem# 0: +TXCOM_DATA_01/txcom/onlinelog/group_1.274.785770579
Current log# 1 seq# 448546 mem# 1: +TXCOM_DATA_01/txcom/onlinelog/group_1.302.802265189
Mon Jan 7 00:49:10 2013
[code]...
In the alert log, I am able to see the archive destination disk group ( TXCOM_BACKUP_01 ) is getting DISMOUNTED and again getting MOUNTED during every archive file generation. .
Mon Jan 7 00:49:20 2013
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was mounted
SUCCESS: diskgroup TXCOM_BACKUP_01 was dismounted
archive destination parameter in both nodes are not configured. it should read diskgroup name. ( +TXCOM_BACKUP_01 ) and corresponding size limit. Should i configure this ?
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
[code]...
should i bring the database to mount stage and set log_archive_max_proesses to high count ? now value is 2 ( default )
View 2 Replies
View Related
Oct 11, 2010
Problem:
I am using :
Database : Oracle 8i
Application Server: Oracle AS 9i
Developer Suite : Oracle 6i(forms & reports)
I have created some character reports in oracle reports 6i.. when reports used run from my ERP(oracle 6i oriented) ... report usually took time to create on server. Sometimes my ERP used to hang up due to reports lock. And then we have to kill some processes to finally create charater reports in emergency basis.
What is the valid reason for locks on report(character file )creation.?
View 4 Replies
View Related
Nov 5, 2012
Is it possible to generate AWR report for the duration of 5 min? As we know that snapshots are generated for every 1 hour, which we specified in parameter.
By changing the parameter to 2 min, what could be the impact on database?
View 7 Replies
View Related
Oct 24, 2011
When I want to generate AWR report for database, I dont see any Snap IDs. What was disabled?
Is it possible to generate awr reports for unseen snap IDs?
Here below is the log:
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
49472052 WPSDBSTG 1 wpsdbstg
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 49472052 1 WPSDBSTG wpsdbstg rcolnx88700
Using 49472052 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
View 15 Replies
View Related
