Sponsored Content
Top Forums Shell Programming and Scripting piping oracle output to a file? Post 302176758 by kwliew999 on Wednesday 19th of March 2008 05:26:26 AM
Old 03-19-2008
Oracle Analytical Processes

Sorry for posting some Oracle related question in this forum as I don't know if any other place that I can post the question. I am sure there are many of you familiar with Oracle database and can answer my question.
My DBA recently written a analytic script to perform a table space analyse as well as reform of indexes in order to have a better performance on some application processes. The scripts that written by him is as follow :
======================
Analyze table GNMM_INTEREST_RATE_MASTER estimate statistics for table for all indexes for all indexed columns;
Analyze table PS_INTEREST_TRANSACTION_TEMP estimate statistics for table for all indexes for all indexed columns;
Analyze table PSDT_APL_TRANSACTION_BREAKUP estimate statistics for table for all indexes for all indexed columns;
Analyze table PSMT_CLEARANCE_MASTER estimate statistics for table for all indexes for all indexed columns;
Analyze table PSDT_CLEARANCE_DETAILS estimate statistics for table for all indexes for all indexed columns;
===========================
I can run the script on AIX platform with Oracle priviledge. And the application process run after the script will have a very much improve performance.

I have also found another scripts written by some other DBA from other company who claimed that is the same as the one I stated above. The scripts are as below :
=======================
create or replace
procedure BFN_ANALYZE_INT_ACCTABLES is

Begin
dbms_stats.gather_table_stats( ownname => 'PROD',
tabname => 'gnmm_interest_rate_master',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
dbms_stats.gather_table_stats( ownname => 'PROD',
tabname => 'PS_INTEREST_TRANSACTION_TEMP',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
dbms_stats.gather_table_stats( ownname => 'PROD',
tabname => 'PSDT_APL_TRANSACTION_BREAKUP',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
dbms_stats.gather_table_stats( ownname => 'PROD',
tabname => 'PSMT_CLEARANCE_MASTER',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
dbms_stats.gather_table_stats(ownname => 'PROD',
tabname => 'PSDT_CLEARANCE_DETAILS',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all indexed columns size auto',
cascade => true,
degree => 7
) ;
END BFN_ANALYZE_INT_ACCTABLES ;
====================================

I found that the above script doesn't have the same effect as the previous one which will improve performance of application processes.

Please advise whether the 2 scripts are similar or any differences that limited the performance?

Thanks in advance.
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Piping output to while read

Hi. Im using cat to output the contents of a file, then piping it to my while read loop.In this loop variables get assigned values. However when i try to use the variables outside the loop their values has been reset.I understand about subshells etc. but I have no idea how to "preserve" the... (3 Replies)
Discussion started by: Ultimodiablo
3 Replies

2. UNIX for Dummies Questions & Answers

piping the output of find command to grep

Hi, I did not understand why the following did not work out as I expected: find . -name "pqp.txt" | grep -v "Permission" I thought I would be able to catch whichever paths containing my pqp.txt file without receiving the display of messages such as "find: cannot access... Permisson... (1 Reply)
Discussion started by: 435 Gavea
1 Replies

3. Shell Programming and Scripting

piping output to echo

Hi, I was wondering why ls * | echo does not print the contents of the directory to the screen? The way I see it, ls * returns a whole lot of information, and then we pipe all this info to echo, so surely it should all come to our screen! Is there a serious flaw in my understanding? ... (3 Replies)
Discussion started by: A1977
3 Replies

4. Shell Programming and Scripting

piping output of tail running in background

Not sure why this does not work in bash: tail -f err.log |& -bash: syntax error near unexpected token `&' I am attempting to continuously read a file that is being updated by doing a "tail -f" on the file and piping the output to stdin which can then be read by the next shell command Thnx (4 Replies)
Discussion started by: anuramdas
4 Replies

5. Shell Programming and Scripting

piping output from PHP file into variable

Hi. I have a script like so: #!/bin/bash download='php /var/www/last.php' echo $download if $downloadHow do I pipe the output of the php file into a variable, as when i run the if statement, it just echos the file output to the screen and does not actually consider the output (it will be... (2 Replies)
Discussion started by: daydreamer
2 Replies

6. Shell Programming and Scripting

Piping and assigning output to a variable in Perl

Hi All, I am trying to convert the below Csh code into Perl. But i have the following error. Can any expert help ? Error: ls: *tac: No such file or directory Csh set $ST_file = `ls -rt *$testid*st*|tail -1`; Perl my $ST_file = `ls -rt *$testid*st*|tail -1`; (10 Replies)
Discussion started by: Raynon
10 Replies

7. Fedora

Piping output of "top" to a text file

I would like to pipe "top -n" to a text file, but I get an error: top: cannot open /dev/kmem kvm_open: Permission denied I am a non-root user. If I could find a way to get this type of output: "Memory: 2048M real, 1516M free, 4099M swap free" into a text file, I could further automate... (13 Replies)
Discussion started by: safraser
13 Replies

8. Shell Programming and Scripting

need help piping the output from an app... uh, yeah...

Ok, so there is a perl script that runs as a server, on my local host. It tells me which port to use. I want to pipe that output into my browser so I can do the whole thing with a single command. The problem is, I think, that the program doesn't actually exit cause it's running a server, so...... (6 Replies)
Discussion started by: ninjaaron
6 Replies

9. Shell Programming and Scripting

Piping output of ls to a text file

Basically I was wondering if any of you know how to pipe the output of ls to a text file? so in my shell script one of the lines is ls but i want to pipe it into a file called directory listing. Cheers. I have tried ls | Directorylisting.txt but it keeps saying " line 7: DirectoryListing.txt:... (9 Replies)
Discussion started by: LinuxNubBrah
9 Replies

10. Shell Programming and Scripting

Abnormality while piping tr command output to sed

i have a file seperated each line seperated by newline. For example alpha beta gamma i am trying to replace the newlines to "," but dont want , present at the end of the line so i am trying the below one liner . but not sure whats wrong but its not working cat myfile | tr -s '\n' ',' | sed... (9 Replies)
Discussion started by: chidori
9 Replies
DB2_STATISTICS(3)							 1							 DB2_STATISTICS(3)

db2_statistics - Returns a result set listing the index and statistics for a table

SYNOPSIS
resource db2_statistics (resource $connection, string $qualifier, string $schema, string $table-name, bool $unique) DESCRIPTION
Returns a result set listing the index and statistics for a table. PARAMETERS
o $connection - A valid connection to an IBM DB2, Cloudscape, or Apache Derby database. o $qualifier - A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string. o $schema - The schema that contains the targeted table. If this parameter is NULL, the statistics and indexes are returned for the schema of the current user. o $table_name - The name of the table. o $unique - An integer value representing the type of index information to return. o $0 - Return only the information for unique indexes on the table. o $1 - Return the information for all indexes on the table. RETURN VALUES
Returns a statement resource with a result set containing rows describing the statistics and indexes for the base tables matching the specified parameters. The rows are composed of the following columns: +--------------+---------------------------------------------------+ | Column name | | | | | | | Description | | | | +--------------+---------------------------------------------------+ | TABLE_CAT | | | | | | | The catalog that contains the table. The value is | | | NULL if this table does not have catalogs. | | | | | TABLE_SCHEM | | | | | | | Name of the schema that contains the table. | | | | | TABLE_NAME | | | | | | | Name of the table. | | | | | NON_UNIQUE | | | | | | | An integer value representing whether the index | | | prohibits unique values, or whether the row rep- | | | resents statistics on the table itself: | | | | | | box, tab (|); c | c | . T{ Return value | | | | | | Parameter type | | | | +--------------+---------------------------------------------------+ |0 (SQL_FALSE) | | | | | | | The index allows duplicate values. | | | | |1 (SQL_TRUE) | | | | | | | The index values must be unique. | | | | | | | | NULL | | | | | | | This row is statistics information for the table | | | itself. | | | | +--------------+---------------------------------------------------+ T} T{ INDEX_QUALIFIER T} |T{ A string value representing the qualifier that would have to be prepended to INDEX_NAME to fully qualify the index. T} T{ INDEX_NAME T} |T{ A string representing the name of the index. T} T{ TYPE T} |T{ An integer value representing the type of information contained in this row of the result set: +------------------------+---------------------------------------------------+ | Return value | | | | | | | Parameter type | | | | +------------------------+---------------------------------------------------+ | 0 (SQL_TABLE_STAT) | | | | | | | The row contains statistics about the table | | | itself. | | | | |1 (SQL_INDEX_CLUSTERED) | | | | | | | The row contains information about a clustered | | | index. | | | | | 2 (SQL_INDEX_HASH) | | | | | | | The row contains information about a hashed | | | index. | | | | | 3 (SQL_INDEX_OTHER) | | | | | | | The row contains information about a type of | | | index that is neither clustered nor hashed. | | | | +------------------------+---------------------------------------------------+ T} T{ ORDINAL_POSITION T} |T{ The 1-indexed position of the column in the index. NULL if the row contains statistics information about the table itself. T} T{ COLUMN_NAME T} |T{ The name of the column in the index. NULL if the row contains statistics information about the table itself. T} T{ ASC_OR_DESC T} |T{ A if the column is sorted in ascending order, D if the column is sorted in descending order, NULL if the row contains statistics informa- tion about the table itself. T} T{ CARDINALITY T} |T{ If the row contains information about an index, this column contains an integer value representing the number of unique values in the index. If the row contains information about the table itself, this column contains an integer value representing the number of rows in the table. T} T{ PAGES T} |T{ If the row contains information about an index, this column contains an integer value representing the number of pages used to store the index. If the row contains information about the table itself, this column contains an integer value representing the number of pages used to store the table. T} T{ FILTER_CONDITION T} |T{ Always returns NULL. T} SEE ALSO
db2_column_privileges(3), db2_columns(3), db2_foreign_keys(3), db2_primary_keys(3), db2_procedure_columns(3), db2_procedures(3), db2_spe- cial_columns(3), db2_table_privileges(3), db2_tables(3). PHP Documentation Group DB2_STATISTICS(3)
All times are GMT -4. The time now is 05:35 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy